Using SQL Toolkit + SQL Compare to sync partial databases

Comments

• Options

  Brian Donahue Posts: 6,590 Bronze 1

  November 9, 2006 9:00AM

  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
  	}
  }
  

  0 · Share on Twitter
• Options

  Justin Spindler Posts: 18

  November 9, 2006 9:19AM

  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.

  0 · Share on Twitter
• Options

  Brian Donahue Posts: 6,590 Bronze 1

  November 9, 2006 10:51AM

  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?

  0 · Share on Twitter
• Options

  Justin Spindler Posts: 18

  November 9, 2006 11:03AM

  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.

  0 · Share on Twitter
• Options

  Brian Donahue Posts: 6,590 Bronze 1

  November 12, 2006 5:27PM

  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.

  0 · Share on Twitter