4

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!

Edit:

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?

Ali Razeghi - AWS
  • 7,518
  • 1
  • 25
  • 38
  • 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. – Hannah Vernon Apr 03 '13 at 21:38
  • Good pont Max, how are you guys currently handling it in your environment? I was hoping the sproc that removes access after an hour and logs who called it would be a good step to ensure people are careful. – Ali Razeghi - AWS Apr 03 '13 at 21:43
  • 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: http://dba.stackexchange.com/a/25052/10832 – Hannah Vernon Apr 03 '13 at 21:45
  • The storedproc you are thinking of might be an interesting idea. Perhaps you should detail what you are thinking of by adding the code to your question. – Hannah Vernon Apr 03 '13 at 21:49
  • Yup, will do. I haven't thought about the details yet only because the business and the DBA team hasn't really discussed who does it. If the DBA team does it, the sproc won't be needed. If we end up going with it I'll add it. Thanks for the suggestion – Ali Razeghi - AWS Apr 03 '13 at 21:59
  • 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 03 '13 at 23:16
  • Thanks Phil. What do you see when a error is encounters? Let's say the release is through 30 SQL scripts using SQLCMD but an error comes up. I troubleshoot it but does it make sense to end the responsibility of the 'dba' in this case at making sure it's released, and we push it back to the release engineer/devs... or do we troubleshoot it as well? Updating question. – Ali Razeghi - AWS Apr 03 '13 at 23:17
  • 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 03 '13 at 23:46
  • Hi @Mark, let's expand on that a little. We currently have 1 script that calls literally 30 other scripts at times in it's current form, which is ran across all the QA, UAT, Stage, etc. environments. We very well could be doing it wrong, I am just getting involved in the release management process actually. I am looking at red gate to create a master script so we just roll that one out. It seems like using RedGate SQL Compare and Source Control would resolve most of our other issues. How do you guys handle it? – Ali Razeghi - AWS Apr 03 '13 at 23:53
  • 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 04 '13 at 00:07
  • Thank you Mark, I am looking into SQL Server Data Tools right now. This could be very useful. – Ali Razeghi - AWS Apr 04 '13 at 00:13
  • I'm on the same track as @MarkStorey-Smith, though on a previous version of the tools. We use Visual Studio 2010 with Database projects (former Data Dude) and it's very good at keeping the database and the application projects together. It's not perfect, but still the developers have it all together. We don't have a deploy manager, but a release manager, that does what Phil said. – Marian Apr 04 '13 at 08:15
  • Thanks everyone, I wish I could mark one of these as the answer! I'll go through it and just write what we ended up with the answer after going through the tools stated. – Ali Razeghi - AWS Apr 04 '13 at 20:11

1 Answers1

2

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

SQLDBAWithABeard
  • 645
  • 1
  • 6
  • 13
  • 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 - AWS Apr 06 '13 at 17:23