SQL Server Merge Replication حل تضادها و Custom Resolver

راهنمای جامع مدیریت و حل تضادها در SQL Server Merge Replication

SQL Server Merge Replication یکی از ابزارهای قدرتمند برای همگام‌سازی داده‌ها بین سرورهای مختلف است که انعطاف‌پذیری بالایی ارائه می‌دهد. اما مانند هر سیستم توزیع‌شده‌ای، مدیریت تضادها (Conflicts) یکی از چالش‌های اصلی آن محسوب می‌شود. در Merge Replication، تضاد زمانی رخ می‌دهد که یک ردیف داده مشخص در دو مکان مختلف (مثلاً ناشر و مشترک) به طور مستقل تغییر کند و Replication Engine نتواند به صورت خودکار تشخیص دهد که کدام تغییر معتبر است. درک صحیح و پیاده‌سازی مکانیزم‌های حل تضاد برای حفظ یکپارچگی داده‌ها و عملکرد بهینه سیستم Replication ضروری است.

انواع مختلفی از تضادها در Merge Replication وجود دارد، از جمله:

Update-Update Conflict: یک ردیف در ناشر و مشترک به طور مستقل به‌روزرسانی می‌شود.
Insert-Insert Conflict: یک ردیف با همان کلید اصلی در ناشر و مشترک به طور مستقل درج می‌شود (این حالت کمتر رایج است و معمولاً با محدودیت‌های کلید اصلی جلوگیری می‌شود).
Update-Delete Conflict: یک ردیف در یک طرف به‌روزرسانی و در طرف دیگر حذف می‌شود.
Delete-Update Conflict: یک ردیف در یک طرف حذف و در طرف دیگر به‌روزرسانی می‌شود.

برای حل این تضادها، SQL Server Merge Replication از مفهوم “Conflict Resolver” استفاده می‌کند. Resolverها منطقی هستند که تعیین می‌کنند کدام تغییر برنده است و کدام تغییر بازنده (یعنی نادیده گرفته می‌شود یا به عنوان خطای Replication ثبت می‌شود).

انواع Conflict Resolverها:

SQL Server چندین Resolver داخلی ارائه می‌دهد:

PublisherWinner: تغییرات ناشر همیشه برنده می‌شوند.
SubscriberWinner: تغییرات مشترک همیشه برنده می‌شوند.
PrioritizedPublisherWinner: تغییری که از ناشر با بالاترین اولویت می‌آید برنده می‌شود.
PrioritizedSubscriberWinner: تغییری که از مشترک با بالاترین اولویت می‌آید برنده می‌شود.
Max/Min Value: Resolver بر اساس حداکثر یا حداقل مقدار یک ستون مشخص تصمیم می‌گیرد.
Average Value: Resolver بر اساس میانگین مقادیر یک ستون مشخص تصمیم می‌گیرد.
Custom Resolver: امکان ایجاد Resolverهای سفارشی با استفاده از T-SQL، COM یا .NET را فراهم می‌کند. این روش انعطاف‌پذیری بالایی برای پیاده‌سازی منطق‌های پیچیده حل تضاد می‌دهد.

مدیریت و مشاهده تضادها:

وقتی یک تضاد رخ می‌دهد، بسته به نوع Resolver، یکی از تغییرات اعمال شده و تغییر دیگر نادیده گرفته می‌شود یا در جدول تضادها ثبت می‌شود. اطلاعات مربوط به تضادها در جداول سیستمی خاصی (مانند `MSmerge_conflicts_info` در پایگاه داده مشترک) ذخیره می‌شوند. همچنین می‌توانید از Replication Monitor برای مشاهده و مدیریت تضادها استفاده کنید.

پیاده‌سازی یک سناریوی حل تضاد (Custom Resolver):

برای درک بهتر، یک سناریوی عملی ایجاد می‌کنیم که در آن تضادها را با یک Custom Resolver مدیریت خواهیم کرد. فرض کنید جدولی برای مشتریان داریم و می‌خواهیم در صورت بروز تضاد، مقدار “CreditLimit” را به جای انتخاب یک تغییر به عنوان برنده، به میانگین مقادیر موجود در ناشر و مشترک تنظیم کنیم.

ابتدا، محیط Replication را تنظیم می‌کنیم.

USE master;
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MergeReplicationTest')
    DROP DATABASE [MergeReplicationTest];
GO

CREATE DATABASE [MergeReplicationTest];
GO

USE [MergeReplicationTest];
GO

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    CreditLimit DECIMAL(10, 2)
);
GO

