Understanding SQL Server patching

Once you install SQL Server, you need to watch for future updates released by Microsoft. You can recognize what updates were installed to your server by checking the build number of the SQL Server. You can find the build number in the SQL Server Management Studio as you can see in the following screenshot or via the SELECT @@VERSION command. There are several types of updates released for SQL Server:

• Service packs
• Cumulative updates
• Security updates

Service packs are usually the largest update option for your SQL Server. They frequently include updates released by more cumulative updates and should be tested more thoroughly regarding performance and stability of the system. It's also important to keep your environment with recent service packs due to system support by Microsoft. Service packs not only fix issues, but often also bring new features to SQL Server. A good example was the service pack 1 for SQL Server 2016, which enabled many features previously available only in enterprise and standard editions. This had a tremendous impact on many smaller environments that were not utilizing enterprise edition, as DBAs and developers were able to start using many new features that were previously unavailable to them.

Cumulative updates are smaller compared to service packs and released more frequently. Cumulative updates usually fix many errors and include more updates, and undergo the same comprehensive tests as service packs. As an example, we can see that SQL Server 2014, by the time of writing this book, had only two service packs available, but for the first service pack, Microsoft had already released twelve cumulative updates with additional fixes for errors, performance, and stability. You can now install cumulative updates proactively with the same level of confidence as you would with service packs. Cumulative updates are incremental in nature so cumulative update 4 includes all the updates that were released in cumulative updates 1 to 3.

Security updates are smaller than cumulative updates and are usually fixing some sort of error or security vulnerability. These are released usually in a monthly cycle along the regular Windows updates and should be evaluated for your environment. Usually, a Chief Security Office (CSO) team or security team in general may request you to install such a security update in a reasonable time frame to your SQL Servers, which may be a complex task if you're managing larger environments.

You can find a nice list of updates for all SQL Server versions at http://sqlserverbuilds.blogspot.com/ where you can identify the correct build of your SQL Server.