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.

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.

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


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

4 How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances

5 High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study

6 Proven Customer Deployed Architectures and Scenarios for SQL Server HA/DR

7 Microsoft Support Lifecycle