Synchronize database structure between applications

Anyone who has ever developed applications that use the database, probably faced with the problem of updating the database structure when deploying and updating the application.

The most common approach is to create a set of SQL scripts to modify the database structure from version to version. Of course, there is such a powerful tool as Red Gate , but firstly it is not free, and secondly it does not solve the problem of full automation of the update.

The migrations technology, first introduced in ORM Hibernate and implemented in Linq, is very good and convenient, but it involves the code first structure development strategy, which is very time-consuming for already existing projects, and using triggers, stored procedures and functions in the database makes the transition to code first virtually impracticable.

This article proposes an alternative approach to solving this problem, using the storage of the reference database structure in the XML file and automatic generation of the SQL script based on a comparison of the reference and the existing structure. So, let's begin...

Generating an XML file with database structure

For the experiments we will use the DbSyncSample database. The script for creating the database is shown below.

USE [DbSyncSample] GO /****** Object: Table [dbo].[Orders] Script Date: 06/01/2017 10:37:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Orders]( [Id] [int] IDENTITY(1,1) NOT NULL, [OrderNumber] [nvarchar](50) NULL, [OrderTime] [datetime] NULL, [TotalCost] [decimal](18, 2) NOT NULL, CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_Orders_OrderNumber] ON [dbo].[Orders] ( [OrderNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO /****** Object: Table [dbo].[Details] Script Date: 06/01/2017 10:37:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Details]( [Id] [int] IDENTITY(1,1) NOT NULL, [Descript] [nvarchar](150) NULL, [OrderId] [int] NULL, [Cost] [decimal](18, 2) NOT NULL, CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Trigger [Details_Modify] Script Date: 06/01/2017 10:37:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[Details_Modify] ON [dbo].[Details] AFTER INSERT,UPDATE AS BEGIN UPDATE Orders SET TotalCost = s.Total FROM ( SELECT i.OrderId OId, SUM(d.Cost) Total FROM Details d JOIN inserted i ON d.OrderId=i.OrderId GROUP BY i.OrderId ) s WHERE Id=s.OId END GO /****** Object: Trigger [Details_Delete] Script Date: 06/01/2017 10:37:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[Details_Delete] ON [dbo].[Details] AFTER DELETE AS BEGIN UPDATE Orders SET TotalCost = s.Total FROM ( SELECT i.OrderId OId, SUM(d.Cost) Total FROM Details d JOIN deleted i ON d.OrderId=i.OrderId GROUP BY i.OrderId ) s WHERE Id=s.OId END GO /****** Object: Default [DF_Details_Cost] Script Date: 06/01/2017 10:37:43 ******/ ALTER TABLE [dbo].[Details] ADD CONSTRAINT [DF_Details_Cost] DEFAULT ((0)) FOR [Cost] GO /****** Object: Default [DF_Orders_TotalCost] Script Date: 06/01/2017 10:37:43 ******/ ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_TotalCost] DEFAULT ((0)) FOR [TotalCost] GO /****** Object: ForeignKey [FK_Details_Orders] Script Date: 06/01/2017 10:37:43 ******/ ALTER TABLE [dbo].[Details] WITH CHECK ADD CONSTRAINT [FK_Details_Orders] FOREIGN KEY([OrderId]) REFERENCES [dbo].[Orders] ([Id]) GO ALTER TABLE [dbo].[Details] CHECK CONSTRAINT [FK_Details_Orders] GO 

For experiments we create the console application. We connect to it nuget-package Shed.DbSync .

