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

SQL Server Backup Simulator Download Location


I have received a number on questions about the download location of the SQL Server Backup Simulator. I am writing this post to let you know that the v2.0 version of the SQL Server Backup Simulator is now available on the tigertoolbox GitHub repository under Releases.

We are also working on an updated version which will account for the latest VDC_Complete enhancements.

Another SQL VDI error that can mystify you


While performing a backup of a SQL Server using a backup software which uses SQLVDI, you might encounter a failure which reports the following failure hex code: 0x080070005. This is basically an Access Denied error message. If your backup software logs all the VDI API calls that are made, then you might even know which function call failed.

If you are using SQL Server Backup Simulator to simulate a VDI backup of the any SQL Server database with the same account that raised the error, you will find the following message in the Backup Simulator logs.

Trying to perform VDI test on a default instance
Error: VDS::Create fails: 0x80070005

The above message tells me that the IClientVirtualDeviceSet2::CreateEx function call failed. Now the important question here is why did the CreateEx function fail. If you look into the application event log, you will find an Error reported by SQLVDI (source) with the following text:

SQLVDI: Loc=CVDS::CreateEx. Desc=Create Memory. ErrorCode=(5)Access is denied.. Process=24456. Thread=13984. Client. Instance=. VD=Global\SQLBackupSim_SQLVDIMemoryName_0.

The above message tells me that I am creating a Global Shared Memory Object but I currently do not have the necessary privilege to create the object. Check the output of whoami /priv for the account that is running the Backup Simulator or the backup software and see if the following privilege is listed: SeCreateGlobalPrivilege. This privilege is granted by default to administrators, services, and the local system account. In case you are trying to use Backup Simulator on a Windows version which has UAC, then you need to run Backup Simulator using “Run as Administrator” option to avoid this issue. If the account trying to create this global memory object doesn’t have this privilege, then you can grant the privilege to this account using the following steps:

  1. On the Start menu, click Run. In the Open box, type gpedit.msc.

    The Group Policy dialog box opens.

  2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

  3. Expand Security Settings, and then expand Local Policies.

  4. Select the User Rights Assignment folder.

    The policies will be displayed in the details pane.

  5. In the pane, double-click Create global objects.

  6. In the Local Security Policy Setting dialog box, click Add.

  7. In the Select Users or Groups dialog box, add an account with privileges to run backup software or the Backup Simulator application.

Once this is done, you need to re-launch Backup Simulator or re-start the backup using the Backup Software as the security privilege will not modify the token that is currently being used by the backup software or Backup Simulator.

Another issue that you can run into even if you have the above mentioned security privilege is if you have multiple backups trying to create the same global shared memory object. In such scenarios, you would want to create a unique virtual device name. More information about the same is mentioned in the KB Article below:

903646    An application that uses the Virtual Device Interface feature of SQL Server 2000 to back up a SQL Server 2000 database may not back up the database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;903646

del.icio.us Tags: ,,