How to: Define a Logical Record Relationship Between Merge Table Articles (Replication Transact-SQL Programming)
업데이트: 2005년 12월 5일
Merge replication allows you to define a relationship between related rows in different tables. This means that rows can be processed as a transactional unit during synchronization. A logical record can be defined between two articles whether or not they have a join filter relationship. For more information, see 논리적 레코드를 사용하여 관련된 행의 변경 내용을 그룹화. You can programmatically specify logical record relationships between articles using replication stored procedures.
To define a logical record relationship without an associated join filter
If the publication contains any articles that are filtered, execute sp_helpmergepublication, and note the value of use_partition_groups in the result set.
If the value is 1, then precomputed partitions are already being used.
If the value is 0, then execute sp_changemergepublication at the Publisher on the publication database. Specify a value of use_partition_groups for @property and a value of true for @value.
[!참고] If the publication does not support precomputed partitions, then logical records cannot be used. For more information, see Requirements for Using Precomputed Partitions in the topic 미리 계산된 파티션으로 매개 변수가 있는 필터 성능 최적화.
If the value is NULL, then the Snapshot Agent needs to be run to generate the initial snapshot for the publication.
If the articles that will comprise the logical record do not exist, execute sp_addmergearticle at the Publisher on the publication database. Specify one of the following conflict detection and resolution options for the logical record:
- To detect and resolve conflicts that occur within related rows in the logic record, specify a value of true for @logical_record_level_conflict_detection and @logical_record_level_conflict_resolution.
- To use the standard row- or column-level conflict detection and resolution, specify a value of false for @logical_record_level_conflict_detection and @logical_record_level_conflict_resolution, which is the default.
Repeat step 2 for each article that will comprise the logical record. You must use the same conflict detection and resolution option for each article in the logical record. For more information, see 논리적 레코드에서 충돌 감지 및 해결.
At the publisher on the publication database, execute sp_addmergefilter. Specify @publication, the name of one article in the relationship for @article, the name of the second article for @join_articlename, a name for the relationship for @filtername, a clause that defines the relationship between the two articles for @join_filterclause, the type of join for @join_unique_key and one of the following values for @filter_type:
- 2 - Defines a logical relationship.
- 3 - Defines a logical relationship with a join filter.
[!참고] If a join filter is not used, the direction of the relationship between the two articles is not important.
Repeat step 2 for each remaining logical record relationship in the publication.
To change conflict detection and resolution for logical records
To detect and resolve conflicts that occur within related rows in the logical record:
- At the Publisher on the publication database, execute sp_changemergearticle. Specify a value of logical_record_level_conflict_detection for @property and a value of true for @value. Specify a value of 1 for @force_invalidate_snapshot and @force_reinit_subscription.
- At the Publisher on the publication database, execute sp_changemergearticle. Specify a value of logical_record_level_conflict_resolution for @property and a value of true for @value. Specify a value of 1 for @force_invalidate_snapshot and @force_reinit_subscription.
To use the standard row-level or column-level conflict detection and resolution:
- At the Publisher on the publication database, execute sp_changemergearticle. Specify a value of logical_record_level_conflict_detection for @property and a value of false for @value. Specify a value of 1 for @force_invalidate_snapshot and @force_reinit_subscription.
- At the Publisher on the publication database, execute sp_changemergearticle. Specify a value of logical_record_level_conflict_resolution for @property and a value of false for @value. Specify a value of 1 for @force_invalidate_snapshot and @force_reinit_subscription.
To remove a logical record relationship
At the Publisher on the publication database, execute the following query to return information about all logical record relationships defined for the specified publication:
SELECT f.* FROM sysmergesubsetfilters AS f INNER JOIN sysmergepublications AS p ON f.pubid = p.pubid WHERE p.[name] = @publication;Note the name of the logical record relationship being removed in the filtername column in the result set.
[!참고] This query returns the same information as sp_helpmergefilter; however, this system stored procedure only returns information about logical record relationships that are also join filters.
At the Publisher on the publication database, execute sp_dropmergefilter. Specify @publication, the name of one of the articles in the relationship for @article, and the name of the relationship from step 1 for @filtername.
예
This example enables precomputed partitions on an existing publication, and creates a logical record comprising the two new articles for the SalesOrderHeader and SalesOrderDetail tables.
-- Remove ON DELETE CASCADE from FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID;
-- logical records cannot be used with ON DELETE CASCADE.
IF EXISTS (SELECT * FROM sys.objects
WHERE name = 'FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID')
BEGIN
ALTER TABLE [Sales].[SalesOrderDetail]
DROP CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]
END
ALTER TABLE [Sales].[SalesOrderDetail]
WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]
FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
GO
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @table3 AS sysname;
DECLARE @salesschema AS sysname;
DECLARE @hrschema AS sysname;
DECLARE @filterclause AS nvarchar(1000);
DECLARE @partitionoption AS bit;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @table1 = N'SalesOrderDetail';
SET @table2 = N'SalesOrderHeader';
SET @salesschema = N'Sales';
SET @hrschema = N'HumanResources';
SET @filterclause = N'Employee.LoginID = HOST_NAME()';
-- Ensure that the publication uses precomputed partitions.
SET @partitionoption = (SELECT [use_partition_groups] FROM sysmergepublications
WHERE [name] = @publication);
IF @partitionoption <> 1
BEGIN
EXEC sp_changemergepublication
@publication = @publication,
@property = N'use_partition_groups',
@value = 'true',
@force_invalidate_snapshot = 1;
END
-- Add a filtered article for the Employee table.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table1,
@source_object = @table1,
@type = N'table',
@source_owner = @hrschema,
@schema_option = 0x0004CF1,
@description = N'article for the Employee table',
@subset_filterclause = @filterclause;
-- Add an article for the SalesOrderHeader table.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table2,
@source_object = @table2,
@type = N'table',
@source_owner = @salesschema,
@schema_option = 0x0034EF1,
@description = N'article for the SalesOrderHeader table';
-- Add an article for the SalesOrderDetail table.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table3,
@source_object = @table3,
@source_owner = @salesschema,
@description = 'article for the SalesOrderDetail table',
@identityrangemanagementoption = N'auto',
@pub_identity_range = 100000,
@identity_range = 100,
@threshold = 80;
-- Add a merge join filter between Employee and SalesOrderHeader.
EXEC sp_addmergefilter
@publication = @publication,
@article = @table2,
@filtername = N'SalesOrderHeader_Employee',
@join_articlename = @table1,
@join_filterclause = N'Employee.EmployeeID = SalesOrderHeader.SalesPersonID',
@join_unique_key = 1,
@filter_type = 1,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
-- Create a logical record relationship that is also a merge join
-- filter between SalesOrderHeader and SalesOrderDetail.
EXEC sp_addmergefilter
@publication = @publication,
@article = @table3,
@filtername = N'LogicalRecord_SalesOrderHeader_SalesOrderDetail',
@join_articlename = @table2,
@join_filterclause = N'[SalesOrderHeader].[SalesOrderID] = [SalesOrderDetail].[SalesOrderID]',
@join_unique_key = 1,
@filter_type = 3,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
GO
참고 항목
관련 자료
논리적 레코드를 사용하여 관련된 행의 변경 내용을 그룹화