Data Backup and Recovery

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

ericfitzpatrick

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

abhisek

Hi Eric ,

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

Regards,

Abhishek

ericfitzpatrick

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

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.

ericfitzpatrick

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.

Announcements
NetApp on Discord Image

We're on Discord, are you?

Live Chat, Watch Parties, and More!

Explore Banner

Meet Explore, NetApp’s digital sales platform

Engage digitally throughout the sales process, from product discovery to configuration, and handle all your post-purchase needs.

NetApp Insights to Action
I2A Banner
Public