Patrick Purviance – SQL Server DBA Blog
Blogging impactful solutions for SQL Server DBAs worldwide

Procedure to Validate SQL Server Replication Convergence

Today I’m posting a turn-key solution for those of you out there supporting SQL Server replication. The purpose of the stored procedure created by the script provided below is simply to leverage a little-known, but very useful, tool provided by Microsoft for the sake of convergence validation (validate that all of your table based articles are identical between the publisher and subscriber(s)).

The execution of the SP (once compiled) simply generates syntax in text format that can be saved as a batch file or run as-is (carefully) from a command prompt to check for convergence issues between a published DB and its subscriber’s DB tables.

The underlying tool, called TableDiff.exe, again, is provided by Microsoft and can be found with the SQL Server binaries. Because of this, the SP should be executed on the server upon which you will be executing the resulting script so that the correct binary location will be provided.

I typically compile the SP in a DBA “tools” database, execute it providing all of the required parameters, save the resulting text as .bat file on the desktop of the publisher’s host server, and then execute the .bat. The batch file will create a log report and, optionally, a fix-it T-SQL script for each article to bring the table(s) back into convergence if differences are found.

Note: There currently is NO error checking done for the the location of TableDiff.exe. It can be added if needed.


Example Usage: EXEC DBA_TOOLS.dbo.Repl_Gen_TableDiff_Code_usp @Pub_SvrName = @@servername , @Sub_SvrName = 'SERVER_A' , @Pub_DBName = 'CUSTOMER' , @Sub_DBName = 'CUSTOMER' , @Provide_Fix = 1 , @Outputdir = 'C:\' Where: @Pub_SvrName is the name of the publisher server @Sub_SvrName is the name of the subscriber server @Pub_DBName is the name of the published database @Sub_DBName is the name of the subscriber database

This has been tested on SQL Server 2005/2008/2008R2, but only with Transactional & Snapshot Replication. Merge Replication uses some alternative tables for configuration and lookups and often includes parameterized filters for subscribers, so I won’t claim this solution is appropriate for Merge or Peer-to-Peer replication, but am open to comments on the topic.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
USE [DBA_Tools]
GO
 
IF OBJECT_ID('Repl_Gen_TableDiff_Code_usp') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.Repl_Gen_TableDiff_Code_usp
END
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[Repl_Gen_TableDiff_Code_usp] 
	  @Pub_SvrName	SYSNAME
	, @Sub_SvrName	SYSNAME
	, @Pub_DBName	SYSNAME
	, @Sub_DBName	SYSNAME
	, @Provide_Fix  BIT = 0
	, @OutputDir	VARCHAR(4000)
	, @DebugFlag	BIT = 0
AS
--================================================================================
--==  Procedure:		dbo.Repl_Gen_TableDiff_Code_usp  
--==  Author:			Patrick Purviance
--==
--==  Description:		Simple SP to generate syntax that can be saved as a batch
--==					file or run as-is (carefully) from a command prompt to 
--==					check for convergence issues between a published DB and
--==					its subscriber's DB tables.  TableDiff.exe is provided
--==					with the SQL Server binaries and therefore this SP should
--==					from the server upon which you will be executing the 
--==					resulting script so that the correct binary location will
--==					be selected.  
--==
--==
--==					Note:  There currently is NO error checking done for the 
--==					the location of TableDiff.exe.  It can be added if needed.
--==
--==
--==  Usage:			EXEC DBA_TOOLS.dbo.Repl_Gen_TableDiff_Code_usp 
--==  								@Pub_SvrName	= @@servername
--==  								, @Sub_SvrName	= 'SERVER_A'
--==  								, @Pub_DBName	= 'CUSTOMER'
--==  								, @Sub_DBName	= 'CUSTOMER'
--==								, @Provide_Fix = 1
--==								, @Outputdir = 'C:\'
--==
--==					Where @Pub_SvrName is the name of the publisher server,
--==							@Sub_SvrName is the name of the subscriber server,
--==							@Pub_DBName is the name of the published database
--==							@Sub_DBName is the name of the subscriber database
--==
--==  Version history:
--==					v1.0 Created, PPurviance, 11/29/2010
--==					v1.1 Modified to use replication database syspublications to be sure 
--==						 to pick up all articles. I found a bad publication_id in 
--==						 distribution..mspublications causing some articles not to 
--==						 get picked up for convergence checking.
--================================================================================
SET NOCOUNT ON 
 
