SQL & Snapshots - some clarity/help please

[doubledrat]

hi

i've done some reading on this, but there seems to be little definitive, hence my question.

1. To have a SQL consistent backup, my understanding is that a standard vmware snapshot will do this as it kicks of a SQL level backup before the snapshot and then quiesces the file system before taking the actual backup.

2. The issue is that SQL itself isn't told to clear down it's logs, so it doesn't know it's been backed up.

are 1 & 2 correct?

if they are, surely there must be a way of scripting the clearing down of the sql transaction logs too? I've found 2 products that cost between $2000 and $8000 that claim to do it. but can't believe that level of investments is actually necessary?!

is there a way of scripting this?

[Hekim]

From my understanding, you'll need to manually set up the coordination of snapshots and SQL quiescing (read: shutting down), so 1 is incorrect. SQL is not a simple thing to back up, so the answer to 2 isn't very straightforward.

I'm going to assume you're using MS SQL server, here. When you're doing a backup of a SQL server, you're probably better off simply using a backup agent, script, or even the built-in tools to copy the SQL server backup files to your backup media. SQL's built in backup system will create a full and/or transaction log backup file anywhere you tell it, and that file is all you need for a restore. I can't think of a scenario in which adding snapshots on top of that would cause anything but extra work for little benefit.

Make sure that you become familiar with how the disaster recovery model and the different options work, because it's not particularly intuitive if you don't have much experience with databases. It's quite easy to fill your drive with transaction logs if you miss a checkbox. Everyone's environment and requirements vary, so there are decisions you'll need to make about how much granularity you need, as well as the recovery window. There are a lot of resources out there about backing up SQL. Microsoft's are usually thorough but heavy. Here's one link for SQL 2008 R2:

And NO, you don't need to spend thousands of dollars on this.

[doubledrat]

Hi

I should have said, that I am using netapp snapmanager for VI. This will back up all my VMs in a matter of seconds. this gives OS level cover, but not app level. I would like to cover the app level as well. I don't really want to have to deal with multiple backup products if I can help it. I'd like to be able to revert to snapshot start up the machine - job done.

my reading indicated that the snapshot process backed up SQL in a VM, so I tried it, and sure enough in the event viewer, each DB show something like

18264 :

Database backed up: Database: pubs, creation date(time): 2000/08/06(01:40:58), pages dumped: 1, first LSN: 11:275:1, last LSN: 11:277:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{4F1EF655-3CDC-4F5E-AA2D-46336948B00D}5'}).

so that would give credence to the assertation that a snapshot does indeed trigger a sql level backup (as VSS is designed to do).

[Hekim]

Ahh, I didn't know that you were using a third party snapshot manager. I'm not particularly familiar with NetApp's manager, but it seems as if it is indeed performing the backup. Still, you'll want to keep an eye on those transaction logs (or set SQL to "simple" backup), and try testing it in a lab setting.

I do feel, though, that I need to emphasize: snapshots alone are not backups; they're copies.

[doubledrat]

to be pedantic, the above behaviour is exhibited from a normal vmware vsphere snapshot (which netapp instigates). the netapp snapshot is a filesystem-level snapshot that requires the VM to be in a state that it can recover from in the event of a restore of that snapshot.

my goal is that all VMs will be in this "happy" state when the filesystem takes its snapshot (or at least have a vmware-level snapshot of the machine in it's happy state!)