Data Backup and Recovery

Using "crash consistent" snapshots for MSSQL backup/recovery?

ericfitzpatrick
4,719 Views

Oracle has the ability to use crash consistent snapshots as valid backups (TR-3858) which means that entire volumes of Oracle databases can be easily backed up and restored with little intrusion into the guest OS at the time of the snapshot. 

I am doing my due diligence on MSSQL and was wondering if there is anything that can be done to provide the same capability for MSSQL.  While we do have the SMSQL software, we are looking for an easier methodology to deploy, since we have literally hundreds of MSSQL databases.

The details:

Running NFS datastores to VMware

All database/OS files are contained within VMDKs on the same volumes (no consistency groups needed)

Various MSSQL versions...

Average database size ~300Gb

Thanks!

-Fitz

4 REPLIES 4

ericfitzpatrick
4,719 Views

BTW, I spoke with MS SQL Development, and, currently, there is no way of doing this, nor is there any means of doing this on the roadmap.

abhisek
4,719 Views

Hi Eric ,

I would like to inform you that SQL Server is application consistent. SMSQL does generate application consistent snapshots.

Regards,

Abhishek

ericfitzpatrick
4,719 Views

I apologize - it seems that I left out a few details:  While we can get a crash consistent backup at any time via snapshots, when you restore MSSQL from a crash consistent backup (snapshot), the database opens without any chance of applying transaction logs.  If the ability to apply logs is required, the backup has to be application consistent (e.g., using.SnapManager for SQL).  Oracle and DB2 do *not* have this issue.

Sorry I omitted the important details.

-Fitz

radek_kubka
4,719 Views

Providing you will be lucky enough to recover from a crash consistent MS SQL snapshot - it doesn't have the same 4k block granularity / alignment as Oracle.

Public