When planning a SQL Server upgrade -- such as an upgrade to SQL Server 2008 -- one of the most important aspects to consider is testing. Too often, people move their data to the newer version of SQL Server and assume that everything will work out. It usually does, but it is never a certainty. This is especially true for upgrades from SQL Server 2000 to SQL Server 2005, which appear to be the most untested upgrade path in quite a while.
Considering that SQL Server administrators test every stored procedure change they put into the database platform, the minimal amount of testing done when the database version itself changes never ceases to amaze me. A lack of testing during a service pack upgrade is understandable, as not much should differ after that procedure. But upgrading to a different edition of SQL Server can cause major changes to the system.
Long before a system upgrade takes place, you should start by upgrading your development and quality assurance (QA) systems to the new version. This way, your developers see firsthand any issues that might arise. Upgrading QA systems is important because they are most likely where you'll perform the bulk of your testing. In addition to testing the actual process of upgrading the system, you should perform full regression testing, as well as full load testing, against the database. This helps identify the problem points.
What happens when you don't test?
Previously, I worked for a company that performed an in-place upgrade from SQL Server 2000 to SQL Server 2005. Running on older 32-bit hardware, the system had only 4 GB of RAM. It also made heavy use of dynamic SQL, as each large process had its own set of tables within the database. Combined with the changes in how SQL Server 2005 handles its procedure cache, the upgrade sent the SQL Server's CPU load from 20% on SQL Server 2000 to 100% on SQL S...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
erver 2005. On SQL Server 2000, the SQL Server was able to manage the dynamic SQL within its plan cache, but SQL Server 2005 wasn't able to operate in that fashion. This forced the SQL Server to recompile the execution plans almost each time the procedures were run.
As far as I could tell, there was little to no load testing done in QA beforehand. The result of this failed upgrade was to choose one of two solutions. Option one was to upgrade to both 64-bit hardware and 64-bit SQL Server, and option two was to downgrade to SQL Server 2000. Downgrading, however, meant a two-week-old database restore, along with using Data Transformation Services (DTS) to move the missing data from the SQL 2005 database to the SQL 2000 database.
If sufficient testing had occurred in QA, specifically load testing, this issue could have been caught long before the production system was upgraded. The problems that resulted from insufficient testing cost this company tens of thousands of dollars in lost revenue. Other costs included using the Microsoft Professional Services team, paying for overtime at the company's outsourced database administration provider, third-party consulting fees and the loss of customers canceling the service.
What can happen when you do test?
Performing adequate testing on your SQL Server upgrade can greatly reduce the stress that stems from upgrading issues. Another company I worked for was upgrading from SQL Server 2000 to SQL Server 2005. For this upgrade, we did a full code review, ran the SQL Server Upgrade Advisor and searched for issues that were reported against the Microsoft Knowledge Base. As we completed each section of the application, we sent that part to QA for thorough application testing, and, when all sections were complete, we also enacted full regression testing. Through testing, we identified several issues that were sent back to the development team to be resolved. Thanks to ample testing, we successfully upgraded the system with zero problems. Any concern about the size of your system should not deter you; the system we tested so comprehensively was a large auto finance loan origination system that processed more than $1 billion in auto loans per year.
While these two examples are extreme scenarios, they make a specific point. With testing, you can discover many potential problems. Without testing, you are flying blind. To make matters more difficult, rectifying any issues you encounter by downgrading to your original version is a complex process. If you've already released the new version to production, it will be even more difficult.
With the next release of Microsoft SQL Server, testing could be even more important. Many features that have been considered for removal in SQL Server 2005 and SQL Server 2008 are scheduled to be definitively removed in the next release. This will have a major impact on any plans to upgrade to future versions, and testing will end up being the most comprehensive solution.
ABOUT THE AUTHOR:
[IMAGE]Denny Cherry has more than a decade of experience managing SQL Server, including MySpace.com's 175-million-plus user installation, one of the largest in the world. Cherry's areas of expertise include systems architecture, performance tuning, replication and troubleshooting. He uses these skills on a regular basis in his current role as a senior database administrator and architect at Awareness Technologies. Cherry holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. He is a member of PASS and Quest Software's Association of SQL Server Experts and has written numerous technical articles on SQL Server management.
Check out his blog:
SQL Server with Mr. Denny.
