19
SQL Server is a data management platform that consists of several major components. As it stands in the 2005/2008 versions of the product, SQL Server’s four main components are the relational engine, the analysis services engine (BI), Reporting Services, and Integration Services (SSIS). Upgrading an instance of SQL Server does entail the risk of application compatibility issues arising with the new version of any of these four components. There are primarily two sources of incompatibility, 1) TSQL code that is dynamically built by the application and 2) TSQL code that resides in the database in the form of a stored procedure, view, function, etc. Any upgrade strategy should address compatibility issues from both sides. In this post, I am going to focus on upgrading the relational engine and tools that you can use to improve your success. We’ve also captured some great information in our corresponding podcast on the topic.
Upgrade Paths
There are several types of upgrades that are supported for SQL Server. These include variations of side by side upgrades utilizing database restores, attaches, etc. However, the most straight forward upgrade is what is called an “in place” upgrade where the setup binaries for the new version of SQL Server are run on the machine that currently hosts the previous version. Though primarily focused on in-place upgrades, tools and ideas covered in this blog apply to side by side upgrades as well.
SQL Server 2008 supports in place upgrades from SQL Server 2000 SP4 and SQL Server 2005 SP2 . This means that if you are running SQL Server 2000 you have the option of upgrading to SQL Server 2005 today and then to SQL Server 2008 when it releases or directly to SQL Server 2008 upon release.
The Upgrade Tools and Their History
In the SQL Server 2000 time frame, there was a tool that was released called the Best Practices Analyzer for SQL Server 2000 . This tool would scan a SQL Server and identify areas for improvement of things such as schema, data types, stored procedures, and other core elements of the relational engine. This eventually became the basis for the Microsoft SQL Server 2005 Upgrade Advisor . The upgrade advisor analyzes an instance of SQL Server 7.0 or SQL Server 2000 and identifies configurations and other aspects of a database that could present a problem when upgrading to SQL Server 2005. It also presents the user with links to documentation where the identified challenge is documented. There was also a set of tools utilized internally by Microsoft called the SQL Server Database Upgrade Test Tool or SQL DUTT. This tool kit was used in the SQL Server 2005 timeframe to analyze over 300 Independent Software Vendor’s (ISV) applications. This process included running actual upgrades on the ISV’s applications in a lab environment, taking the learnings from labs, and re-incorporating the learnings from the uprade into SQL DUTT. Scalability Experts actually ran the ISV labs for Microsoft and collaborated with Microsoft in incorporating the learnings from the labs into SQL DUTT. In the end, both Microsoft and Scalability Experts entered into an agreement that allowed Scalability Experts to license the IP obtained during this work and provide it via a free downloadable tool to the public. The result is The SQL Server Upgrade Assistant.
SQL Server Upgrade Assistant
The SQL Server Upgrade Assistant (SSUA) is a tool made available as a free download from Scalability Experts. The tool can be downloaded at the following link: http://www.scalabilityexperts.com/default.asp?action=article&ID=43 . SSUA uses traces to allow an IT professional to evaluate the behavior of an application on different versions, service pack levels, editions, or configurations of SQL Server. The tool can even be used to test the behavior of different version of an application release when a hotfix or service pack is installed on SQL Server. You can listen to the podcast below for further info on how SSUA can be of assistance to your organization. You can also visit the official site of SSUA at http://www.scalabilityexperts.com/default.asp?action=article&ID=43 where you can find FAQs and much more.
SQL Server Upgrade Assistant currently supports upgrading SQL Server 7.0 and SQL Server 2000 to SQL Server 2005. In the near future, the next version of this tool will be released which will also support upgrades to SQL Server 2008. Check http://www.scalabilityexperts.com or if you’d like to get on a mailing list that will provide you an email update on the release of the next version of SSUA, go to http://newsletter.scalabilityexperts.com.
The following audio PodCast talks in depth about upgrading to SQL Server 2008 and the relevant tools such as SQL Server Upgrade Advisor and SQL Server Upgrade Assistant.
-Derick Schaefer
