Tag Archives: SQL Server

VSS and the SQL Writer Service

What is VSS?

Volume Shadow Copy Service (VSS) is a set of Microsoft APIs. It allows users to perform backups or snapshots on files and volumes even when they are in use. The VSS provides a consistent interface that allows coordination between user applications. Even if they update data on disk (writers) or back up applications (requestors).

How it works

  1. Gather the writer metadata and prepare for shadow copy creation.
  2. Each writer creates an XML description of what is getting backed-up. These XMLs are provided to the VSS. The writer also defines a restore method for all components. The VSS provides the writer’s description to the requester. It then selects the components that is getting backed up.
  3. The VSS notifies all the writers to prepare their data for making a shadow copy.
  4. Each writer prepares the data as appropriate. When finished, the writer notifies the VSS.
  5. The VSS tells the writers to freeze application write I/O requests. Read I/O requests are still possible. This allows it to create the shadow copy of the volume or volumes. The application freeze is not allowed to take longer than 60 seconds. The VSS flushes the file system buffers and then freezes the file system. This ensures that the file system metadata logged and is in a consistent order.
  6. The VSS tells the provider to create the shadow copy. This period lasts no more than 10 seconds. During which all write I/O requests to the file system remain frozen.
  7. The VSS releases file system write I/O requests.
  8. VSS tells the writers to thaw application write I/O requests. At this point applications are free to resume writing data to the disk.
    • The shadow copy creation can abort if the writers stay frozen for longer than 60 seconds. Or, if the providers take longer than 10 seconds to commit the shadow copy.
  9. The requester can retry the process (go back to step 1) or notify the administrator to retry at a later time.
  10. Upon creation, the VSS returns location information for the shadow copy to the requester.

More detailed information here.

SQL Writer Service

When it relates to SQL server, most of the time any errors on backups are not caused by SQL. But when they are, it is likely that the SQL Writer Service is usually not enabled. The service installs without user intervention when installing SQL Server.

This service provides added functionality for backup and restore of SQL Server through VSS.

SQL Writer supports:

  • Full database backup and restore including full-text catalogs
  • Differential backup and restore
  • Restore with move
  • Database rename
  • Copy-only backup
  • Auto-recovery of database snapshot

SQL Writer does not support:

  • Log backups
  • File and filegroup backup
  • Page restore

Limitations/Problems

  1. Point-in-time recovery – It is possible you will not meet your RPO for your business. As usually, you can only recover to the last point of your backup not a specific time.
  2. Transaction Log Clearing – VSS backups will not clear the SQL log file. This needs to happen through the Naive SQL commands.
  3. Pauses in I/O – As mentioned in the “How it works” section. During a backup, it is possible that database I/O will pause for just under 70 seconds. This can lead to users complaining about performance issues or kick-outs.

Common troubleshooting

A few things to check when getting errors:

  1. The service needs to be running at the time of the backup.
  2. This service also needs to be running as the Local System account.
  3. NT Service\SQLWriter login needs to be active inside SQL server. This account is designated as no login, which limits vulnerability.

Next steps

If none of these are causes of your problem, it is a good idea to reach out to your backup solution vendor. You will want to include the following in the email:

  1. That you checked the three steps listed above
  2. The event viewer errors or warnings
  3. The detailed description of what you are having
  4. How often the problem occurs