DECLARE @MSG VARCHAR(2000)
		, @SQL VARCHAR(4000)
		, @LogDate VARCHAR(13)
		, @Version VARCHAR(4)
		, @TablediffLoc VARCHAR(256)
		, @FixFile VARCHAR(256)
 
DECLARE @SysArticles TABLE (pubid int, artid int, artname SYSNAME)
DECLARE @Publications TABLE (pubname sysname, pubid int)
 
 
--==========================================
--== Determine the current SQL version:
--==========================================
 
Select @version = left(convert(varchar, SERVERPROPERTY('ProductVersion')), 2)
 
Select @version = 
	Case @version 
		When '7.' then '7'
		When '8.' then '2000'
		When '9.' then '2005'
		When '10' then '2008'
	End
 
 
--==========================================
--== Check all supplied values:
--==========================================
 
--== First check the publisher server name. This must be run on the publisher.
IF rtrim(@Pub_SvrName) <> @@servername
BEGIN
	SET @MSG = 'This procedure must be run on the publisher to work correctly.  The servername provided does not match the local server name.'
	RAISERROR(@MSG,0,1)	WITH NOWAIT;
 
	RETURN
END
 
IF NOT EXISTS(SELECT 1 FROM master.sys.databases where name = rtrim(@Pub_DBName))
BEGIN
	SET @MSG = 'Supplied Publisher Database cannot be found.'
	RAISERROR(@MSG,0,1)	WITH NOWAIT;
 
	RETURN
END
 
--==========================================
--==  End check all supplied values
--==========================================
 
SET @SQL = 	'SELECT pubid, artid, name FROM [' + rtrim(@Pub_DBName) + '].[dbo].[sysarticles]'
 
INSERT @SysArticles	
EXEC(@SQL)
 
IF @DebugFlag = 1
BEGIN
	PRINT @SQL
	RETURN
END
 
SET @SQL = 	'SELECT name, pubid FROM [' + rtrim(@Pub_DBName) + '].[dbo].[syspublications]'
 
INSERT @Publications
EXEC(@SQL)
 
IF @DebugFlag = 1
BEGIN
	PRINT @SQL
	RETURN
END
 
--==Build the date and time and tablediff location portion for the log name
SET @LogDate = CONVERT(VARCHAR, (DATEPART(YYYY, GETDATE()))) + CONVERT(VARCHAR, (DATEPART(MM, GETDATE()))) + CONVERT(VARCHAR, (DATEPART(DD, GETDATE()))) + '_' + CONVERT(VARCHAR, (DATEPART(HH, GETDATE()))) + CONVERT(VARCHAR, (DATEPART(MI, GETDATE())))
 
SELECT @TablediffLoc = CASE @version 
	WHEN '2000' THEN '"C:\Program Files\Microsoft SQL Server\80\COM\tablediff" '
	WHEN '2005' THEN '"C:\Program Files\Microsoft SQL Server\90\COM\tablediff" '
	WHEN '2008' THEN '"C:\Program Files\Microsoft SQL Server\100\COM\tablediff" '
END
 
SET @FixFile = ''
 
--==Now build the statement
		SELECT @TablediffLoc 
			+ '-sourceserver "' + rtrim(@Pub_SvrName)
			+ '" -sourcedatabase "' + rtrim(@Pub_DBName)
			+ '" -sourcetable "' +  sa.artname
			+ '" -destinationserver "' + rtrim(@Sub_SvrName)
			+ '" -destinationdatabase "' + rtrim(@Sub_DBName)
			+ '" -destinationtable "' + sa.artname 
			+ CASE WHEN @Provide_Fix <> 0
				THEN '" -f "' + RTRIM(@OutputDir) + '\Make_' + RTRIM(@Sub_SvrName) + '_' + RTRIM(@Sub_DBName) + '_LookLike_' + RTRIM(@Pub_SvrName) + '_' + RTRIM(@Pub_DBName) + '_For_' + RTRIM(sa.artname) + '.sql"'
			  ELSE '"'
			  END   
			+ ' >> "' + RTRIM(@OutputDir) + '\Tablediff_Log_' + RTRIM(@Sub_SvrName) + '_' + RTRIM(@Sub_DBName) + '_vs_' + RTRIM(@Pub_SvrName) + '_' + RTRIM(@Pub_DBName) +  '_' + rtrim(@LogDate) +  '.txt"'
		FROM @SysArticles sa inner join
			@Publications pub on pub.pubid = sa.pubid
		ORDER BY sa.artname 
RETURN

-Patrick

Leave a Reply

Your email address will not be published. Required fields are marked *