Behavior Change for VSS Backups on VMs with Basic Availability Groups

This week we released SQL Server 2016 SP2 CU2 and SQL Server 2017 CU9. In this blog, I’d like to go into a little more detail on one of the fixes that is part of these CUs which involves the SQL Writer Service.

If you are running SQL Server 2016 (or later) Standard Edition with a Basic Availability Group (AG) on a Virtual Machine (VM), you may have noticed that backing up the VM that hosts the secondary replica of the AG fails. This is because the VM backup uses the Volume Shadow Copy Service (VSS) to back up all the SQL Server databases that are hosted on the VM in a consistent way. In a Basic Availability group, backups of secondary databases are not supported, so rather than allow the VM backup to succeed with a subset of the databases being backed up, the entire VM backup would fail. In earlier versions of SQL Server, Database Mirroring would allow these VSS backups to complete by skipping any mirrored databases hosted on the VM. In SQL Server 2016 SP2 CU2 and SQL 2017 CU9, we have changed the default behavior of VSS backups on a Basic AG to be like Database Mirroring in that it will skip any secondary databases that are hosted on a Basic AG secondary replica rather than failing. This means the VM backup will succeed, but it will not contain any databases that were in a secondary state at the time of the backup.

The SQL Writer Service is the service that performs VSS backups in SQL Server. This is a service that is separate from the SQL Server engine and is shared across different versions of SQL Server. If you have multiple instances of SQL Server installed on the same server or VM, these instances will all share the same instance of the SQL Writer Service. Even though the same service is shared across versions, the file that ships with each version of SQL Server will be marked with the same version number as the instance it is shipped with. This means if you apply SQL Server 2016 SP2 CU2 to a server, you’ll get the new SQL Writer Service with the new functionality, but if you subsequently either upgrade that instance to SQL Server 2017 RTM or install a separate instance of SQL Server 2017 RTM on the same server, the new SQL Writer Service binary will be replaced with the old one because it will have a newer version number. Because of this you want to be sure to apply the latest CU if you upgrade to or install an instance of SQL 2017 on a machine that has been patched with SQL 2016 SP2 CU2.