Posted by: Colin Smith
Developer DBA, MSSQL, Roles, Seperation of Roles, Systems DBA
I have a scenario that I would like to talk to you all about and get your take on what should be done. I am a Systems DBA. I am not a Developer. Lets say that in the not so distant past my team of System DBA’s got a phone call from a developer in a panic. They needed a restore and needed it now. Something happened to a production Database and a site that is mission critical was down. Of course nobody had any idea what happened or who did it. I looked into doing a restore and guess what. No backup had ever been done on this Database. All of the other Databases on the instance were being backed up but not this one. What could have been going on here?
Well let me give you a bit more of the back story. In my company the DBA group, my group, is responsible for Backups, Restores, Indexing, etc. We are also in charge of migrating any database object into production. This is meant to keep developers from developing in production. They all have test instances and they are all sysadmin role in those instances so that they can do what ever it is that they need in order to develop. Well, we have one group who has convinced the right people that they, the developers, need full access in production as well. They are too important to the business to be slowed down by process and that will cost us money.
Now that you know that, let me explain why the database was not backed up. The backup job was put in place before this database was created. Now, creating a database is usually up to my team, but since they all have sysadmin role they created it and did not tell us. They also did not set up backups to run against this database. They also were developing in production on the day that it all went down. Millions of rows were lost, database using simple recovery, and no backups. Then they blamed my team for not making sure that the database was backed up.
This brings me to my question. Since I am not a developer I would love to hear from System DBA’s as well as Development DBA’s on this. What should the seperation of roles be here? Am I crazy to think that the System DBA’s should not allow development and creation of databases and database objects in a production database by the developers?
I would like to tell them that either we lock it down and the System DBA’s will migrate all Database Objects and do all Database creation in the future and at that point we will be responsible for all that a Systems DBA should be responsible for. Or, We can give them all SysAdmin role and we will do nothing. If they want to muck around in it then they deal with the fallout and not us. Does that sound right to all of you? Please let me know what you think.