February 29, 2008 at 6:15 am
We have a situation where the Server Admins want to be able to back up and restore SQL Server databases. They feel that it is their responsibility to be able to restore an entire server and say that it is a PCI requirement. In addition, the Computer Operators would have the same ability and none of them are skilled in SQL Server. They are convinced that a third-party point-and-click application can mimic a DBA so we will be having a meeting to hash things out.
I just wondered if other companies are trying to do the same thing, or if our server group is just unique. I welcome all feedback.
February 29, 2008 at 6:42 am
I the application admins take over that responsibility, what the heck do they need DBA's for? :w00t:
We've had similar requests and have asked what it is they are looking for. Mostly they wanted the ability to create an ad hoc backup before running a batch process. So we created a way for them to do that by creating a batch program that would allow them to do this. Secondary to ad hoc backups they really wanted more attention from the DBA.
I don't know how many DBA's you have on your team/department, but maybe they are looking for someone to be more accessible/available. The long and short of it is, I would not grant them the ability to complete backups/restores or other admin functions for fear that something would accidentally get drop, deleted, maladjusted, or otherwise corrupted in the Database or the Server.
Regards, Irish
February 29, 2008 at 6:43 am
The lack of communication kind of makes it sound like there's a 2-sided problem. The Server Administration should should in fact have the ability to back up just about anything they please, but that doesn't mean that how, when and what method is their purview and no one else's. Of course - I'd say that's not solely up to them on ANY amount of data: there has to be consultation and agreement as to what data is retained in what way and for how long, and that's a discussion needed to happen between the business unit owning the data, and the unit performing the operation.
In almost every case - third party apps going in and pulling backups via their own customized agent have caused me issues of one kind or another. Also - the one-size-fits-all approach to backing up everything in the same way on a SQL server, well, just doesn't make sense. Some data is deemed critical, and should be treated differently.
That being said - once the rules are established, and the responsibilities for safeguarding the data properly line up with those units doing each step of the work - there really isn't a conceptual problem with someone other than the DBA "running" the backups. It does however require ongoing discussions between the two groups from now on.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 29, 2008 at 7:58 am
there is no right answer and it's all CYA
where i work DBA's always took care of the SQL servers down to doing windows updates and installing windows, sql, etc. but that is only because there has always been at least one DBA that has come from a server background
other companies the DBA's do strictly SQL and others the housekeeping tasks on the hardware
the issue here is that one group doesn't want to be held responsible in case something goes wrong. say a backup fails and the data needs to be restored then and there. server people don't want the dba's to say that their tape drive or whatever failed.
for backups we used to share a tape robot, but management was separated where DBA's tracked SQL backups and blank tapes. it was OK, but everyone would steal tapes from each other from time to time. now the server people do disk backups on Evault and us SQL people are about to get our own Evault server for backups and the tape robot is now 90% SQL
February 29, 2008 at 8:32 am
Great feedback. Thank you all. I also should mention that the application they intend to use needs to be a sysadmin on all of the database servers. I cannot see any logic of giving sysadmin rights to an account that inexperienced people can use.
The software also can run DBCCs before doing backups. I have all maintenance jobs set up and scheduled already, and they don't understand the impact DBCCs can have if they are run during peak times. Who do you think the users are going to call?
Do the admins know anything about differential and log backups or how to restore them? I can only think of "cons" and not a single "pro" to the idea. Thanks for letting me vent 🙂
February 29, 2008 at 8:32 am
I could go either way, though I tend to favor Matt's view.
Communicate with them and work it out with managers. Let managers know that there are dangers, and that if these guys drop or delete something, it's their fault. That's the responsibility that comes with the privilege of having rights.
And train them. Spend time showing them, maybe over brown bags at lunch, how to do the backups.
February 29, 2008 at 8:43 am
Hey i would go with the Database tasks done only by the DBA and let the admins take care of the network and OS related tasks. I would prefer the way when the admin says that its his responsibility to backup and restore a SQL Server database.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 29, 2008 at 9:10 am
We do two types of backups.
As DBA, I schedule my backups of the SQL2005 servers using SQLbackup routines for time that are best for the databases and save to disk on one of the other servers and vice a versa. The Windows admin then has a nightly backup routine that goes in and does it's backup of the system files and the backup folder with the database backups. Since the backup application can trigger at any time within a window this works as best control.
For older servers with SQL 2000, we don't have the space to do the Sql backup routines as described above. So we use the 3rd party app to do the backup but I sit with the Windows Admin and decide what databases are needed to be backed up and at what schedule. The 3rd party app sees "all" databases and cannot determine what is offline and the Windows adm wouldn't know this.
Future builds will be according to the first method.
February 29, 2008 at 9:10 am
Hi,
Overall, its really a team effort with the split being division of responsibility and each team member taking care of what they are skilled at doing well. This is a regularly surfacing issue and coming from a infrastrusture background, completely understand this challenge.
In most organisations I have consulted, there is a boundry between server admins and database admins, where server admins are responsible for everything server-side such as operating system, network interfaces, O/S monitoring and application installation. Database administrators take over from server admins immediately once server (e.g. db server) is production grade and apply the appropriate security to enforce is division of labour. There are occassions where database admins would appreciate having server admin rights and the same issue surfaces, yet again.
Overall, as outlined earlier, its a team effort that requires a solid communication structure between all involved and clear allocation of responsibility to reduce overall risk. It doesn't matter who does the backup or restore as long as they are properly trained, the team communicate and the correct change control processes are in-place to reduce risk of mistakes.
Currently, I work within a team of three and I take care of majority of SQL Server server work and the infrustructure members take care of non-SQL Server tasks, but I still take the time to knowledge transfer SQL Server tasks and this works nicely.
Thank you,
Phillip Cox
MCITP - DB Admin
February 29, 2008 at 9:32 am
All excellent info in these responses and I agree with all of them. In our company we have a project request process in place and when our development team needs a backup prior to a change they need to make, we coordinate it through the test servers, test it for a week, and then do the same in the live environment.
Our servers are built by one guy, he hands it over to the team I'm on of DBAs and we maintain it. We do all the ghosting, backups, and patching. The application and development (TSQL) folk send us requests to copy stored procedures between environments etc. It all works very well
I think you should be controlling the backups, and communication between the different departments is key to executing ad-hoc backups successfully and smoothly. Cheers...:P
February 29, 2008 at 9:43 am
Like Lowry, in our environment (both SQL 2000 and 2005) I am responsible as the DBA to make sure all databases are being backed up correctly and am responsible for the integrity of those backups and that there is proper chaining of backup history to meet the clients' SLAs. I am responsible for the SQL installs, performance, and patching. Essentially everything SQL = DBA.
The server team is responsible for the server O/S and hardware. They are responsible for the process that will nightly copy the static files (including the backup volume(s)) to tape from the server disk or san.
Once upon a time they wanted it all and I had no access to the servers I was the DBA for, that all changed when they destroyed a server and needed to do restores - and couldn't.
As a DBA it is my responsibility to know everything that is occuring on my servers that could affect performance and work with the Server Engineers to resolve issues that may fall onto their side of the court. (Adding RAM, dealing with external fragmentation, etc.)
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 29, 2008 at 10:05 am
Ummm... I've seen questions like this go back and forth and even some flame wars get started over it and similar things like privs on the Windows Server, etc.
I think the real key is a bit of communication, sharing of knowledge, and some team work. Yeah, I know... no, I don't live in a Utopia with rose colored glasses. The real key is going to be... what's good for the company? What's best for the data?...
... heh... and who's gonna get blamed if the bloody backups don't restore? :hehe:
Share the knowledge, the responsibility, the kudos, and the blame... it's all the same team if you do it right. The important part is to communicate and understand the roles and responsibilities. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 29, 2008 at 7:56 pm
Every place I have worked the DBA's were responsible for SQL Backups to disk and then the Server/Network admins were responsible for getting the backups to tape. We worked together to make sure backups of mission critical db's were complete before the tape backups hit that location.
As has been said, commicate clearly and give valid reasons why server admins should not be responsible for SQL Server.
If the app needs sysadmin access I would go to the mat to prevent it's use. If you fall under any of the fairly recent law, HIPPA, SOX, you don't want sysadmin given to anyone.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply