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?

  • How can I prevent hidden .svn folder from being copied from the _bin_deployableAssemblies folder?
  • Auto-stage in Git Extensions
  • TFS Branch refused to go
  • How to add an existing solution to a Team Foundation Server?
  • Source control unable to access database
  • How to change SVN authentication details in AnkhSVN in Visual Studio 2010?
  • What will be Solution for fatal: bad config value for 'color.diff' in .gitconfig?
  • DLL locked - Visual Studio 2010 C++
  • 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
      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
    • 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.

    Git Baby is a git and github fan, let's start git clone.