Синхронізація даних таблиць двох баз даних 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;