SQL Compare SDK is Retired? Is there a documented walkthrough of using DLM?

SJW

First of all sorry if this has been asked before. I have searched but not found what I need.

Scenario
We have ~200 branches each using SQL Express (2014). These instances are not on a domain or even contactable from our development environment. I had intended to use SQL Compare SDK to write an internal application to ship a "Staging" schema and update the branch "Production" schema with any required changes. However I now read that is retired.

How can I synchronise schemas without manual intervention in a remote location that is not connected to our development environment using DLM / SQL Change, or even SQL Compare! Is there a walk-through available?

PlantBasedSQL

Hi @SJW thank you for your post, there are some great walkthroughs of using SQL Change Automation here: https://www.red-gate.com/hub/university/sql-change-automation-with-migrations to help get you started.

There are a number of ways of using the tooling to achieve what you're looking for and I believe the most relevant pages in the Redgate docs to point you at for this would be regarding command line deployment here: https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployment-with-sql-change-automation-core/command-line-deployment-msbuild or SQLCMD package deployment here: https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployment-with-sql-change-automation-core/sqlcmd-package-deployment

Though we would be advocates of the full CI/CD model that, although Dev shouldn't have access to Test and Staging etc. there's nothing stopping you from using the SQL Change Automation Plugins for various build and release tools such as Azure DevOps to carry out most of this work. You can read more about the model here if you aren't already aware of it: https://www.red-gate.com/solutions/need/automate

Let me know if you have any questions about this, always happy to help!

PlantBasedSQL

Oh I should also mention the SQL Packager Utility: https://documentation.red-gate.com/sqp8 that you can use to package up any scripts generated by SQL Change Automation if needs be

SJW

@dOCTOClone Thank you for your detailed replies. However I still cannot see how the links will provide the solution I need.

The tools you link to appear to require a development database and a known deployment database which is contactable from our development environment?

We have ~200 remote branches which could have any number of differences in their current schema compared with what it "should" be. They are on different versions of software and historically changes have not been audited. Frankly it's a mess, and now I've joined the company I'm trying to sort it out.

I'm looking for a way for a routine to run on the remote client that will dynamically generate the required SQL to update it to a single given schema. SQLCompare seems perfect but I'm not about to purchase 200 licenses and manually go round each branch just because of our network setup. Hence me investigating SQLCompare API (which seems to work by the way).

Eddie D

Hi, 

There is no simple example available or walk-through for comparing one source database to many / multiple target databases.

The SQL Comparison SDK was retired some 18 months ago and does not support the latest SQL Compare and SQL Data Compare versions.  

SQL Compare, will only compare a single pair of data sources in one comparison and subsequent deployment.

Using the SQL Compare Command Line, it is possible to schedule a batch file that performs the one to many comparisons and deployments.  However it will still only be comparing two databases for each comparison, as per the simple example below

REM --

"C:\Program Files (x86)\Red Gate\SQL Compare 13\sqlcompare.exe" /server1:MyLocalServer\SQL2014 /database1:AdventureWorks /username1:sa /password1:sch00lb@y /server2:RemoteServer1\SQL2014 /database2:AdventureWorks /username1:sa /password1:sch00lb@y /synchronize /loglevel:verbose

"C:\Program Files (x86)\Red Gate\SQL Compare 13\sqlcompare.exe" /server1:MyLocalServer\SQL2014 /database1:AdventureWorks /username1:sa /password1:sch00lb@y /server2:RemoteServer2\SQL2014 /database2:AdventureWorks /username1:sa /password1:sch00lb@y /synchronize /loglevel:verbose

and so...

Longer term, using SQL Change Automation Powershell with a build system and deployment system such as Octopus would be the way to go.  So whenever you make a change on the source build system, the Devops process within SCA and your build system will generate a nuget package and using Octopus, deploy  the package created to each of your target remote systems.

Many thanks
Eddie 

SJW

@Eddie D
Thanks for taking the time to respond. 

As it turns out I have just proven I can make some network config changes and be able to contact the remote databases directly, which is huge turning point for me! \o/

I understand I can only compare two databases at a time.

I'd still like to log how many differences, warnings etc for audit purposes. I can see there is at least an exit code so the command line is turning into an option and I'm sure if I trawl through the output I could still get the additional information.

However, I still don't see why the SDK is retired though; Surely it gives more control for developers. I'm sure I'll get over it but thought it important to provide customer feedback.

SJW

@Eddie D
Making the relevant changes to the command line examples you provided I have been able to get some output. I have a question though:

Is it feasible (or sensible) to launch 184 instances of the command line at the same time. Each would compare a single staging DB (local) with a difference remote DB (over a WAN (MPLS)).

Thanks.

Eddie D

Hi SJW

Thank you for your replies.

In the scenario I described, it is my understanding that only one instance of the command line would be in use for each comparison.  Unless I am incorrect, I believe there would not be 184 command line interfaces open simultaneously.

I will be honest, the scenario I have described as never been tested with this number of required comparisons.  I am aware that some users have performed between 10 and 20 comparisons in a single batch file.

I suspect a better solution would be to use SQL Change Automation Power Shell, with a build server such as Jenkins or TeamCity or Bamboo which will use the comparison engine in the background.  Once the build is complete use a release tool such as Octopus Deploy to deploy the build to each remote system.
The help information for SQL Change Automation is available here.  There is also training videos available using this link to the Redgate University that may help you get started.

Many Thanks
Eddie