Синхронизация данных таблиц двух баз данных SQL Server

Параметры переноса предопределенных данных таблиц приложения во-время обновления структуры БД к новой версии приложения в SQL Server

Перенести данные

Эта функция в первую очередь предназначена для приведения в соответствие предопределенных данных таблиц, таких как каталоги, словари, данные приложения, функциональные схемы приложения, параметры и т.д. Она позволяет синхронизировать данные таблицы с помощью оператора MERGE или операторов DELETE, UPDATE и INSERT для оптимизированных в памяти таблиц, не поддерживающих MERGE.

Есть два способа получить исходные данные для синхронизации. Кроме того, их можно экспортировать в файл вместе со структурой шаблонной базы данных.

1. Исходные данные могут быть загружены из шаблонной базы данных или файла и перенесены в скрипт в виде инструкций вставки для наполнения временных исходных таблиц.

2. Если шаблонная база данных доступна из целевой, исходные данные также можно адресовать непосредственно из скрипта с помощью запросов выборки, без создания временных таблиц. Для этого используйте опцию "Расположение шаблонной БД".

Исходные данные экспортируются в файл структуры базы данных или файл пакета в бинарном формате. Размер хранения типов данных подобен размерам в SQL Server. Сам бинарный файл заархивирован.

Когда общий размер исходных данных довольно велик или они могут содержать большие значения BLOB-типов, - это может чрезмерно увеличить размер экспортируемого файла или размер инструкций вставки в скрипте. В таком случае следует обеспечить прямой доступ к шаблонной базе данных и работать только так.

Синхронизация данных выполняется в конце, после синхронизации структуры, но перед финальным скриптом.

Для перечисления синхронизируемых таблиц и описания правил по ним используется XML-файл определенного формата. Для каждой таблицы можно ограничить диапазон данных, поля синхронизации, задать дополнительные условия и т.д. Чтобы создать шаблона такого файла, - просто сохраните его из-под Диалога как новый и отредактируйте вручную, следуя инструкциям в комментариях.

Расположение шаблонной БД

Этот параметр декларирует расположение шаблонной базы данных и определяет способ получения исходных данных из неё. Если шаблонная база данных представлена файлом, то несмотря на этот параметр, исходные данные всегда будут передаваться через скрипт.

Удаленный сервер или Файл - Переносить данные через скрипт

Шаблонная база данных находится на удаленном (другом) сервере или представлена файлом структуры базы данных/пакета AutosyncDB.

Тот же экземпляр - Выбирать данные напрямую

Шаблонная база данных находится в том же экземпляре SQL Server, что и целевая.

Связанный сервер на себя:

Имя связанного сервера на себя самого. Оно используется для доступа к данным оптимизированных в памяти таблиц через OPENQUERY, когда обе базы данных находятся в одном экземпляре. Если значение опции будет затребовано, но не было указано, приложение остановит задание.

Пример привязки сервера на себя:

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';

Связанный сервер - Выбирать данные напрямую

Шаблонная база данных находится на удаленном (другом) сервере, который связан с сервером целевой базы данных.

Связанный сервер:

Имя связанного сервера шаблонной базы данных на сервере целевой базы данных.



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;