Synchronize the data of two SQL Server databases

Settings for synchronizing predefined application data of database tables while updating the database schema to a new application version

Carry over data

This feature is primarily designed to align predefined data of tables, such as catalogs, dictionaries, application data, application functional schemes, options, etc. It allows you to synchronize table data using a MERGE statement or DELETE, UPDATE, and INSERT statements for memory-optimized tables that do not support MERGE.

There are two ways to get sample data for merges. In addition, it can be exported to a file along with the structure of a source database.

1. The sample data can be loaded from the source database or file and carried to a script as insert statements to populate temporary sample tables.

2. If the source database is available from the target, the sample data can also be addressed directly from the script using select queries, but without creating temporary tables. To do this, use the "Source database location" option.

The sample data is exported to a database structure file or a package file in a binary format. The storage sizes of datatypes are similar to those in SQL Server. The binary itself is zipped.

When the total size of the sample data is quite large, or it may contain large BLOB values, these can excessively increase the size of the exported file and the size of the insert statements in the script. In this case, you should provide direct access to the source database and only work that way.

The data synchronization follows at the end, after the structure synchronization but before the final script.

An XML file of a certain format is used to list synchronized tables and describe rules for them. For each table, you can limit the data range, synchronization fields, set additional conditions, etc. To create a template for such a file, just save it from the Dialog as a new one and edit it manually, following the instructions in the comments.

Source database location

This option declares the source database location and sets the way to get sample data from it. Despite this option when the source database is a file, the sample data will always be carried over a script.

Remote server or File - Carry data over script

The source database located on a remote (different) server or is an AutosyncDB database structure/package file.

Same instance - Select data directly

The source database located in the same instance of SQL Server as the target.

Linked server to self:

A linked server name of the same instance to itself. It is used to reach data of memory-optimized tables with OPENQUERY when both databases are located in the same instance. If an option value is requested but not specified, the application will stop a task.

Link server to self example:

DECLARE @servername nvarchar(128);

SET @servername = CAST(SERVERPROPERTY('SERVERNAME') AS nvarchar(128));

EXEC master.dbo.sp_addlinkedserver @server = N'SELF', @srvproduct = N'', @provider = N'SQLNCLI', @datasrc = @servername;

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @servername, @locallogin = NULL, @useself = N'True';

--EXEC master.dbo.sp_dropserver @server = N'SELF';

Linked Server - Select data directly

The source database located on a remote (different) server that is linked to the target database's server.

Linked source server:

A linked server name of the source database’s server on the target database's server.



Carry-over data description file

An XML file that lists tables and describes the rules for how to take data from them and merge that data on the target side.

Format
<?xml version="1.0" encoding="utf-8"?>
<root>
	<merge
		schema=""
		name=""
		iioff="0"
		tgoff="0"
		fkoff="0"
		cols=""
		top="-1"
		scond=""
		sorderby=""
		mcond=""
		ins="1"
		icols=""
		icond=""
		upd="0"
		ucols=""
		ucond=""
		del="0"
		dcond=""
		optsize="1"
		before=""
		after=""
		batchrows="-1"
		batchbytes="-1"
		printstats="1"
	/>
	<merge ... />
</root>

or

<?xml version="1.0" encoding="utf-8"?>
<root>
	<merge>
		<schema></schema>
		<name></name>
		<iioff>0</iioff>
		<tgoff>0</tgoff>
		<fkoff>0</fkoff>
		<cols></cols>
		<top>-1</top>
		<scond></scond>
		<sorderby></sorderby>
		<mcond></mcond>
		<ins>1</ins>
		<icols></icols>
		<icond></icond>
		<upd>0</upd>
		<ucols></ucols>
		<ucond></ucond>
		<del>0</del>
		<dcond></dcond>
		<optsize>1</optsize>
		<before></before>
		<after></after>
		<batchrows>-1</batchrows>
		<batchbytes>-1</batchbytes>
		<printstats>1</printstats>
	<merge/>
	<merge ... />
</root>

or a combination of them.

EXAMPLE

Insert rows not matched by the target for table dbo.TicketStates, make the data the same for table SalesDept.PropertyCategories

<?xml version="1.0" encoding="utf-8"?>
<root>
	<merge schema="dbo" name="TicketStates">
	<merge>
		 <schema>SalesDept</schema>
		 <name>PropertyCategories</name>
		 <upd>1</upd>
		 <del>1</del>
	<merge/>
</root>
Parameters
<root>

A root tag. According to the XML standard, only one root tag is allowed.

<merge>

A merge description tag for a specific table. Only one merge tag is allowed per the table.

schema=""

A table schema.

String.

Required.

name=""

A table name.

String.

Required.

iioff="0"

