Software Maintenance (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.
Updates to software—whether for supportability, bug fixes, or for issue (or security issue) prevention—are a common characteristic that needs to be considered as part of every solution. For Microsoft SQL Server, cumulative updates (CUs), service packs (SPs), and security patches are three of the most common forms of software maintenance.
Software maintenance can potentially lead to downtime and have an impact on the application/solution in terms of its use and the changes it may make on the system. Therefore, processes and procedures need to be implemented with these considerations in mind.
This section provides some best practice guidance and resources for more information. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)
For background on the SQL Server 2008 servicing installation requirements and process review, see Overview of SQL Server Servicing Installation.1
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/DR2 PowerPoint presentation.
Case Studies and References
The following case studies can be used for reference.
There are several ways to minimize software maintenance and upgrade downtime. An example of a successful implementation is ServiceU, which uses a planned downtime scenario using failover clustering and database mirroring. See High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study.3 In the same location, view the subsections: "Planned Downtime Scenarios," "Patches and Cumulative Updates," and "Database and Application Changes".
For an example of how downtime was minimized during planned failovers, see the article Minimize downtime with DB Mirroring.4
Questions and Considerations
This section provides questions and issues to consider when working with your customers.
With SQL Server 2008 SP1, it is possible to slipstream the released product with service pack code. For new deployments (or new builds that will be deployed) this provides an easier way of getting the benefits of SPs with a single install.
Formalize a testing and release management process for the software updates. Process other changes to the solution (for example, application changes) into the overall scope and implementation of the database software changes.
Establish or reference the run book or set of guidelines and procedures for software updates and maintenance in the data center.
Consider the risk and impact of downtime when applying software updates, and if necessary plan for rollback procedure that will require significant thought, planning, and testing. Keep in mind that uninstall of CUs is supported from SQL Server 2008 SP1 and SQL Server 2008 R2. But this is only a component; application data and impact on related applications should also be considered.
Are there processes or procedures currently in place for software maintenance in the environment? For example, is there a monthly or quarterly maintenance window?
For guidelines on product support, see the Microsoft Support Lifecycle5 website. See the "Security Update Policy" section for the update release policy.
For information about the security notification system, see Microsoft Technical Security Notification.6
There are many "layers" to consider in software maintenance. For SQL Server, you must also consider the underlying Windows operating system and its maintenance.
Following are the full URLs for the hyperlinked text.
1 Overview of SQL Server Servicing Installationhttp://msdn.microsoft.com/en-us/library/dd638062(SQL.100).aspx
2 Proven Customer Deployed Architectures and Scenarios for SQL Server HA/DRhttp://ecn.channel9.msdn.com/o9/te/NorthAmerica/2010/pptx/DAT401.pptx
3 High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Studyhttp://msdn.microsoft.com/en-us/library/ee355221.aspx
4 Minimize downtime with DB Mirroringhttp://blogs.msdn.com/b/sqlcat/archive/2009/02/09/minimize-downtime-with-db-mirroring.aspx
5 Microsoft Support Lifecyclehttp://support.microsoft.com/?LN=en-us&scid=gp%3B%5Bln%5D%3Blifecycle&x=7&y=17
6 Microsoft Technical Security Notificationhttp://technet.microsoft.com/en-us/security/dd252948.aspx