Automatic synchronization of two database schemas

Attachment: Firmware application. (Sample solution created to demonstrate automatic synchronization of database structures).

Introduction Making changes to database structure is one of the most time-consuming and routine operations developer of corporate solutions faces with. In case of frequent updates, schedule time of the project can be significantly extended. Existing tools solve this problem partially, as in most cases they require manual programming and installation of expensive software to synchronize database schemas on the client side or to make special patches for every modification of database structure.

Description of the problem

Requirement of modern software development methodologies (Extreme programming in particular) is to put software into operation as fast as possible. Such approach presupposes improvement of applications at their run-time. So, if you use databases, developer database structure often differs from that of application end user, as developer regularly extends and modifies source structure. In order to make current version of application run correctly, it is necessary to have equal developer and client database structures. This problem has some solutions, but performance of such solutions is often very poor.

Possible solutions of database structure synchronization problem

We won’t consider variant of deleting database on the client side and creating new one with the structure equal to developer database, as this variant is not acceptable.

At the first sight, the simplest variant is to form a set of SQL commands that should be executed to get database into shape. This approach as all others has its advantages and disadvantages. Cheapness of this variant can be conventionally labeled as advantage (conventionally, as everything depends on amount of modifications made by the developer). But this approach has a lot of disadvantages:

• If there are a lot of changes, developer will spend a lot of time to form a set of SQL commands;
• There is a risk of incomplete synchronization of database structures, as commands are formed manually, and human factor still stands;
• Upgrade from one version to another requires accurate execution of all sets of commands to upgrade to all intermediate database versions in the necessary order.

• Purchasing third-party tool or in-house development of such tool;
• Installing this tool on every customer computer;
• Synchronization of database using this instrument.

• Ready-made solutions are quite expensive. In-house development of such solution is even more expensive;
• Installation of the tool on the client side requires payment of royalties;
• Developer has to deliver third-party tools together with his software;
• Very often it is not possible to use such tools programmatically, so, intervention of a developer responsible for installation of the update on the client side is required. And this in its turn requires developers to train how to work with the tool.

• No need to use third-party tools;
• No need to pay royalties for every copy of the product developed with the use of SQL Schema Sync API;
• The product is easy to use; it features minimal set of settings, availability of complete product documentation and class reference integrated into MSDN;
• Time necessary to create and deploy new version of database is dramatically reduced. The product saves both developer and client time.

using System;

using System.Data;

using System.Text;

using System.Data.SqlClient;

using PerpetuumSoft.DataModel;

using PerpetuumSoft.DataModel.MsSql;

using PerpetuumSoft.DataModel.MsSql.Synchronizers;

namespace FirmwareApp

{

  public partial class MainForm : System.Windows.Forms.Form

  {

    public MainForm()

    {

      InitializeComponent();

    }

    private StringBuilder log = new StringBuilder();

    private SqlConnection targetConnection;

    private SqlTransaction targetTransaction;

    private string GetConnectionString(string serverName, string databaseName)

    {

      SqlConnectionStringBuilder sourceConnStr = new SqlConnectionStringBuilder(

        @"Integrated Security=True;Connect Timeout=30;User Instance=False;");

      sourceConnStr.DataSource = serverName;

      sourceConnStr.InitialCatalog = databaseName;

      return sourceConnStr.ConnectionString;

    }

    private void MainForm_Load(object sender, EventArgs e)

    {

      databaseSync.ScriptExecuting +=

        new EventHandler<ScriptExecuteEventArgs>(databaseSync_ScriptExecuting);

    }

    void databaseSync_ScriptExecuting(object sender,

      PerpetuumSoft.DataModel.ScriptExecuteEventArgs e)

    {

      log.AppendLine(e.Text);

      log.AppendLine();

    }

    private void synchronizeButton_Click(object sender, EventArgs e)

    {

      log = new StringBuilder();

      try

      {

        targetConnection = new SqlConnection(

          GetConnectionString(targetServerTextBox.Text, targetDbTextBox.Text));

        targetConnection.Open();

        targetTransaction = targetConnection.BeginTransaction();

        try

        {

          Database database = databaseSync.ReverseDatabase(

            GetConnectionString(sourceServerTextBox.Text, sourceDbTextBox.Text));

          databaseSync.UpdateDatabase(database, targetConnection, targetTransaction);

          targetTransaction.Commit();

          logTextBox.Text = log.ToString();

        }

        catch (Exception ex)

        {

          targetTransaction.Rollback();

          logTextBox.Text = log.ToString();

          logTextBox.Text += ex.Message;

        }

        finally

        {

          targetConnection.Close();

        }

      }

      catch (Exception ex)

      {

        logTextBox.Text = log.ToString();

        logTextBox.Text += ex.Message;

      }

    }

  }

}

Deployment of new database

CREATE TABLE [dbo].[computer]([computer_id] INT NOT NULL IDENTITY(1,1),[name] NCHAR(100) COLLATE Cyrillic_General_CI_AS NULL )

CREATE TABLE [dbo].[software]([sofware_id] INT NOT NULL IDENTITY(1,1),[computer_id] INT NOT NULL ,[name] NCHAR(100) COLLATE Cyrillic_General_CI_AS NULL ,[version] INT NULL ,[serialKey] VARCHAR(30) COLLATE Cyrillic_General_CI_AS NULL ,[manufacturer] VARCHAR(100) COLLATE Cyrillic_General_CI_AS NULL )

ALTER TABLE [dbo].[computer] ADD CONSTRAINT [PK_computer] PRIMARY KEY CLUSTERED ([computer_id] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

ALTER TABLE [dbo].[software] ADD CONSTRAINT [PK_software] PRIMARY KEY CLUSTERED ([sofware_id] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

ALTER TABLE [dbo].[software] WITH CHECK ADD CONSTRAINT [FK_software_computer] FOREIGN KEY (computer_id) REFERENCES [dbo].[computer] (computer_id)  ON UPDATE NO ACTION ON DELETE NO ACTION

Adding new table

ALTER TABLE [dbo].[software] DROP CONSTRAINT [FK_software_computer]

ALTER TABLE [dbo].[hardware] DROP CONSTRAINT [FK_hardware_computer]

ALTER TABLE [dbo].[software] DROP CONSTRAINT [PK_software]

ALTER TABLE [dbo].[computer] DROP CONSTRAINT [PK_computer]

ALTER TABLE [dbo].[computer] ADD TEMP_COLUMN VARCHAR(100) COLLATE Cyrillic_General_CI_AS NULL UPDATE [dbo].[computer] set TEMP_COLUMN = ([name])ALTER TABLE [dbo].[computer]

DROP COLUMN [name]

Execute sp_rename 'dbo.computer.TEMP_COLUMN','name','COLUMN'

ALTER TABLE [dbo].[software] ADD TEMP_COLUMN VARCHAR(100) COLLATE Cyrillic_General_CI_AS NULL UPDATE [dbo].[software] set TEMP_COLUMN = ([name])ALTER TABLE [dbo].[software]

DROP COLUMN [name]

Execute sp_rename 'dbo.software.TEMP_COLUMN','name','COLUMN'

ALTER TABLE [dbo].[software] ADD TEMP_COLUMN VARCHAR(30) COLLATE Cyrillic_General_CI_AS NULL UPDATE [dbo].[software] set TEMP_COLUMN = ([version])ALTER TABLE [dbo].[software]

DROP COLUMN [version]

Execute sp_rename 'dbo.software.TEMP_COLUMN','version','COLUMN'

ALTER TABLE [dbo].[software] ADD TEMP_COLUMN VARCHAR(2048) COLLATE Cyrillic_General_CI_AS NULL UPDATE [dbo].[software] set TEMP_COLUMN = ([serialKey])ALTER TABLE [dbo].[software]

DROP COLUMN [serialKey]

Execute sp_rename 'dbo.software.TEMP_COLUMN','serialKey','COLUMN'

ALTER TABLE [dbo].[computer] ADD CONSTRAINT [PK_computer] PRIMARY KEY CLUSTERED ([computer_id] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

ALTER TABLE [dbo].[software] ADD CONSTRAINT [PK_software] PRIMARY KEY CLUSTERED ([sofware_id] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

ALTER TABLE [dbo].[hardware] WITH CHECK ADD CONSTRAINT [FK_hardware_computer] FOREIGN KEY (computer_id) REFERENCES [dbo].[computer] (computer_id)  ON UPDATE NO ACTION ON DELETE NO ACTION

ALTER TABLE [dbo].[software] WITH CHECK ADD CONSTRAINT [FK_software_computer] FOREIGN KEY (computer_id) REFERENCES [dbo].[computer] (computer_id)  ON UPDATE NO ACTION ON DELETE NO ACTION

ALTER TABLE [dbo].[software] DROP CONSTRAINT [FK_software_computer]

ALTER TABLE [dbo].[hardware] DROP CONSTRAINT [FK_hardware_computer]

ALTER TABLE [dbo].[computer] DROP CONSTRAINT [PK_computer]

ALTER TABLE [dbo].[computer] ADD adminPassword VARCHAR(50) COLLATE Cyrillic_General_CI_AS NULL

ALTER TABLE [dbo].[computer] ADD CONSTRAINT [PK_computer] PRIMARY KEY CLUSTERED ([computer_id] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

ALTER TABLE [dbo].[hardware] WITH CHECK ADD CONSTRAINT [FK_hardware_computer] FOREIGN KEY (computer_id) REFERENCES [dbo].[computer] (computer_id)  ON UPDATE NO ACTION ON DELETE NO ACTION

ALTER TABLE [dbo].[software] WITH CHECK ADD CONSTRAINT [FK_software_computer] FOREIGN KEY (computer_id) REFERENCES [dbo].[computer] (computer_id)  ON UPDATE NO ACTION ON DELETE NO ACTION

1. conversion of password into convertible form (varchar bigint) is executed on server and password is saved in the same field;
2. carry data from adminPassword to temporary table on the client side, and after database is synchronized conversion of password to its hash-code is executed;
3. carry data from adminPassword to temporary table on server, and after database is synchronized conversion of password to its hash-code is executed.

• subscribe for the DatabaseUpdating event and execute SQL command in its handler:

  update computer set adminPassword = cast(substring(hashbytes('SHA1', adminPassword), 1, 7) as bigint).



Execution of this command will result in writing hash-code instead of the password itself in the adminPassword field. As this field will actually contain number (written as string), it will be easily converted to bigint when database is synchronized.

Realization:


 

void databaseSync_DatabaseUpdating1(object sender, DatabaseUpdatingEventArgs e)

{

  log.AppendLine("Database updating - begin");

  SqlCommand cmd = targetConnection.CreateCommand();

  cmd.Transaction = targetTransaction;

  cmd.CommandText = @"update computer set adminPassword =

    cast(substring(hashbytes('SHA1', adminPassword), 1, 7) as bigint)";

  log.AppendLine(cmd.CommandText);

  cmd.ExecuteNonQuery();

  log.AppendLine("Database updating - end");

• Result of application work:

Database updating - begin

update computer set adminPassword = cast(substring(hashbytes('SHA1', adminPassword), 1, 7) as bigint)

Database updating - end

ALTER TABLE [dbo].[software] DROP CONSTRAINT [FK_software_computer]

ALTER TABLE [dbo].[hardware] DROP CONSTRAINT [FK_hardware_computer]

ALTER TABLE [dbo].[computer] DROP CONSTRAINT [PK_computer]

ALTER TABLE [dbo].[computer] ADD TEMP_COLUMN BIGINT NULL

UPDATE [dbo].[computer] set TEMP_COLUMN = ([adminPassword])

ALTER TABLE [dbo].[computer] DROP COLUMN [adminPassword]

Execute sp_rename 'dbo.computer.TEMP_COLUMN','adminPassword','COLUMN'

ALTER TABLE [dbo].[computer] ADD CONSTRAINT [PK_computer] PRIMARY KEY CLUSTERED ([computer_id] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

ALTER TABLE [dbo].[hardware] WITH CHECK ADD CONSTRAINT [FK_hardware_computer] FOREIGN KEY (computer_id) REFERENCES [dbo].[computer] (computer_id)  ON UPDATE NO ACTION ON DELETE NO ACTION

ALTER TABLE [dbo].[software] WITH CHECK ADD CONSTRAINT [FK_software_computer] FOREIGN KEY (computer_id) REFERENCES [dbo].[computer] (computer_id)  ON UPDATE NO ACTION ON DELETE NO ACTION

• Retrieve data from adminPassword to a temporary table in the DatabaseUpdating event handler.

private DataTable tempTable = new DataTable();

 

void databaseSync_DatabaseUpdating2(object sender, DatabaseUpdatingEventArgs e)

{

  log.AppendLine("Database updating - begin");

  SqlDataAdapter adapter = new SqlDataAdapter(

    "SELECT computer_id, adminPassword FROM computer", targetConnection);

  adapter.SelectCommand.Transaction = targetTransaction;

  log.AppendLine(adapter.SelectCommand.CommandText);

  adapter.Fill(tempTable);

  SqlCommand cmd = new SqlCommand(

    "UPDATE computer SET adminPassword = NULL", targetConnection);

  cmd.Transaction = targetTransaction;

  log.AppendLine(cmd.CommandText);

  cmd.ExecuteNonQuery();

  log.AppendLine("Database updating - end");

}

• Execute update of rows in the computer table. Hash-code will be calculated for every row. GetHashCode() will be used for this purpose.

void databaseSync_DatabaseUpdated2(object sender, EventArgs e)

{

  SqlCommand cmd = targetConnection.CreateCommand();

  cmd.Transaction = targetTransaction;

  cmd.CommandText = @"UPDATE computer SET adminPassword = @adminPassword

    WHERE computer_id = @computer_id";

  cmd.Parameters.Add("@adminPassword", SqlDbType.Int);

  cmd.Parameters.Add("@computer_id", SqlDbType.Int);

  foreach (DataRow row in tempTable.Rows)

  {

    if (row["adminPassword"] == DBNull.Value)

      cmd.Parameters["@adminPassword"].Value = 0;

    else

      cmd.Parameters["@adminPassword"].Value = row["adminPassword"].GetHashCode();

    cmd.Parameters["@computer_id"].Value = row["computer_id"];

    cmd.ExecuteNonQuery();

  }

}

• DatabaseUpdate event handler:

void databaseSync_DatabaseUpdating3(object sender, DatabaseUpdatingEventArgs e)

{

  log.AppendLine("Database updating - begin");

  SqlCommand cmd = targetConnection.CreateCommand();

  cmd.Transaction = targetTransaction;

  cmd.CommandText = @"CREATE TABLE #tempTable (

    computer_id int, adminPassword varchar(50))";

  log.AppendLine(cmd.CommandText);

  cmd.ExecuteNonQuery();

 

  cmd.CommandText = "insert #tempTable select computer_id, adminPassword from computer";

  log.AppendLine(cmd.CommandText);

  cmd.ExecuteNonQuery();

 

  cmd.CommandText = "UPDATE computer SET adminPassword = NULL";

  log.AppendLine(cmd.CommandText);

  cmd.ExecuteNonQuery();

  log.AppendLine("Database updating - end");

}

• DatabaseUpdate event handler:

void databaseSync_DatabaseUpdated3(object sender, EventArgs e)

{

  log.AppendLine("Database updated - begin");

  SqlCommand cmd = targetConnection.CreateCommand();

  cmd.Transaction = targetTransaction;

  cmd.CommandText = @"update computer set adminPassword =

  ( select top 1 cast(substring(hashbytes('SHA1', adminPassword), 1, 7) as bigint)

    from #tempTable where #tempTable.computer_id = computer.computer_id)";

  log.AppendLine(cmd.CommandText);

  cmd.ExecuteNonQuery();

  log.AppendLine("Database updated - end");

}

• Application work result:

Database updating - begin

CREATE TABLE #tempTable (computer_id int, adminPassword varchar(50))

insert #tempTable select computer_id, adminPassword from computer

UPDATE computer SET adminPassword = NULL

Database updating – end

ALTER TABLE [dbo].[software] DROP CONSTRAINT [FK_software_computer]

ALTER TABLE [dbo].[hardware] DROP CONSTRAINT [FK_hardware_computer]

ALTER TABLE [dbo].[computer] DROP CONSTRAINT [PK_computer]

ALTER TABLE [dbo].[computer] ADD TEMP_COLUMN BIGINT NULL

UPDATE [dbo].[computer] set TEMP_COLUMN = ([adminPassword])

ALTER TABLE [dbo].[computer] DROP COLUMN [adminPassword]

Execute sp_rename 'dbo.computer.TEMP_COLUMN','adminPassword','COLUMN'

ALTER TABLE [dbo].[computer] ADD CONSTRAINT [PK_computer] PRIMARY KEY CLUSTERED ([computer_id] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

ALTER TABLE [dbo].[hardware] WITH CHECK ADD CONSTRAINT [FK_hardware_computer] FOREIGN KEY (computer_id) REFERENCES [dbo].[computer] (computer_id)  ON UPDATE NO ACTION ON DELETE NO ACTION

ALTER TABLE [dbo].[software] WITH CHECK ADD CONSTRAINT [FK_software_computer] FOREIGN KEY (computer_id) REFERENCES [dbo].[computer] (computer_id)  ON UPDATE NO ACTION ON DELETE NO ACTION

Database updated - begin

update computer set adminPassword =

      ( select top 1 cast(substring(hashbytes('SHA1', adminPassword), 1, 7) as bigint)

        from #tempTable where #tempTable.computer_id = computer.computer_id)

Database updated – end

void sync_SkipSynchronizing(object sender, SkipSynchronizingEventArgs e)

{

  if (e.OldObject != null && e.OldObject is Schema &&

    (e.OldObject as Schema).Name.ToUpper() == "SomeSchema".ToUpper())

    e.NeedSynchronizing = false;

  if (e.NewObject != null && e.NewObject is Schema &&

    (e.NewObject as Schema).Name.ToUpper() == "SomeSchema".ToUpper())

    e.NeedSynchronizing = false;

  if (e.NewObject != null && e.NewObject is SchemaObject &&

    (e.NewObject as SchemaObject).Schema.Name.ToUpper() == "SomeSchema".ToUpper())

    e.NeedSynchronizing = false;

  if (e.OldObject != null && e.OldObject is SchemaObject &&

    (e.OldObject as SchemaObject).Schema.Name.ToUpper() == "SomeSchema".ToUpper())

    e.NeedSynchronizing = false;

}