IDENTITY_INSERT OFF. Do not set IDENTITY_INSERT ON for the target table and do not export the identity column values from the source. It also answers the question can or cannot the identity column be used for matching.

Boolean. Allowed values: "0" - export identity values from the source table, "1" - the source and target tables have their own identity values.

Optional. If the table has an identity column the default is "0" (export the identity, SET IDENTITY_INSERT ON) otherwise "1".

tgoff="0"

Triggers Off. Whether or not to disable table triggers (including CLR triggers if any) before the merge.

Boolean. Allowed values: "1" - disable, "0" - do not process.

Optional. The default is false (do not disable).

fkoff="0"

Foreign Keys Off. Whether or not to disable foreign key constraints on the table before the merge.

Boolean. Allowed values: "1" - disable, "0" - do not process.

Optional. The default is false (do not disable).

cols=""

Columns. A subset of columns to be carried over. If this option is not specified, all table columns will be exported except computed columns, a row version column, a column set for all sparsed columns, and an identity column if the "iioff" option is true.

String. Allowed values: quoted or unquoted column names separated by commas or an empty string.

Optional. The default is an empty string (all columns).

top="-1"

Top. How many rows export from the table. This value is substituted into the select query (SELECT TOP N ...). Be careful using "top" when deletion is allowed. Should be used in conjunction with the "sorderby" option, but not required.

Int64. Allowed values: Greater than zero - use the TOP keyword, less than zero or zero - do not use.

Optional. The default is -1 (do not use).

scond=""

Select Condition. A condition clause for selecting data from the source table (SELECT ... WHERE scond). Be careful using "scond" when deletion is allowed. Consider setting the exact deletion condition in this case (but with "t." alias).

String. Allowed values: SELECT-WHERE clause for the table or an empty string.

Optional. The default is an empty string (all rows).

sorderby=""

Select Order By. An ORDER BY condition clause for selecting data from the source table (SELECT ... ORDER BY sorderby). The ordering of the rows makes the data script more readable, comparable, faster, and easier to understand. By default, the application orders rows by key table columns, if any.

String. Allowed values: SELECT-ORDER-BY condition for the table, an empty string, or two hyphens to do not use it.

Optional. The default is an empty string (order by key columns).

mcond=""

Match Condition. A MERGE-ON condition clause for matching rows (MERGE ... AS t USING ... AS s ON mcond). If "mcond" is not specified, the application looks for key columns among the carrying over ones to build the match condition. If they do not contain valid key columns for the table after synchronization, the entire subset of the carrying over columns will be included in the match condition. Try avoiding this case, especially if there are a lot of columns. According to SQL Server documentation, this may even return unexpected and incorrect results.

String. Allowed values: MERGE-ON condition for the table or an empty string. Use "t" and "s" table aliases to identify the target and source columns.

Optional. The default is an empty string (build from key columns).

ins="1"

Insert. Build the insert statement.

Boolean. Allowed values: "1" - allow insertion, "0" - disallow insertion.

Optional. The default is "1" (insert).

icols=""

Insert Columns. A subset of the columns in the insert statement.

String. Allowed values: quoted or unquoted column names separated by commas or an empty string.

Optional. The default is an empty string (all columns).

icond=""

Insert Condition. A condition clause for inserting rows (MERGE ... WHEN NOT MATCHED AND (icond) THEN ... ).

String. Allowed values: WHEN-NOT-MATCHED-AND condition for the table or an empty string. Use "s" table alias to identify the source columns.

Optional. The default is an empty string (insert all rows that not matched by target).

upd="0"

Update. Build the update statement.

Boolean. Allowed values: "1" - allow the update, "0" - disallow the update.

Optional. The default is "0" (do not update).

ucols=""

Update Columns. A subset of the columns in the update statement.

String. Allowed values: quoted or unquoted column names separated by commas or an empty string.

Optional. The default is an empty string (update all rows that matched by target).

ucond=""

Update Condition. A condition clause for updating rows (MERGE ... WHEN MATCHED AND (ucond) THEN ... ).

String. Allowed values: WHEN-MATCHED-AND condition for the table, an empty string, or two hyphens to brutal update without comparison of the updated columns. Use "t" and "s" table aliases to identify the target and source columns.

Optional. The default is an empty string (update all rows that matched by target).

del="0"

Delete. Build the delete statement.

Boolean. Allowed values: "1" - allow deletion, "0" - disallow deletion.

Optional. The default is "0" (do not delete).

dcond=""

Delete Condition. A condition clause for deleting rows (MERGE ... WHEN NOT MATCHED BY SOURCE AND (dcond) THEN DELETE).

String. Allowed values: WHEN-NOT-MATCHED-BY-SOURCE-AND condition for the table or an empty string. Use "t" table alias to identify the target columns.

Optional. The default is an empty string (delete all rows that do not matched by source).

before=""

Script Before the merge. A custom script before the merge statement in the same batch. Can be used for example to provide indexes on the temporary table or data check. The temporary table name is '#' plus the table name plus '##tmp'. Always check if the temporary table exists before adresses it like IF OBJECT_ID(N'tempdb..[#table_name##tmp]', 'U') IS NOT NULL

String.

Optional. The default is an empty string.

after=""

Script After the merge. A custom script after the merge statement in the same batch. Can be used for example for checking data in the target table.

String.

Optional. The default is an empty string.

optsize="1"

Optimize Size. Mainly defines the storage format of CLR data types. When size optimization is applied, CLR values will be stored as binary, otherwise as strings. The result script will be different too. Binary format requires less space and usually is more efficient for comparison, but less informative.

Boolean. Allowed values: "1" - optimize size, "0" - do not optimize.

Optional. The default is "1" (optimize, use binary form)

batchrows="-1"

Rows per Batch. A number of rows per insert statement while inserting into a temporary table. Increasing this value can reduce the time period for the temporary table to populate. However, SQL Server cannot parse well many rows in insert batches, especially if there are cast operators or expressions inside them.

Definitely, values greater than 100 shouldn't be used.

Int32. Allowed values: Greater than zero to apply, less than zero, or zero - to calc the value by application.

Optional. The default is -1 (calc by app).

batchbytes="-1"

Bytes per Batch. The number of bytes per insert statement when inserting into a temporary table. A new batch forces to start after exceeding this value. The number of rows may differ for different output encodings.

Int32. Allowed values: Greater than zero to apply, less than zero, or zero - to calc the value by application.

Optional. The default is -1 (calc by app).

printstats="1"

Print Statistics. Whether or not to print (output) the merge statistics (a number of inserts, updates, and deletes).

Boolean. Allowed values: "1" - calculate and print, "0" - do not process.

Optional. The default is "1" (print).

Do not forget to escape special XML characters in tag or attribute values, like
    <    &lt;
    >    &gt;
More examples
Table dbo.TicketStatus

It is a very simple table with a primary key. New values can only be added from time to time by the developer. Rows from it are never deleted.

CREATE TABLE dbo.TicketStatus
(
	TicketStatusId int NOT NULL PRIMARY KEY,
	Name nvarchar(60) NOT NULL
)

Allow the update and also specify ins="1" and del="0" for clarity. Specify printstats="0" just to reduce a merge statement in this example.

<merge schema="dbo" name="TicketStatus" ins="1" upd="1" del="0" printstats="0" />

The result will be like this

