#WCOM SqlBulkSync WCOM SQL Bulk Sync is a lightweight, performant non-intrusive SQL Server data sync tool. It doesn’t use any triggers or events, but instead uses the change tracking features available from SQL Server 2008 and up. The tool was developed primary for syncing on premise SQL server data to Azure in an efficient way, where only the changes are transferred. But it will also work just fine between non cloud instances.
##Table of contents
- Sample sync
SqlBulkSync.exe PROCESS|CREATETEMPLATE [SyncJobFilePath]
SqlBulkSync.exe PROCESS OrderTablesToAzure.hron
process scope is the one that does the data synchronization.
The sync job file contains source & target database connection and which tables to sync.
The tool currently has some opinions:
- It assumes table schema and naming are the same at source and target
- It assumes that change tracking is enabled
- And that a
syncschema is present at the target(it's used for the sync tables as i.e. Azure cant BulkImport into temp tables)
Other than that it will dynamically investigate schema, create sync tables and only sync data from non-calculated columns. ###Example create template
SqlBulkSync.exe CREATETEMPLATE OrderTablesToAzure.hron
The "createtemplate" scope is used for creating an base job file to use as an template for sync process.
##HRON The serialization format used for sync jobs and state is HRON, you can read more about it here. ###Sample job sync file
=SourceDbConnection Data Source=sourceserver;Initial Catalog=sourcedatabase;User ID=userid;Password=password =TargetDbConnection Data Source=targetserver;Initial Catalog=targetdatabase;User ID=userid;Password=password =Tables dbo.Table1 =Tables dbo.Table2 =BatchSize 1000
The job sync file is the definition of what and where from/to sync.
SourceDbConnectionis the data connection string to the instance/database where the source data relies
TargetDbConnectionis the data connection string to the instance/database where the date should be synced to
Tablesone to many tables to sync
BatchSizebatch size of bulk copy to sync table
###Sample table sync state file
=CurrentVersion 256 =MinValidVersion 1 =QueriedDateTime 2014-11-13 11:35:18
These files are created automatically in a SyncState folder in the same location as the program. One file per table is created, after every successful sync it's updated with the synced version. If file doesn't exist a full sync will occur.
##Sample sync A sample database for testing is avalilable in the sample-sync folder in the repositiory.
###CreateSourceDb.sql The CreateSourceDb.sql SQL script will create a sample source database, with schema, data and change tracking enabled.
###CreateTargetDb.sql The CreateTargetDb.sql SQL script will create an sample target database with schema but no data.
The CompanySync.hron job sync file is set to sync the sample Company table from
localhost default instance using integrated security.
###RandomUpdate.sql The RandomUpdate.sql SQL script just updates 5 random rows in the table to test the sync.