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

SJWSJW Posts: 5 New member
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?
Tagged:

Answers

  • 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!
  • 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 :)
  • SJWSJW Posts: 5 New member
    @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 DEddie D Posts: 1,780 Rose Gold 5
    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 
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • SJWSJW Posts: 5 New member
    @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.
  • SJWSJW Posts: 5 New member
    @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 DEddie D Posts: 1,780 Rose Gold 5
    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   
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.