Sql-server – How to user the Tablediff Utility with Replication Filters

merge-replicationreplicationsql server

I am using merge replication in SQL 2012. I am trying out the TableDiff utility to show non convergence.

I see a problem with this approach though. This is because I am using parameterised filters to filter the subscriptions.

In this instance the TableDiff utility tells me that there are missing rows in the subscription and generates the SQL to insert them. If I swap the source and destination around it generates a whole lot of delete statements to remove the records at the publication.

This is not a correct result. It should just check the rows that exist at the subscriber, or understand the filters and know which rows should be at the subscriber.

Can I use this utility in this instance? If not is there an alternative? I find it surprising that none of the documentation about this utility mentions this problem.

Best Answer

Run TableDiff.exe against a view configured to limit the source table in the same way your replication filter does. I've created an example here:

USE Test;

    /* Create two test tables, TableDiff1 is the source and TableDiff2 is the destination */
CREATE TABLE TableDiff1
(
    TableID INT NOT NULL CONSTRAINT PK_TableDiff1 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , FilterColumn INT NOT NULL
    , TableData NVARCHAR(255) CONSTRAINT DF_TableDiff1_TableData DEFAULT (NEWID())
);

CREATE TABLE TableDiff2
(
    TableID INT NOT NULL CONSTRAINT PK_TableDiff2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , FilterColumn INT NOT NULL
    , TableData NVARCHAR(255) CONSTRAINT DF_TableDiff2_TableData DEFAULT (NEWID())
);


    /* Create some dummy data in TableDiff1 */
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (2, DEFAULT);


    /* Copy the dummy data into TableDiff2, with a FILTER */
INSERT INTO TableDiff2 (FilterColumn, TableData)
SELECT FilterColumn, TableData
FROM TableDiff1
WHERE TableDiff1.FilterColumn = 1;

    /* Add some more data to TableDiff1 that does not exist in TableDiff2
       so TableDiff.exe shows differences between the two tables */
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);

SELECT * FROM TableDiff1;
SELECT * FROM TableDiff2;

enter image description here

Next, we run TableDiff.exe against both tables, clearly there will be differences since we are not excluding the filtered rows (FilterColumn = 1)

C:\Program Files\Microsoft SQL Server\110\COM>TableDiff -sourceserver localhost -sourcedatabase Test -sourceschema dbo -sourcetable TableDiff1 -destinationserve r localhost -destinationdatabase Test -destinationschema dbo -destinationtable T ableDiff2

Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:
-sourceserver localhost
-sourcedatabase Test
-sourceschema dbo
-sourcetable TableDiff1
-destinationserver localhost
-destinationdatabase Test
-destinationschema dbo
-destinationtable TableDiff2

Table [Test].[dbo].[TableDiff1] on localhost and Table [Test].[dbo].[TableDiff2]
 on localhost have 2 differences.
Err     TableID
Src. Only       3
Src. Only       4
The requested operation took 0.1573824 seconds.

Next, we create a View that has a WHERE filter to select only the FilterColumn = 1 rows:

GO
CREATE VIEW TableDiff1_Filtered
AS
SELECT TableID, FilterColumn, TableData
FROM TableDiff1
WHERE TableDiff1.FilterColumn = 1;

Now, when we run TableDiff.exe against the view, we get the desired results. Only a single row is identified as missing from the destination table (the row we added after "replicating" the rows from the source table).

C:\Program Files\Microsoft SQL Server\110\COM>TableDiff -sourceserver localhost -sourcedatabase Test -sourceschema dbo -sourcetable TableDiff1_Filtered -destina tionserver localhost -destinationdatabase Test -destinationschema dbo -destinati ontable TableDiff2

Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:
-sourceserver localhost
-sourcedatabase Test
-sourceschema dbo
-sourcetable TableDiff1_Filtered
-destinationserver localhost
-destinationdatabase Test
-destinationschema dbo
-destinationtable TableDiff2

Table [Test].[dbo].[TableDiff1_Filtered] on localhost and Table [Test].[dbo].[Ta
bleDiff2] on localhost have 1 differences.
Err     TableID
Src. Only       4
The requested operation took 0.1220787 seconds.
Related Question