Getting ready for SQL Server 2005 (906773)

Get to know SQL Server 2005

The first thing you want to do is to familiarize yourself with the new architecture of SQL Server 2005, the new features, and the changes and enhancements that have been incorporated into SQL Server 2005. There are several resources available to help you with this effort. The following resources and content are available:

• SQL Server Books Online contains a lot of information that will help you with planning and organizing information to help you get ready for SQL Server 2005. You can download the complete SQL Server Books Online from the following Microsoft Web site:

  http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

  You will have to download the installer files and perform the installation to obtain SQL Server Books Online for an individual installation on your computer. When you browse through the contents of SQL Server Books Online, you will find a topic titled "What's New in SQL Server 2005." This topic has several categories under which the different enhancements are discussed in detail. This topic is a good place to start your journey into the exciting world of SQL Server 2005.
• There are several Webcasts that are delivered by members of the SQL Server development team and the SQL Server escalation team. These Webcasts discuss the enhancements that are available in SQL Server 2005. Many of these Webcasts are targeted toward new tools and techniques available for troubleshooting different issues and problems. These are available for on-demand viewing on the following Microsoft Web site:

  http://www.microsoft.com/events/series/technetsqlserver2005.mspx

  Several more Webcasts are planned for delivery in the near future.
• Several members of the SQL Server development team have spent significant time generating white papers that contain good quality information about the different features and how they may apply to your specific situation. These white papers are detailed and contain most of the relevant information. You can read these white papers on the following Microsoft Web sites:
  • http://www.microsoft.com/technet/prodtechnol/sql/2005/default.mspx
  • http://msdn.microsoft.com/SQL/2005/default.aspx
• Several new features are introduced in SQL Server 2005. Some of the existing features from SQL Server 2000 have been enhanced to provide more scalability, robustness, and availability. Based on the targeted environments, some of these features are available in various editions of SQL Server 2005. You will want to carefully review the availability of the features that correspond to certain editions. You can find this information in the "Features supported by the editions of SQL Server 2005" topic in SQL Server Books Online. This information is also available on the following Microsoft Web site:

  http://www.microsoft.com/sql/prodinfo/features/features-at-a-glance.mspx

Resource planning

The next important aspect of getting ready for SQL Server 2005 is planning how to set up a new instance of SQL Server 2005 or upgrade from SQL Server 7.0 or SQL Server 2000 to SQL Server 2005. There are several resources available to help you with this effort:

• Every product will have a specific set of software and hardware requirements to function correctly. Similarly, SQL Server 2005 also has a comprehensive set of software and hardware requirements. These requirements differ based on various factors, including the edition, the operating system, the features, and many other combinations. You can find these requirements and analyze them in the "Hardware and software requirements for installing SQL Server 2005" topic in SQL Server Books Online.
• There may be situations where the person who is planning or performing the installation of SQL Server 2005 may overlook a specific requirement. Generally, this oversight would cause a setup failure or an incorrect installation of SQL Server 2005. SQL Server 2005 introduces the System Configuration Checker tool. The System Configuration Checker tool is launched as part of the initial setup process. This tool can detect problems with the requirements for performing a successful installation of SQL Server 2005 and will notify the person performing the installation. Based on this report, you can take corrective actions, and then perform the installation or the setup. This tool helps with appropriate identification of potential problems with setup and minimizes the time that you will spend setting up SQL Server 2005. You can find a list of parameters that are evaluated by the System Configuration Checker tool and possible remedies in the "Check parameters for the System Configuration Checker" topic in SQL Server Books Online.
• There is a new tool that can help with issues that may occur when you upgrade an existing instance of SQL Server 7.0 or SQL Server 2000 to SQL Server 2005. The Upgrade Advisor tool works in a way that is similar to the way that the Setup program provides an early warning of potential problems. We strongly recommend that you use this tool to identify any sort of incompatibility or possible incompatibility issues that may occur after or during the upgrade of the instance and the databases. The Upgrade Advisor tool performs an analysis of various components of the existing installation, and then generates a report. You can install the Upgrade Advisor tool from the SQL Server 2005 CD-ROM, or you can download the tool from the following Microsoft Web site:

  http://www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e05-4322-a677-95ab44f12d75&DisplayLang=en

Application testing

After you make sure that you have all the required resources and you have evaluated any upgrade issues, you should make sure that your application will behave the same way in SQL Server 2005 as it did in earlier versions of SQL Server. You should be very careful when you do this. Because of the many enhancements and changes that were made in SQL Server 2005, it is possible that some of the application components behave the same way while some of them do not, including error messages and conditions, application performance and responsiveness, as well general application behavior and the flow of logic. For some applications, you may be able to predict these possible changes, but in a majority of applications, you will have to perform some level of testing to understand the behavior of the application against SQL Server 2005. Yes, this is a point we want you to consider seriously. You will be thankful and relieved later when you end up with a very successful installation or upgrade if you devote the proper amount of time and resources to test your applications against SQL Server 2005. You will have your own testing methods that your organization follows. You can also use the following three tools to help you with this process:

