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.

Best Practices

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.)

Case Studies and References

The following samples can be used for reference.

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.)

Appendix

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)https://www.microsoft.com/downloads/en/details.aspx?FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52&displaylang=en

2 SQL Server 2008 R2 Upgrade Technical Reference Guidehttps://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 R2https://support.microsoft.com/kb/935197

4 How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instanceshttps://msdn.microsoft.com/en-us/library/bb677181.aspx

5 High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Studyhttps://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 websitehttps://support.microsoft.com/lifecycle/#tab0