Database synchronization in the Software Development Life Cycle

Since, the focus of this article is database synchronization, I'll skip the first three stages and go straight to the stages which deal with database synchronization, which are testing and maintenance.

In the context of testing, database synchronization is useful in preparing the testing environment. For example, usually when a web application or a new feature is completed it will first be published on a test domain which is made available only to some chosen individuals / groups. This test site will probably need its own database that will have to be created or updated every time a change is made. You could backup the development database and restore it in the test environment or generate the database's script and execute it on the test environment but this would require the database to be recreated every time which can be a very time consuming process for large databases. Also, this is not possible if the databases are in the Azure cloud environment. The best option here is to use a synchronization tool like xSQL Schema Compare or xSQL Data Compare. So, you could use xSQL Schema Compare to transfer any schema changes from the development database to the testing environment and xSQL Data Compare to transfer any changes in lookup data. As a side benefit, xSQL Schema Compare can also be used to take periodic snapshots of the database in order to keep track of the changes made to it and rollback any of them is necessary.

Since the synchronization process is fairly straightforward and more or less the same in most of the scenarios, I'll first explain the other scenario in which database synchronization is necessary and then demonstrate how it's done and how to automate it.

The other, very important stage where database synchronization might take place in, is maintenance. In this stage, this process is done in both directions. First of all, for every bug fix or new feature, schema changes and lookup data are transferred from the development environment to the testing and then the live one. This process is the same as the one in the testing stage. Secondly, to make sure that the software is working correctly, quality assurance is performed. Quality assurance can have its own environment or it can use the testing environment. One thing is for certain though, it needs to have data that is as close as possible to the live data. Obviously, no type of data satisfies this condition better than the live data itself. By using xSQL Data Compare, these live data can be transferred to the quality assurance environment where they can then be used as needed.

Without further ado, let's proceed to a demonstration of schema and data synchronization. I'll be using the AdventureWorks2012 database in this demo. I made a copy of this database (AdventureWorksCopy) which will be used as the testing environment's database. Suppose this database belongs to a company that has decided to extend its medical and dental benefits to the employee families. Obviously, they will want to have some data in the system for the employee's family members. To do this, a table called EmployeeDependant is added to the HumanResources schema in the development database which has records for the employee's families. Of course, before this feature can be published it needs to be tested, which means that the schema changes need to be transferred to AdventureWorksCopy. There are 4 very simple steps to this process.

1. Add the databases in xSQL Schema Compare.
2. Compare AdventureWorks2012 with AdventureWorksCopy. The comparison reveals that there is a table EmployeeDependant in AdventureWorks2012 that does not exist in AdventureWorksCopy (note the one-way arrow to the right).
   
3. Generate the synchronization script.
4. Execute the script. By opening the database in SSMS I can see that the EmployeeDependant table is added to the AdventureWorksCopy database.

The new EmployeeDependant table has a column named 'RelationshipWithEmployee'. Instead of it being a simple VARCHAR column it's better to have it as a foreign key to a 'Relationships' table. The records in this table will probably never change (there is a fixed number of relationship types in a family), so it can be used as an example of the lookup data that may need to be transferred from the development environment to the testing one. Doing this synchronization with xSQL Data Compare is, again, a very straightforward process. Before I show this process' steps, here is a screen shot of the data in the Relationships table that will be transferred to the AdventureWorksCopy database:

To add these data to the Relationships table in AdventureWorksCopy database do the following:

1. Add the databases to xSQL Data Compare.
2. Choose the objects that need to be compared. In this case, only the Relationships table will be synchronized, so only that table will be selected from the objects to be compared.
   
3. Compare the tables
4. Generate the synchronization script for the AdventureWorksCopy database, Here is the script that is generated:

   SET XACT_ABORT ON;
   SET ARITHABORT ON;
   SET NUMERIC_ROUNDABORT OFF;
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   BEGIN TRANSACTION;
   /*-- -Insert(s): [HumanResources].[Relationships] */SET IDENTITY_INSERT [HumanResources].[Relationships] ON;
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(1,'Spouse');
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(2,'Sibling');
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(3,'Parent');
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(4,'Child');
   SET IDENTITY_INSERT [HumanResources].[Relationships] OFF;
   COMMIT TRANSACTION;
   

5. Excecute the script. After execution, if a select query is run on the Relationships table AdventureWorksCopy database, it will display the following data: 

Using a similar process to the one in this demo you can copy the data in the live database in the Quality Assurance database and run any tests that you need.

In most cases, these tasks are repetitive. For example, an organization might have the practice of synchronizing the live data with quality assurance every 3 days. Instead of doing this manually every time, you can just generate a XML configuration file for xSQL Data Compare Command Line by clicking the button in the picture below and then, using Windows Scheduler, schedule the comparison to be run periodically with that XML file as an argument.