Production Database Releases - Worst Practice

• Malcolm Leach

  SSCommitted

  Points: 1517

  More actions

  June 22, 2007 at 1:09 am

  #714516

  It's amazing what people come up with as a "change management" process but I've never encountered that kind of madness before...did someone mention ITIL training?  I think it's just basic common sense...

  If you're interested in a proper process for managing SQL Server changes then take a look at DB Ghost http://www.dbghost.com.

  It's being doing for 5 years what Microsoft have only just started doing with Visual Studio Team Edition for Database Professionals (and they charge 10 times the price!).

  The main idea is that all your SQL code should be in CREATE scripts under source control so that it can be fully audited and then released in an easy, reliable and coherent way.  Basically it means you can regard the set of CREATE scripts as your "source database".

  Malcolm
  DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
  www.dbghost.com

• CanterburyRulz

  SSC Enthusiast

  Points: 159

  More actions

  June 24, 2007 at 9:05 pm

  #714838

  Thanks for the replies Brandie & lindss01. For all our environments, except the one mentioned, we use both of your procedures combined. Development, testing, UAT and finally production. Nothing gets to testing, UAT or production without release scripts and change controls. Works well.

  ITIL, commonsense, I'm sure some people have never heard of either!

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  June 24, 2007 at 11:33 pm

  #714852

  Step 1.  Nominate the current DBA for a "Darwin Award".

  Step 2.  Fire the current DBA.

  Step 3.  Promote the developer to DBA with the caveat that if he doesn't impliment the correct code promotion safeguards, that he will also be fired the very first time he does it wrong.

  And, yes, the idea of anyone promoting code without at least a unit test and a "second party" test, is dead wrong.  Here's our process...

  1. Developer writes the code and test the individual parts as (s)he completes them.
  2. Developer integrates the code into the development environment and completes unit testing.
  3. Developer submits code for peer review for functionality, standards compliance, and performance compliance (including expected row counts for cleanup scripts)
  4. Peer Reviewer reviews code, sends back code (to step 1) if changes are required, and sends code that passes to QA/UAT for second party testing.
  5. QA/UAT may send code all the way back to step one if any faults are found.  Once code passed, QA sends code to DBA via Visual Source Save or Sub-Version (part of the backout plan should something go wrong).
  6. DBA reviews code for "safety" and performance.  Code that fails can be sent all the way back to step 1.
  7. Change control is scheduled.  If it's all "non-impacting" code, no outage is scheduled.  If the code is "impacting", the change control is setup for an outage during a special time once every two weeks reserved for such outages.  "Impacting" is any code that takes more than about 1 second to execute or requires locks that may cause deadlocking during normal customer use (mostly, large data cleanups like an across the board price change).

  Note that we also write datacleanup scripts so they are NOT self committing.  The DBA examines the expected rowcounts (well proven by then) and compares to the actual rowcounts.  Depending on the type of cleanup, if the rowcounts aren't either spot on (ie. configuration changes) or within a certain tolerance (ie. changes to all customers or groups of customers), the DBA immediately rolls back the code and all associated code.

  At NO time, is anyone allowed to promote code directly.  Except for key maintenance developers, no "normal" developers have write privs on the production database and, since the dev environment is updated from production on a regular basis, many of them don't have read privs on production.

  It took us a long time to get to a "no faults promoted" scenario using the methods outlined above, but it has been worth every bloody minute.   It takes a lot longer to find and fix a problem in production than it takes to slow down a bit and use the steps outlined above.

  Now, if I can just get the Java boys to follow the same rules

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)