February 16, 2010 at 2:32 am
I have a slightly unusual situation in as much as my sql servers are in a managed data centre - we ( the client ) do most of the config/management but I've run into an interesting situation i've never encountered with a SQL Server before and I'm asking for some feedback please.
The server is clustered x64 on w2k8 dedicated high performance san - 7 x 24 transactional system which I guess you'd classify as a billing system - it is deployed to clients world wide.
During benchmarking I discovered some anomalies in timing - I've now discovered that the data centre run a VSS file copy backup across the entire server every night. They exclude .ldf .md and .ndf files ( they say )
So does anyone use this method to secure their production sql server - especially 7 x 24 fully transactional systems.
Any feedback really appreciated.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 17, 2010 at 7:24 pm
Any chance they are using DPM?
Are they excluding mdfs too or just ndfs and ldfs?
February 18, 2010 at 3:54 am
no not using DPM. I personally think this is not the way to secure a production system, however, I wondered if anyone else was using this type of backup and what their experiences were.
mdf, ldf and ndf had been excluded I'm told, which sort of makes it rather pointless to take a volume copy of a lun which only has ldf , mdf or ndf files on it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 19, 2010 at 4:09 am
I found two of my newer servers getting 02:00 backups that I hadn't scheduled, with a brief I/O freeze logged beforehand. This turned out to be caused by HP Data Protector Express, a backup utility that uses VSS - hence the I/O freeze while the snapshot is taken. It is bright enough to log these backups properly in SQL Server so I am fairly content to let it do so. Unlike your situation, it does catch all the files.
I wouldn't want to rely on this mechanism instead of the flat .BAK files we schedule at 22:00 as part of our backup regime. But maybe my view owes more to superstition and preferring to be in control of the process - the HP utility is set up by our System Engineers, who simply see a program that will back up open files, rather than a data integrity mechanism for databases.
I assume many people are using this and similar Third Party backups, so should I be trusting what appears to be a well-behaved utility? Have I just got to get past the stage of feeling a bit uncomfortable about it? I still need the regular Trans log backups taken by SQL Server, but do I really need those .BAK files every night...?
February 19, 2010 at 7:56 am
The whole shadow copying thing scares me because if you have log and data on seperate drives you could introduce corruption like you would see with SAN snapshots. I prefer a tested and approved SQL Backup process like Native Backups in SQL Server or LiteSpeed from Quest.
The other big question I would have about VSS is does the backup ever get written to tape and stored off-site? If not what does your D-R plan look like?
February 19, 2010 at 9:13 am
David Levy (2/19/2010)
The whole shadow copying thing scares me because if you have log and data on seperate drives you could introduce corruption like you would see with SAN snapshots.
I had naively assumed the I/O freeze on the database would keep things sound, but thinking in terms of a utility that backs up drives I see your point. We do have data and logs on separate drives, so I wonder how (if at all) the backup deals with this. Since it is SQL Server-aware it ought to be clever enough, but I'd better find out. It seems I am justified in feeling uncomfortable about it - and in not relying on it - until I am a bit more clued up.
I believe the snapshot is a prelude to backing up to tape, so the files will get written there. This might not be the case for manually-initiated VSS actions.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply