How It Works: SQL Server - VDI (VSS) Backup Resources

Last week I worked on an interesting case.   The issue was a NT Backup against a single volume containing 500 SQL Server databases.  It is rare to have so many databases on a single volume but it is possible and we had a case on this very issue.

When a VDI backup (BACKUP ... with SNAPSHOT to DEVICE ...) is issues it currently requires 3 threads to complete the backup.   The controlling thread that accepts the BACKUP command and 2 helpers to handle VDI completion and meta data streaming activities.

The formula is quite simple.     (Databases with files on the volume) * 3 = Required Workers.

 

For the issue I worked, 500 databases results in a requirement of 1,500 threads to complete the activity.

Step #1: SQLWriter (VSS Object) is loaded.

Step #2: SQLWriter enumerates all database file locations and determines the list of databases with files on the volume.

Step #3: SQLWriter issues the BACKUP ... with SNAPSHOT .. TO DEVICE command for all databases in the list.

BACKUP .. with SNAPSHOT will FREEZE the I/O for the database and report to the VDI consumer that the database I/O is frozen.   At this point the VDI consumer can do whatever action required to secure the volume snapshot.   Some solutions enable copy on write behavior, others split a physical mirror and other techniques can be employed by the hardware vendors.

Once the consumer has secured the data from the volume it uses the VDI interface to tell SQL Server that I/O can resume.   SQL Server THAWs the frozen database and completes the backup command.

Review the SQL Server Errorlog to see the series of freeze and thaw activities that take place.

Understanding that ALL databases on the volume have to be frozen before the disk volume is considered safe from writes is a key to understanding the issue I worked.    Since all 500 databases have to be frozen the required overhead for SQL Server is 1500 threads.

SQL Server optimizes this by leaving closed databases in a closed state.   There is no reason to open, recovery and freeze I/O for a database that is currently closed.   You should also be aware that SQL Server does not prevent the database from opening during the FREEZE / THAW window.   Do not rely on this as workaround to the issue.

This brought up quite a bit of discussion on how to change the design of SQLWriter and BACKUP ... with SNAPSHOT to reduce thread resource consumption.   Several design changes are under evaluation for future versions of SQL Server.

Until then you may need to increase your max worker thread setting to use VSS backup with large numbers of databases on the same volume.

Bob Dorr
SQL Server Principal Escalation Engineer

Comments

• Anonymous
  March 03, 2009
  PingBack from http://www.anith.com/?p=15139

• Anonymous
  March 18, 2009
  Hi Bob, Quick question if you can help on this formula (Databases with files on the volume) * 3 = Required Workers. We Created a Lab machine with 2000 databases Placed all the data/Log files on C:SQLfiles. When started backup of C:SQLfiles using NTbackup.exe 718 Hidden schedulers are Created with one thread each and only one active thread For all Hidden schedulers. (718 Hidden schedulers,718 Threads, 1 Active thread) What is the Logic Behind the creation of threads and Schedulers for VSS Backup. We restarted the SQLServer to destroy all the threads then Started the backup of Physical files of only One database in C:SQLfiles and we see 718 Hidden schedulers,718 Threads and 1 Active thread Why do we create so many schedulers for back up of the single database? Should I simply appy the formula ( Irrespective of number of databases selected we simply create threads based on number of databases on that Volume). Thanks for your help Karthick  

• Anonymous
  March 25, 2009
  I have implemented Split Mirror backups in many locations for Oracle and a few for DB2. I have a number of clients wanting to do this with SQL Server as well. Oracle has the ability to quisce the database and bring the log files current via RMAN scripts. I can not find a similar way to do this with SQL Server, but have found references to the VDI process. I am comfortable programming in C# and VB, is there a way to call these APIs in .NET? If so do you know of any samples that are available? A vbscript process would be the preferred solution for most of my clients though. Thanks for any info you may have.

• Anonymous
  July 28, 2010
  I have same issue like kds facing. We have requirement to do split/mirror backup and we would like put SQL database in backup mode or quies mode before splitting disks. How do we do that ? Oracle/SAP/Informix etc.. database easy to handle with Split mirror and I am struggling with SQL method. Any advice will be greatly appreciated

• Anonymous
  March 03, 2011
  Hi, I am using VSS to backup an SQL Express DB. I can't get a one answer, some say yes, other no. I have been told that using VSS to take a snapshot may lead to an unrecoverable DB since the log file and the mdf file may not necessarily be in sync. Does anybody know if this is correct? Chazzie / IT Mgr www.automaticdoorsusa.com

• Anonymous
  August 04, 2013
  Excellent explanation of how VSS backup works. Thankyou so much Bob for the article

• Anonymous
  February 20, 2014
  Does SQL server EXPRESS have the capability (or hooks) to perform a DB quiesce to allow the backup to be performed by the disk subsystem (I.e. NetApp snapmanager for SQL)?

• Anonymous
  December 04, 2018
  Hey Bob,Is this the same as the VDI_CLIENT_WORKER observed with AlwaysOn AutoSeeding? If so, does it have a configuration time-out values?Thank you.John Tee