First up, you need to have a little think about the problem at hand - you have a number of databases that you need to sync. SQL Compare gives you a nice GUI to do all of this, which is fine if you only have one db to worry about (or at a stretch, a couple.) but if you have a large amount of db's spread across multiple environments this will become a problem very quickly. To get around this problem, you can make use of the command line version of SQL Compare or you can do what I did and make use of the API to automate things just a little further. One clear benefit I have noticed so far is that using the API over the command line version results in a quicker operation.
Another thing to consider is this: If you have more than one db to manage, you are going to have to put the login credentials somewhere. This will potentially panic your IT manager or administrator - they dont really want login credentials floating around on machines, which is a very valid and fair point. Froms the example I have below, you will see that I am managing my login credentials by placing them in NAnt's app.config. This will send a shiver of fear down some peoples spines, as this means that they are there for all to see in plain text - but, dont panic. It is a very simple thing to encrypt them so that no one can see them. Just plonk all your db credentials in there, encrypt it and make sure that this .config is deployed correctly when ever the details change.
I am sure there will be other considerations to take into account depending on your own setup (there always is). But for me, this is all I needed. So, lets move on - what do I need to capture in my NAnt build script? Well, if all the db credentials are stored away in a .config file then all I need to do is retrieve them by name. This means that in my NAnt script, I just need to specify the source and destination db's based on the key value, e.g;
<add value="db-server-name, db-name, username, password" key="test">
It's up to you if you want this to be in plain text in NAnt's config file, personally I would encrypt it - but thats just me.
Now, onto the task. As I said above, I am only getting two attributes: the source and destination database names. This means the task would look a little like this:
[TaskName("schema-sync")]
public class SchemaSync : Task
{
public Sync sync = new Sync();
private string _sourceDBName;
[TaskAttribute("source", Required = true)]
public string SourceDBName
{
get { return _sourceDBName; }
set { _sourceDBName = value; }
}
private string _destinationDBName;
[TaskAttribute("destination", Required = true)]
public string DestinationDBName
{
get { return _destinationDBName; }
set { _destinationDBName = value; }
}
Now, we get onto the good stuff - getting the sync underway!
I am going to present this in the way I figured it out - I am fairly sure that a lot of people reading this will want to rejig it to fit there own needs. Lets start off getting a collection of differences between two databases:
private Differences GetDifferences(string dbServerNameSource,
string dbUserNameSource,
string dbPasswordSource,
string dbNameSource,
string dbServerNameDest,
string dbUsernameDest,
string dbPasswordDest,
string dbNameDest)
{
Differences differences = null;
Database db1 = new Database();
Database db2 = new Database();
db1.Register(new ConnectionProperties(dbServerNameSource,
dbNameSource,
dbUserNameSource,
dbPasswordSource),
Options.Default);
db2.Register(new ConnectionProperties(dbServerNameDest,
dbNameDest,
dbUsernameDest,
dbPasswordDest),
Options.Default);
differences = db1.CompareWith(db2, Options.Default);
foreach (Difference difference in differences)
{
difference.Selected = ExcludeObject(difference);
}
return differences;
}
This method returns a collection of differences between two database's as supplied through the NAnt script. Note that this doesnt actually affect any changes at all on your db's, all it does it find out just how many differences there are. Next, we want to build the SQL needed to sync the schema from DB1 to DB2, this is fairly simple:
private ExecutionBlock ScriptDifferences(Differences differences)
{
Work work = new Work();
work.BuildFromDifferences(differences, Options.Default, true);
ExecutionBlock block = work.ExecutionBlock;
return block;
}
The returned block now contains the SQL script that will alter the destination db. To execute this SQL, we need the following:
private BlockExecutor SyncDifferences(string dbServerNameDest,
string dbNameDest,
string dbUserNameDest,
string dbPasswordDest,
ExecutionBlock block)
{
BlockExecutor executor = new BlockExecutor();
executor.ExecuteBlock(block,
dbServerNameDest,
dbNameDest,
true,
dbUserNameDest,
dbPasswordDest);
return executor;
}
And thats it, this will effectively sync db1 to db2 for you. Incidentally, this seemed to me to be a lot simpler that fudging a script together using the commandline Compare app... You will notice that I am also filtering out some database's from the compare it self, the following will do this for you:
private static bool ExcludeObject(Difference difference)
{
if (difference.DatabaseObjectType != ObjectType.User &&
difference.DatabaseObjectType != ObjectType.Role &&
difference.DatabaseObjectType != ObjectType.Queue &&
difference.DatabaseObjectType != ObjectType.Service &&
SpecialObjects(difference) == false)
{
return true;
}
return false;
}
private static bool SpecialObjects(Difference difference)
{
if ((difference.DatabaseObjectType == ObjectType.Table &&
difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql"))
(difference.DatabaseObjectType == ObjectType.StoredProcedure &&
difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql"))
(difference.DatabaseObjectType == ObjectType.StoredProcedure &&
difference.Name.ToLower().StartsWith("[dbo].[sqlquery")))
{
return true;
}
return false;
}
To tie it all up, I have one public method, which is called from my task class:
public object SyncDB(string dbServerNameSource,
string dbUserNameSource,
string dbPasswordSource,
string dbNameSource,
string dbServerNameDest,
string dbUserNameDest,
string dbPasswordDest,
string dbNameDest)
{
object obj = null;
Differences diff = GetDifferences(dbServerNameSource,
dbUserNameSource,
dbPasswordSource,
dbNameSource,
dbServerNameDest,
dbUserNameDest,
dbPasswordDest,
dbNameDest);
ExecutionBlock script = ScriptDifferences(diff);
obj = SyncDifferences(dbServerNameDest,
dbNameDest,
dbUserNameDest,
dbPasswordDest,
script);
return obj;
}
Et voila, we now have enough code to automatically sync schema from one db to another. All that is left to do is to fit this all into my NAnt task. Below is the complete code I used:
using NAnt.Core;
using NAnt.Core.Attributes;
using NAnt.Core.Tasks;
using NAnt.Core.Util;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using RedGate.Shared.SQL.ExecutionBlock;
using RedGate.SQLCompare.Engine;
using Custom.NantTasks.RedGate.Compare;
namespace Custom.NantTasks
{
[TaskName("schema-sync")]
public class SchemaSync : Task
{
public Sync sync = new Sync();
private string _sourceDBName;
[TaskAttribute("source", Required = true)]
public string SourceDBName
{
get { return _sourceDBName; }
set { _sourceDBName = value; }
}
private string _destinationDBName;
[TaskAttribute("destination", Required = true)]
public string DestinationDBName
{
get { return _destinationDBName; }
set { _destinationDBName = value; }
}
protected override void ExecuteTask()
{
string dbServerNameSource;
string dbNameSource;
string dbUserNameSource;
string dbPasswordSource;
GetSourceDetails(out dbServerNameSource,
out dbNameSource,
out dbUserNameSource,
out dbPasswordSource);
string dbServerNameDest;
string dbNameDest;
string dbUserNameDest;
string dbPasswordDest;
GetDestDetails(out dbServerNameDest,
out dbNameDest,
out dbUserNameDest,
out dbPasswordDest);
try
{
SyncDB(dbServerNameSource,
dbNameSource,
dbUserNameSource,
dbPasswordSource,
dbServerNameDest,
dbNameDest,
dbUserNameDest,
dbPasswordDest);
}
catch (Exception e)
{
Log(Level.Error, "Sync failed");
throw new BuildException(e.Message);
}
}
private void SyncDB(string dbServerNameSource,
string dbNameSource,
string dbUserNameSource,
string dbPasswordSource,
string dbServerNameDest,
string dbNameDest,
string dbUserNameDest,
string dbPasswordDest)
{
Log(Level.Info, "Syncing {0} to {1}", SourceDBName, DestinationDBName);
try
{
sync.SyncDB(dbServerNameSource,
dbUserNameSource,
dbPasswordSource,
dbNameSource,
dbServerNameDest,
dbUserNameDest,
dbPasswordDest,
dbNameDest);
Log(Level.Info, "Sync complete");
}
catch (Exception e)
{
throw new BuildException(e.Message);
}
}
private void GetDestDetails(out string dbServerNameDest,
out string dbNameDest,
out string dbUserNameDest,
out string dbPasswordDest)
{
Log(Level.Info, "Getting credentials for: " + DestinationDBName);
string[] destDetails =
ConfigurationManager.AppSettings.GetValues(DestinationDBName)[0].ToString().Split(',');
dbServerNameDest = destDetails[0].Trim();
dbNameDest = destDetails[1].Trim();
dbUserNameDest = destDetails[2].Trim();
dbPasswordDest = destDetails[3].Trim();
}
private void GetSourceDetails(out string dbServerNameSource,
out string dbNameSource,
out string dbUserNameSource,
out string dbPasswordSource)
{
string[] sourceDetails =
ConfigurationManager.AppSettings.GetValues(SourceDBName)[0].ToString().Split(',');
Log(Level.Info, "Getting credentials for: " + SourceDBName);
dbServerNameSource = sourceDetails[0].Trim();
dbNameSource = sourceDetails[1].Trim();
dbUserNameSource = sourceDetails[2].Trim();
dbPasswordSource = sourceDetails[3].Trim();
}
}
}
GetDestinationDetails() and GetSourceDetails() handle the retrieval of db credentials from NAnt's .config file. To put this new task into use, the build script would look a little like this:
<?xml version="1.0" encoding="utf-8" ?>
<project name="Test" default="test">
<target name="Test">
<schema-sync source="WidGetDev" destination="WidGetLive" />
</target>
</project>
If an error is encountered, then this will be thrown as a build exception and fail the build (which is nice). If you are syncing a number of db's at one time it means the operation will stop at the last successful sync. If you have a number of db's to sync, then it would be a good idea to us NAnt's foreach over a list of db's that you want to sync. This lets you skip over anything you dont want to go from the source to the destination (like a db full of environment variables for instance).
And thats pretty much it, one simple and fairly quick way to use the power of RedGate's SQL Compare engine with NAnt. Thus giving you a nice opportunity to use CI servers like CruiseControl.Net or TeamCity etc.
Below is the complete code for the schema compare:
using NAnt.Core;
using NAnt.Core.Attributes;
using NAnt.Core.Tasks;
using NAnt.Core.Util;
using RedGate.Shared.SQL.ExecutionBlock;
using RedGate.SQLCompare.Engine;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Custom.NantTasks.RedGate.Compare
{
public class Sync
{
private Differences GetDifferences(string dbServerNameSource,
string dbUserNameSource,
string dbPasswordSource,
string dbNameSource,
string dbServerNameDest,
string dbUsernameDest,
string dbPasswordDest,
string dbNameDest)
{
Differences differences = null;
Database db1 = new Database();
Database db2 = new Database();
db1.Register(new ConnectionProperties(dbServerNameSource,
dbNameSource,
dbUserNameSource,
dbPasswordSource),
Options.Default);
db2.Register(new ConnectionProperties(dbServerNameDest,
dbNameDest,
dbUsernameDest,
dbPasswordDest),
Options.Default);
differences = db1.CompareWith(db2, Options.Default);
foreach (Difference difference in differences)
{
difference.Selected = ExcludeObject(difference);
}
return differences;
}
private ExecutionBlock ScriptDifferences(Differences differences)
{
Work work = new Work();
work.BuildFromDifferences(differences, Options.Default, true);
ExecutionBlock block = work.ExecutionBlock;
return block;
}
private BlockExecutor SyncDifferences(string dbServerNameDest,
string dbNameDest,
string dbUserNameDest,
string dbPasswordDest,
ExecutionBlock block)
{
BlockExecutor executor = new BlockExecutor();
executor.ExecuteBlock(block,
dbServerNameDest,
dbNameDest,
true,
dbUserNameDest,
dbPasswordDest);
return executor;
}
private static bool ExcludeObject(Difference difference)
{
if (difference.DatabaseObjectType != ObjectType.User &&
difference.DatabaseObjectType != ObjectType.Role &&
difference.DatabaseObjectType != ObjectType.Queue &&
difference.DatabaseObjectType != ObjectType.Service &&
SpecialObjects(difference) == false)
{
return true;
}
return false;
}
private static bool SpecialObjects(Difference difference)
{
if ((difference.DatabaseObjectType == ObjectType.Table &&
difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql"))
(difference.DatabaseObjectType == ObjectType.StoredProcedure &&
difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql"))
(difference.DatabaseObjectType == ObjectType.StoredProcedure &&
difference.Name.ToLower().StartsWith("[dbo].[sqlquery")))
{
return true;
}
return false;
}
public object SyncDB(string dbServerNameSource,
string dbUserNameSource,
string dbPasswordSource,
string dbNameSource,
string dbServerNameDest,
string dbUserNameDest,
string dbPasswordDest,
string dbNameDest)
{
object obj = null;
Differences diff = GetDifferences(dbServerNameSource,
dbUserNameSource,
dbPasswordSource,
dbNameSource,
dbServerNameDest,
dbUserNameDest,
dbPasswordDest,
dbNameDest);
ExecutionBlock script = ScriptDifferences(diff);
obj = SyncDifferences(dbServerNameDest,
dbNameDest,
dbUserNameDest,
dbPasswordDest,
script);
return obj;
}
}
}
No comments:
Post a Comment