Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

Accessing the SQL Server Virtual Device Interface via .NET (C#)

Rate me:
Please Sign up or sign in to vote.
4.76/5 (15 votes)
3 Jul 2007CPOL10 min read 101.1K   1.1K   35   33
This article is an introduction to the SQL Server Virtual Device Interface and how to access it via any .NET language

Introduction

The virtual device interface of SQL Server allows developers to define "virtual devices" for SQL Server to backup and restore databases and transaction logs. Having access to these virtual devices gives you access to the raw data that is passed into and out of SQL Server during a backup or restore operation. This access allows you to perform any number of tasks on this data though the most common tasks are compressing and/or encrypting the data before it is written to disk. Microsoft has published an API to access this functionality of SQL Server. The only catch is that to the best of my knowledge, there is no easy way to access this API via the .NET Framework. With that being the case, I set out to develop a Managed C++ component which will act as a bridge between the "Virtual Device Interface" (VDI) API and the .NET Framework.

Background

This article is meant to cover the basics of the VDI API and how to integrate it into a .NET application. As such, only a very small subset of the VDI API is covered in this article. There are many other capabilities of the VDI such as multiple input and output streams, and various performance tweaking opportunities. Once you have a firm handle on the technologies outlined here, you can see this reference for more details on the VDI specification.

Using the Code

General Approach

Since the API for the VDI is designed to be consumed by C/C++, it made sense to develop a .NET wrapper for the VDI using Managed C++. Since the nature of the VDI is streaming data into and out of the SQL Server, it made sense to me to use a System.IO.Stream for communication between the .NET application and VDI.

Interface

ExecuteCommand Method

The component in this article has one method named ExecuteCommand which, as the name implies, executes a backup or restore command to the VDI. This method takes two parameters as input and returns a void. The first argument for this method is a string which represents a normal SQL Server backup or restore command with one requirement. When specifying the device to which you will backup or from which you will restore, you must specify a VIRTUAL_DEVICE. This virtual device will be named within the component so you will need to use the {} string format syntax to specify the actual device name. For example:

SQL
BACKUP DATABASE AdventureWorks TO VIRTUAL_DEVICE='{0}' WITH STATS = 1

The purpose of this functionality is to allow for maximum flexibility when issuing commands to the SQL Server. Of course, in a production grade application, you would likely lock this functionality down, but for the educational purposes of this article, I left it open.

I would like to point out at this point that while the example above backs up a database, you can also backup transaction logs, perform differential backups, verify a backup with restore verifyonly or any other command that you would normally issue to SQL Server via T-SQL.

The second parameter to the method is any object that derives from System.IO.Stream. In the case of a backup, this will be the stream to which the raw bytes from SQL Server are written. The most basic usage would be passing a FileStream object for this parameter which would just write the entire backup directly to disk. The resulting file will be exactly as if you issued a normal BACKUP DATABASE command via Query Analyzer.

Another option might be to wrap your FileStream object in a DeflateStream object and pass the DeflateStream object as your parameter. What you will end up with is a compressed SQL Server backup. The advantage here is that rather than writing the entire backup to disk and then compressing that backup, you can simply write compressed data directly to disk which will save a step, disk I/O, time and disk space requirements.

In order to restore the database, you simply reverse the process by passing a DeflateStream object wrapped around another FileStream object that reads from your compressed file as the second argument along with the appropriate restore command.

CommandIssued Event

This event is fired whenever a command is issued to the component. If you intercept this event, you will see the exact command that is being issued to the VDI.

InfoMessageReceived Event

This event is fired whenever SQL Server sends back an informational message. For example, when backing up or restoring a database using the WITH STATS option, the "1 percent processed, 2 percent processed…" messages will show up in real time to allow you to monitor long running operations.

Internals

The ExecuteCommand Method

The first thing the ExecuteCommand method does is setup and configure the VDI for our use and creates one virtual device. VDI supports up to 64 virtual devices. This means that on the .NET end of things, you could modify this component to support up to 64 separate streams. This would be of use if you were planning to write the output to multiple disks, or if you would like to take advantage of multiple processors by processing data in multiple streams (and therefore multiple threads). The current ExecuteCommand method only supports one device and one thread for the sake of simplicity.

The next thing the ExecuteCommand method does is format the command. As I mentioned earlier, the command that you pass to this method must be in a "format string" format and specify VIRTUAL_DEVICE as the output medium. At this point, we need to generate a unique name for the virtual device. A great mechanism for this is to generate a GUID which is exactly how we name our virtual device in this article. Once we apply the newly created name of our device to the command, we spawn a thread to execute the command.

You may be wondering why we are not just using the asynchronous BeginExecuteNonQuery method of the SqlCommand object to execute the command. You could do this, but the InfoMessage event of the SqlConnection object which handles all messages back from SQL Server does not fire until the command is complete. This is a problem when you want real-time status updates in your client application. The workaround to this is to use the OdbcCommand in its own thread as the OdbcConnection object's InfoMessage event fires immediately.

After the command has been issued to the SQL Server, we finish configuring the device set and open our one device.

Once the command has been issued and our devices are configured, we call the ExecuteDataTransfer method from within the ExecuteCommand method.

ExecuteDataTransfer

Once the device has been configured and the command has been sent to SQL Server to perform the backup or restore operation, the ExecuteDataTransfer method handles the rest.

There are two parameters that are passed to this method. The first parameter is the virtual device that we created previously in our code. The second parameter is the Stream object that will either contain the data needed for a restore or will accept the data passed to us from a backup.

The main loop in this function simply calls the GetCommand method of the virtual device, and then analyzes the commandCode property of the command structure to determine the next appropriate steps.

The first command we look for is VDC_Read. This is the command we would expect to see during a restore operation. This is SQL Server's request for data. In this particular component, we first read the requested number of bytes from the stream that the .NET client application passed. We store the number of bytes read from the stream in the variable bytesTransferred for later use. Then we copy the ".NET bytes" to the memory location specified by cmd->buffer using the System.Runtime.InteropServices.Marshal.Copy method. At this point, we make sure that bytesTransferred is equal to the cmd->size parameter. This ensures that we transferred all of the data that SQL Server sent to us. If we get a match, then we can set the completionCode to ERROR_SUCCESS. If for some reason, we were not able to move that many bytes, we need to specify a completion code of something else to indicate the appropriate error circumstances. For the purposes of this exercise, we'll just use the ERROR_READ_FAULT completionCode if we cannot read the appropriate number of bytes.

The next command we look for is VDC_Write. This is the command we expect to see during a backup operation. In this particular case, we first copy the data from the cmd->buffer into a .NET array, and then we write that .NET array to the stream. In this case, we don't expect bytesTransferred to be anything other than cmd->size because if there was a problem writing to the stream, an exception would be raised. As such, we always set the bytesTransferred parameter to cmd->size.

The other commands are VDC_Flush which is just a cue for us to flush the stream; VDC_ClearError doesn't do much, but is required by the VDI specification and finally a default case to handle any unknown command that may come to us from VDI.

Once we've handled the command that has been passed to us, we call the CompleteCommand method of the virtual device. This gives feedback to the SQL Server and lets it know that we are ready for another command. This loop will continue until SQL Server is finished processing the T-SQL command (i.e. the backup or restore command issued to SQL Server) at which time it will issue the VD_E_CLOSE HRESULT indicating that it is finished and closing down the virtual device. At this point, the back up or restore operation is complete.

Points of Interest

One of the greatest opportunities access to the VDI affords us is the ability to compress SQL Server backups in memory before they are written to disk. By simply passing a compressed file stream as the input to the ExecuteCommand method, you will achieve the following benefits:

  1. Reduced disk I/O as less data is written to the disk.
  2. Reduced disk space requirements as the backup is compressed.
  3. Possible backup speed improvements if your compression engine is sufficiently fast.
  4. Almost certain restore speed improvements as there will be less disk I/O and decompression is almost always faster than compression.

The easiest way to implement compression is by using the DeflateStream and GZipStream objects in the .NET Framework. While there are certainly faster compression algorithms and implementations available, these are free and do a reasonable job of compressing your data.

There are two warnings when using these classes however. The first is that if your database has a lot of compressed data already (i.e. image files, compressed binary files, etc.) you may in fact see an increase in size. This is due to a verbatim implementation of the Deflate algorithm (GZipStream is simply a wrapper around Deflate with a CRC32 checksum). I won't go into the details of this, but suffice it to say that other implementations of Deflate optimize their code to minimize these effects.

The second issue which applies to both the DeflateStream and GZipStream classes is file size. The Microsoft documentation says the following about both classes:

This class cannot be used to compress files larger than 4 GB.

Now, with that said, I don't know if they mean the input file cannot be larger than 4 GB or the output file cannot be larger than 4 GB. In either case, I have successfully backed up and restored databases that are much larger than 4 GB. I have also created compressed backup files much larger than 4 GB using at least the DeflateStream. I have not tested this on the GZipStream, but I would expect much the same result. The point is that while this seems to work in my tests, Microsoft says that it doesn't. So if you have databases / backups larger than 4 GB, you may want to look for an alternate compression stream.

There are countless other potential opportunities available for example:

You can also use the CryptoStream object to easily encrypt any SQL Server backup before it is even written to disk ensuring that the unencrypted bytes of the backup never even hit the disk.

You could create an encrypted TCP stream and backup a database directly across any network without fear of the backup being intercepted.

The bottom line is that you can backup and restore from any .NET stream. This gives .NET developers of any language a very powerful tool to add to their tool chest.

History

  • 3rd July, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Founder Mini Cities, Inc.
United States United States
SQL Server Expert, Steve Abraham, http://SQLSteve.com holds 8 Microsoft certifications with his claim to fame being he passed all but one of the certifications within the span of 19 days – passing all tests on the first attempt.

The SQL Server 2008 exam was also passed on the first attempt but at a later date.

SQL Steve has been developing SQL Server based software for 13 years. Steve specializes in SQL Server and .Net Framework architecture, high availability, capacity planning, development, and performance tuning.

Steve has lead teams for some of the biggest and best known companies many of them dealing with Terabytes of data AND in crisis situations - including the U. S. State Department, T-Mobile, USA, Eddie Bauer, 1-800-Flowers and Spiegel. In 2006, Steve co-founded MiniCities, a hyper-local search company, based on the HYPERLOCAL ENGINE (TM), offering the first hyper local web franchises.

Steve Abraham is available for consulting on short term and possibly long term projects.
Contact: Steve Abraham
Email: steve@minicities.com
Phone: 813.300.0165

Comments and Discussions

 
QuestionRe-Instance Pin
Paul Roullier17-Oct-19 3:16
Paul Roullier17-Oct-19 3:16 
QuestionHow does it know what local SQL server Instance to use? Pin
Paul Roullier17-Oct-19 1:09
Paul Roullier17-Oct-19 1:09 
QuestionI can't Build it Pin
Member 1187255030-Jul-15 2:54
Member 1187255030-Jul-15 2:54 
QuestionSend two Backup commands Pin
CHUK_ANDREW13-Jul-15 20:51
CHUK_ANDREW13-Jul-15 20:51 
QuestionUnable to create and configure the virtual device set. HRESULT: -2147024891 Pin
akhil4946-Oct-14 6:02
akhil4946-Oct-14 6:02 
AnswerRe: Unable to create and configure the virtual device set. HRESULT: -2147024891 Pin
Member 1183415219-Jul-20 8:07
Member 1183415219-Jul-20 8:07 
GeneralClose missing Pin
warny25-Jan-11 22:12
warny25-Jan-11 22:12 
Generalworks great, possible to use from a SQL Server Agent job Pin
Member 309769520-May-10 16:06
Member 309769520-May-10 16:06 
GeneralRe: works great, possible to use from a SQL Server Agent job Pin
Steve Abraham20-May-10 16:14
Steve Abraham20-May-10 16:14 
QuestionHaving problem with creating the virtual device Pin
AOSMatt18-Nov-09 6:08
AOSMatt18-Nov-09 6:08 
AnswerRe: Having problem with creating the virtual device Pin
AOSMatt18-Nov-09 8:53
AOSMatt18-Nov-09 8:53 
GeneralRe: Having problem with creating the virtual device Pin
Timaty24-Mar-10 7:50
Timaty24-Mar-10 7:50 
GeneralRe: Having problem with creating the virtual device Pin
Steve Abraham24-Mar-10 9:32
Steve Abraham24-Mar-10 9:32 
GeneralRe: Having problem with creating the virtual device [modified] Pin
Timaty25-Mar-10 5:48
Timaty25-Mar-10 5:48 
GeneralRe: Having problem with creating the virtual device Pin
Steve Abraham30-Mar-10 7:07
Steve Abraham30-Mar-10 7:07 
GeneralRe: Having problem with creating the virtual device Pin
Timaty1-Apr-10 0:45
Timaty1-Apr-10 0:45 
GeneralRe: Having problem with creating the virtual device Pin
Steve Abraham6-Apr-10 8:21
Steve Abraham6-Apr-10 8:21 
GeneralRe: Having problem with creating the virtual device Pin
Timaty6-Apr-10 8:24
Timaty6-Apr-10 8:24 
GeneralRe: Having problem with creating the virtual device Pin
tabination28-Dec-10 13:47
tabination28-Dec-10 13:47 
GeneralSource code is incomplete Pin
The G28-Jul-09 4:32
The G28-Jul-09 4:32 
GeneralRe: Source code is incomplete Pin
Steve Abraham28-Jul-09 5:05
Steve Abraham28-Jul-09 5:05 
GeneralEZManage SQL Pro use VDI Pin
itayl27-May-09 3:11
itayl27-May-09 3:11 
GeneralReally interesting, but i can't compile de c++ part Pin
Stefano Straus16-Nov-08 23:33
Stefano Straus16-Nov-08 23:33 
GeneralRe: Really interesting, but i can't compile de c++ part Pin
Steve Abraham23-Nov-08 23:02
Steve Abraham23-Nov-08 23:02 
Generalthird party tool for VDI backup Pin
roby548-Nov-08 11:01
roby548-Nov-08 11:01 
http://www.futureitsoft.com

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.