Introducing VDC_Complete for Backup and Restore applications using SQLVDI


Cross post from Tiger team blog.

In addition to its built-in functionality for backup and restore, SQL Server is supported by a large number of third-party backup solutions. SQL Server provides application programming interfaces (APIs) that enable independent software vendors to integrate SQL Server backup and restore operations into their products. These APIs are engineered to provide maximum reliability and performance, and support the full range of SQL Server backup and restore functionality, including the full range of hot and snapshot backup capabilities. In the current implementation of the SQL Server Virtual Backup Device Interface (VDI) protocol, the last message sent from SQL Server to the VDI client will be a VDC_Flush command. To prevent data loss, the VDI client must finish the backup before responding to the VDC_Flush command. There are certain situations like during backups of filestream enabled databases where a VDC_Flush command can be sent more than once during a backup operation. For certain backup applications, processing more than one VDC_Flush might be a challenge. If the VDI client responds to a VDC_Flush command without ensuring the backup is hardened when more data is coming after the VDC_Flush, SQL Server may truncate the transaction log. However, if the backup eventually fails on the VDI client, and the transaction log is also truncated, data loss might occur. If you don’t test your log backups at regular intervals, you wouldn’t figure out that you have a broken transaction log chain till the time you need to actually execute disaster recovery.

If you want to simulate a backup for your SQL Server instance, then you use the SQL Server Backup Simulator which is available on our tigertoolbox GitHub repository. The updated SQLVDI header files required to use VDC_Complete is available on the Microsoft SQL Server Samples GitHub repository.

Improvement

A new change was introduced in SQL Server 2012, SQL Server 2014 and SQL Server 2016 to allow backup and restore applications to know when SQL Server has completed sending the data to the client (VDI) so that it can perform the necessary end of backup tasks. KB3188454 has details about the change. This update adds a new VDI command VDC_Complete that indicates SQL Server has completed sending data to the VDI client. Therefore, the VDI client will be able to finish the backup before it sends response to SQL Server. This functionality allows the VDI client to fail the backup in case something goes wrong, and also prevents the transaction log being truncated without hardening the log backup by the client application.

The improvement was designed keeping backward compatibility in mind since backup applications can target multiple releases and versions of SQL Server at the same time. There can be four different scenarios which are outlined in the table below.

SQL Server Instance (VDI Server) Backup Application (VDI Client) Behavior
Supports VDC_Complete Supports VDC_Complete Client has to request VDF_RequestComplete while fetching the configuration to let the server know that it understands VDC_Complete. Once the server sends back a confirmation using the VDI configuration that it supports VDC_Complete, the client needs to execute the appropriate code path to handle VDC_Complete
Supports VDC_Complete Does not support VDC_Complete Since client does not request VDF_RequestComplete while fetching the configuration, server proceeds using previous behavior to maintain backward compatibility
Does not support VDC_Complete Supports VDC_Complete Server will return a NULL response because it does not support VDC_Complete for the requested feature VDF_RequestComplete
Does not support VDC_Complete Does not support VDC_Complete Behaves with legacy behavior of using only VDC_Flush

VDC_Complete is available for both scenarios backup and restore. If you want to use VDC_Complete for a database restore, then that is possible as well. If you choose to do so, then you will need to negotiate (as shown in the sample below) the use of VDC_Complete before the restore while fetching the VDI configuration.

Sample Code

Let us now look at the code changes required on the client side application which will help backup application work

I am going to use references from the sample simple.cpp file available in “SQL Server Virtual Backup Device Interface (VDI) Specification”. The download location is available in the references listed at the end of this post.

A handshake was implemented for the server and client to negotiate if VDC_Complete is supported by either. This can be done by the client requesting for the VDF_RequestComplete configuration. When the server receives this feature request, it will know that the client understands VDC_Complete and will respond accordingly indicating that it supports VDC_Complete.

      // Setup the VDI configuration we want to use.

      memset (&config, 0, sizeof(config));

      config.deviceCount = 1;

 

    // Request for VDC_Complete feature from the server

    config.features = VDF_RequestComplete;

