July 27, 2013 at 3:28 pm
Recently, our company has decided to go with CommVault as an enterprise backup and archiving solution. As a result, a bit of a riff has occurred between the DBA team and the Storage team. The Storage team believes they should be in charge of doing backups and restores.
What I'm looking for here is how other company who use an enterprise backup and archiving solution? Does the DBA team still handle backup and restores or is this a function handled by a separate team?
Also any feedback or discussion specifically towards the CommVault software and how it performs against products such as Red Gate SQL Backups would be appreciated.
thanks
July 27, 2013 at 7:29 pm
I guess I'd have to say the horse is already out of the barn on which backup software to use. That's a battle I wouldn't bother fighting.
So far as to who the backups and restores are done by, we have a system at work that has been working wonderfully. As the DBA, I backup the systems to disk. The "storage team" takes care of giving me the disk space I need (yes, I justify it... it's part of being on a team) and they take care of copying my backups to tape, tape rotation, etc. It works out really great because I frequently have to make ad hoc backups because of upcoming changes and other special requests plus, simply because I know much more about SQL Server, backups, and restores than the rest of the storage team combined (let's see them do a "piece-meal partitioned restore"... they don't even know such a thing exists), I'm able to create huge savings insofar as backup time, restore time, disk space, tape space, tape backup time, etc, etc, etc. Of course, you have to consider that the software they bought (CommVault) might also be able to do all of that, as well
So far as the growing rift that you speak of, that's a damned shame. My apologies to both groups but that's starting to sound a little like empire building by both groups. Both groups need to drop any personal bias and figure out what's the best for the company (which means what's best to protect the data) and just because there might be more of them or the fact that you're the DBA doesn't necessarily mean that's best for the company. In my humble opinion, "best for the company" is going to be something like what I described in my second paragraph above where both teams do key parts of the job. The DBA is left to figure out which backup/restore methodology is best at any given moment and the "storage team" makes damned sure that what the DBA saves to disk is correctly backed up to tape. If a restore is necessary from any time in the last 2 days, the DBA should be the one to do it and from disk. Anything further out than 2 days will need a coordinated effort from both teams to retrieve the proper tape(s) and restore them to the database.
I also believe that you, as a DBA, will probably know what's best to protect the data and what's best for the company when it comes to backups and restores. That, notwithstanding, we DBAs sometimes don't get things done our way. You have 3 choices if they decide to go in a manner you don't agree with...
1. Look at the bright side of having one less thing to do and throw your arms up in the air and point at the "storage team" if an emergency restore doesn't work. I, of course, don't recommend that method.
2. Find a different company that will let you be large and in-charge when it comes to backups and restores Of course, you'd better not fail... ever.
3. Be a team player. You gave it your best shot with your recommendations and they didn't follow those recommendations. If they decide the "storage team" is going to be totally responsible for backups and restores, make sure they understand that they need to practice emergency recovery from off-site stored tape at least once a month and that everyone has worked out how to identify which databases need Point-in-Time (PIT) recoverability, which databases need "Read Only" recoverability, and everything in between including "split" databases where some file-groups are "Read Only" and some file-groups need PIT recoverability. The company should also identify "critical" file groups for Piece-Meal restores (to get "back in business") and file-groups that can be "On-Line Restored" after the critical parts of the system are recovered.
I'm sure that neither team is trying to turn things into a rift but sometimes the desire to do a good and proper job causes such rifts. You ARE the DBA... Be "the MAN!" that makes it work no matter what...
... and, if you elect option 3 above and if you're like me, you'll have a "secret backup" system squirreled away (even if it takes 2 days to write it to a portable USB drive) even if you have to buy the bloody drive yourself just in case something goes wrong... goes wrong... goes wrong... (Whack! Bang! Smack! Ugh! Hurl!!!) goes wrong. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 29, 2013 at 6:28 am
A couple months back, I changed jobs...
My new employer uses (and has been using) CommVault for several years to handle all the backups, and here's what I've seen / discovered / realized:
1. Commvault does support and can do "native" SQL backups. When such a backup is running, the actual SQL Query text is "backup database [whatever] etc, etc"
2. It's either not possible, or a PITA to do a "ad-hoc" backup in CommVault. Refreshing my QA environment I just take a copy-only full backup in SSMS, it's less trouble.
3. Restores from Commvault aren't too bad, although the wording in the message boxes can lead you to think it's not going to work like you expect because you haven't had the chance to specify the restore location yet so it looks like it's going to restore into Production and did you maybe miss an option? Nope, you select what to restore, then hit OK, THEN you can specify the where (another server?,) name (yes, you can rename the DB,) the point-in-time, and whether to leave the DB in recovery or not.
4. Commvault requires JAVA. Deal with it... 😉
All told, so far it's not as bad as I thought. It will definately be to your advantage to work with, rather than fight with, the Commvault Admin. My CV Admin and I get along, she's got some limited knowledge of SQL, and I do my best to explain *why* I want the backups done in a particular way, while still trying to also work within the limitations she has (storage, storage, storage. The employer wants even a lot of the desktops backed up nightly) The backups are kept on local media (I think a SAN array) for 30-days, then they drop to tape and are moved off-site.
So, nice part from my point of view, I can pawn off backup problems on someone else (unless it's my server causing the problems,) downside is, that someone else may not be able to resolve the issue as quickly as I would prefer. (CV here was down for almost a week because of a problem with the CV library. Admin worked on it for 16-20hrs at a time with CV support before getting it fixed. Not a fun week, worrying that I'd need a restore of recent data, with no backups. Even more annoying, myself and the Oracle admin thought we had fixed an on-going Oracle backup issue to CV, and couldn't test until the library problem in CV was fixed...)
So, similar to what Jeff suggested in option 3, rather than fighting the storage team, work with them. Even more, get on the CV Admins good side, explain that you would like at least enough access to CV to be able to add / remove DBs to the backup clients as needed, run restores of your DBs, etc, without having to ask / wait for the CV Admin / Storage team.
So far, that's working for me.
Jason
July 30, 2013 at 7:58 am
I'm cool with anyone handling backups. As long as they get done and (the kicker) as long as we can successfully restore them in a timely manner. If it takes hours to track down a backup (it's happened to me) or I don't have access to the backup software at 3AM (that's happened) or the "backups" work great but the database either won't restore (happened) or restore corrupted (happened), then we are RAPIDLY renegotiating how backups are done (probably following Jeff's model). I don't have a territory to defend. I have processes and protections for the companies information that I have to support. How I do it just doesn't matter. That it gets done is what matters.
With any 3rd party backup utility (and that includes Red Gate), I would put it through a pretty thorough set of backups and restores, as many scenario's as you've run into recently. Be sure. That's all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 11, 2013 at 1:25 pm
We have IBM team handling servers and backups
August 12, 2013 at 3:00 pm
With around 200 servers and lots of databases, se (the DBAs) do the backups and restores of the databases. The Server group backups up our files to tape and restores the file when we need it.
-SQLBill
August 12, 2013 at 10:27 pm
Snigdha Vartak (8/11/2013)
We have IBM team handling servers and backups
Who is handling test restores? Are they even being done?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2013 at 6:58 am
Our responsibilities are nearly identical to Jeff's. The DBA's perform the backups to disk and the storage team is responsible for copying the files to and from tape. The DBA's also are responsible for restoring the databases.
In testing the process, we randomly pick a day(s) for the storage team to copy files off of tape back to disk for us to restore. This helps us validate the tape backup process and our backup/restore process.
Everyone is fine with their specific roles in this regard as they see it's best to let each specialist perform their specific work.
Steve
August 13, 2013 at 9:07 am
We also share responsibility:
DBA's handle Database<->Disk and disk retention/aging, automated and ad-hoc and PITR.
Backup team handle Disk<->Backup software (disk and tape), automated and ad-hoc, and tape retention/aging.
DBA's get requests, and if the backups files are no longer on disk, DBA's send the Backup team a request to get the files from the backup software.
If a non-DBA group wants to handle backups and restores, and talk to them about what happens if someone kicks off a restore they shouldn't have and causes data loss/production impact. Be mindful DBA's can cause these same problems, too. Also, have them test ad-hoc Point in Time Recovery to a different database name (i.e. "I deleted X by accident, can you get it back")
August 21, 2013 at 6:01 pm
Nadrek (8/13/2013)
We also share responsibility:DBA's handle Database<->Disk and disk retention/aging, automated and ad-hoc and PITR.
Backup team handle Disk<->Backup software (disk and tape), automated and ad-hoc, and tape retention/aging.
DBA's get requests, and if the backups files are no longer on disk, DBA's send the Backup team a request to get the files from the backup software.
If a non-DBA group wants to handle backups and restores, and talk to them about what happens if someone kicks off a restore they shouldn't have and causes data loss/production impact. Be mindful DBA's can cause these same problems, too. Also, have them test ad-hoc Point in Time Recovery to a different database name (i.e. "I deleted X by accident, can you get it back")
This is how I've usually done this. DBAs manage the db and get files to disk. The backup team takes it from there.
August 22, 2013 at 7:50 am
Steve Jones - SSC Editor (8/21/2013)
Nadrek (8/13/2013)
We also share responsibility:DBA's handle Database<->Disk and disk retention/aging, automated and ad-hoc and PITR.
Backup team handle Disk<->Backup software (disk and tape), automated and ad-hoc, and tape retention/aging.
DBA's get requests, and if the backups files are no longer on disk, DBA's send the Backup team a request to get the files from the backup software.
If a non-DBA group wants to handle backups and restores, and talk to them about what happens if someone kicks off a restore they shouldn't have and causes data loss/production impact. Be mindful DBA's can cause these same problems, too. Also, have them test ad-hoc Point in Time Recovery to a different database name (i.e. "I deleted X by accident, can you get it back")
This is how I've usually done this. DBAs manage the db and get files to disk. The backup team takes it from there.
+1 to this. But as Grant has said, I don't really care who handles the backups as long as they are done in such a way that I can meet SLA's and Recovery objectives. At my current job I'm a Dev DBA so I don't do anything with backups, the Ops DBA is responsible for that, I think 😛
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 20, 2013 at 9:20 am
Like a good few other posters, the database team handle the backups here.
Once on disk, infrastructure take over and make their necessary backups and also perform copies from the data center to our office so they can be restored on demand by users (client services, dev, qa etc).
The DBA's are also responsible for the restore scripts to the internal servers. However, that being said, stopping/starting of application servers, restore of the databases is actually done via an IRC chat bot. The sysadmin here loves that process...Personally, I don't like it, but hey, it's all automated and under user control, so I very rarely restore to internals unless I am doing restore testing whereby this is done using SQL Scripts only (bot only supports a full restore).
November 19, 2013 at 7:24 pm
We have outsourced SQL Server maintenance to IBM. They look after our servers and all backup, performance related activities. We have internal team for application support who backs up individual databases as requested by the database owner.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply