Automating Red gate Source Control DB Linking and Commit to SVN

Question

We have a requirement for which we want to have Red Gate Source Control as a part of the Automated Interface.

We would like to programatically Link the Required Database and do Commit for the Changes to the Database.

Is this possible? If yes, which api should we use?

score 2 · Answer 1 · answered Jul 20 '16 at 15:47

I know this question is old but I'm doing exactly what you are describing.

I have a job that runs every X minutes and puts the current state of the database into version control (for us it's mercurial but you can achieve exactly the same thing with git or whatever).

cd c:\data\SourceCodeDirectory
hg pull
hg update
if not exist "c:\data\SourceCodeDirectory\databaseName" mkdir "c:\data\SourceCodeDirectory\databaseName"
cd "c:\Program Files (x86)\Red Gate\SQL Compare 11"
sqlcompare /s1:DBServer /db1:databaseName /scr2:"c:\data\SourceCodeDirectory\databaseName" /synchronize

cd c:\data\SourceCodeDirectory\databaseName
hg add
hg commit -m "Database Changes" -u DatabaseSchemaUser
hg push

Any changes made to the database will be in version control as soon as this job runs.

score 0 · Answer 2 · answered Jul 29 '13 at 10:23

That's a tough one. Depending on how you automate, you could use a combination of other programs. For instance, if you have some sort of CI system, you could automatically compare the files in source control to a live database using SQL Compare and tell it to synchronize. You would need the source control system's command-line interface to check the changes back in.

SQL Source Control, as a piece of software, was designed to work interactively only.

If you want to do this in code (c#, VB), then you could leverage the source control system's API to check in files if they have one. For instance, SVN has "SharpSVN" and there is also an API for TFS.

Thanks. We will look into this and if I still have queries I will raise them. — Bandita, Jul 30 '13 at 10:49

alroc · Answer 3 · 2013-07-29T14:06:26.657

0

Maybe this should be a comment, but I need formatting & space that isn't available there.

"Automated commits" like this are usually risky, because they risk breaking atomicity and loss of information. Consider these scenarios:

Two developers are working on independent changes in the database. They both alter their respective tables at the same time, and this automation picks that up and commits the table changes as one unit. How do you separate these two activities?
When the above happens, whose name goes in the repository change log?
A developer is working on an extensive change that involves changes to multiple tables & stored procedures. This automation commits each of those independently. How do you "bundle" these for logging & auditing, reviewing changes that have been made, etc.?
In my environment, every change committed to the code repository is required to have a change ID recorded in the commit message. How would this work here?

My recommendation is that you get the requirement changed. There's too many ways for unintended (bad) consequences to crop up.

edited Jul 29 '13 at 14:06

answered Jul 29 '13 at 11:30

alroc

27,574
6
51
97

I agree with you but the thing is, we are automating it in the test and production environment. The chances of these errors to occur is considerably less in the test and Production Environment. Also to answer for the Scenario #4, if we automate the process then the Change ID will be pertaining to the application and if anyone else tries to enter a change it will show their Name and we can find out if there was any sort of tampering. – Bandita Jul 30 '13 at 10:53
This shouldn't even be required in those environments, because there should be no changes going into them without first stopping in source control and your development servers. Do you have restricted access to test and production (separation of duties), and a well-defined process for promoting changes from development to test to production? If not, this will do nothing to fix that, and possibly make things more confused. If so, this shouldn't be needed. – alroc Jul 30 '13 at 11:19

score 0 · Answer 4 · answered Aug 05 '13 at 15:57

Are you doing this as part of an automated build process? If so, it would be helpful to know which build tool and which source control tool you're using.

This should provide some helpful resources:

http://www.red-gate.com/products/sql-development/sql-automation-pack/

There are NAnt and MSBuild scripts that can perform useful build/test tasks for your SQL Server database. If you want to do something more custom, you can use a combination of your version control system command line and the SQL Compare command line.

Automating Red gate Source Control DB Linking and Commit to SVN

4 Answers4