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.
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.
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”);
// 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.”);
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.
// 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;