Version Upgrade (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions
Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions. |
Data warehouse version upgrades usually involve additional tables and/or table structure changes, such as new/deleted columns or new partitioning or indexes. Additionally, upgrades to the presentation layer are involved. Whenever version upgrades are introduced, they can have a significant impact on all aspects of the data warehouse, including data loads, user reports, downstream systems, backup/restore strategies, data security, end-user queries, and so on.
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.
Downtime and rollback procedure. There is usually an associated downtime with the upgrade process. Some of this can be limited by upgrading multiple components at a time or utilizing another instance of SQL Server to do the upgrade (references below).
The Microsoft SQL Server Upgrade Advisor1 is utility customers can use to help evaluate their environment for potential issues during an upgrade.
For list of version upgrade best practices, use the SQL Server 2008 Upgrade Technical Reference Guide.2
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
Hyper-V/Live Migration can also be used to minimize the impact to SQL Server/the Hyper-V instance, in terms of a Windows upgrade of a host system.
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 Scenarios for SQL Server HA/DR.6
Questions and Considerations
This section provides questions and issues to consider when working with your customers.
Most tier-one data warehouses will have a rigid set of guidelines for handling intrusive upgrades, but many do not, and one should carefully understand and plan accordingly.
It is imperative that mission-critical or very large database (VLDB) data warehouses have a test-and-development environment on which all upgrades are thoroughly tested before deploying to production.
Evaluate features and the need to upgrade. There is usually a trade-off or evaluation process in terms of need for a new feature included in the version upgrade versus time to test, sign off, and deploy into production.
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.
Appendix
Following are the full URLs for the hyperlinked text.
1 Microsoft SQL Server Upgrade Advisor http:/
2 SQL Server 2008 Upgrade Technical Reference Guide http:/
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 R2 https://support.microsoft.com/kb/935197
4 How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances https://msdn.microsoft.com/en-us/library/bb677181.aspx
5 High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study https://msdn.microsoft.com/en-us/library/ee355221.aspx
6 Proven Customer Deployed Architectures and Scenarios for SQL Server HA/DR http://ecn.channel9.msdn.com/o9/te/NorthAmerica/2010/pptx/DAT401.pptx
7 Microsoft Support Lifecycle https://support.microsoft.com/lifecycle/#tab0