How to Upgrade to Microsoft SQL Server 2005: Webcast 1-23-2006

Thank you for attending the web cast as promised here is the scrubbed Q/A log (you kept Chris Henley busy!).  Please comment if you need more information.  A couple question cam in where can I down load the slides and individual demos for this webcast take a look at the session home page: https://www.microsoft.com/technet/community/events/sql2005/sql-10.mspx 

Also if you can watch the stream of the web cast here: https://www.microsoft.com/events/EventDetails.aspx?CMTYSvcSource=MSCOMMedia&Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22ID%22+Value%3d%221032287291%22%2f%5e%7earg+Name%3d%22ProviderID%22+Value%3d%22A6B43178-497C-4225-BA42-DF595171F04C%22%2f%5e%7earg+Name%3d%22lang%22+Value%3d%22en%22%2f%5e%7earg+Name%3d%22cr%22+Value%3d%22US%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%5e

 A couple of links before we get into the Q/A:

Great web site for upgrade information in general: https://www.microsoft.com/technet/prodtechnol/sql/2005/library/deployment.mspx
I also mentioned the great ten part series that Bryan Von Axelson did: https://www.microsoft.com/events/series/technetsqlserver2005.mspx
Lastly, there is a TON of great information in the Books online: https://msdn2.microsoft.com/en-us/library/ms130214(en-US,SQL.90).aspx 

Enjoy!

Question: My Company is very wary of upgrading to SS2005; is it possible to use the SQL Server 2005 Management Studio client version with an Ss2000 instance in everyday use? Especially compiling and deploying store procedures and other code excluding DTS? We have several 2000 instances, are there any problems for doing this?
Answer: Yes it is possible to manage SQL 200 instances with a SQL 2005 management studio.  For the most part the instances will be managed like they were in the past.  The only functions not available to the 2000 are the new features in 2005. I.e. Peer to peer replication will not show up on the 2000 instances.  There is a lot of information about the new management studio here: https://msdn2.microsoft.com/en-us/library/ms174173.aspx

Question: What about BCP utility?
Answer: BCP is still available and can be used.  For more information check this out here: https://msdn2.microsoft.com/ms162802.aspx

Question: What about Sqlimail? Is it available in all versions of SQL Server 2005?
Answer: There are two ways to work with mail notifications in SQL 2005, Database Mail and SQLimail. Database mail is new and a great thing now about database mail and SQL 2005 is that MAPI is no longer required.  For some more information checks out this site: https://msdn2.microsoft.com/library/ms175887.aspx   to learn more about SQLimail, check out the following: https://www.microsoft.com/technet/community/events/sql2005/tnt1-133.mspx

Question: Are there any plans to enhance Upgrade Advisor to make easier to examine a list of servers all at once and import all the output into db? Am aware of the -CSV switch on command line and XML configuration file but would be nice to be to generate from Enterprise Manager / a list of servers and then import all results into SQL Server and then examine there. I can write a wrapper to do this myself, but it would nice to have this built in.
Answer: That is a great question, I do not know the product group has updated the advisor a few times, I do not know of they will put that in the next version.  I would keep checking for updates.  To download the January 2006 tools please go here: https://www.microsoft.com/downloads/details.aspx?familyid=451FBF81-AB07-4CCB-A18B-DA38F6BCF484&displaylang=en   

Question: If we where to migrate our databases over to the evaluation version of SS2005, is there a way of upgrading to the real purchased version (before the 180 day expires) without uninstalling or stopping the converted migrated running or production 2005 databases.
Answer: The 180-day trial version of SQL Server 2005 Enterprise Edition can be upgraded to the fully licensed version of SQL Server 2005 Enterprise Edition without uninstalling the trial software prior to the end of the 180-day period. If upgrading to the fully licensed version of SQL Server 2005 Standard Edition, Workgroup Edition, or Developer Edition, it is recommended that you uninstall the 180-day trial version of SQL Server 2005 Enterprise Edition before installing the fully licensed version of the other editions. Please refer to the product documentation for information about upgrading to the fully licensed software.  To learn more check out this link: https://www.microsoft.com/sql/downloads/trial-system-requirements.mspx

Question: Can we run upgrade advisor for new installation of SQL server on new box?
Answer: Upgrade advisor would run against a prior edition database. If you don’t have an existing database there really will not be much benefit

Question: Does the upgrade advisor affect the server you are analyzing while it's running?
Answer: It does add a small amount of impact.

Question: Running Upgrade Advisor on a production SQL 2000 box, will you see a performance hit?
|Answer: minimal depending on the hardware

Question: Does the Upgrade advisor include upgrading MSDE to SQL Express and can this be done for free?
Answer: It will still run, for more information check out the following link: https://www.microsoft.com/sql/editions/express/default.mspx

Question: Will it script Link Servers configured in existing system or we need to configure link server again?
Answer: Yes it is scriptable.  Additionally you can save this information in the SQL 2005 Management Studio projects.

Question: How does upgrading affect SQL server 2000 Replicated Databases?
Answer: Significantly. You would need to upgrade your replication strategy.  There are several changes to replication to learn more check out here: https://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx   also I would recommend to take a look here: https://msdn2.microsoft.com/en-us/library/ms143699.aspx

Question: When there is log shipping which server has to be upgraded first?
Answer: Either, fundamentally you would upgrade the secondary server first, but log shipping will need to be reconfigured in both instances of SQL.  There is a fantastic article here that discussed how to upgrade replication (log shipping): https://msdn2.microsoft.com/ms188297.aspx

