Understanding VSS and SQL Server

In the early days of SQL Server and Windows, backups weren’t always easy to create. Sometimes you had to completely stop the SQL Server services to get full backups. It could be difficult to backup everything without a pre-backup script to stop required services and post-backup scripts to get everything running again.  In the last 10 years, however, a lot has changed. Microsoft has included a few technologies in Windows Server that facilitate the conversation between these different components to allow them to work better together. The main component behind all this success is the Volume Shadow Copy Service (VSS) introduced in Windows Server 2003. The idea is actually quite simple: create a Windows service that is able to coordinate the actions required to create a consistent shadow copy (also known as a snapshot or a point-in-time copy) of the data you want to backup. VSS operates at the block level of the file system. You can then use those shadow copies as your backup or you can take them to another disk or to tape as required, without affecting the running application at that point.

Basically, as SQL Server has data to write to disk, it notifies the VSS writer service. The service stores the change in a temporary location as the data is actually written to disk, and logs all the disk activity. The VSS writer is the component that guarantees that we have a consistent data set to backup.

Steps for completing a database backup (use the image above to see the flow):

  • Using your backup software (VSS requester), your software commands the start of the database backup (creating a shadow copy).
  • The database software (VSS writer) “freezes” the database, making sure that it is in a consistent state until further notice.
  • The SAN hardware (VSS provider) creates a snapshot of the data.
  • The database software (VSS writer) is notified that the shadow copy is done and it’s OK to write to the database again.
  • Your backup software (VSS requester) tells you that the shadow copy was successfully created.

The critical part of this operation, when the VSS writer is told to freeze and pause all writes, usually only takes a few seconds. During that period of time, all IO operations are simply queued and will only be completed only after the snapshot is complete. Because of that, creating a shadow copy does not significantly impact the performance of the production system. If the system is unable to queue the IO requests during that period or if it takes longer than 10 seconds, the shadow copy creation process will just fail and will have to be retried later. Usually the 10 seconds is enough time for VSS to commit all transactions to disk before telling the coordinator that it’s ready to go.

This technique makes VSS quite efficient. A snapshot only contains as much data as has changed since the snapshot started – you do not need an entire copy of your disk. Also, writes are only affected for used space – free space does not need to copy anything, as there is no original to preserve.

SQL Server isn’t the only application that uses VSS. A lot of applications make use of the VSS features to make those application work better.

VSS Writer Service Name Service Display Name
ASR Writer VSS Volume Shadow Copy
BITS Writer BITS Background Intelligent Transfer Service
COM+ REGDB Writer VSS Volume Shadow Copy
DFS Replication service writer DFSR DFS Replication
DHCP Jet Writer DHCPServer DHCP Server
FRS Writer NtFrs File Replication
FSRM writer srmsvc File Server Resource Manager
IIS Config Writer AppHostSvc Application Host Helper Service
IIS Metabase Writer IISADMIN IIS Admin Service
Microsoft Exchange Replica Writer MSExchangeRepl Microsoft Exchange Replication Service
Microsoft Exchange Writer MSExchangeIS Microsoft Exchange Information Store
Microsoft Hyper-V VSS Writer vmms Hyper-V Virtual Machine Management
MSSearch Service Writer WSearch Windows Search
NTDS NTDS Active Directory Domain Services
OSearch VSS Writer OSearch Office SharePoint Server Search
OSearch14 VSS Writer OSearch14 SharePoint Server Search 14
Registry Writer VSS Volume Shadow Copy
Shadow Copy Optimization Writer VSS Volume Shadow Copy
SMS Writer SMS_SITE_VSS_WRITER SMS_SITE_VSS_WRITER
SPSearch VSS Writer SPSearch Windows SharePoint Services Search
SPSearch4 VSS Writer SPSearch4 SharePoint Foundation Search V4
SqlServerWriter SQLWriter SQL Server VSS Writer
System Writer CryptSvc Cryptographic Services
TermServLicensing TermServLicensing Remote Desktop Licensing
WIDWriter WIDWriter Windows Internal Database VSS Writer
WINS Jet Writer WINS Windows Internet Name Service (WINS)
WMI Writer Winmgmt Windows Management Instrumentation

This list is far from complete.

You can manage the Volume Shadow Copy service using the Vssadmin command-line tool from an elevated command-line prompt. You can use this tool to run the following commands:
  • Vssadmin List Providers Lists registered Volume Shadow Copy providers. Windows Vista and above include Software Shadow Copy Provider 1.0.
  • Vssadmin List Shadows Lists existing volume shadow copies, the time the shadow copy was created, and its location.
  • Vssadmin List ShadowStorage Lists the volume shadow storage space currently in use, the space that is reserved for future use (labeled as allocated), and the maximum space that might be dedicated. This space is used to store changes while a shadow copy is active. The following sample output was generated using a computer that currently had about 3GB of files stored in a shadow copy, but that might allocate as much as 6.4GB.
  • Vssadmin List Volumes Lists volumes that are eligible for shadow copies.
  • Vssadmin List Writers Lists shadow copy writers, which support communicating with the Volume Shadow Copy service to ensure that files are captured in a consistent state. By default, subscribed writers include an operating system writer, a registry writer, a WMI writer, and a search service writer, among others. SQL Server also provides a Volume Shadow Copy writer.

  • Vssadmin Resize ShadowStorage Resizes Volume Shadow Copy storage. You can use this command to increase the maximum space that might be used by Volume Shadow Copy. Typically, this is unnecessary. However, if you discover that backups are failing on a computer because of an extremely high volume of changes during a backup, and Vssadmin List ShadowStorage reveals that the used Shadow Copy Storage space is at the maximum, you might be able to resolve the problem by manually increasing the maximum size.

Where the trouble begins if the writers stop or generate errors. You can execute the Vssadmin List Writers command to see if there are any errors. If there are errors, you can restart the service to help resolve the problem. The important step to take today is run the commands and become familiar with what is normal output. If you then run into issues with backups, you can run these commands again and see if you are getting abnormal messages.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s