Dayton, Ohio Questions and Answers SQL 2005 (10-4-2005)

Good Morning everyone,
Once gain thank you for all that attended the event in Dayton!  It was a fantastic event and I am glad I got to come back.  I also had a chance to attend the DANPA user group on Tuesday night.  That was fantastic as well, and the subway sandwiches where great!  If you live in the Dayton area and are looking for a great user group I highly recommend you check out becoming a member of DANPA .  So here are the questions that you asked from the session, as always please feel free to comment and let me know if you have any additional questions:

Q: Will SQL 2005 be supported on MBS solutions (such as Great Plains), and where can I get more information on upgrading and features?
A:
This one that I have not found a lot of information on yet, I am however actively researching this topic.  What I have been able to find out that the answer is generally going to be yes, SQL 2005 is supported.  I have heard that CRM 3.0 will be supported. I do however recommend that you take this with a grain of salt.  I wish I had a better answer, and as soon as I find out more information look for additional post on the topic, I will put it in the Live Questions and Answers rss feed.

Q: How to Migrate DTS Packages to SQL 2005?
A:
  The question should really be re-stated, the migration is to SQL Server 2005 Integration services (SSIS).  One very important thing to remember as I did some poking around is that, You are not required to migrate SQL Server 2000 Data Transformation Services (DTS) packages to the SQL Server 2005 Integration Services (SSIS) package format.  These two formats can coexist side by side.  There are two great articles to assist you if you choose to upgrade or migrate:  Upgrade from Data Transformation Services to Integration Services and Migrate Data Transformation Services Packages to Integration Services  In addition two those good articles, I also did find a great KB article that does describe a possible issue during the migration of DTS Packages  here: https://support.microsoft.com/default.aspx?scid=kb;en-us;904796.  This brings a great question to mind: Why would I migrate to SSIS?  In previous versions DTS had some limitations in terms of scalability and the ease of deploying packages. SSIS is a completely new system that was designed from the ground up. DTS and SSIS still work very similarly with the different tools however, SSIS has taken a new tack and has split the control flow and data flow of packages into separate components, enabling the creation of much more complex and robust packages.  So I would highly recommend when you go to SQL 2005 t take a look at leveraging SSIS and gradually moving your packages over to SQL 2005.  This is a great article on SSIS.

Q: How to run OSQL (command prompt) to SQL 2000 from a SQL 2005 server?
A:
You can use SQLCMD to do this work, SQLCMD supports OSQL and ISQL commands but also provides better operation in application scripts, such as Microsoft® Visual Basic® for Applications (VBA) scripts.  You can also Edit SQLCMD Scripts with Query Editor.  The books online also had a great article on the different switches on SQLCMD

Q: Can you separate the individual jobs that are created in the maintenance plan?
A:
I did some searching and my good buddy Kevin Remde had a similar question on his blog as well.  Fundamentally you should consider plans as full sets of work, however based on a comment on Kevin’s blog post in this revealed: Maintenance Plans in SQL Server 2005 are SSIS packages. With an SSIS package you can use "checkpoints" to restart your package and it will pick up where the package failed. This is a better way to finish the Maintenance Plan than creating a package just for the last few tasks. I've done plenty of DTS development and its a pain to copy and paste a few tasks from a package that failed into a new package just so you can finish the process.  Information on Checkpoints can be found in the following TechNet doc at https://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx

Q: Can you cluster SQL Express as a part of a database mirror witness?
A:
  Unfortunately, you cannot use database mirroring within a cluster.  This is a great article on database mirroring that will highlight all the details about how mirrors work.

Q: Can you tie SQL 2005 to a different windows policy other than the default domain policy?
A:
In fact you can!  The great thing about SQL 2005 is that it leverages the local security policy of the system, so if you do not want to use the default domain policy (BTW I recommend you use the domain if you can).  There is a great posting here on Kent Tegels blog about the functionality for SQL passwords.  One way to change this is to modify the local policy of the server by going into the Administrative Tools | Local Security Policy, I do not recommend doing this if your system is in a domain.  The potential for the domain policy to overwrite this is possible.  The other way is place the SQL server’s in another OU, and when you modify the password policy on the local OU it will modify the local password policy but not impact the domain policy for domain users.  If you want to learn more about group polices, check out the 14 part web cast series we did on group policy: Group Policy: Explore Fundamentals and Advanced Concepts