How to version control SQL Server database with Visual Studio's Git Source Control Provider

Question

I have Git Source Control Provider setup and running well.

For Visual Studio projects, that is.

The problem is that each such project is tightly tied to a SQL Server database.

I know how to version control a database in its own .git repository but this is neither convenient nor truly robust because ideally I would want the same ADD, COMMIT, TAG and BRANCH commands to operate on both directory trees simultaneously, in a synchronized manner.

Is there a way to Git SQL Server database with Visual Studio's Git Source Control Provider in the manner I described?

Solution 1

You can install the SQL Server Data Tools if you want to, but you don't have to: You can Use the Database Publishing Wizard to script your table data right from Visual Studio into the solution's folder, then Git it just like you do with the other project files in that folder.

Solution 2

You can store your database schema as Visual studio project using SQL Server Data Tools and then version control this project using Git.

Solution 3

Being in the database version control space for 5 years (as director of product management at DBmaestro) and having worked as a DBA for over two decades, I can tell you the simple fact that you cannot treat the database objects as you treat your Java, C# or other files and save the changes in simple DDL scripts.

There are many reasons and I'll name a few:

• Files are stored locally on the developer’s PC and the change s/he makes do not affect other developers. Likewise, the developer is not affected by changes made by her colleague. In database this is (usually) not the case and developers share the same database environment, so any change that were committed to the database affect others.
• Publishing code changes is done using the Check-In / Submit Changes / etc. (depending on which source control tool you use). At that point, the code from the local directory of the developer is inserted into the source control repository. Developer who wants to get the latest code need to request it from the source control tool. In database the change already exists and impacts other data even if it was not checked-in into the repository.
• During the file check-in, the source control tool performs a conflict check to see if the same file was modified and checked-in by another developer during the time you modified your local copy. Again there is no check for this in the database. If you alter a procedure from your local PC and at the same time I modify the same procedure with code form my local PC then we override each other’s changes.
• The build process of code is done by getting the label / latest version of the code to an empty directory and then perform a build – compile. The output are binaries in which we copy & replace the existing. We don't care what was before. In database we cannot recreate the database as we need to maintain the data! Also the deployment executes SQL scripts which were generated in the build process.
• When executing the SQL scripts (with the DDL, DCL, DML (for static content) commands) you assume the current structure of the environment match the structure when you create the scripts. If not, then your scripts can fail as you are trying to add new column which already exists.
• Treating SQL scripts as code and manually generating them will cause syntax errors, database dependencies errors, scripts that are not reusable which complicate the task of developing, maintaining, testing those scripts. In addition, those scripts may run on an environment which is different from the one you though it would run on.
• Sometimes the script in the version control repository does not match the structure of the object that was tested and then errors will happen in production!

There are many more, but I think you got the picture.

What I found that works is the following:

1. Use an enforced version control system that enforces check-out/check-in operations on the database objects. This will make sure the version control repository matches the code that was checked-in as it reads the metadata of the object in the check-in operation and not as a separated step done manually. This also allow several developers to work in parallel on the same database while preventing them to accidently override each other code.
2. Use an impact analysis that utilize baselines as part of the comparison to identify conflicts and identify if a difference (when comparing the object's structure between the source control repository and the database) is a real change that origin from development or a difference that was origin from a different path and then it should be skipped, such as different branch or an emergency fix.
3. Use a solution that knows how to perform Impact Analysis for many schemas at once, using UI or using API in order to eventually automate the build & deploy process.

An article I wrote on this was published here, you are welcome to read it.

Comments

[Souper]

@marc_s Thanks for correcting the typo.

[Souper]

I didn't know that it is possible to store a database schema as a Visual Studio project. That's great! I originally thought of using SSMS to Script Database as > DROP And CREATE To to export the entire thing as a text-based script to the Visual Studio solution's directory, but an integrated solution like you describe sounds more promising. Which tool of the SQL Server Data Tools actually exports? Do I have to install the entire set of tools? +1

[Piotr Sobiegraj]

It's one package, you can download it here: msdn.microsoft.com/en-us/data/hh297027 Here you have some documwntation: msdn.microsoft.com/en-us/library/hh272702(v=vs.103).aspx or videos: msdn.microsoft.com/en-us/data/hh297028

[bbsimonbb]

This is a good answer that deserves more attention! Can I add that if you manage your application's SQL with QueryFirst, the tool can generate self-test methods for every query, compiled into your binary. As such, you can integration test a binary against a production DB, and assure yourself that all queries run, and return the same columns as they did when the app was developed. QueryFirst is the only tool I'm aware of that will do this. Disclaimer: I wrote it.