The structure of the database in the form of XML is obtained as follows:

 class Program { private const string OrigConnString = "data source=.;initial catalog=FiocoKb;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"; static void Main(string[] args) { //  XML    var db = new Shed.DbSync.DataBase(OrigConnString); var xml = db.GetXml(); File.WriteAllText("DbStructure.xml", xml); } } 

After launching the program in the DbStructure.xml file, we see the following:

 <?xml version="1.0"?> <DataBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Version>0</Version> <Tables> <Table Name="Orders" ObjectId="2137058649" ParentObjectId="0"> <Columns> <Column Name="Id"> <ColumnId>1</ColumnId> <Type>int</Type> <MaxLength>4</MaxLength> <IsNullable>false</IsNullable> <IsIdentity>true</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="OrderNumber"> <ColumnId>2</ColumnId> <Type>nvarchar</Type> <MaxLength>100</MaxLength> <IsNullable>true</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="OrderTime"> <ColumnId>3</ColumnId> <Type>datetime</Type> <MaxLength>8</MaxLength> <IsNullable>true</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="TotalCost"> <ColumnId>4</ColumnId> <Type>decimal</Type> <MaxLength>9</MaxLength> <IsNullable>false</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> </Columns> <Indexes> <Index Name="PK_Orders"> <IndexId>1</IndexId> <Type>CLUSTERED</Type> <IsUnique>true</IsUnique> <IsPrimaryKey>true</IsPrimaryKey> <IsUniqueConstraint>false</IsUniqueConstraint> <Columns> <IndexColumn> <TableColumnId>1</TableColumnId> <KeyOrdinal>1</KeyOrdinal> <IsDescendingKey>false</IsDescendingKey> </IndexColumn> </Columns> </Index> <Index Name="IX_Orders_OrderNumber"> <IndexId>2</IndexId> <Type>NONCLUSTERED</Type> <IsUnique>false</IsUnique> <IsPrimaryKey>false</IsPrimaryKey> <IsUniqueConstraint>false</IsUniqueConstraint> <Columns> <IndexColumn> <TableColumnId>2</TableColumnId> <KeyOrdinal>1</KeyOrdinal> <IsDescendingKey>false</IsDescendingKey> </IndexColumn> </Columns> </Index> </Indexes> <PrimaryKey Name="PK_Orders" ObjectId="5575058" ParentObjectId="2137058649"> <UniqueIndexId>1</UniqueIndexId> </PrimaryKey> <ForeignKeys /> <Defaults> <Default Name="DF_Orders_TotalCost" ObjectId="69575286" ParentObjectId="2137058649"> <ParentColumnId>4</ParentColumnId> <Definition>((0))</Definition> </Default> </Defaults> </Table> <Table Name="Details" ObjectId="85575343" ParentObjectId="0"> <Columns> <Column Name="Id"> <ColumnId>1</ColumnId> <Type>int</Type> <MaxLength>4</MaxLength> <IsNullable>false</IsNullable> <IsIdentity>true</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="Descript"> <ColumnId>2</ColumnId> <Type>nvarchar</Type> <MaxLength>300</MaxLength> <IsNullable>true</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="OrderId"> <ColumnId>3</ColumnId> <Type>int</Type> <MaxLength>4</MaxLength> <IsNullable>true</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="Cost"> <ColumnId>4</ColumnId> <Type>decimal</Type> <MaxLength>9</MaxLength> <IsNullable>false</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> </Columns> <Indexes> <Index Name="PK_Details"> <IndexId>1</IndexId> <Type>CLUSTERED</Type> <IsUnique>true</IsUnique> <IsPrimaryKey>true</IsPrimaryKey> <IsUniqueConstraint>false</IsUniqueConstraint> <Columns> <IndexColumn> <TableColumnId>1</TableColumnId> <KeyOrdinal>1</KeyOrdinal> <IsDescendingKey>false</IsDescendingKey> </IndexColumn> </Columns> </Index> </Indexes> <PrimaryKey Name="PK_Details" ObjectId="117575457" ParentObjectId="85575343"> <UniqueIndexId>1</UniqueIndexId> </PrimaryKey> <ForeignKeys> <ForeignKey Name="FK_Details_Orders" ObjectId="149575571" ParentObjectId="85575343"> <ReferenceTableId>2137058649</ReferenceTableId> <References> <Reference> <ColumnId>1</ColumnId> <ParentColumnId>3</ParentColumnId> <ReferenceColumnId>1</ReferenceColumnId> </Reference> </References> <DeleteAction>NO_ACTION</DeleteAction> <UpdateAction>NO_ACTION</UpdateAction> </ForeignKey> </ForeignKeys> <Defaults> <Default Name="DF_Details_Cost" ObjectId="101575400" ParentObjectId="85575343"> <ParentColumnId>4</ParentColumnId> <Definition>((0))</Definition> </Default> </Defaults> </Table> </Tables> <Views /> <ProgrammedObjects> <ProgObject Name="Details_Modify" ObjectId="165575628" ParentObjectId="0"> <Definition>CREATE TRIGGER [dbo].[Details_Modify] ON dbo.Details AFTER INSERT,UPDATE AS BEGIN UPDATE Orders SET TotalCost = s.Total FROM ( SELECT i.OrderId OId, SUM(d.Cost) Total FROM Details d JOIN inserted i ON d.OrderId=i.OrderId GROUP BY i.OrderId ) s WHERE Id=s.OId END</Definition> <Type>SQL_TRIGGER</Type> </ProgObject> <ProgObject Name="Details_Delete" ObjectId="181575685" ParentObjectId="0"> <Definition>CREATE TRIGGER [dbo].[Details_Delete] ON dbo.Details AFTER DELETE AS BEGIN UPDATE Orders SET TotalCost = s.Total FROM ( SELECT i.OrderId OId, SUM(d.Cost) Total FROM Details d JOIN deleted i ON d.OrderId=i.OrderId GROUP BY i.OrderId ) s WHERE Id=s.OId END</Definition> <Type>SQL_TRIGGER</Type> </ProgObject> </ProgrammedObjects> </DataBase> 

Deploy / update the database structure using the resulting XML.

Now learn how to use the resulting XML. Create another empty DbSyncSampleCopy database, add the following to the code of our console program:

  class Program { private const string OrigConnString = "data source=.;initial catalog=DbSyncSample;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"; private const string TargetConnString = "data source=.;initial catalog=DbSyncSampleCopy;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"; static void Main(string[] args) { //  XML     var dborig = new Shed.DbSync.DataBase(OrigConnString); var xml = dborig.GetXml(); File.WriteAllText("DbStructure.xml", xml); //       ,  // Shed.DbSync.DataBase.ClearDb(TargetConnString); //     var dbcopy = Shed.DbSync.DataBase.CreateFromXml(xml); dbcopy.UpdateDb(TargetConnString); //       : // dborig.UpdateDb(TargetConnString); // dbcopy         XML } } 

After starting the program, you can make sure that the structure of the tables, identical to the reference database, appeared in DbSyncSampleCopy. Experiments with changing the reference structure and updating the target leave the reader.

In testing scenarios, you may need to create a test database every time from scratch. In this case it will be useful to use the function Shed.DbSync.DataBase.ClearDb (string connString)

Automatic tracking of the structure of the database.

Tracking the structure will be issued as a separate function, which should be called at the start / restart of the application or in another place at the request of the developer.

  static void SyncDb() { //      Shed.DbSync.DataBase.Syncronize(OrigConnString, @"Struct\DbStructure.xml", //     @"Struct\Logs", //      @"Struct\update_script.sql" // (.)      //     ,  //    ); } 

Tracking is performed using the Version (tag) parameter in XML. The scenario of using the procedure is as follows:

1. Assign DB version. In Microsoft SqlServer Management Studio on the required database node, right-click Properties.
2. Next, Extended Properties and in the property table add the Version property with the value 1. With each subsequent modification of the structure, this property should be increased by 1.
3. When launching an application, if there is no XML file or its version is smaller than that of a database, it is created.
4. If the version of the XML file is larger than that of the database, a script to update the database is generated and executed.
5. If errors occur during the execution of the script, all changes are rolled back.
6. The synchronization results are written to a log file created in the folder specified by the logDitPath parameter.
7. If the SqlScriptPath parameter is specified, a file is created with the script in Section 4.

Experiments leave the readers. I wish you success!