5

I'm looking for a way to flush all pending writes to disk in Windows, then buffer all future writes until a command gives the go-ahead.

I'd like to flush SQL Server writes and Windows system writes, then buffer both.

To be clear about what I'm doing:

I use Windows 2008 R2 and SQL Server 2008 R2 on EC2. I run an hourly snapshot on the drive these reside on. When nothing critical is changing, these snapshots come out just fine - but every now and again I get a bad snapshot. Worst-case scenario, in the event of drive failure (EBS failure technically), if I have 3 bad hourly snapshots I've lost 4 hours of data.

The snapshots already solve the fast, differential backup, and fast, easy restore - so all I'm looking for is a way to flush everything so it's in a consistent state on disk, then suspend all writes until the snapshot is complete. I'm happy to write some code for a service I call into to make this happen, but I need to know what APIs/commands I need to write against in order to get the task done.

I'm aware that I could create a separate volume which I continually run Windows Backup to and then snapshot that, but that significantly lengthens the backup process and feels like a hack. I know Windows and SQL Server are both very good at buffering writes, so this seems like something I should be able to accomplish in-place.

Ideas?

5 Answers 5

6

An option for you is to leverage Shadow Copy in Windows. The Shadow Copy process itself drains any write-buffers before committing its own snapshot. Schedule it to take a snapshot a minute or two before the EC2 snapshot. That way when the EC2 snapshot fires you have a recent, consistent copy already in the system.

6
  • If utilizing VSS with SQL installed, you need to verify that it doesn't bork your existing backups. VSS triggers a backup event for SQL and can hose your backup chain. May 11, 2011 at 21:10
  • Did not know that about SQL. Good to know.
    – sysadmin1138
    May 11, 2011 at 21:14
  • I've seen a lot of posts about VSS screwing up SQL Server - it seems like something to avoid. I currently have that service disabled, and would prefer to use some other method of ensuring SQL integrity... ideally flushing writes then telling them to buffer until I say go somehow. May 11, 2011 at 23:25
  • Novice here - I see now that VSS (Volume Shadow copy Service) is really the only game in town. May 12, 2011 at 0:26
  • @Holocryptic I actually would prefer it overwrite the SQL backup since at best it's writing a backup I don't need (the Snapshot is the backup) - I just need Windows and SQL quiesced long enough to start the Snapshot. Is there a way to trigger a SQL quiesce without actually performing the backup? May 12, 2011 at 0:32
5

I can't answer about how to lock the disk so Windows doesn't write or force the flush so this will be a half answer, but there is one relevant piece of information your question doesn't note:

The ec2-snapshot process is instantaneous. You don't have to wait for it to finish! Once you start it, you can start using your disk again and the snapshot will still get the data at the point of the snapshot, not the current state. This happens because once you tag it for snapshotting, all NEW data written to the disk is queued in a kind of overlay disk and the data at the time of you asking for a snapshot is preserved.

That should make it easier for your other problem. All you need to do is flush to the disk to get everything consistent, then pause for a couple seconds while you make sure the snapshot API returned a success code so you know the process kicked in, then you can go back to your writing.

1
  • That's an interesting detail. Since the Snapshot service itself does not instantaneously kick off a job though, I still need to buffer things around when I request it and when it responds with having started, though. So basically I need to get the disk into a consistent state, buffer writes, request the snapshot, then let writes continue once the Snapshot has begun. Essentially the same task, but it does mean I can buffer for a shorter time, which is a big add. May 11, 2011 at 23:24
3

I'm going to add a separate answer to address some of your comments. VSS is a good way to go if you're trying to quiesce the data. In fact, MSFT DPM uses that subsystem to great effect with SQL from what I understand. Of course they had dedicated teams to hash things out.

VSS won't screw SQL up, but it can (and has) interfered with SQLs native backup methods (see my answer to a question here for some more insight). You need to be very aware of what you are trying to accomplish, what VSS is doing, and whether or not the two are coinciding.

You can write something to call the VSS APIs natively (don't ask me how, I'm not a developer), or you can use something like vshadow.exe to make and manipulate shadow copies for you. Vshadow.exe is available in the MSFT SDK; make sure you get the right version for your OS.

Depending on your SQL recovery model, SQL will react differently when VSS is called. It's been a while since I've dealt with the nitty gritty of SQL and VSS, but from what I remember if you have Simple Recovery Model set, the logs will truncate when VSS is called. In Full and Bulk-Logged, this will not be the case and you'll need to do something to manage the size of the logs.

And you probably know this, but test, test, test. Your snapshots are only good if you can actually restore from them.

1
  • Great answer. Thanks for tying in the related question. May 12, 2011 at 15:30
2

I'm not an EC2 expert by any means but, outside of EC2, if you want to create consistent backups using storage snapshots you have to quiesce the filesystem before the backup is taken.

The SQL Writer Service must be running (see http://msdn.microsoft.com/en-us/library/ms175536.aspx for more information on the SQL Writer Service), and then the backup would use the VSS COM APIs to freeze IO, take the snapshot then thaw it again (lots of detail here: http://msdn.microsoft.com/en-us/library/aa384589%28v=vs.85%29.aspx).

SAN vendors generally integrate calls to the VSS API into their snapshot routine and this would be done via an agent running on the server.

I can't answer whether EC2 actually does this call for you, or whether it is something you would need to write yourself (use the VSS APIs to freeze IO, then call the EC2 API to take the snapshot), but hopefully my answer gives you a few pointers.

3
  • EC2 doesn't have much in the way of niceties for Windows - including not performing this operation. May 12, 2011 at 0:31
  • This appears to be the key - using VSS API calls to quiesce both Windows and SQL. Digging into the docs now. May 12, 2011 at 0:57
  • 1
    This opensource project allows you to interact with the VSS from .net alphavss.codeplex.com . Thought you might find it useful
    – Ameer Deen
    Aug 4, 2011 at 23:45
0

AutomatiCloud does exactly what you are looking for. It has an optional VSS-agent that uses the VSS snapshot-provider delivered by MS-SQL, MS-Exchange or others.

During backup it freezes/redirects I/O on the disk(s), triggers the EC2 snapshot and then allows I/O again.

The whole process is controlled in an easy to use windows GUI without any need for scripting.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .