Backup compression and Virtual Device Interface (VDI)

Backup compression and Virtual Device Interface (VDI)

  • Comments 6

People often ask if they can get native SQL Server backup compression using VDI interface. The simple answer is 'yes' but there are few things you need to be aware of as described below. You can get details on VDI by clicking link-to-VDI-Specifications.

Within the VDI protocol, the backup application specifies a TSQL backup command.  This means that the backup application can use any TSQL command line options including the option 'WITH COMPRESSION'. In this case, the SQL Server will send a pre-compressed (using SQL native compression algorithm) data stream. However, if the server default is set to always compress, the SQL Server will not send compressed data stream to VDI initiated backup unless the application specifically requests it. The reason for this logic is that some backup applications depend on reading and interpreting the backup stream, and if SQL Server sends compressed bytes, it can potentially break the backup application.  For example, there are 3rd partly backup tools that allow extracting an object (i.e. a table) from the backup and if the backup is compressed in proprietary format, the tool will not be able to extract the object. On the other hand, if the backup application explicitly requests the backup compression, then we know that the application is expecting an opaque, compressed stream.  Secondarily, some backup applications do their own compression and may prefer to use their own algorithm.  That’s perfectly OK too.

Thanks

Sunil Agarwal and Kevin Farlee

 

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • SQL 2008 doesn't use VDI, It can't use mutiple CPU threads for doing backups or restores like Litespeed, so cannot be faster than Litespeed.

  • That is an incorrect statement.  SQL2008 includes VDI just like previous releases for years and years.

    VDI is nothing more than an alternate method for transferring data to its final destination.  It is at the layer where we write to the backup media.  If the device is a virtual device (VDI) then the data is written to a shared memory area, with a 3rd-party app like Litespeed on the other side.  If it is a physical device, we just write the data.  So VDI really doesn't apply to this discussion.

    The threads within the SQL engine used for backup and restore are THE SAME threads and code, whether the backup device is one or more physical files, or an app like Litespeed.

    The compression engine used internally is very efficient, and has a pool of threads doing compression on buffers there as well.

  • SQL Rocker, SQL Litespeed uses multiple threads like you would use multiple files in a native backup. It simply hides this and writes all the files to a single file. For each thread that is specified a VDI devices is setup.

  • Does SQL native backup use VDI to backup the DB to NAS volume. As per the article - support.microsoft.com/.../304261 i need to use VDI to backup the DB to NAS volume. That means that i need use 3rd party vendor tools (which use VDI - like litespeed)

  • The KB article is outdates (As of SQL Server 2008 R2, traceflag 1802 is not required to create a database on NAS/SMB-attached storage), and the comments about VDI being required for correct backup are just flat wrong.

    I will update/re-write the KB article.

    What is a risk is if you were to use a backup utility which runs on the NAS box to copy the database pages. In that event, there would be no coordination to avoid torn pages etc.

    All of that is orthogonal to VDI.

    VDI is nothing more than an "output adapter" to the standard TSQL backup mechanisms.  VDI is the interface between the SQL Engine and  a 3rd-party backup app.  It sits at the stage in the data pipeline where a standard TSQL backup would be writing to an open file handle.  Upstream of that there is no difference.

    So, to be clear:

    You do NOT need to use a VDI-based backup solution to correctly back up a NAS-attached database.

    You DO need to use a SQL-based backup solution to correctly back up a NAS-attached database.

  • That first comment is the dumbest thing I've ever seen

Page 1 of 1 (6 items)