CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

Rate This
  • Comments 7

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.

 

image

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

Leave a Comment
  • Please add 2 and 7 and type the answer here:
  • Post
  • PingBack from http://www.anith.com/?p=15139

  • 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  

  • 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.

  • 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

  • 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

  • Excellent explanation of how VSS backup works. Thankyou so much Bob for the article

  • 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)?

Page 1 of 1 (7 items)