Once the client receives the configuration, it needs to check the features available (see below) by determining if VDF_CompleteEnabled is set. Once the client determines that the server supports VDC_Complete, it can execute the code path which does the appropriate processing (end of backup book keeping, closing the backup etc.) after it receives the VDC_Complete message.

    hr = vds->GetConfiguration (10000, &config);

      

    if (!SUCCEEDED (hr))

    {

             printf_s (“\nError: VDS::Getconfig fails: 0x%X\n”, hr);

        if (hr == VD_E_TIMEOUT)

        {

                    printf_s(“\nError: Failed to retrieve VDI configuration due to timeout value (10,000 ms).\n”);

        }

        goto shutdown;

    }

      

    // Determine if the server supports VDC_Complete based on configuration parameters returned

if (!(config.features & VDF_CompleteEnabled))

       {

             printf_s(“\nServer does not support VDC_Complete.”);  

       }

       else

       {

             printf_s(“\nServer supports VDC_Complete.”);

       }

      

When the backup application receives a VDC_Complete, the backup application will need to harden the backup and complete book keeping tasks before it acknowledges success for the VDC_Complete message (see below). This will ensure that SQL Server does not advance the LSN without the client application hardening the backup which could lead to a potential data loss situation.

case VDC_Complete:

// Ensure that book keeping is completed.

printf_s(“\n\nSQL Server has signaled the end of the operation.”);

// Harden the backup and close the file

       completionCode = ERROR_SUCCESS;

       break;

Reference

How It Works: SQL Server Backup Buffer Exchange (a VDI Focus)

SQL Server Virtual Backup Device Interface (VDI) Specification

SQL Server Backup Simulator

Updated SQLVDI Header files required for VDC_Complete

Advertisements

A full house–We could not have asked for more


The turnout at today’s event had an overwhelming response today. A big thank you to everyone who attended today’s event. An overwhelming response… So thank you everyone who attended and made this UG meeting a grand success! This was our very own version of introducing SQL Server 2014 to the SQLBangaloreUG community!

We had a brilliant line-up of topics… Just to re-cap the day.. Here is a short summary..

The day started off with a keynote session from Anupam Tiwari who is Business Program Manager and closely works with the team that supports developer technologies at Microsoft. He talked about where IT as we know it today is headed tomorrow.

Sourabh Agarwal [Blog | Twitter] covered the performance enhancements in SQL Server 2014 and explained why this version is bigger, faster and stronger! Smile

Then I had the opportunity of explaining the new enhancements in backup and restore in SQL Server 2014. I talked about backing up to a URL i.e. a storage account in Azure, managed backups to Azure and encrypted backups. The slide deck from today’s presentation is available below.

This was followed by Pinal Dave’s [Blog | Twitter] session on “Performing an effective Presentation” One key takeaway from this session is that the art of story-telling can weave out a presentation which can keep the audience hooked! He demonstrated this during his session and kept the audience awake after lunch! Smile

Then we had Balmukund Lakhani [Blog | Twitter] talking about InMemory Enhancements with SQL Server 2014. You can follow his SQL Server 2014 learning series to gain in-depth insights into the various enhancements that he talked about.

His session was followed by Vinod Kumar [Blog | Twitter] who talked about the Some more lesser known enhancements with SQL Server 2014 where he talked about managed lock priority, delayed durability and more.

And last but not the least was Kane Conway – (Support Escalation Engineer at Microsoft) who talked about Power Packed – Power BI with SQL Server. If you have not played around with PowerBI, then you should start now! Presenting data with compelling visualizations just got a new definition.

image image

It was a house full event and I had to put in two pictures to showcase the turnout today! It is not possible to have a successful event without the audience participation. We personally want to apologize to folks who came in late and had to return because the hall was overcrowded and the seats were full. Please do come in early for next UGs for sure.

A special thanks to Microsoft GTSC for giving us the space and the support till date. Also a special thanks to SQL Server Product Marketing team for sponsoring this Launch Event. Finally, a special thanks to team Pluralsight for supporting the UG till date and giving us numerous gifts as giveaway at the event.

