Software Maintenance (DW)---a Technical Reference Guide for Designing Mission-Critical DW 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.

  • With SQL Server 2008 Service Pack 1 (SP1), we allow for slipstreaming of the SP and release bits. For new deployments, or new builds which will be deployed, this provides an easier way of getting the benefits of Service Pack’s 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 datacenter.

  • For background on the SQL Server 2008 servicing installation requirements and process review, see Overview of SQL Server Servicing Installation.1

  • There are potential ways to minimize the downtime for software maintenance and upgrade. A few successful implementations include ServiceU (Planned Downtime Scenario using Failover Clustering and DB Mirroring).

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.

  • Consider the downtime (if any) for the data warehouse and make sure that all business users are notified appropriately.

  • Consider having a "test and dev" environment onto which software updates are applied first, before attempting them in production. For large data warehouses, this can be a significant.

  • If necessary, ensure database backups are up-to-date.

  • Consider risk and impact of downtime in applying the software update. Potential for rollback procedure.

  • 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 Lifecycle4 website. See the "Security Update Policy" section for the update release policy.

  • For information about the security notification system, see Microsoft Technical Security Notification.5

  • There are many layers to software maintenance for a system to consider. In the case of SQL Server we usually also need to take into account the underlying Windows OS, and the maintenance of that as well.

Appendix

Following are the full URLs for the hyperlinked text.

1 Overview of SQL Server Servicing Installation https://msdn.microsoft.com/en-us/library/dd638062(SQL.100).aspx

2 High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study https://msdn.microsoft.com/en-us/library/ee355221.aspx

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

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

5 Microsoft Technical Security Notification https://technet.microsoft.com/en-us/security/dd252948.aspx