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.

  • Why does git clean -dX not remove my ignored directory?
  • Regexp search as case insensitive in gitk
  • changing file permission to 770 in git
  • Changing remote of git does not work on digital ocean
  • 'Can't create session Unable to connect to repo' when trying to migrate svn to git
  • Does Git need more space than SVN?
  • 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?

  • Heroku push via HTTPS
  • cherry-pick changes of multiple non sequential commits from remote branches to “master” branch in single commit
  • Git equivalent of `svn cat` with remote repository
  • git how to move back commit
  • Git blame — prior commits?
  • Is there a standard naming convention for git tags?
  • 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
      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.

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