Red Gate MSBuild Tasks
I've been sitting on these tasks for some time and decided it was about time to share them. I had wanted to write up a full article on their use but this will have to do for now, so here are the two class files I wrote and at the bottom is usage.
Enjoy.
RedGateSyncronizeSchema.cs
using System;
using Microsoft.Build.Framework;
using Microsoft.Build.Utilities;
using RedGate.SQLCompare.Engine;
using RedGate.SQL.Shared;
namespace BuildLibrary
{
public class RedGateSyncronizeSchema : Task
{
#region Properties
private string _OriginServer;
[Required]
public string OriginServer
{
get { return _OriginServer; }
set { _OriginServer = value; }
}
private string _OriginDatabase;
[Required]
public string OriginDatabase
{
get { return _OriginDatabase; }
set { _OriginDatabase = value; }
}
private string _OriginUsername;
[Required]
public string OriginUsername
{
get { return _OriginUsername; }
set { _OriginUsername = value; }
}
private string _OriginPassword;
[Required]
public string OriginPassword
{
get { return _OriginPassword; }
set { _OriginPassword = value; }
}
private string _TargetServer;
[Required]
public string TargetServer
{
get { return _TargetServer; }
set { _TargetServer = value; }
}
private string _TargetDatabase;
[Required]
public string TargetDatabase
{
get { return _TargetDatabase; }
set { _TargetDatabase = value; }
}
private string _TargetUsername;
[Required]
public string TargetUsername
{
get { return _TargetUsername; }
set { _TargetUsername = value; }
}
private string _TargetPassword;
[Required]
public string TargetPassword
{
get { return _TargetPassword; }
set { _TargetPassword = value; }
}
#endregion
public override bool Execute()
{
#region Create and Register the two Database Servers
Log.LogMessage(MessageImportance.High, "================= Red Gate Syncronize Schema =================");
Log.LogMessage(MessageImportance.High, "Creating and Registering the two Database Servers ...");
Database OriginDB = new Database();
Database TargetDB = new Database();
OriginDB.Register(new ConnectionProperties(this.OriginServer, this.OriginDatabase, this.OriginUsername, this.OriginPassword), Options.Default);
TargetDB.Register(new ConnectionProperties(this.TargetServer, this.TargetDatabase, this.TargetUsername, this.TargetPassword), Options.Default);
Log.LogMessage(MessageImportance.High, "Registered.");
#endregion
#region Work out the Differences
Log.LogMessage(MessageImportance.High, "Working out the differences ...");
Differences differences = OriginDB.CompareWith(TargetDB, Options.Default);
foreach (Difference dif in differences)
{
dif.Selected = true;
}
Log.LogMessage(MessageImportance.High, "Compared.");
#endregion
#region Perform the Syncronization
Log.LogMessage(MessageImportance.High, "Syncronizing ...");
Work work = new Work();
Log.LogMessage(MessageImportance.High, "Building differences ...");
work.BuildFromDifferences(differences, Options.Default, true);
Log.LogMessage(MessageImportance.High, "Differences built ...");
Log.LogMessage(MessageImportance.High, "Executing Workload ... ");
RedGate.SQL.Shared.Utils u = new RedGate.SQL.Shared.Utils();
u.ExecuteBlock(work.ExecutionBlock, this.TargetServer, this.TargetDatabase, false, this.TargetUsername, this.TargetPassword);
Log.LogMessage(MessageImportance.High, "Executed Workload.");
Log.LogMessage(MessageImportance.High, "Syncronized.");
#endregion
#region Destroy the Database objects
OriginDB.Dispose();
TargetDB.Dispose();
#endregion
return true;
}
}
}
RedGateSyncronizeData.cs
using System;
using Microsoft.Build.Framework;
using Microsoft.Build.Utilities;
using RedGate.SQLDataCompare.Engine;
using RedGate.SQL.Shared;
namespace BuildLibrary
{
public class RedGateSyncronizeData : Task
{
#region Properties
private string _OriginServer;
[Required]
public string OriginServer
{
get { return _OriginServer; }
set { _OriginServer = value; }
}
private string _OriginDatabase;
[Required]
public string OriginDatabase
{
get { return _OriginDatabase; }
set { _OriginDatabase = value; }
}
private string _OriginUsername;
[Required]
public string OriginUsername
{
get { return _OriginUsername; }
set { _OriginUsername = value; }
}
private string _OriginPassword;
[Required]
public string OriginPassword
{
get { return _OriginPassword; }
set { _OriginPassword = value; }
}
private string _TargetServer;
[Required]
public string TargetServer
{
get { return _TargetServer; }
set { _TargetServer = value; }
}
private string _TargetDatabase;
[Required]
public string TargetDatabase
{
get { return _TargetDatabase; }
set { _TargetDatabase = value; }
}
private string _TargetUsername;
[Required]
public string TargetUsername
{
get { return _TargetUsername; }
set { _TargetUsername = value; }
}
private string _TargetPassword;
[Required]
public string TargetPassword
{
get { return _TargetPassword; }
set { _TargetPassword = value; }
}
#endregion
public override bool Execute()
{
#region Create the Database and Session objects
Log.LogMessage(MessageImportance.High, "================= Red Gate Syncronize Data =================");
Log.LogMessage(MessageImportance.High, "Creating and Registering the two Database Servers ...");
SqlProvider provider = new SqlProvider();
ComparisonSession session = new ComparisonSession();
Database OriginDB = provider.GetDatabase(new SqlConnectionProperties(this.OriginServer, this.OriginDatabase, this.OriginUsername, this.OriginPassword));
Database TargetDB = provider.GetDatabase(new SqlConnectionProperties(this.TargetServer, this.TargetDatabase, this.TargetUsername, this.TargetPassword));
Log.LogMessage(MessageImportance.High, "Registered.");
#endregion
#region Compare the Databases
Log.LogMessage(MessageImportance.High, "Working out the differences ...");
Tables commonTables = Tables.CreateFromIntersection(OriginDB.Tables, TargetDB.Tables);
TableComparisonSettings settings = new TableComparisonSettings();
foreach (Table table in commonTables)
{
settings.Add(new TableComparisonSetting(table.FullyQualifiedName, table.Fields, table.PrimaryKey.Fields));
}
session.CompareDatabases(OriginDB, TargetDB, settings);
Log.LogMessage(MessageImportance.High, "Compared.");
#endregion
#region Set all Tables to be Syncronized
foreach (TableDifference difference in session.TableDifferences)
{
Table table = OriginDB.Tables[difference.Name];
if (IsView(table))
{
difference.Selected = false;
}
else
{
difference.Selected = true;
}
}
#endregion
#region Perform the Syncronization
Log.LogMessage(MessageImportance.High, "Syncronizing ...");
ExecutionBlock block = provider.GetMigrationSQL(session, true);
Utils utils = new Utils();
utils.ExecuteBlock(block, this.TargetServer, this.TargetDatabase, false, this.TargetUsername, this.TargetPassword);
Log.LogMessage(MessageImportance.High, "Syncronized.");
#endregion
#region Destroy the objects
block.Dispose();
session.Dispose();
#endregion
return true;
}
private static bool IsView(RedGate.SQLDataCompare.Engine.Table table)
{
SqlTable sqlTable = table as SqlTable;
if (sqlTable == null)
{
return false;
}
return sqlTable.IsView;
}
}
}
Then to use these with MSBuild: -
<UsingTask TaskName="BuildLibrary.RedGateSyncronizeSchema" AssemblyFile="..\BuildLibrary\bin\Release\BuildLibrary.dll" />
<UsingTask TaskName="BuildLibrary.RedGateSyncronizeData" AssemblyFile="..\BuildLibrary\bin\Release\BuildLibrary.dll" />
<RedGateSyncronizeSchema OriginDatabase="myDB" OriginServer="local" OriginUsername="sa" OriginPassword="pass" TargetDatabase="myDB" TargetServer="remote" TargetUsername="sa" TargetPassword="pass" />