Software Maintenance (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

  • Article

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.

Best Practices

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

Case Studies and References

The following case studies can be used for reference.

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.

Appendix

Following are the full URLs for the hyperlinked text.

1 Overview of SQL Server Servicing Installationhttps://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 Studyhttps://msdn.microsoft.com/en-us/library/ee355221.aspx

4 Minimize downtime with DB Mirroringhttps://blogs.msdn.com/b/sqlcat/archive/2009/02/09/minimize-downtime-with-db-mirroring.aspx

5 Microsoft Support Lifecyclehttps://support.microsoft.com/?LN=en-us&scid=gp%3B%5Bln%5D%3Blifecycle&x=7&y=17

6 Microsoft Technical Security Notificationhttps://technet.microsoft.com/en-us/security/dd252948.aspx