Automate Visual Studio DB Schema Compare and check into GIT

Welcome to the world of Database Lifecycle Management (DLM). It's a pretty big topic, I'll do my best to keep this as short as possible.

In general you should be making changes in source control first and then deploying to your production databases from source control. This gives you the opportunity to test your code in dev before deploying it to production. It also ensures that the production database is in line with the version that you tested.

There are a range of Microsoft, third party and open source tools that help you to script out your database and get it into Git (or any other source control system). Some of the most popular are SSDT, Redgate SQL Source Control, Redgate ReadyRoll, Flyway, DBup, Liquibase and DB Maestro but there are many others.

The packaging and deployment of this source code absolutely can be automated. For the automation most people use an automation tool (or a pipeline of tools) like TeamCity, TFS/VSTS, Jenkins and/or Octopus Deploy to package up the source code and (optionally) deploy it to a database (or several databases). This can either be done each commit or at the click of a button. Of course, exactly how this all works (and how well it all works) will depend on the tools you use.

Given that there are so many options it's not possible to provide a straight forward step by step solution without knowing which database source control tool and which automation tool(s) for builds/release management you use or without recommending one. There is also quite a lot involved here and way more than can be discussed in a single SO response.

However, adopting database source control and automating the release process is fantastically valuable so I do encourage you to keep going. It's clear from your question that you want to improve your processes. :-)

You are probably best off starting by looking at one of the following (or looking up any of the other names I mentioned above):

• Automating with SSDT (Microsoft) <- Blog post by Phil Factor
• Redgate ReadyRoll (Third party)
• FlyWay (Open source)

Separately it appears you have an audit concern. Keeping track of the changes that occur directly on production, for example, when people make hot fixes without going through source control. There's another great Phil Factor blog post on this topic that details how to create your own automated process for tracking drift. However, if I was you I'd look at Redgate DLM Dashboard. It's a third party tool but it's free so why waste time re-inventing the wheel?

If you would like further support/training my company, DLM Consultants, runs weekly online workshops (in partnership with Redgate) where you'll get hands on practice setting up source control, CI and release management processes for SQL Server.

You may need to rethink your approach a little.

In general, the workflow of

Make changes in database -> Update Database Project -> Commit changes to Source Control

is not well supported by SSDT; in particular the part about updating a project based on changes to a database.

If this were a .NET project, would you be patching the binaries on the server using a hex editor and then decompiling the results into a csproj and associated cs files to store in source control? This sounds ridiculous, but it is analogous to the workflow you are suggesting for your database projects.

I believe the Redgate tools - with which I am not particularly familiar - have some support for updating source control from a deployed database. I am however familiar enough with said tools to know that the intended use case is not

Make changes in production -> Update Source Control

IMV, You should probably be looking to solve the "source control" and "audit" problems separately.

To do this (with SSDT), you only need to update the database project manually once, and add the resulting files to source control.

After that, you can make changes in the project first, commit them to source control, and then deploy these changes to your database. This process is easily automated.

Presumably it is only a subset of the data in the database - the "static" or "reference" data - that you need to store in source control? The most common way to do this is using post-deployment scripts in the database project.

Regarding audit, you have a couple of options. Given that the history of your "deliberate" changes will be in source control, the main concern of audit is detecting uncontrolled change in production. This can be done with database triggers, or, I believe, by some commercial products (that generally use database triggers behind the scenes). On detecting such changes, you then have a couple of options - roll back the change, fire the DBA, update the files in source control, etc, etc. I'm not sure it's sensible to automate this part of the process, as you will probably want to consider why these changes have occurred.