Our company has now hired a full time release engineer for the MS Windows side and we were thinking of a release management process for MS SQL Server.

IF we have confidence in their abilities to deploy SQL Server scripts, is it common to just have the release manager do it or is it something that the DBAs typically do? Our enterprise is growing fast and our DBA's are somewhat overloaded (surprise surprise what else is new) but we can hire more, but that's another matter.

How do you guys manage this? Do you allow a release manager to have access to prod to roll out changes? Do you take away rights and turn them on when they need to release? I'm thinking I will give them access to a sproc that gives them prod access for an hour, but it logs who calls it.

Or am I totally off, and this is something a DBA should always manage?

Any thoughts would be appreciated!


Update: Also what happens when we encounter anomoloies? For example, a dev stated that 'these tables should match this other environment (by environment I mean customer prod environment, not qa/stage/etc.)'. Usually they would do a spot check. I did a checksum and noticed issues which ended up being just whitespace issues. In a case like this, do we push it back to the release manager/qa person to fix after doing basic troublehshooting?

Another example: We have scripts by about 20 developers, sometimes they are dependent on each other. The ordering of the script was wrong. I can't keep up with 20 developers work and also manage the data, but after some troubleshooting we discovered the issue and changed the order. Is this something the DBA should typically be deeply involved in or is it fair after basic testing and look over, we send it back to the release manager and devs to fix?

  • 2
    For me this boils down to the level of trust you have, and how much you value your database. The more people who have access to the database, the harder it is to secure. – Max Vernon Apr 3 '13 at 21:38
  • 1
    We have a database trigger that logs all DDL statements into a table along with who made the change and when. This does not allow you to roll-back easily, it simply is an audit trail. See this: dba.stackexchange.com/a/25052/10832 – Max Vernon Apr 3 '13 at 21:45
  • 2
    In my eyes, and previous experience, a release manager actually puts together the release, patch notes and installation instructions. They do not actually install the software. That should be down to production DBAs etc – Philᵀᴹ Apr 3 '13 at 23:16
  • 4
    There is no such thing as a release that contains 30 scripts. It's one script. It's the one script that was run against the multitude of development and test systems, then the UAT and pre-production databases, multiple times, as preparation for the release. If anything about the release process requires a human being to interpret or assess the state of the system, you're doing it wrong. – Mark Storey-Smith Apr 3 '13 at 23:46
  • 3
    Very little objectivity here, I'm a raving SSDT fanboi. Redgate et al are good tools but they perpetuate the disconnect between developer, database and data. Schemas get changed in development, often via GUI tools, and the problem of how to migrate a live schema and data between versions is dealt with as an after thought. SSDT forces you to consider the migration mechanism up front. It doesn't solve the complexities of data motion in live but it puts it front and centre. – Mark Storey-Smith Apr 4 '13 at 0:07

Firstly, every environment is different. What works in my environment won't automatically work in yours. (We = DBA Team)

In mine we have the following environments for this process

Dev - Normally we restore a copy of live in preparation for a release and sanitise data as required. We then back that up and enable the dev to restore to that backup at will. They then prepare and test their deployment scripts. We expect the Devs to have tested the upgrade/release process in full successfully before it needs to go to UAT. We will often be required to help and advise at this stage

UAT - Again we would restore the copy of live and sanitise (if we are going to use a QA environment). The release and config manager then provides us with the correct scripts for the upgrade from the developers and we will check them, run them whilst monitoring to be able to provide all info required for CAB regarding loss to service, time to upgrade and any changes required to maintenance/monitoring scripts which we then advise the Release Manager.

Sometimes we use a QA environment, usually when sanitised data has been used in the two environment above and the business are required to QA rather than the test team.

CAB - The Release Manager provides all the information for CAB to make their decision from the proceeding steps to lay out exactly what happens during the release. They are also responsible for storing the scripts used to go from Dev to UAT and providing them to us for release into live. We only use the scripts provided by the Release Manager and referenced in the CRP. We obviously check that the scripts are the same as the ones we used in UAT as I am ultra cautious

Live - We deploy to live exactly following the steps in the Change release Plan authorised by CAB, often co-ordinating with other teams

The reason we do it like this is partly as it is the process that we have to follow here (Using CAB etc) but in answer to your question it is because the DBA team are the ones responsible for the safety and accessibility of the data. Also because we have the skills, knowledge and experience to recover from unexpected problems. What happens to the Live databases is our responsibility and we take it very seriously. No-one else is allowed to perform tasks on the live databases other than the DBA team. We are the guardians and the bouncers (door men/women)

For your update. I bounce that sort of issue back to the developers and require them to fix it before I deploy to UAT. Once the script is finalised (at UAT) we know it will work in live.

For your another example - That is the job of the release manager. If they are providing us with scripts that fail we bounce it bak to them and require them to fix it before we will deploy into UAT. We WILL NOT deploy into UAT anything that requires bodging or changing from the instructions provided and in the few occasions wen this has happened (a developer used the app account to manipulate some data after we had run the scripts to enable the release to pass UAT) I stand up in CAB and explain this and explain that I am not happy to release this to live without a correct test of the release. This usually works :-) I know I am harsh but I am also fair.

I tell the release managers and the devs that the process of passing the release to UAT is the test not only of the release but of how the release will be performed therefore we will follow the exact same steps in both examples (dev - UAT and Live) and that if it fails in UAT it goes back to them to fix.

I hope that helps

  • Mr. Rob, thanks for the well laid out and insightful response. We have something similar but not as mature. I'll review your suggestions with the team and get back tot his thread. – Ali Razeghi Apr 6 '13 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.