SQL Server Database Backups using VSS versus native SQL Server

We are currently considering using Dell's EqualLogic Auto-Snapshot Manager/Microsoft Edition (ASM/ME) to backup our SQL Server databases. However, I am extremely skeptical of this, as ASM/ME uses Microsoft Volume Shadow Copy Service (VSS) to provide a framework for backing up and restoring data.

My concern is that while the VSS backup will technically work, there is the potential for database corruption using this method since the database isn't aware of the VSS backup and cannot ensure that everything is committed before the backup.

Do Microsoft VSS-based backups work acceptably for SQL Server database backups and restores? Are there other concerns or caveats I need to be aware of?

Solution 1:

VSS and SQL Server are well aware of each other. There is even specific service isntalled by SQL Server to support VSS and other backup technologies, the SQL Writer service. Vendors plug themselves into this ASPI, see A Guide for SQL Server Backup Application Vendors.

Solution 2:

The VSS backup approach is fine for use with SQL Server. I've used them many times and never had any issues restoring from them as the VSS provider is designed to work with SQL Server.

That said, my preferred approach is to always have SQL dumping backups with a more traditional FULL > DIFF > LOG schedule so that I've got full control of the process and can be sure that I have point in time restore capability.

In my last post we actually had both scenarios running in parallel - the SQL backups were for there for our general use and would be used for most recoveries. The snapshots were taken separately by our hosting company along with the whole machine so we had their backups available as a belt-and-braces fall back in case anything went wrong with our SQL jobs.