Magnificent SQL Server Patching

What is becoming clear when speaking to customers, both old and new, is that we are doing something pretty special in RockSolid v5.0 in relation to the management of SQL Server patching.

SQL Server Patching Challenges

Without a doubt, in larger sites, proper management of SQL Server patching has been a challenge. And it is a challenge for a number of reasons, which include:

The frequency of patches. Microsoft typically releases a handful of patches each year, for each mainstream version of SQL Server, and most organisations are running multiple versions.

The size of environment. Once you multiply the number of patches by the size of the environment, the scale of challenge becomes clear. Sites with hundreds of SQL Server instances can have a requirement to apply thousands of patches each year.

Vendor certification. Different vendors approve patches for use with their applications at different rates. Some may not approve certain releases, such as cumulative updates, while others may be proactive and confirm support readily.

Outage availability. Patching requires an outage. In modern 24/7 systems these outages can be difficult to arrange and can delay SQL Server patching.

The above are just the challenges in planning the patching process and does not include the logistics of actual patch deployment. Patch deployment challenges can include:

Resourcing. Each patch takes approximately 30 minutes to deploy manually, if all goes well. Multiply that by the scale above and we are talking about significant FTEs dedicated to patching.

Ensuring that a consistent patch process is followed on every occasion. Your process may include ensuring backups are taken prior to patching, ensuring users are off the system, applying the patch and then validating the patch post deployment.

Handling complex environments. With standalone instances, patching can be quite straightforward, but when clusters and availability groups come into the picture patching becomes more complex. You may, for example, have a requirement to patch the DR node, failover then patch the PROD node then fail back. Managing these logistics adds to the patching overhead.

Consistent rollback. If patching fails, having the confidence and supporting process in place to allow for quick rollback is essential. Developing rollback strategies for each environment prior to patching can be time consuming and makes patching all the more resource intensive.

So what happens in the real world when enterprises are faced with these patching challenges? Typically we see one of the following three strategies:

Don’t patch / don’t have a strategy. More large organisations than you would think don’t have any clear patching strategy for SQL Server. They may apply the latest SQL Server patches when instances are built, but may never patch those instances again. Somewhat troubling is that we see this often with organisations who have outsourced their database management, while the client organisations themselves are unaware of the poor patch management.

A semi-proactive patching strategy. To try and combat the patching resource demands, some organisations may elect to implement a time based patch strategy. This is semi-proactive but falls short of desired patch management goals. For example, organisations may decide to patch 4 times a year and during each cycle apply whatever the latest patches are.

Proactive, but resource intensive strategy. Organisations which are very security conscious, such as companies within the FSI sector, may be very proactive with clear and consistent delivery of patches as they are released by Microsoft. But this approach comes at significant cost with significant resources required to deliver on this strategy, even if this is delegated to lower cost resources.

Within RockSolid, we have been aware of these challenges for some time and have sought to help reduce the burden within our latest RockSolid release. In version 5.0 we have focused on addressing both the patch management challenges, using effective organisational features, and the patch delivery process via the RockSolid Automation component.

RockSolid Patch Management

With RockSolid v5.0 patching becomes a policy decision and the power of the RockSolid policy framework is utilised for patch management. You can define different patch management strategies and different application patching strategies, then use RockSolid policies to automatically apply those strategies to the relevant hosts. Once your patching strategies are defined, the management of patching becomes simply a case of setting the target SQL Server patch level for each SQL Server version in your environment, and updating this when new patches are released.

Once you set a target build for a given SQL Server version, RockSolid automatically finds all the instances in your environment which do not comply with the policy. RockSolid then raises change requests for each violation and these are rolled up into groups for easy management as a collective. You now have full visibility on what patching is required within your organisation.

Following on from this, you can control the scheduling of patches using RockSolid’s sophisticated scheduling functionality. You can choose, for example, to patch all instances concurrently during a maintenance window, break down patching into smaller chunks or schedule an instance to be patched on an individual basis to meet system specific needs. Once scheduled, the installation of the patch can be undertaken by RockSolid Automation.

Patch Implementation

At a simplistic level the installation of a SQL Server patch just requires the patch executable to be run. However, in the real-world things are more complex. Seemingly straightforward tasks such as getting the right patch executable to the target host when hundreds of systems and complex network topologies are in place can itself be a challenge. Additionally, of course, when making any change to production systems, the patching process should include proper risk management.

Risk management is ensuring a process is followed that, in a worst case scenario, allows the system to be recovered with minimal data loss. This may mean that prior to patching you undertake full ad-hoc backups. It may also mean that you ensure all users are off the system and no batch processes are part way through processing when patching commences. It may also mean you have a complete environment build specification so that if worst comes to worst you can rebuild the instance, something RockSolid does automatically for all systems it manages.

RockSolid automation solves all these challenges, while significantly reducing the resources required to deliver a proactive patching strategy. From automatic delivery of the patch files to relevant hosts even in complex networks, to all your defined pre-patching processes, such as backups, through to the actual implementation of the patch. Finally, post implementation, RockSolid validates the patch and if there is an issue it can automatically rollback the patch or escalate to the DBA team for manual resolution.

The RockSolid platform is designed to allow enterprise customers to deliver, at scale, the database management standards they desire, while significantly reducing the resources required to achieve. This is something I am immensely proud of. If SQL Server patching is a challenge within your organisation, please consider joining us for a patching webcast or schedule a demo with my team and we will walk you through how RockSolid may be of value to your organisation.

Tony Bain is the founder of the RockSolid product and is one of the Directors of the RockSolid SQL business. Tony has a long history of delivering products and services in the field of database management and was previously responsible for Red Rock Consulting’s SQL Server division.