MERGE [dbo].[TicketStatus] AS t
USING [#TicketStatus##tmp] AS s
ON
	t.[TicketStatusId] = s.[TicketStatusId]
WHEN NOT MATCHED THEN
	INSERT ([TicketStatusId], [Name])
	VALUES (s.[TicketStatusId], s.[Name])
WHEN MATCHED AND
	t.[Name] != s.[Name] THEN
UPDATE SET
	t.[Name] = s.[Name];
Table Core.Pages

This table represents WEB pages as hierarchy tree. It uses in-built CLR data type 'hierarchyid', a foreign key constraint, and computed columns. The primary key is nonclustered, but the app will use it for matching, because the clustered key is a computed column. Also, the AFTER INSERT, UPDATE trigger is set on the table.

CREATE TABLE Core.Pages
(
	PageId hierarchyid NOT NULL PRIMARY KEY NONCLUSTERED,
	Link varchar(200) NOT NULL,
	H0 nvarchar(160) NOT NULL,
	ParentPageId AS PageId.GetAncestor(1) PERSISTED REFERENCES Core.Pages(PageId),
	Level AS PageId.GetLevel() PERSISTED CONSTRAINT Pages_Level UNIQUE CLUSTERED (Level, PageId)
)
CREATE TRIGGER Pages_TR ON Core.Pages AFTER INSERT, UPDATE
AS	...

Allow also the update and delete. Temporary disable all table triggers and foreign keys. Specify optsize="0" to show the hierarchyid values in the script as string literals. Specify printstats="0" just to reduce a merge statement in this example.

<merge schema="Core" name="Pages" tgoff="1" fkoff="1" ins="1" upd="1" del="1" optsize="0" printstats="0" />

The result will be like this

MERGE [Core].[Pages] AS t
USING [#Pages##tmp] AS s ON
		t.[PageId] = s.[PageId]
WHEN NOT MATCHED THEN
	INSERT ([PageId], [Link], [H0])
	VALUES (s.[PageId], s.[Link], s.[H0])
WHEN MATCHED AND
		(t.[Link] != s.[Link] OR
		t.[H0] != s.[H0]) THEN
	UPDATE SET
		t.[Link] = s.[Link],
		t.[H0] = s.[H0]
WHEN NOT MATCHED BY SOURCE THEN
	DELETE;
Table Core.AOSchemas

A table for storing schemas that describe application objects (definition, behavior, report templates, etc.). Most of the table data is defined by the developer, but users can also add their own custom object schemas. The source and target tables do not share the identity.

CREATE TABLE Core.AOSchemas
(
	AOSchemaId int NOT NULL IDENTITY PRIMARY KEY NONCLUSTERED,
	AOId int NOT NULL REFERENCES Core.AO(AOId),
	Name varchar(100) NOT NULL UNIQUE CLUSTERED,
	Dsc nvarchar(200) NULL,
	Comment nvarchar(200) NULL,
	IsCustom bit NOT NULL,
	DateIns datetime2(3) NOT NULL DEFAULT SYSDATETIME(),
	DateUpd datetime2(3) NULL,
	DateDel datetime2(3) NULL,
	Content varbinary(MAX)
)

Allow also the update and delete. Temporary disable all table triggers and foreign keys.
Specify printstats="0" just to reduce a merge statement in this example.
Specify iioff="1" to do not SET IDENTITY_INSERT ON.
Columns AOSchemaId, Comment, DateIns, DateUpd, DateDel have their own values in the source and target. Define the subset of carrying over columns to exclude them.
Custom object schemas from the source must not be transfered to the target. Define a select condition for this.
We also do not want to delete custom schemas in the target. Define a delete condition for this.

<merge
	schema="Core" name="AOSchemas" iioff="1" tgoff="1" fkoff="1" ins="1" upd="1" del="1" printstats="0"
	cols="AOId,Name,Dsc,IsCustom,Content"
	scond="IsCustom = 0"
	dcond="t.IsCustom = 0"
/>

The result will be like this

MERGE [Core].[AOSchemas] AS t
USING [#AOSchemas##tmp] AS s ON
		t.[Name] = s.[Name]
WHEN NOT MATCHED THEN
	INSERT ([AOId], [Name], [Dsc], [IsCustom], [Content])
	VALUES (s.[AOId], s.[Name], s.[Dsc], s.[IsCustom], s.[Content])
WHEN MATCHED AND
		(t.[IsCustom] != s.[IsCustom] OR
		t.[AOId] != s.[AOId] OR
		(t.[Dsc] IS NULL AND s.[Dsc] IS NOT NULL) OR (t.[Dsc] IS NOT NULL AND s.[Dsc] IS NULL) OR (t.[Dsc] IS NOT NULL AND s.[Dsc] IS NOT NULL AND t.[Dsc] != s.[Dsc]) OR
		(t.[Content] IS NULL AND s.[Content] IS NOT NULL) OR (t.[Content] IS NOT NULL AND s.[Content] IS NULL) OR (t.[Content] IS NOT NULL AND s.[Content] IS NOT NULL AND t.[Content] != s.[Content])) THEN
	UPDATE SET
		t.[IsCustom] = s.[IsCustom],
		t.[AOId] = s.[AOId],
		t.[Dsc] = s.[Dsc],
		t.[Content] = s.[Content]
WHEN NOT MATCHED BY SOURCE AND
		(t.IsCustom = 0) THEN
	DELETE;

* For making a brutal update without the 'not equal' checking just put two hyphens in 'ucond' or define your own condition.

Table dbo.product_cat

It is a simple dictionary that can be changed on both sides. Developer predefined rows have ID values less than 1000. Only this range must be synchronized.

CREATE TABLE dbo.product_cat
(
	product_cat_id int NOT NULL IDENTITY PRIMARY KEY,
	name nvarchar(100) NOT NULL
)
GO
/* for the target */
DBCC CHECKIDENT(N'[dbo].[product_cat]', RESEED, 1000) WITH NO_INFOMSGS;

Allow also the update and delete. Define conditions.
Specify iioff="0" and ins="1" for clarity.
Specify printstats="0" just to reduce a merge statement in this example.

<merge schema="dbo" name="product_cat" iioff="0" ins="1" upd="1" del="1" printstats="0"
		scond="product_cat_id < 1000"
		dcond="t.product_cat_id < 1000"
/>

The result will be like this

SET IDENTITY_INSERT [dbo].[product_cat] ON
GO

MERGE [dbo].[product_cat] AS t
USING [#product_cat##tmp] AS s ON
		t.[product_cat_id] = s.[product_cat_id]
WHEN NOT MATCHED THEN
	INSERT ([product_cat_id], [name])
	VALUES (s.[product_cat_id], s.[name])
WHEN MATCHED AND
		(t.[name] != s.[name]) THEN
	UPDATE SET
		t.[name] = s.[name]
WHEN NOT MATCHED BY SOURCE AND
		(t.product_cat_id < 1000) THEN
	DELETE;