Category Archives: Backups

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

Recovery Model Behavior

Full Recovery Model – This recovery is the most common recovery model used for SQL Server. This model is required for database mirroring and availability groups.

  • Allows log backups and zero-to-minimal data loss after a disaster
  • All changes to the database are fully logged to the transaction log file
  • The transaction log will not clear data until a log backup has been performed, meaning that the log backup is what clears the transaction log
    • This means that log backups are required, otherwise the transaction log will continually grow until your drive is full

Simple Recovery Model – Commonly used when log backups and point-in-time recovery are not required.

  • Some operations can use minimal logging
    • All other operations are fully logged, as in the FULL recovery model
  • The transaction log will not clear data until a log backup has been performed, meaning that the log backup is what clears the transaction log
  • Log backups are NOT possible with this recovery model

Important -Disaster recovery is only possible using full and differential backups, so data loss will occur back to the most recent data backup. Recovery is not an appropriate choice for production systems where loss of recent changes is unacceptable.

Bulk_Logged Recovery Model – Commonly used to minimize log growth during bulk operations, while preserving the ability to take log backups.

  • Some operations can use minimal logging
    • All other operations are fully logged, as in the FULL recovery model
  • The transaction log will not clear data until a log backup has been performed, meaning that the log backup is what clears the transaction log
    • This means that log backups are required, otherwise the transaction log will continually grow until your drive is full
  • The time running in this mode should be minimized to reduce potential loss of data

Important – As mentioned above, the Bulk_Logged recovery model should not be used if there is the possibility of losing user transactions.