Version Upgrade (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions
Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.
Version upgrades are usually characterized by new features, new functionality, and/or changes to existing features in the product. Therefore, a version upgrade can have a substantial impact on a current implementation and usually requires much more testing than a software update, such as a cumulative update (CU) or service pack (SP), requires.
The following section provides some advice and examples of customer scenarios for upgrading Microsoft SQL Server; also listed are general SQL Server upgrade reference materials. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)
The Microsoft SQL Server 2008 Upgrade Advisor1 is a utility that customers can use to help evaluate their environment for potential issues during an upgrade.
The SQL Server 2008 R2 Upgrade Technical Reference Guide2 lists version upgrade best practices.
In a failover cluster environment, there is no rolling Windows upgrade path from Windows Server 2003 to Windows Server 2008 or from Windows Server 2008 to Windows Server 2008 R2. This can have a significant effect on the SQL Server availability in a cluster upgrade or migration scenario. This issue is described in the Microsoft Support article You cannot upgrade the operating system of a clustered server from Windows Server 2003 to Windows Server 2008 or Windows Server 2008 R2 and from Windows Server 2008 to Windows Server 2008 R2.3
Case Studies and References
The following samples can be used for reference.
The article How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances4 describes the rolling upgrade procedure using database mirroring.
The article High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study5 demonstrates how Windows failover clustering and SQL Server 2008 database mirroring can help eliminate single points of failure in data centers and enable fast recovery from a possible disaster.
Windows maintenance and security patching can also have a large effect on the uptime of a SQL Server. For a sample architecture to help avoid downtime in this scenario, see the CareGroup example in slides 21-26 of the Proven Customer Deployed Architectures and .6
Questions and Considerations
This section provides questions and issues to consider when working with your customers.
There is usually downtime associated with an upgrade process. Some of this downtime can be limited by upgrading components one at a time or by using another instance of SQL Server to perform the upgrade.
Hyper-V/Live Migration can also be used to minimize the impact to the SQL Server or Hyper-V instance in terms of a host system Windows upgrade.
Is there application support for the later version? This is particularly important with independent software vendor (ISV) or third-party applications that interact with the database.
There is usually a trade-off or evaluation process in terms of need for a new feature included in the version upgrade compared to time to test, sign off, and deploy into production.
Compatibility with other versions of the product that may be deployed should always be considered.
New product versions have an impact on the supportability of previous versions of the product, as explained in the Microsoft Support Lifecycle7 website.
Windows upgrades can also have a major impact on maintenance and uptime for SQL Server. This can be a significant impact to SQL Server running on Windows Server.
A very important consideration is the ability to fall back if the new version is not acceptable for some reason and if the problems did not appear in the test environment. Minimizing the number of changes can help you identify the cause of the issue and can help you revert to the previous version if necessary. (It is also helpful if you have not yet adopted new features of the new version; theoretically, it is then still possible to fall back.)
Following are the full URLs for the hyperlinked text.
1 Microsoft SQL Server 2008 R2 Upgrade Advisor (Part of the SQL Server 2008 R2 Feature Pack)http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52&displaylang=en
2 SQL Server 2008 R2 Upgrade Technical Reference Guidehttp://download.microsoft.com/download/3/0/D/30DB8D46-8ACF-442A-99A2-0F4CE74AE14D/SQL_Server_2008_R2_Upgrade_Technical_Reference_Guide.docx
3 You cannot upgrade the operating system of a clustered server from Windows Server 2003 to Windows Server 2008 or Windows Server 2008 R2 and from Windows Server 2008 to Windows Server 2008 R2http://support.microsoft.com/kb/935197
4 How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instanceshttp://msdn.microsoft.com/en-us/library/bb677181.aspx
5 High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Studyhttp://msdn.microsoft.com/en-us/library/ee355221.aspx
6 Proven Customer Deployed Architectures and Scenarios for SQL Server HA/DRhttp://ecn.channel9.msdn.com/o9/te/NorthAmerica/2010/pptx/DAT401.pptx
7 Microsoft Support Lifecycle websitehttp://support.microsoft.com/lifecycle/#tab0