Redgate Comparison SDK Source Control Source Database

Question

I'm working with the Redgate SQL Comparison SDK at the moment and have got it set up to nicely diff 2 databases.

What I would like to do now is be able to diff an sqlproj from source control with a destination database.

I have tried pulling the sql files using the tfs/vsts SDKs but to no avail.

Is there any way to either build a sqlproj from source control into a dacpac and then pull this in as a source database, or to directly pull the sqlproj in as a source?

Edit:

My ultimate goal with this is to be able to basically compare the version of the database that is in source control with the the database running across many different environments and create delpoyment scripts for the diffs.

I have another couple of Redgate tools that accomplish this (SQL compare & SQL Source), but these can only be installed on 1 (maybe 2 max?) devices, the difficulty I have is in using Amazon RDS (where the endpoints are unreachable outside the VPC), I cannot connect one central install of these tools to all of my environments, and I can't buy an additional license for every environment. So I was trying to use the Comparison SDK to attempt to "roll my own" middle ground.

Many Thanks,

Answer 1

I also work at Redgate, please do email me via [email protected] if you want to go into more details into your specific questions and I'll set up a call for us.

In general the process that Redgate recommends for what you're doing below would be to keep the canonised schema that you want all the database to have in version control. You could get that schema in either by each developer using the SQL Source Control product to bring their changes in from SSMS as they develop them, or by using the SQL Compare product to put a version in at the end of a sprint.

You can then use our DLM Automation tools in conjunction with a CI server to automate creating difference reports and sync scripts for your target servers. DLM Automation is a set of PowerShell commandlets and plugins for common CI servers like TeamCity, Jenkins, VSTS, TFS etc. You could also use the SQL Compare Pro command line.

If your whole team have our SQL Toolbelt product then you're licensed to install the DLM Automation tools as many times as you like on build/release agents, so you don't need additional licences per environment.

Answer 2

Are you doing this in the context of an automation build/ci system? You mention VSTS, so the way this normally works is that this would have already pulled the files from source control. Once the files are in the build agent's working folder, you should be able to point the SDK (or SQL Compare command line) at this. Bear in mind that a sql proj isn't an officially supported data source for Redgate tools, although it will work in many instances.

It would be good if you could edit your question and give some background on the higher level problem you're trying to solve just in case we (I work for Redgate) can recommend a more suited set of tools or techniques.