Using SQL Toolkit + SQL Compare to sync partial databases

Justin Spindler

Good evening,

My company is currently using SQL Toolkit to package schema updates for our software. This works exceptionally well for our main software package. However we have several subsidiary products which each depend on their own database objects which are not a part of the core product. These subsidiary products are developed out of band from the main product so might not necessarily be capable of following the same release cycle as the main product.

My question is how could we make use of SQL Toolkit to deploy partial database schema updates? Would it be preferable to use a source database which only contains the target database objects or would it be possible to programmatically modify the resultant database snapshot to remove the other objects?

Any assistance in this matter would be appreciated.

Brian Donahue

Hi Justin,

Provided you don't need to send any data and are concerned only with the schema, the SQL Toolkit os well suited to this task. The basic idea would be to take a 'snapshot' of the reference schema using SQL Compare, then putting the snapshot file and a custom Toolkit application into an Installer package and create a custom task that will synchronize the customer's on-site schema to the snapshot.

Using Visual Studio's Setup and Depolyment projects, you can create a new Installer Class to do your custom task. This installer class is called by the custom task and feeds the arguments to it. Here is an example of an Installer Class for synchronizing database schema as part of a deployment package:

using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration.Install;
using RedGate.SQLCompare.Engine;
using RedGate.SQL.Shared;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DB_Comparer
{
	/// <summary>
	/// Summary description for DBComparer.
	/// </summary>
	[RunInstaller(true)]
	public class DBComparer : System.Configuration.Install.Installer
	{
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;
		public DBComparer()
		{
			// This call is required by the Designer.
			InitializeComponent();

			// TODO: Add any initialization after the InitializeComponent call
		}
		protected void SyncDatabases(string DBServer, string DBName, string DB1Snapshot) 
		{
			//Set up a progress bar
						
			Database db1=new Database();
			Database db2=new Database();
			
			db1.LoadFromDisk(DB1Snapshot);
			db2.Status+=new StatusEventHandler(StatusCallBack);

			db2.Register(new ConnectionProperties(DBServer, DBName), Options.Default | Options.IgnorePermissions | Options.IgnoreUsers | Options.IgnoreUserProperties);

			Differences differences=db1.CompareWith(db2, Options.Default | Options.IgnorePermissions | Options.IgnoreUsers | Options.IgnoreUserProperties);

			foreach (Difference difference in differences)
			{
				//Do not select users and roles.
				if (difference.DatabaseObjectType!=ObjectType.User && difference.DatabaseObjectType!=ObjectType.Role) difference.Selected=true;
				else difference.Selected=false;
				//Do not drop things from the target database
				if (difference.ObjectIn2==null) difference.Selected=false;
			}

			Work work=new Work();

			//calculate the work to do using sensible default options
			//the script is to be run on WidgetProduction so the runOnTwo parameter is true
			work.BuildFromDifferences(differences, Options.Default | Options.IgnorePermissions | Options.IgnoreUsers | Options.IgnoreUserProperties, true);

			ExecutionBlock block=work.ExecutionBlock;
			//and run the SQL

			RedGate.SQL.Shared.BlockExecutor exe=new RedGate.SQL.Shared.BlockExecutor();
			
			exe.ExecuteBlock(block, DBServer, DBName);
			
			//dispose of the objects
			block.Dispose();
			db1.Dispose();
			db2.Dispose();
		}
		/// <summary>
		/// Used by GetFeedback
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void StatusCallBack(object sender, StatusEventArgs e)
		{
			//fired by the SqlProvider to indicate events

			if (e.Message!=null)
			{
				//TODO: Make some kind of progress thing
			}

			if (e.Percentage!=-1)
			{
			}

		}
		
		public override void Install(System.Collections.IDictionary stateServer) 
		{
			//this.Install(stateServer);
			SyncDatabases(this.Context.Parameters["DBServer"], this.Context.Parameters["DBName"],this.Context.Parameters["DB1Snapshot"]);
		}
		
		/// <summary> 
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}


		#region Component Designer generated code
/// I have left this out intentionally.
		private void InitializeComponent()
		{

		}
		#endregion
	}
}

Justin Spindler

Brian,

Thanks for the quick response. Is your recommendation then to have the model database from which the snapshots are generated only contain the objects we wish to propogate?

Our current development databases for these subsidiary products contain objects for both our full product and those specific to the subsidiary product. There are cross-dependencies between the two sets of objects. The goal is to be able to merge the objects for the subsidiary product into a database containing the full product objects and then have subsequent snapshots to upgrade just those objects as new releases are cut.

I've toyed with the idea of using extended properties to mark the objects for the product and then using reflection to attempt to remove all objects except those marked with the extended property, but I assume that would be an unsupported method.

Brian Donahue

The main product and the 'subsidiary products' are all using the same one database on the client? But the 'subsidiary product' schema is developed independently in its' own separate database?

Justin Spindler

Brian Donahue wrote:

The main product and the 'subsidiary products' are all using the same one database on the client? But the 'subsidiary product' schema is developed independently in its' own separate database?

Effectively yes. The main product is developed using it's own database which only include the database objects that pertain to the main product. The other products are developed against copies of that main database which include database objects specific to that product. It is the maintenance of those database objects which we are interested in. We are currently using SQL scripts, and you know how much of a mess that can be.

Brian Donahue

Hi,
This should work then. This Installer class code does not drop objects from the target database if they exist in the target database, but not in the snapshot. Actually, the project that I had written this for had a similar requirement. I have a databse where I keep a variety of tables that aren't related to the application I'm deploying. These tables are so inconsequential that they don't warrant having their own database, so when this project is deployed or upgraded, the installer leaves the tables unrelated to the application alone.

So this would be perfect for you -- create a snapshot of a schema usng SQL Compare for each of these modules, but only include the database objects that pertain to the individual module. You may have a bit of grief because you would need to build a separate database for each module in your development SQL server. SQL Compare snapshots must contain the entire database schema. If the customer adds the module to this application, only the module's database objects will be added,removed, or modified if the snapshot only contains the relevant database objects.

The other option would be take a snapshot of the entire database for the main application and all of the modules, and exclude the objects that you don't want to synchronize by name in the Toolkit project code. This would have the downside of making the application a bit more difficult to maintain, as if the main schema changes, you would need to ensure that the list of objects not to synchronize is maintained.