SQL Server DB changes deployment

Question

I am struggling to keep up with DB changes and how to cleanly maintain and deploy DB changes from Dev to PRD from one release to another.

Currently, I use a SQL script to figure out what has changed since the last release;

• Procs and Views are the easier to take forward as i can simple drop and recreate if exist.
• Table Changes are more difficult since existing tables cannot be simply dropped.

But i think my main issue which i am unable to get my head around is that we have multiple mini releases to Dev and UAT between any two major PRD releases.

Dev and UAT mini releases are OK so far as I get a list of changes from other developers and I combined it with mine and then do a release and plus I am sys admin on the Dev and UAT DBs so I know exactly what is there and what happens when I apply the change.

For PRD I have to prepare a clean script and hand it over to the DBA so he can run it on there but apart from table names, columns and data on PRD I cannot view anything else.

and this script for PRD should capture almost everything that has gone into mini releases but most of the time these mini releases are not sequential and sometimes do negate each other as in one adds Feature A and then a subsequent release removes Feature A but for PRD we may have to add Feature A so no need to take second mini release which will effectively remove Feature A.

So in short I am looking at ways to manage and track DB changes between Releases and also a good way of building a deployment script for DB changes.

Note: I do keep a copy of all SQL objects in TFS maintained manually.

I tried using Database Project but I didnt get much luck achieving what I wanted.

Any ideas guys? any help much appreciated

Answer 1

I know am late but try redgate tool which is the best to control the source and synchronization of database objects. I have been using this tool for a long and convinced of the results. there are many options to sync your SQL scripts, users, security, settings & specially data from one to another database.

Answer 2

I'm not familiar with TFS, I used Git (CVS, and SVN previously), hence take this answer in perspective.

Managing and versioning the SQL bits have been the worst of my nightmares. there are some tools that easy the changes management, red-gate SQL Developer Bundle, which handles comparisons, and allows connection to versioning systems too. in a lesser measure for your request you have idera SQL comparison toolset, idera's perfectionist approach to everything shouldn't be over-viewed.

Anyway in my opinion managing changes can't and shouldn't be approached as a technical thing it has to be part of the IT governance, and procedural, it requires to document and communicate among any relevant functions.

When some mini-release, hot-fix, micro-change or similar stuff happens, they have to be reflected at every other environment that needs to get it.

Of course if there is a mini-release in production and you are the middle of a UAT, you might not want to insert the mini-release in that environment. IMO, soon after the UAT you should apply the mini-release to the environment and perform a E2E QA session in order to make sure that the environment is fine.

If you read my previous paragraph you might find some problems lets say that UAT has a SP version 4 and the mini-release the same SP is 2... how do you make sure that you are not causing regressions?

This is when version control systems and merging tools come to help. Red Gate tools should also help you building the deployments.

But, I believe that governance, communications and follow the rules are the most important factor for continuous success.

The tendency today is implementing continuous integration, and build testing environments on-demand, there are in-house, hosted and on the cloud. usually they are not cheap to build and to maintain, but they pay off!

Last but not least, a couple of same of rules that you should abide when developing for SQL deployments:

1. Every change script should be repeatable, for example
   • before creating a procedure, check if it exists and delete it before the creation
   • before any insert check that the row doesn't exists and then insert else update
2. when deploying to multiple databases, every change script must make sure that you USE the proper database (here I found that synonyms are one of the best inventions since the creation of the can opener)
3. change scripts should follow a sequence. for each object:
   • DDL
   • apply the DCL for the DDL above
   • apply the DML for the table.
4. I found that the best sequence in for scripts are
   • create/alter Tables
   • create/alter views
   • refresh all views
   • create/alter functions
   • create/alter functions
   • perform every DML.
5. in every script avoid failing to the temptation of ordering the sub parts of the scripts by ABC, usually you have dependencies like FK or SP that execute other SP that make the ABC approach frustrating.