How to version control SQL Server database with Visual Studio's Git Source Control Provider
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?
3 Solutions collect form web for “How to version control SQL Server database with Visual Studio's Git Source Control Provider”
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.
You can store your database schema as Visual studio project using SQL Server Data Tools and then version control this project using Git.
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
- 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
- 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
- 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
- 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:
- 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.
- 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
- 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.