How up to date is your MSSQL Server?

The car, a proud possession of many people. We are careful with that; we clean the car regularly and make sure that it is also maintained. Something that is often indicated on your dashboard. If you do not have maintenance carried out, performance will deteriorate and you run the risk of high repair costs to return to the ‘old’ performance.

What about your MSSQL Server environment? It also needs attention and maintenance. Only this server does not have a dashboard by default that indicates that maintenance is necessary. There is some information available, but this is not as easy as in a car. As a result, maintenance is soon “forgotten”.

To maintain the condition of the server, it is wise, and in many cases even necessary, to regularly install the latest updates and check the status of the server. This way you can avoid all kinds of performance and security issues. Often found software problems are fixed in updates.

Microsoft has different variants of updates. These are, in order of applicability:

• Hotfixes . These are very specific updates for a particular situation. Such a hotfix is often specifically written for a certain combination of hardware and software and cannot be installed just like that. The quality control on a hotfix may also have been less thorough, as it is often released under great time pressure. Therefore, we recommend hotfixes only if they resolve an existing issue and cannot wait for the next cumulative update
• Cumulative Update (CU). This is a collection of improvements and bug fixes and is released every 2 months. The quality control of a CU has been the same as the quality control of a Service Pack since January 2016 and is therefore thoroughly tested.
• Service Pack (SP). This is a collection of Cumulative Updates and is released on average once a year. The quality control is equivalent to the quality control of a CU.

In addition, there are security updates. These come in two flavors:

• QFE update: . This is actually a Cumulative Update with additional security updates.
• GDR update: This update contains only security fixes.

An overview of the current MSSQL updates can be found here .

Best practices for an update policy

Although Microsoft performs a quality check, it is possible that side effects may occur after the installation of an update. We therefore recommend waiting at least 30 days before installing a CU or SP. Of course, this does not apply to a hotfix, because it was just released to fix a problem quickly. We therefore recommend that you install the updates manually, preferably initially on your test environment, and not let Windows determine it. This gives you the opportunity to test the possible effects of the updates before implementing them on your production environment.

If the MSSQL instance is running on a virtual server, it is recommended to take a snapshot of the server first if possible. This is the easiest way to roll back an update, although current updates will neatly uninstall themselves if errors occur during installation. A full database backup beforehand is also recommended. This secures all changes to the database.

Microsoft has indicated that Cumulative Updates have become as important as Service Packs , so it is important not to wait for a Service Pack, but also to proactively install Cumulative Updates.

Importantly, the OS is also updated with updates. Note that an OS update may result in a server reboot. Therefore, never let Windows decide when the update takes place.

Only install the updates during a maintenance window, so everyone knows that the databases are temporarily unavailable and recovery measures can be used for multiple purposes. Restart the server after installing an update. It is strongly recommended not to perform database changes on the same day with the installation of OS updates. If this does happen, it is almost impossible to determine the cause of any problems afterwards.

If you use mirroring, clustering or AlwaysOn, the downtime for installing an update can be greatly reduced by using a “rolling upgrade”

Many administrators are not aware that installing updates does not automatically activate all improvements in this update. In many cases, these must still be explicitly enabled with a trace flag. See the publication on our website about this.

How do I know that my MSSQL Server is in good condition?

Do you want certainty and knowledge? DBA.nl has extensive experience with the installation of updates and we are happy to carry this out for you. This way we relieve you of your worries and we keep your MSSQL environment in top condition! We would like to make a plan together with you to optimize the management of your SQL server environment. Feel free to contact us if you are interested. Check out our website for contact details and the options we can offer you.