• SQL Profiler replay: In most environments, it will be impossible to set up the actual application to perform the tests (for example, in a production environment). Most of the robust test tools exist for that reason. If you would like to simulate the same load that SQL Server encounters in a production environment, you should evaluate the use of the replay mechanism that is available with the SQL Profiler tool. This mechanism lets you capture a SQL Server trace file that contains information about the activity against SQL Server, and then replay it against the same server or a different server. You will have to capture a specific set of events, and make sure that the basic inputs are configured properly for the replay to work. For more information about prerequisites, setup, and performing the replay, see the "Replaying traces" topic in SQL Server Books Online. By using this method, you can isolate problems that could prevent you from a successful application deployment in a SQL Server 2005 environment.
  
  Make sure that you save the Profiler replay trace of SQL Server as soon as you have finished the testing. This trace can be very helpful both for you and Microsoft Customer Support Services (CSS) to understand the difference between SQL Server 2000 and SQL Server 2005 if you experience any performance issues with SQL Server 2005.
• Database Engine Tuning Advisor: You might have designed and created the database schema to support your application, and it might work very well in the existing version of SQL Server. Before you upgrade or deploy your application against SQL Server 2005, you might want to make sure that your current database design is suitable for the new feature set that you will use. You might also want to consider how an enhanced feature will respond to application requests. You may have to make some modifications to the underlying index specifications for your application to work efficiently with SQL Server 2005. To help with these efforts, you can use the Database Engine Tuning Advisor. You can definitely use workloads that you created for replay with the tuning task. The Database Engine Tuning Advisor can make suggestions based on the workload that you supplied, and you can use the new features to make improvements. For more information, see the "Database Engine Tuning Advisor reference" topic in SQL Server Books Online.
• Dynamic management views and functions: Dynamic management views and functions reveal server state information that may be used to monitor the health of a server instance, diagnose problems, and tune performance. You can use these dynamic management views to understand the capacity and resource utilization of SQL Server 2005 when you are replaying the traces or performing stress tests to understand the behavior. These views reveal a lot of information that could help you to identify any bottlenecks that the system experiences when you perform these early stress tests. For more information, see the "Dynamic management objects" topic in SQL Server Books Online.

A sample checklist

To successfully upgrade from SQL Server 2000 to SQL Server 2005, it is very important to examine the health of your databases and the server as a whole. The following checklist is a sample used by Customer Support to guarantee a smooth transition while working on proactive upgrade engagements. You might want to design something that could help you get a step closer to a successful upgrade. This checklist is not complete, and you may want to add or subtract steps based on your requirements:

• Run the Upgrade Advisor tool to identify potential blocking issues and backward compatibility problems.
• Make sure that the computer on which you are installing SQL Server 2005 does not have any hardware errors.
• Run the DBCC Checkdb command on all the user databases and the system database to make sure that the databases are clean.
• Make sure that the backups of the SQL Server 2000 user and system databases are current and in a safe location.
• Stop all scheduled SQL Server Agent jobs, and make sure that no conflicts exist.
• Stop all third-party software that is running on the server.
• Verify the rights of the logged-in user who is installing SQL Server 2005 or upgrading SQL Server 2000 to SQL Server 2005.
• Make sure that no databases are set to Read Only.
• Review the Setup Consistency Checker output, and then address any error messages that you received.
• Finish the installation.
• Back up the new installation databases.
• Place the previous backups and the new backups in a safe location.

Backward compatibility

Because there are so many changes in any new product release, some of the commands, interfaces, features, and behaviors may be changed, discontinued, or deprecated. You will definitely have to analyze the changes because some breaking changes might stop you from deploying your application against SQL Server 2005. It is better for you to understand these changes and limitations very well in advance for a smooth transition to SQL Server 2005. A lot of work has been started to reveal all this information to users and administrators. In SQL Server Books Online, there is a complete list of discontinued functionalities, deprecation announcements, breaking changes, and behavior changes that are present in SQL Server 2005. For more information, see the "Backward compatibility" topic in SQL Server Books Online. This topic has all the details listed according to the components that you may be interested in. Additionally, SQL Profiler has two new event classes that reveal this information when SQL Server encounters any one of these situations. The new event classes are the Deprecation Announcement event class and the Deprecation Final Support event class.

When you run the Upgrade Advisor tool, it will generate a report of your current SQL Server 2000 instance, and point out any known backward compatibility issues between SQL Server 2000 and SQL Server 2005.

Known issues

It is always useful to know some of the issues that you will experience when you are working with a new product. This information will save you valuable time and effort. You can view the articles that discuss known issues and problems that were uncovered during the Beta timeframe and their possible resolutions. To do this, perform an advanced search for "SQL Server 2005 Beta" content on the following Microsoft Web site:

I'd like to thank Suresh Kandoth and Vishal Anand for their hard work on this article. Suresh Kandoth is an escalation engineer with the SQL Server Support team. He has been with Microsoft for the past four years working on SQL Server technologies. He has in-depth experience on several Microsoft Windows server products and enjoys supporting customers and debugging problems. Vishal Anand is a technical lead with the SQL Server Support team and has been focused on SQL Server 2005 for more than one year. He has been with Microsoft for the past four years and has also worked as a database administrator. Vishal enjoys working with customers and solving problems. Hope you enjoyed this month's topic! Thanks for reading! As always, feel free to submit ideas on topics you want addressed in future columns or in the Knowledge Base using the Ask For It form.