Question: I have a huge SQL 2000 database. I need to migrate to 2005. Will I get the same performance? Are there any more considerations?
Answer: I wish there were a good straightforward answer to this, but it really depends on the environment. Hardware and connectivity play such a key role. The potential for performance improvement is significant.

Question: Does SQL server 2005 support SQL Authentication or only Windows Authentication?
Answer: It supports both types as well as mixed.

Question: After a successful upgrade/migration to SS2005, when is it required to upgrade client components to SQL Native Client?
Answer: It is up to you. There is a good discussion of the concept here https://blogs.msdn.com/dataaccess/archive/2005/04/26/412161.aspx

Question: How can we design DTS packages on SQL 2000 server using SQL 2005 enterprise manager?
Answer: Unfortunately there is not DTS designer currently for SQL 2005.  The new services are SQL Server 2005 Integration services (SSIS). Hopefully there will be something soon.  There is a great site on DTS backwards compatibility here: https://msdn2.microsoft.com/en-us/library/ms143706.aspx

Question: Where can I get more information about upgrading Reporting Services?
Answer: https://msdn2.microsoft.com/en-us/library/ms143747.aspx

Question: Can you run SQL2000 and SQL2005 instances on the same box?
Answer: You can run instance of SQL 2000 on a SQL 2005 box

Question: What are the SQL Server 2005 hardware recommendations?
Answer: https://www.microsoft.com/sql/editions/enterprise/sysreqs.mspx

Question: Can we use Intellisense in the query analyzer?
Answer: There is nothing currently built into the SQL Server 2005 Management Studio; however there is a 3^rd party (thank you to the person on the web cast who pointed me to this, and you know who you are. J) tool to look into called PromptSQL. To learn more take a look here: https://www.promptsql.com/

Question: Where can I learn more reporting services 2005?
Answer: Reporting services are already availably for SQL Sever 2005. For the FAQ look here: https://www.microsoft.com/sql/technologies/reporting/faq.mspx

Question: What can the CDW do?
Answer: For information take a look here: https://msdn2.microsoft.com/ms188664.aspx

Question: During install process is there a log file we can monitor to detect the progress or view any install errors?
Answer: Yes, there is a log file, take a look on your system root here: ~Microsoft SQL Server\90\Setup Bootstrap\LOG\Files (again thank you for someone on the web cast finding this as well)

Question: If I am going to upgrade a Microsoft Application that was installed with SQL 2000 how do I get specific upgrade instructions for SQL 2005, for example MOM, CRM, and Small Business Accounting?
Answer: For most of those applications SQL 2005 will work fine, but check on those individual applications sites to learn more.  Specifically for CRM check out this web cast: https://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032275629&EventCategory=5&culture=en-US&CountryCode=US

Question: How can we modify system tables directly in SS2005? How can we transfer logins from SS2000 to SS2005 server?
Answer:  Take a look at the product guide for more information: https://download.microsoft.com/download/0/7/9/07966841-6a3c-4a71-aef4-09d2f216d81c/Product_Guide.doc

Question: If using backup-restore or detach-attach method for upgrade, are SQL logins migrated?
Answer: The easiest way would be to use the CDW to copy them over.  For more information take a look here: https://msdn2.microsoft.com/en-us/library/ms190775(en-US,SQL.90).aspx

Question: Are there a lot of changes between 2000 & 2005 SQL clustering?
Answer: Yes, take a look here to see some of the new features: https://msdn2.microsoft.com/en-us/library/ms190990(en-US,SQL.90).aspx

Question: What strategy have people used for Client redirection - i.e. do a Migration to new Instance and therefore Instance name will change... ie Have 100 clients pointing @ existing name, need to change..DNS Alias, redirection
Answer: Depends on how that application has been written

Question: Does the SQL Server Agent service still exist? or are there any new or renamed services installed?
Answer: yes it still exists to learn more about the enhancements, take a look here: https://www.microsoft.com/technet/prodtechnol/sql/2005/evaluate/newsqlagent.mspx

Question: Does SQL 2005 Standard edition support clustering or is that only in enterprise edition?
Answer: Yes, for more information take a look here: https://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Question: When the database is backed up from SS2000 and then loaded to SS2005, HOW does it handle the schema ownership?
Answer: Take a look at this streaming web cast to learn more about the Schema separation: https://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032289132&Culture=en-US

Question: Is there a white paper on geographically dispersed clusters?
Answer: Yes you can find more information here: https://www.microsoft.com/windowsserver2003/techinfo/overview/clustergeo.mspx

Question: After 2005 install, I am having problems starting SSIS. We uninstall SQL Server 2005 beta version and install SQL Server 2005. We ran into registry problems. To get around this, a registry entry was renamed. We were then able to install SQL Server 2005. However, we have been unable to start Integration Services. In the Event Viewer, we get an error message that MS SSIS service failed to start. Error: Method 'GetVersionInfo' in type.
Answer: I found a thread and it looks like you may to re-install, take a look here for more information: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=216937&SiteID=1 before I re-installed I would make sure you have removed the beta of the .NET framework 2.0

Question: What DBCC commands should be run before the upgrade?
Answer: I would probably run at least DBCC CHECKDB and CHECKTABLE.  Does anyone else have any suggestions?

Question: In SQL 7.0 and SQL 2000 allow a "blank" or default instance, does SQL 2005 also allow this?
Answer: Yes in 2005 you do have a default instance, take a look here for more information: https://msdn2.microsoft.com/en-us/library/ms143531.aspx

Question: Will SQL Server run on Virtual Server?
Answer: Yes, for testing only, I believe it is not supported in Production.

Question: Are there any issues with Kerberos security and SQL Server 2005?
Answer: Not that I am aware of, take a look at this KB for more information: https://support.microsoft.com/kb/319723