INSERT INTO Customers (CustomerID, FirstName, LastName, CreditLimit)
VALUES (1, 'John', 'Doe', 1000.00);
GO

SELECT * FROM Customers;
GO

اکنون، Publication را برای جدول `Customers` ایجاد می‌کنیم.

USE [MergeReplicationTest];
GO

-- Add a merge publication
EXEC sp_addmergepublication
    @publication = N'MergePublication',
    @description = N'Merge publication for Customer table.',
    @retention = 14,
    @sync_mode = N'automatic';
GO

-- Add a snapshot publication (for initial sync)
EXEC sp_addpublication_snapshot
    @publication = N'MergePublication';
GO

-- Add the Customers table as an article
EXEC sp_addmergearticle
    @publication = N'MergePublication',
    @article = N'Customer_Article',
    @source_object = N'Customers',
    @publication_database = N'MergeReplicationTest';
GO

سپس، یک مشترک (Subscriber) ایجاد کرده و آن را به Publication اضافه می‌کنیم.

-- On the Subscriber server (or same server for testing)
USE [MergeReplicationTest];
GO

-- Add a merge subscription
EXEC sp_addmergesubscription
    @publication = N'MergePublication',
    @subscriber = N'SQLSERVERCENTRAL\SQL2012', -- Replace with your subscriber server name
    @subscriber_db = N'MergeReplicationTest',
    @subscription_type = N'pull',
    @sync_type = N'automatic';
GO

-- Add a pull merge subscription agent
EXEC sp_addmergepullsubscription_agent
    @publisher = N'SQLSERVERCENTRAL\SQL2012', -- Replace with your publisher server name
    @publisher_db = N'MergeReplicationTest',
    @publication = N'MergePublication',
    @subscriber = N'SQLSERVERCENTRAL\SQL2012', -- Replace with your subscriber server name
    @subscriber_db = N'MergeReplicationTest',
    @distributor = N'SQLSERVERCENTRAL\SQL2012', -- Replace with your distributor server name
    @distributor_security_mode = 1,
    @subscriber_security_mode = 1;
GO

ایجاد Custom Resolver:

حالا Custom Resolver خود را به صورت یک Stored Procedure ایجاد می‌کنیم. این Stored Procedure هنگام رخ دادن تضاد فراخوانی می‌شود و منطق حل تضاد را اعمال می‌کند. در این مثال، ما از Stored Procedure برای تعیین مقدار `CreditLimit` به عنوان میانگین مقادیر در ناشر و مشترک استفاده می‌کنیم.

USE [MergeReplicationTest];
GO

CREATE PROCEDURE ResolveCreditLimitConflict
    @owner_login_name sysname,
    @publisher_db sysname,
    @publication_name sysname,
    @article_name sysname,
    @column_name sysname,
    @rowguid UNIQUEIDENTIFIER,
    @originator sysname,
    @conflict_type INT,
    @old_value VARBINARY(MAX),
    @new_value VARBINARY(MAX),
    @pub_value VARBINARY(MAX),
    @sub_value VARBINARY(MAX),
    @out_value VARBINARY(MAX) OUTPUT
AS
BEGIN
    DECLARE @pub_credit_limit DECIMAL(10, 2);
    DECLARE @sub_credit_limit DECIMAL(10, 2);
    DECLARE @avg_credit_limit DECIMAL(10, 2);

    -- Convert VARBINARY values back to DECIMAL
    -- Assume the column is CreditLimit and its type is DECIMAL(10,2)
    SET @pub_credit_limit = CAST(@pub_value AS DECIMAL(10, 2));
    SET @sub_credit_limit = CAST(@sub_value AS DECIMAL(10, 2));

    -- Calculate the average credit limit
    SET @avg_credit_limit = (@pub_credit_limit + @sub_credit_limit) / 2;

    -- Convert the averaged value back to VARBINARY for output
    SET @out_value = CAST(@avg_credit_limit AS VARBINARY(MAX));

    RETURN 0; -- Success
END;
GO

ثبت Custom Resolver:

پس از ایجاد Stored Procedure، باید آن را به عنوان یک Custom Resolver در Replication ثبت کنیم. این کار به سیستم Replication می‌گوید که برای حل تضادهای مربوط به ستون `CreditLimit` از این Stored Procedure استفاده کند.

USE [MergeReplicationTest];
GO

EXEC sp_registercustomresolver
    @article_name = N'Customer_Article',
    @resolver_function = N'ResolveCreditLimitConflict',
    @resolver_type = N'Microsoft SQL Server Stored Procedure Resolver',
    @resolver_info = N'CreditLimit', -- This info is passed to the resolver
    @publisher = N'SQLSERVERCENTRAL\SQL2012', -- Your publisher name
    @publisher_db = N'MergeReplicationTest';
GO

تولید تضاد:

برای آزمایش، یک تضاد ایجاد می‌کنیم.

1. در ناشر: `CreditLimit` مشتری شماره 1 را به 1200.00 تغییر می‌دهیم.
2. در مشترک: `CreditLimit` همان مشتری را به 800.00 تغییر می‌دهیم.

در ناشر:

USE [MergeReplicationTest];
GO

UPDATE Customers
SET CreditLimit = 1200.00
WHERE CustomerID = 1;
GO

SELECT 'Publisher', CustomerID, FirstName, LastName, CreditLimit FROM Customers WHERE CustomerID = 1;
GO

در مشترک:

USE [MergeReplicationTest];
GO

UPDATE Customers
SET CreditLimit = 800.00
WHERE CustomerID = 1;
GO

SELECT 'Subscriber', CustomerID, FirstName, LastName, CreditLimit FROM Customers WHERE CustomerID = 1;
GO

اکنون، وقتی Replication Agent اجرا شود (مثلاً با Force Sync از طریق Replication Monitor یا به صورت خودکار طبق زمانبندی)، تضاد تشخیص داده شده و Custom Resolver ما فراخوانی می‌شود.

نتیجه بعد از حل تضاد:

پس از اجرای Replication Agent، مقدار `CreditLimit` برای `CustomerID = 1` باید روی میانگین 1200.00 و 800.00 یعنی 1000.00 تنظیم شود.

USE [MergeReplicationTest];
GO

-- On both Publisher and Subscriber after synchronization
SELECT 'After Sync', CustomerID, FirstName, LastName, CreditLimit FROM Customers WHERE CustomerID = 1;
GO

مشاهده رکوردهای تضاد:

تضادهای حل شده نیز در جدول `MSmerge_conflicts_info` ثبت می‌شوند.

USE [MergeReplicationTest];
GO

SELECT * FROM MSmerge_conflicts_info;
GO

این جدول جزئیات تضاد، از جمله ستون‌هایی که تغییر کرده‌اند و مقادیر قبل و بعد از حل را نشان می‌دهد.

پاکسازی (Cleanup):

برای حذف محیط Replication ایجاد شده، دستورات زیر را اجرا کنید:

USE [MergeReplicationTest];
GO

-- Remove merge pull subscription
EXEC sp_dropmergepullsubscription
    @publication = N'MergePublication',
    @subscriber = N'SQLSERVERCENTRAL\SQL2012', -- Replace with your subscriber
    @subscriber_db = N'MergeReplicationTest';
GO

-- Remove merge subscription (on Publisher)
EXEC sp_dropmergesubscription
    @publication = N'MergePublication',
    @subscriber = N'SQLSERVERCENTRAL\SQL2012', -- Replace with your subscriber
    @subscriber_db = N'MergeReplicationTest',
    @subscription_type = N'pull';
GO

-- Remove merge article
EXEC sp_dropmergearticle
    @publication = N'MergePublication',
    @article = N'Customer_Article';
GO

-- Remove merge publication
EXEC sp_dropmergepublication
    @publication = N'MergePublication';
GO

-- Unregister custom resolver
EXEC sp_unregistercustomresolver
    @article_name = N'Customer_Article',
    @resolver_function = N'ResolveCreditLimitConflict',
    @publisher = N'SQLSERVERCENTRAL\SQL2012', -- Your publisher name
    @publisher_db = N'MergeReplicationTest';
GO

-- Drop the custom resolver stored procedure
DROP PROCEDURE ResolveCreditLimitConflict;
GO

-- Drop the database
USE master;
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MergeReplicationTest')
    DROP DATABASE [MergeReplicationTest];
GO

 نتیجه‌گیری: 

مدیریت تضادها جزء لاینفک هر پیاده‌سازی Merge Replication است. با استفاده از Resolverهای داخلی SQL Server یا توسعه Custom Resolverها، می‌توانید اطمینان حاصل کنید که داده‌های شما در سراسر Replication یکپارچه و معتبر باقی می‌مانند. انتخاب Resolver مناسب به نیازهای تجاری و منطق حل تضاد شما بستگی دارد. درک عمیق این مکانیزم‌ها به شما کمک می‌کند تا سیستم‌های Replication قوی‌تر و پایدارتری بسازید.