Automate Visual Studio DB Schema Compare and check into GIT

Question

I have a Visual Studio 2015 DB project for SQL Server DB, where I can do a schema compare/data compare and check in the project into Git manually. I wanted to automate this complete process of doing schema/data compare, generate the scripts and check it in into Git. Is it possible to do that? If so how?

May be I shall do something like this? Automating Visual Studio with EnvDTE

Overact

Free? No. However: red-gate.com/products/sql-development/sql-source-control – Phenacaine

Well, there's "sqlpackage" for the schema compare part. Red-Gate has SQL Data Compare. However, I'm not sure why you'd check the schema scripts into Git. Wouldn't it make sense to check in the project and snapshot it for each version? (I can see a case for data scripts, but even then, that gets tricky.) – Fairly

Yes i wanted to check in the project itself not the scripts. Just edited/modified in the question. – Overact

as far as I know @kevin-cunnane has advised us that this is not possible on the command line yet. I think they might be looking at it. As Peter-Schott mentioned above, just commit the whole project and let SQLPackage determine schema changes when you deploy. – Calathus

Can you tell us a bit more about what you're planning to do when you have that data in git please? For example are you planning to do automated deployments, or check to see if that matches your live database, or gather an audit history of how your objects have changed. – Confirm

We wanted to match schema/data to the live database and also need an audit history of the objects. The long-term plan is to automate the database deployments. So in short we wanted to do all of those mentioned. – Overact

Answer 1

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.

Tullusus

Answer 2

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.

Geophilous