Synchronizing database structure between applications

Anyone who has ever developed applications that use a database has probably faced the problem of updating the database structure when the application is deployed and updated.

The most common approach is to create a set of SQL scripts to modify the database structure from version to version. Of course, there are paid tools, but they do not always solve the problem of full automation of the update.

The migration technology, first introduced in Hibernate ORM and implemented in Linq, is very good and convenient, but it implies a “code first” strategy for developing a database structure, which is very laborious for existing projects, and the use of triggers, stored procedures, and functions in a database makes the transition to the “code first” strategy almost impossible.

This article suggests an alternative approach to solving this problem – storing a reference database structure in an XML file and automatically generating a SQL script based on the comparison of the reference and the existing structure. So, let’s begin…

Generating XML file with database structure

We will use the DbSyncSample database. The script for creating the database is shown below.

Create a console application and link the Shed.DbSync nuget-package to it.

The XML database structure is as follows:

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

Deployment/update of database structure using XML

Create another empty DbSyncSampleCopy database, add the following code to the console program code:

After running the program, you can verify that the DbSyncSampleCopy now has a table structure identical to the reference database. Feel free to experiment with changing the reference structure and updating the target one.

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

Automatic database structure tracking

The structure tracking is made a separate function, which should be called at the start/restart of the application, or in another place at the request of a developer.

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

  1. Assign a version to a database. In Microsoft SQL Server Management Studio, right-click the node of the required database and select Properties.

  2. Next, click Extended Properties and add the Version property with value 1 to the property table. With each subsequent modification of the structure, this property should be incremented by 1.

  3. When you start the application, the file will be created, if there is no XML file or its version is smaller than that of the database.

  4. If the version of the XML file is bigger 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 the log file created in the folder specified by the logDitPath parameter.

  7. If the SqlScriptPath parameter is specified, a file with the script from item 4 is created.

John Shkurko

John graduated from the National Aerospace University and has degrees in "Aircraft construction" and "Robotic systems and complexes". John started his career at the National Aerospace University. At present, he is the director of an IT company.
John Shkurko

Latest posts by John Shkurko (see all)