2

I'm a sysadmin who has recently been landed with some SQL DBA responsibility. Not reluctantly I might add, this is a world I'm very much looking to get immersed in.

I am curious about one of the aspects of our existing setup (where DBA tasks were previously largely left to the developers), namely the update mechanism for one of our production databases.

Essentially what happens is that any time the underlying data changes, one of the developers creates a new database in the dev environment and restores it onto the production environment. To facilitate this the development team have been given dbcreator permissions. This happens once or twice a week.

Can any of the more experienced DBAs spot any flaws with this approach? Should I insist on handling all the restores myself (I have written out the T-SQL commands for the dev team to use instead of SSMS which required sysadmin rights to browse the available media)? Could something go wrong during a restore that would knock the database offline?

We're using SQL Server 2008 R2 standard edition.

Thanks all.

Improve this question

2 Answers 2

Reset to default
2

As a general rule, I have an issue with the development team applying changes to a production database I am responsible for.

If I was in your shoes, I would change the process so I applied all database changes in Production. This eliminates surprises (for you) and adds a new set of eyes to the process at the very point it is critical to do so.

I am also a firm believer in using comparison tools. My current choices are the RedGate SQL Compare (for structure comparison) and RedGate SQL Data Compare (for data comparison). You are also able to use the Visual Studio for Database Professionals, if you have it loaded.

Another benefit of using the comparison tools is they minimize the changes to the modified database while providing you with a set of scripts which you are able to add to your source-control system to documentation each release.

Following this path removes the need for the developers to have elevated permissions in the production database and should reduce your exposure. In my mind, reducing exposure is always good.

Improve this answer
3
  • I agree. Updates to a production system should be carried out by the entity responsible for that production system. Commented Jul 5, 2011 at 20:53
  • Thanks Robert, I had a meeting with the dev team yesterday and we have agreed that I will have complete responsibility for database restores. Turns out the DB is read only apart from a handful of tables so we're going to split those out to a separate database (they do not need to be restored to update them). This will not even require cross database queries apparently. Commented Jul 8, 2011 at 18:11
  • I will check out the tools you mention as well, thanks. Commented Jul 8, 2011 at 18:11
1

I'm guessing this is a read-only database? If so, I don't see any huge problems with it. The way I've done this before is saving the update scripts written against the dev database (in source control, naturally!) and then just re-applying the ones since the last update to prod. This method is probably a bit faster than doing a full DB restore, though that probably shouldn't matter too much if you don't do it frequently or if the restores are small. It is still good to snapshot prod before each update, in case something ever goes horribly wrong and prod and dev diverge.

Improve this answer
3
  • Unfortunately we cannot snapshot as the edition is SQL Standard. The database is not read-only either! But the services (web servers mainly) that read and write to the DB are shutdown prior to the restore, as you might expect. Commented Jul 5, 2011 at 8:13
  • It is scary that it is not read-only. Are the live changes somehow mirrored to dev before the updates are made and then restored to prod? I would be worried that in this process you could be losing data. Commented Jul 5, 2011 at 8:23
  • Hmmm ... I'm not sure if they do mirror it back but I will check. I think data loss will be my angle when I approach the dev team. Is there anything else that could go wrong during a restore? Or any other drawbacks to the approach that you can think of aside from data loss? Commented Jul 5, 2011 at 22:32

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.