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.
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.
- Start ApexSQL Data Diff
- For the Source, select Database as the type, your data center as the server, and your central database as the database
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
Click Compare button
All tables in these databases will be compared and shown in the Main grid
The records inserted into the Visits table in the hospital are shown in the Missing tab
Check the tables you want to synchronize
Click Synchronize on the Home tab
- Click Next in the Synchronization direction step of the Synchronization wizard
In the Output options step of the Synchronization wizard, select Synchronize a database as an output action
- Click Next
Check out the summary of actions that will be performed and any warnings shown
- Click Synchronize button on the Home tab:
Click OK in the notification dialog
When the destination database synchronization is complete, the following message will be shown
- 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:
Set the database on the mobile device to be the Source and Central database to be the Destination
- Click Compare
Select the table into which the records have been added during the day. The records are shown in the Missing tab:
- Click Synchronize button in the menu
- Go through the Synchronization wizard and add the missing records to the Central database
- 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:
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
- Start SQL Server Management Studio
In the Object Explorer expand the SQL Server Agent, right-click on Jobs and select New job
Specify the job name
- On the Steps tab, click New to add a new step to the job
- Specify an ”Operating system (CmdExec)” type for it, add a step name, click Open
Select the saved batch file D:\Test\MorningSync.bat
- Click OK button
Open the Schedules tab and specify when the database synchronization job should occur
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