Synchronize SQL Server databases in different remote sources

The scenarios

The applications used by travelling sales representatives, or other field workers – delivery drivers, visiting nurses, etc. are designed to collect data from remote locations and then send it to a data center. Also, the data from the data center, occasionally need to be sent back to these remote locations, to keep them up to date.

For example, whenever nurses pay a visit to a patient, they enter the information about the visit into the database on mobile devices. At the end of the day, all these entries created during the day are sent to the central database in a hospital. After that, the nurses can synchronize mobile devices with the database in the hospital data center, so they get the new information about their patients, and also, the information about any new visits they need to make the next day.

In the scenario such as this, there’s constantly a need to synchronize the information from a mobile device to a central database.

The solutions

1. Use SQL Server replication. It synchronizes databases by copying and distributing the records from one of the databases to another when these databases are in different locations.

There are some limitations when SQL Server replication cannot be used:

  • Only Standard or Enterprise editions of SQL Server can act as a Publisher, so if you’re using free SQL Express, you would have to upgrade
  • A configuration with a SQL 2005 Publisher and SQL 2008 Subscriber is not supported for web replication
  • A Distributor version must be greater than or equal to the Publisher version
  • A Subscriber version in transactional replication must be within two versions of the Publisher version, e.g. a SQL Server 2000 Publisher cannot have SQL Server 2012 Subscribers
  • A Subscriber version in merge replication must be less than or equal to the Publisher version, e.g. SQL Server 2012 Subscribers cannot have a SQL Server 2008 Publisher

2. Use Sync Framework, an API that enables you to build applications and synchronize between databases. It provides a lot of flexibility, but also requires development, which increases the cost and timeframe of implementation.

3. Use ApexSQL Data Diff, a SQL Server data comparison and synchronization tool which detects data differences and resolves them without errors. It can compare and synchronize live databases and native or natively compressed database backups and generate comprehensive reports on the detected differences, create a synchronization file and execute it.

How to do it

Imagine you have the Central database in the hospital data center, and the Visits database on a nurse’s mobile device. The hospital adds the records for the visits into the Visits table, and the nurses synchronize their mobile devices every morning to get the latest records, and find out what their route for the day is. The records inserted by the nurses during the day, are stored in the VisitReports table.

  1. Start ApexSQL Data Diff
  2. For the Source, select Database as the type, your data center as the server, and your central database as the database
  3. For the Destination, select Database as the type, a remote device as the server (nurse’s mobile device), and finally the database that will be synchronized

    Datasources type and details selection in ApexSQL Data Diff

  4. Click Compare button

    All tables in these databases will be compared and shown in the Main grid

    Main grid after SQL Server database comparison

    The records inserted into the Visits table in the hospital are shown in the Missing tab

  5. Check the tables you want to synchronize

    Checking the tables for database synchronization

  6. Click Synchronize on the Home tab

    Synchronize button in ApexSQL Data Diff

  7. Click Next in the Synchronization direction step of the Synchronization wizard
  8. In the Output options step of the Synchronization wizard, select Synchronize a database as an output action

    Choosing output action in Synchronization wizard

  9. Click Next
  10. Check out the summary of actions that will be performed and any warnings shown

    Summary of the actions and warnings preview

  11. Click Synchronize button on the Home tab:
  12. Click OK in the notification dialog

    SQL Server database change notification dialogue

    When the destination database synchronization is complete, the following message will be shown

    Script execution results message

  13. Click Save button on the Home tab of the project, to save the synchronization settings into a project and perform synchronization quickly and easily next time

If in the evening, you need to synchronize the records that the nurses added during the day to their databases, to the records in the central database, the steps are similar:

  1. Set the database on the mobile device to be the Source and Central database to be the Destination

    Selecting the SQL Server database Source and Destination

  2. Click Compare
  3. Select the table into which the records have been added during the day. The records are shown in the Missing tab:

    Comparison and synchronization of the Source and Destination databases

  4. Click Synchronize button in the menu
  5. Go through the Synchronization wizard and add the missing records to the Central database
  6. Click Save on the Home tab of the project, to save the synchronization settings into a project and perform synchronization quickly and easily next time

Now that you have projects saved, you can automate the synchronization processes:

    1. Save the following command as the E:\Test\Morning.bat batch file that will execute the created Morning_Central_Nurse.axdd project:

      "C:\Program files 
      (x86)\ApexSQL\ApexSQLDataDiff2014\ApexSQLDataDiff.com" /
      pf:D: \Test\Morning_Central_Nurse.axdd /
      of:D: \Test\MorningSync.sql / sync / bu:D: \Test / v / f
      

      It will create MorningSync.sql in the D:\Test folder to add the data from the central database into in the nurse’s database, create a full backup of the nurse’s database in the D:\Test folder, and insert the missing records into the nurse’s database

    2. Start SQL Server Management Studio
    3. In the Object Explorer expand the SQL Server Agent, right-click on Jobs and select New job

    4. Specify the job name

    5. On the Steps tab, click New to add a new step to the job
    6. Specify an ”Operating system (CmdExec)” type for it, add a step name, click Open
    7. Select the saved batch file D:\Test\MorningSync.bat

      SQL Server job steps

    8. Click OK button
    9. Open the Schedules tab and specify when the database synchronization job should occur

      Scheduling a SQL Server job

Repeat steps 3 to 9 to schedule a SQL Server job for the evening synchronization.

Comparing and synchronizing SQL Server databases on remote locations with a SQL Server database in the datacenter using ApexSQL Data Diff, can be done regardless of the versions of SQL Servers used on the remote and central locations, with no programming knowledge and under your complete control, down to the row level

April 19, 2013