SQL Server Snapshots : How to setup LUN's to use

[Steve Burkett]

Anyone able to offer any advice on the best way to setup your SQL Server LUN's to take advantage of the snapshot capabilities on the P4500 for short term backup/ rapid recovery purposes?  They sell the snapshot feature of the P4500's as being able to take fairly regular snapshots during the day and then be able to revert back to a good state within a short amount of time.

I would normally stick our database data files on one LUN, and it's log file on another LUN (as per normal practices of seperating your data file and log file on to seperate volumes/spindles) but as you can't snapshot both LUNs simultaneously, it'd give you an inconsistent database state on reverting to a previous snapshot?  Or does the application aware snapshot know to quiesce and snapshot both LUNs at once?

Other option I guess is to put both the data and the log file on the same LUN, but that would impact on performance and goes against the Lefthand best practice docs for SQL.

Any opinions out there?

Thanks guys!

Steve.

[Paul Hutchings · Accepted solution]

If it works like the Exchange VSS aware snapshots do, you have 2 volumes, DB + log, and when you do an application aware snapshot of the DB volume you'll see that a snapshot is automatically taken of the log volume.

I don't know if you take an application aware snapshot with the databases and logs on the same volume, but as you say you don't really want to be doing this from a performance and "best practise" perspective (though with virtualised storage such as Equallogic/P4000 you never know exactly which spindles your data and logs are on so perhaps it comes down more to OS/file system level problems than the "good old days" of separate physical RAID groups).

[Steve Burkett]

Aah, excellent, thanks for the confirmation there Paul.  Looks like for our main DB's we'll split the data and log files out in to individual volumes in that case.