SQL Server database change workflow best practices

Posted by kubi on Stack Overflow See other posts from Stack Overflow or by kubi
Published on 2010-05-18T18:52:32Z Indexed on 2010/05/18 19:10 UTC
Read the original article Hit count: 312

Filed under:
|
|
|

The Background

My group has 4 SQL Server Databases:

  • Production
  • UAT
  • Test
  • Dev

I work in the Dev environment. When the time comes to promote the objects I've been working on (tables, views, functions, stored procs) I make a request of my manager, who promotes to Test. After testing, she submits a request to an Admin who promotes to UAT. After successful user testing, the same Admin promotes to Production.

The Problem

The entire process is awkward for a few reasons.

  1. Each person must manually track their changes. If I update, add, remove any objects I need to track them so that my promotion request contains everything I've done. In theory, if I miss something testing or UAT should catch it, but this isn't certain and it's a waste of the tester's time, anyway.
  2. Lots of changes I make are iterative and done in a GUI, which means there's no record of what changes I made, only the end result (at least as far as I know).
  3. We're in the fairly early stages of building out a data mart, so the majority of the changes made, at least count-wise, are minor things: changing the data type for a column, altering the names of tables as we crystallize what they'll be used for, tweaking functions and stored procs, etc.

The Question

People have been doing this kind of work for decades, so I imagine there have got to be a much better way to manage the process. What I would love is if I could run a diff between two databases to see how the structure was different, use that diff to generate a change script, use that change script as my promotion request. Is this possible? If not, are there any other ways to organize this process?

For the record, we're a 100% Microsoft shop, just now updating everything to SQL Server 2008, so any tools available in that package would be fair game.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql