SQL Comparison SDK

Painless Daily Synchronization with the SQL Comparison SDK

Case Study by David Yousefi (Developer and DBA at Stibbe)

Like many people, I find myself filling two roles within my company: .NET developer and DBA; building applications based on the business requirements, and also managing the SQL Server environment.

I was recently working on a project which required lots of complex SQL and .NET development, and I needed a way to synchronise my work across different servers and databases without adding to that complexity. Rather than build my own solution (possible, but time-consuming, given that ease and speed were a necessity), I decided to start looking for a 3^rd party tool.

In my initial foray across the internet, I stumbled across Red Gate’s SQL Comparison SDK, which seemed to do everything I needed (it was automatic, comprehensive, and seemed very easy to deploy.) As it happens, after giving the SDK a try, I also noticed Red Gate’s SQL Source Control addin for SSMS, which I’m now using to connect my databases to Subversion, but that’s a different story.

Requirements? Check!

A requirement of the project in hand was that certain sets of data be split between different databases (for development), and then the schema and data changes of those databases needed to be synchronised. Daily.

Not the most elegant process in the world, but that’s what the requirements said and, thankfully, the SQL Comparison SDK let me handle the whole process automatically with a remarkable degree of control. Although there were a few tools around that covered similar functionality, I have to admit that I made the decision to evaluate the SDK more deeply straight away, just based on the impressive information I saw on Red Gate’s website.

And that decision proved to be spot-on – the SDK engine has saved me huge amounts of development time, and significantly reduced my overheads. With this tool in my arsenal, I no longer have to worry about data or schema updates at all, as they are all handled automatically. If I had tried to build a similar system on my own, it would have required a lot of complex procedures, not to mention constant maintenance!

To top it off, from my manager’s perspective, this means that even if I’m not in the office for some reason, the process for synchronising our data and schemas still carries on without a hitch.

Verdict

The SQL Comparison SDK and the SQL Source Control tool are very cool! They churn away quietly in the background, not noticed by anybody (except me), but now quite essential to the smooth running of the company’s IT systems.

I’ve had a great experience with Red Gate tools, as they’ve made a major headache of mine simply disappear. I’d certainly recommend that other developers and database administrators take advantage of the great tools Red Gate has created, as the benefits are valuable.

In fact, just to prove how easy it is to use Red Gate tools (and the SQL Comparison SDK in particular), here’s a snippet of the code used in my application to run the synchronizations. It’s all pretty easy to understand. I have two SQL servers (a source and a destination), each of which contains a database. As you can see in the code, I’ve defined a ComparisonSession, Project, ExecutionBlock and BlockExecutor. After creating some table mapping, field mapping, and a WhereClause exception, I initialize the ComparisonSession and, after the CompareDatabases method, I execute the synchronization block on the destination database. Simple!

using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using RedGate.SQLCompare.Engine;
using RedGate.SQLDataCompare.Engine;
using RedGate.SQLDataCompare.Engine.ResultsStore;
using RedGate.Shared.Utils;
using RedGate.Shared.SQL.ExecutionBlock;
using System.Threading;
 
     private ComparisonSession m_Session = null;
     Project project;
     ExecutionBlock block;
     private BlockExecutor executor;

     private void SyncArchiveDatabase()
     {
       try
       {
         project = new Project();
         project.DataSource1.ServerName = Program.SourceServerName;
         project.DataSource2.ServerName = Program.DestinationServerName;
         project.DataSource1.DatabaseName = Program.SourceDatabaseName;
         project.DataSource2.DatabaseName = Program.DestinationDatabaseName;
         project = Project.LoadFromDisk(path);
 
         Database db1 = new Database();
         Database db2 = new Database();
         ConnectionProperties sourceConnectionProperties = new
                           ConnectionProperties(Program.DevServerName,
         Program.DevDatabaseName, "username", "password");
         ConnectionProperties targetConnectionProperties = new
                           ConnectionProperties(Program.LiveServerName,
         Program.LiveDatabaseName, "username","password");
         //Register the Databases
         db1.RegisterForDataCompare(sourceConnectionProperties, Options.Default);
         db2.RegisterForDataCompare(targetConnectionProperties, Options.Default);
 
         TableMappings mappings = new TableMappings();
         mappings.Options = new EngineDataCompareOptions(MappingOptions.Default,
           ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
           SqlOptions.Default);
 
TableMapping custom1 = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[CUSTOM1]"],
db2.Tables["[MHGROUP].[CUSTOM1]"]);
TableMapping custom2 = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[CUSTOM2]"],
db2.Tables["[MHGROUP].[CUSTOM2]"]);
TableMapping custom3 = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[CUSTOM3]"],
db2.Tables["[MHGROUP].[CUSTOM3]"]);
TableMapping custom4 = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[CUSTOM4]"],
db2.Tables["[MHGROUP].[CUSTOM4]"]);
TableMapping custom5 = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[CUSTOM5]"],
db2.Tables["[MHGROUP].[CUSTOM5]"]);
TableMapping custom6 = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[CUSTOM6]"],
db2.Tables["[MHGROUP].[CUSTOM6]"]);
TableMapping custom7 = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[CUSTOM7]"],
db2.Tables["[MHGROUP].[CUSTOM7]"]);
TableMapping custom8 = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[CUSTOM8]"],
db2.Tables["[MHGROUP].[CUSTOM8]"]);
TableMapping custom9 = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[CUSTOM9]"],
db2.Tables["[MHGROUP].[CUSTOM9]"]);
TableMapping custom10 = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[CUSTOM10]"],db2.Tables["[MHGROUP].[CUSTOM10]"]);
TableMapping docusers = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[DOCUSERS]"],db2.Tables["[MHGROUP].[DOCUSERS]"]);
TableMapping groups = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[GROUPS]"],db2.Tables["[MHGROUP].[GROUPS]"]);
TableMapping groupmems = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[GROUPMEMS]"],db2.Tables["[MHGROUP].[GROUPMEMS]"]);
TableMapping usernumdb = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[USERNUMDB]"],db2.Tables["[MHGROUP].[USERNUMDB]"]);
TableMapping doctypes = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[doctypes]"],db2.Tables["[MHGROUP].[doctypes]"]);
TableMapping docclasses = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[docclasses]"],db2.Tables["[MHGROUP].[docclasses]"]);
TableMapping docsubclasses = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[docsubclasses]"],db2.Tables["[MHGROUP].[docsubclasses]"]);
         TableMapping docapps = (TableMapping)mappings.Join(db1.Tables["[MHGROUP].[APPS]"], db2.Tables["[MHGROUP].[APPS]"]);
 
         groupmems.MatchingMappings.Clear();
         usernumdb.MatchingMappings.Clear();
         groupmems.MatchingMappings.Add(groupmems.FieldMappings["USERNUM"]);
         groupmems.MatchingMappings.Add(groupmems.FieldMappings["GROUPNUM"]);
         groupmems.Where = new WhereClause("GROUPNUM NOT IN (SELECT GROUPNUM from
                           [MHGROUP].[GROUPS] WHERE GROUPID ="admin")");
         usernumdb.MatchingMappings.Add(usernumdb.FieldMappings["USERNUM"]);
         custom2.RefreshMappingStatus();
         groupmems.RefreshMappingStatus();
         usernumdb.RefreshMappingStatus();
         docusers.RefreshMappingStatus();
mappings.Add(custom1); mappings.Add(custom2);
mappings.Add(custom3); mappings.Add(custom4);
mappings.Add(custom5); mappings.Add(custom6);
mappings.Add(custom7); mappings.Add(custom8);
mappings.Add(custom9); mappings.Add(custom10);
mappings.Add(docusers); mappings.Add(groups);
       mappings.Add(groupmems); mappings.Add(usernumdb);
 
         using (m_Session = new ComparisonSession())
         {
           m_Session.Options = mappings.Options;
           m_Session.CompareDatabases(db1, db2, mappings, SessionSettings.Default);
           SqlProvider provider = new SqlProvider();
           provider.Options = m_Session.Options;
 
           block = provider.GetMigrationSQL(m_Session, true);
 
           executor = new BlockExecutor();
           executor.ExecuteBlock(block, Program.DestinationServerName,
           Program.DestinationDatabaseName, false, "username","password");
           mSync_dlgMessages();
           Do_RunnAddMessage();
           block.Dispose();
         }
       }
       catch (Exception ex)
       {
         MessageBox.Show(ex.Message, "Error");
       }