DaveWentzel.com            All Things Data

Backups and Snapshots


SAN Snapshots
aka whole volume snapshots.
creates a point-in-time copy.
the process involves detaching one of the redundant storage members.  The process is called "breaking the mirror".
the snapshot is then the basis for a backup, remote copy, reporting server copy of your db, whatever.
More specifically the process usually involves the following steps:
  1. quiesce the application
  2. flush the file system
  3. take the snapshot
  4. backup the volume
SAN Snapshots and SQL Server
SAN vendors sell snapshot software that integrates with their SAN.  The software plugs into SQL Server to
  • temporarily stop disk writes
  • generates the SAN point-in-time copy

The whole process takes just a few seconds regardless of your database size. 

Some potential issues you should be aware of:

  1. most implementations have a performance hit on writes while the P-in-T snapshot is being backed up.  Why?  Writes to the db require the snapshot file to be shuffled.  this is very fragmenting.  Best to do benchmark testing to determine if this is an issue. 
  2. Some SAN vendors can only snapshot one drive at a time.  If you have filegroups spread against different LUNs, this won't work.  May not be an issue, though, if your SAN is virtualized.  Check with your vendor early.  Other implentations can only P-in-T snapshot if all of the drives are part of the same SAN controller. 
  3. If you using SQL Server partitions that are spread across different tiers of storage you may have issues.  SAN vendors usually only implement this on Tier 1 storage. 
Volume Delta Snapshots
this is similar in concept to SQL Server database snapshots. 
when a block's data is updated the original block is copied to a snapshot file. 
Remote Backup
You probably want this to be asynch. 
should probably use DWDM. 
Split Mirror Point-in-Time Backup
Assume you are running an active/passive cluster (probably the most common SQL Server configuration).  Assuming the devices are accessed in fully independent paths through the fabric then the best backup strategy is to backup the standby host of the cluster using split mirror point-in-time copy. 
Just some key points that cause confusion for DBAs:
  • a tape library can support multiple drives.  It could be a small tape stacker to a large jukebox. 
  • an autoloader can support multiple tapes
  • a tape drive is a stand-alone unit

Add new comment