If you are using SQL Server and are looking at putting your database under source control then the best in class program is Red Gate's SQL Source Control. It seamlessly integrates with SQL Management Studio minimizing the learning curve. It supports all the major source control systems, GIT, TFS, SVN, etc. The first time I used it I didn't have to read the manual and I had my database under source control in under five minutes.
Getting it setup and actually using it are two different things though. There were some hard lessons I had to learn in order to move from evaluation to company wide adoption. Something new would come up each time a team started using the tool at Farm Credit Services of America.
Before you even think of putting a database under source control you need to reconcile all the schema differences between each environment.
Let me put say that again because this is the most important step.
Reconcile all schema differences between each environment before putting a database under source control.
When I put a legacy application's database under source control I could not believe the differences. Test tables existed in dev. Column properties existed in dev and test but not production. Views existed in production but not in dev.
Get the instance of the database you want to put under source control in order. In the past the truth center of the schema for the database was production. That is all changing once you put it under source control. Source control becomes the truth center. When you start automating the database deployments any differences not in source control and not filtered out will be wiped out. Deleted. You do not want that to happen to a mission critical table.
Pick a Model
You have two choices. Dedicated or shared. I recommend dedicated. Red Gate recommends dedicated. Each developer has their own copy of the database running locally. This provides many benefits
- Schema changes and code changes go hand in hand. This allows developers to get all the schema changes needed (table changes, sprocs, views, functions, etc.) working locally before pushing them out.
- Supports branching strategies. A shared model only supports one branch, master. With dedicated you better hope all new changes made to your database are non-breaking changes or you will have issues. When using the shared model developer A renamed a single column while developer B changed the code. The rename took five seconds the code changes took a few hours. During that time our development environment was down until we rolled back the change until the code was complete. It should have all be done in a branch.
- Local data to mess up. If I mess up my database no one but me cares. I can easily recover. I don't hurt anything.
Add Database to Source Control
Red Gate's Documentation makes this a pretty easy process. There are few recommendations to consider when you do this.
- I am not a fan of the GIT integration in SQL Source Control. I recommend to the developers on my team to use the "working folder" option. This will save all the changes to a folder and you can use your GIT tool of choice to commit and push those changes.
- Establish a folder structure before you start. The database will be saved as a bunch of create SQL scripts in your source control. You need a place to put them. Our standard is /[RootSourceControlFolder]/Database/[DatabaseName]/src.
- Make one person responsible for initially getting the database into source control and have all other developers pull down their changes. I saw one team have three people try to do this at once. The results were...interesting.
Script database creation
Red Gate will not create the database creation scripts for you. You have to do this yourself. The database has to exist before you can put it under source control. A chicken/egg kind of deal.
Having the database creation script saved off will be a big help for your DBAs as well as your developers if you are using local databases (which you should). It is very easy for you to do this using SQL Server Management Studio. I recommend putting the database creation scripts in /[RootSourceControlFolder]/Database/[DatabaseName]/creation.
Script Local Server Setup
If you are doing local databases (which again you should) then create a bat file that will install SQL Server onto developers machines. Microsoft provides some excellent documentation on how to do this. I recommend putting this in a shared location or in source control. The creation folder (/[RootSourceControlFolder]/Database/[DatabaseName]/creation) works fine for this.
Schedule a meeting and have the developers bring their laptops. When it was time for my team to start using the tool I took two different approaches. My first approach was to send an email with step by step instructions. That had a 40% success rate. I ended up at a lot of developer's desks helping them setup. For the next database I scheduled a meeting. The success rate shot up to 90%. In the meeting the developers on the team should:
- Install SQL Server
- Install Red Gate SQL Source Control
- Setup the database (using database creation scripts)
- Bring changes down from source control and apply them to the database
- Do test commits.
By the time the developers walk out the room they should be able to make changes and check them in.