May 18, 2010 at 6:51 am
Hi. I have pick a tool set and create a process to perform updates to our sql 2k5 databases, both for internal testing and updating client Dbs in the field.
At my previous gig I had to do this but in a much simpler way since it was an internal application. I simply checked my sp,view,fn() scripts into source code control, ran Redgate Sql Compare to identify the schema differences, then manually ran the individual scripts as necessary to update the production db. This process was mostly sane but it was a small environment and very manageable.
I was basically a one man shop..db dev, ui dev, db troubleshooting as necessary, and change management as necessary to support new product versions.
Now at my new company I have to do the same basic thing, push db changes from dev to test and to customer production dbs. Except now it is a full fledged software product with multiple support databases which may be running in our hosted environment or at a customer location.
Currently there is absolutely no process around our db change mgmt. I have multiple developers making changes directly to a test db without any scripts, version control, oversight, or anything.
So now I have to develop the db change mgmt process. I can do this manually if I really tried by using a large script and applying the sections necessary to upgrade a given db from version n to version n+1.
Or I can go with some third party tools such as visual studio 2010 or Redgate. I have no experience with the db change mgmt features in vs2010, and limited exposure to the Redgate tools, mostly using Sql Compare to manually identify schema changes between two databases.
Can anyone offer any suggestions, products, tools, websites, etc. to help with my db change process ? What are tools are you using and would you recommend them ? Thanks very much for your input.
Bill,Charlotte NC
May 18, 2010 at 8:46 am
That's a huge topic. I have a chapter in a book I'm writing for Red Gate waiting for tech edits on this exact topic right now.
There are any number of ways to do it and any number of tools that can help you. The tool you pick will affect the process you create, but there are some standards that apply across tools. First, get your database into source control. Make the developers perform ALL changes through source control. No change in source control, then it doesn't get deployed. It's that simple. Next, build, using whatever tool you determine, from source control and only from source control. This way, you can label, version, and branch your database with the application code. This makes it possible to always know what you're deploying. Finally, get a staging server between you and production. This will allow you to test the deployment prior to running it on production. When it tests negative, reset the staging server and rerun the test in it's entirety. When you have a positive install, don't modify the script, simply pick it up & drop it on production. You need to take human beings out of the process as much as possible (if they're anything like me, they'll screw it up royally). Drive the deployments from source control, test the deployments, only deploy the tested script. Everything else is details.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply