Database deployment is really the elephant in the room with respect to the continuous delivery for software projects. Most of the best practices for doing continuous delivery well documented and there are lots books which talks the same with respect to code .
When it comes to database, It is hard to find best practices and how to use it properly . so here are some best practices which I learnt over the years .
Audience Level : Application developer , Database administrators and Build administrator.
Key Idea
Simple yet powerful principle to always keep in mind when comes to Database CD is , Treat database just like your code .
Shared Development databases are Evil
Think of the days when the code is kept and shared via shared folders and evils it bring . It was a chaos . Here are common problem which happens when using the common database .
- Last writer wins : One who changes the Schema / Data always wins
- Impossible to write unit/Integration tests
- Changes are difficult to track and solve .
When it comes to code , Developer always knows to check-in his code and other developers takes it from the source control and the same can be followed here too .
SQL server has come developer version which is free to use and it is quite enough for development purpose . Well I know the restriction of 5 GB comes with it , But take a seat back and think do you need data more than 5 GB for your development purpose , Most cases it will be a NO .
So how do we solve this issue ? Below are steps I have found useful .
Source Control your database :
Your database must be source controlled and it should be kept as part of your code check in . If you are legacy system and it has not been done for years , Here is the small steps which you can take .
You can use SSMS to generate the schema from your existing database . Below link can help in getting that done.
if you are starting now then start with the simple script folder start adding the create script .
Before I go further into the smallest details of the execution , I like to point out that there two methods of database deployment
- Change Script Based
- State Based deployment — SSDT based
I will be talking about change based deployment in this blog post . I found the State based deployment complicated and it can be done only with some tools in place and I am not a big fan of using Black magic based tool :)
DogFooding the Upgrades :
One more reason for source controlling you database is making sure just like code we should take the latest code every time and run the database code so we can verify one person change is breaking the other person code .
With that said I will write about the tools and technique which we use for the database upgrades in the next blog Post .