The presentation that I used at the event today is available below:

Differential Base and VSS Backups


VSS backups are a common way of taking SQL Server database backups using various backup utilities that are available today. However, one of the not commonly known facts is the differential backups that can be performed using VSS APIs. The SQL Server VSS Writer service is the one which facilitates the backups of SQL Server databases through VSS APIs.

The SQL writer supports differential Backup/Restore through two VSS differential mechanisms: Partial File and Differenced File by Last Modify Time.

  • Partial File The SQL writer uses the VSS Partial File mechanism for reporting changed byte ranges within its database files. 

  • Differenced File by Last Modify Time .   The SQL writer uses the VSS Differenced File by Last Modify Time mechanism for reporting changed files in full-text catalogs.

For more information, read the technical reference document on VSS backups and SQL Writer.

Once of the first points that you need to remember with VSS backups of your SQL Server databases is that a non-copy only VSS backup can break the differential chain. For more information on VSS Copy Only backups, read the blog post on the same topic.

Let me show that with an example. I will start with three full backups of my Adventureworks database in the following order:

1. Database Full backup using VSS backup
2. Database Full backup using native SQL backup
3. Database Full backup using VSS backup

When I look into the system catalogs, I find that the Differential Base LSN changes with each backup done. The differential base time is reported in GMT which is (+5:30 for my server’s time zone). The data shown below was retrieved from the sys.master_files output after each backup.

Differential Base LSN Differential Base Time
58000000764000000 5:09:54 PM
58000000767800000 5:11:55 PM
58000000770500000 5:12:21 PM

I validated the above entries with my SQL Server Errorlog as well:

2013-01-05 22:39:54.310 spid55       I/O is frozen on database AdventureWorks. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2013-01-05 22:39:54.330 spid55       I/O was resumed on database Adventureworks. No user action is required.
2013-01-05 22:39:54.340 Backup       Database backed up. Database: AdventureWorks, creation date(time): 2012/09/21(20:50:44), pages dumped: 21978, first LSN: 58:7640:65, last LSN: 58:7668:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{24635C74-D8AD-475C-88AE-831D0F31AD79}’}). This is an informational message only. No user action is required.

2013-01-05 22:42:04.650 Backup       Database backed up. Database: adventureworks, creation date(time): 2012/09/21(20:50:44), pages dumped: 22027, first LSN: 58:7678:37, last LSN: 58:7695:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘<directory>\adw.bak’}). This is an informational message only. No user action is required.

2013-01-05 22:42:21.300 spid55       I/O is frozen on database AdventureWorks. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2013-01-05 22:42:21.310 spid55       I/O was resumed on database Adventureworks. No user action is required.
2013-01-05 22:42:21.310 Backup       Database backed up. Database: AdventureWorks, creation date(time): 2012/09/21(20:50:44), pages dumped: 21978, first LSN: 58:7705:37, last LSN: 58:7722:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{17FAEB54-A411-4E16-BD96-FF5DE627CEDB}’}). This is an informational message only. No user action is required.

Now when I take a differential backup of the AdventureWorks database, the differential base guid is reported as 9EAAC560-B5AB-4DE6-A44B-A52E8D5BD82B and the differential base lsn is reported as 58000000770500037. This matches with the differential base guid that is reported by sys.master_files. However, this differential backup cannot be restored as the VSS backup taken was not a valid VSS backup.

The screenshot below shows the sequence of backups.

image

To summarize, if you have an application which takes VSS backups of volumes which contain SQL Server database files, then it could inadvertently invalidate your differential backups of your SQL Server databases even if you are not backing up SQL Server database files. If your VSS backup application does not use the COPY ONLY option, then the full backup of the database files taken by the snapshot backup of the volume will become your new differential base.

Reference:

SQL Server records a backup operation in the backupset history table when you use VSS to back up files on a volume
http://support.microsoft.com/kb/951288

The Ntbackup.exe operation may break the differential backup chain of a SQL Server database and may invalidate the differential backups when you perform a snapshot backup of the database
http://support.microsoft.com/kb/903643