While working with merge replication, I found it tedious to have to go to every publication server and view the replication conflicts using the "view conflicts" tool that is provided with SQL Server Management Studio.Plus, call me lazy, but I like an email notification for all repeatable or tedious tasks that could be automated. I looked on the web for solutions already out there, but I found nothing, so I have created a process that will collect all of the replication conflicts into a central database for each publisher and then send out an email notification. In my case, I have 1 main publisher, 2 re-publishers and 15 subscriptions for replication, so I generally have several conflicts a week to deal with. This process consists of the following:

• 1 Job to collect data (on each publisher)
• 1 SSIS package to move the data from flat files to tables
• 1 main Job to call the SSIS package (on the centralized server)
• 1 report Job to create the alert that will be called by the main job above.
• 2 tables to house the conflict information (on the centralized server)

I have created a database that I use for my DBA activities that I call DBA_Reports, however, you can call yours whatever you like just remember to change it in the scripts below. Please make sure that you have the following before you begin:

• DBMail enabled on your server(s) - this is absolutely key to this process
• Depending how you are set up and what servers you are watching, you may need to create a linked server for each server you are sharing data with.
• Create yourself as an operator so you can receive notifications if your job fails. Personally I like to add a notification on every job I create in case it fails. This is optional.

I'm not going to go into details on how to set these up as they are readily available from other sources, however, just consider them as prerequisites.

Step One: Create the tables

The table PublisherConflict_counts_stage is used as the initial holding place where the first round of data is held. This is populated by the SSIS package Get Replication Conflicts that will be discussed in detail in step three.

CREATE TABLE [dbo].[PublisherConflict_counts_stage](
[article] [varchar](max) NOT NULL,
[source_object] [varchar](max) NOT NULL,
[conflict_table] [varchar](max) NOT NULL,
[guidcolname] [varchar](max) NOT NULL,
[centralized_conflicts] [varchar](max) NOT NULL,
[conflicts_ucount] [varchar](25) NULL,
[conflicts_dcount] [varchar](25) NULL
) ON [PRIMARY]

The PublisherConflicts_counts table is used as the final holding place for the conflict data and alert generation as discussed in step three. Data is moved from the PublisherConflict_counts_stag table by the SSIS package Get Replication Conflicts and then the table is updated with the server name and database name for each record.

CREATE TABLE [dbo].[PublisherConflicts_counts](
[article] [varchar](max) NOT NULL,
[source_object] [varchar](max) NOT NULL,
[conflict_table] [varchar](max) NOT NULL,
[guidcolname] [varchar](max) NOT NULL,
[centralized_conflicts] [varchar](max) NOT NULL,
[conflicts_ucount] [varchar](25) NULL,
[conflicts_dcount] [varchar](10) NULL,
[servername] [varchar](100) NULL,
[databasename] [varchar](100) NULL
) ON [PRIMARY]

Step Two: Generate the Flat file using Get Conflicts Job

The Get Conflicts job will run exec sp_MShelpmergeconflictcounts on the publisher database and write the results out to a file. I have my file being saved to my central location, however put yours where ever you like, just make sure you can access it from your central server. I schedule mine to run every 2 hours, again the schedule is up to you. This job will need to run on each publisher. Copy/paste the script below, make sure to change the areas marked <> with your data.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA-Reports - Get Conflicts',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Collects conflict information into flat files to be used in reporting',
@category_name=N'Data Collector',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get Conflicts',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec sp_MShelpmergeconflictcounts',
@database_name=N'<add your database here>',
@output_file_name=N'\\<add your location here>\Conflicts.txt',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily',
@enabled=1,
@freq_type=8,
@freq_interval=62,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110727,
@active_end_date=99991231,
@active_start_time=120000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Job: Get Replication Publisher Conflicts

The Get Replication Publisher Conflicts job calls the SSIS package named Get Replication Conflicts in step one where the data from the files is pulled into the tables. In step two, it will call the job DBA-Reports - Report Get Replication Publisher Conflicts which will send out the email notification; this job will be discussed next. I have this scheduled to run once per day. However if you have a lot of conflicts to deal with throughout a day, this schedule could be increased to what ever you want. Copy/Paste/Run the script below to create the job and remember to change the information in <> to your information.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA Monitoring' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA Monitoring'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA-Reports - Get Replication Publisher Conflicts',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Collects the replication conflicts from each publisher then consolidates them into a table.
Sends out an email notification',
@category_name=N'DBA Monitoring',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Consolidate the data from all publishers',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/SQL "\Get Replication Conflicts" /SERVER <add server name> /CHECKPOINTING OFF /REPORTING E',
@database_name=N'SQL_Overview'
--@output_file_name=N'E:\Reports\GetConflictsErrors.txt',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC msdb.dbo.sp_start_job ''DBA-Reports - Report Get Replication Publisher Conflicts''',
@database_name=N'<Add Your DB here>',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily',
@enabled=1,
@freq_type=8,
@freq_interval=62,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110727,
@active_end_date=99991231,
@active_start_time=150000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Job: Report Get Replication Publisher Conflicts

The Report Get Replication Publisher Conflicts job will send out an email alert of the conflicts collected. I have mine scheduled to run once per day. This is a great reminder for DBA's to resolve conflicts and is the heart of this process. I have this report in a separate job so I can run it anytime after the initial run that is called from Get Replication Publisher Conflicts. This job uses database mail within SQL Server, so make sure you have that setup prior to running. Copy /Paste/Run the script below to create the job and make sure to change the text in <> to your information.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA-Reports - Report Get Replication Publisher Conflicts',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Sends an email of publisher conflicts from all publishers',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'<Add your operator>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'SET nocount ON
--
DECLARE @Subject VARCHAR (100)
SET @Subject=''SQL Server - Replication Conflicts on Publishers''
DECLARE @Count AS INT
SELECT @Count=COUNT(*) FROM [SQL_Overview].[dbo].[PublisherConflicts_counts
PRINT @Count
IF @Count > 0
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N''<table border="1">'' +
N''<tr>'' +
N''<th>Server</th>'' +
N''<th>Database</th>'' +
N''<th>Article</th>'' +
N''<th>Conflicts</th>'' +
N''</tr>'' +
CAST ( ( SELECT td=[servername],''''
,td=[databasename],''''
,td=[article],''''
,td=(cast([conflicts_ucount] as int) + cast([conflicts_dcount] as int)),''''
,''''
FROM [SQL_Overview].[dbo].[PublisherConflicts_counts]
ORDER BY Servername
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''<use your info>'',
@recipients = ''<use your info>'',
@subject = @Subject,
@body = @tableHTML,
@body_format = ''HTML'' ;
END',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Step Three: SSIS Package - Get Replication Conflicts

In the SSIS package called Get Replication Conflicts, this is a simple process that imports data from flat files, then tweeking the data by adding the database name and server name of the source record before moving the data on to its final destination. The package is fairly simple with only 3 main parts plus 3 data flowpieces. In my case, as I stated previously, I have 1 main publisher and 2 re-publishers, so for each server, I have the following in my package.

Connections(1 for each publisher flat file generated):

• SourceConnectionFlatFile_MainPublisher
• SourceConnectionFlatFile_Publisher1
• SourceConnectionFlatFile_Publisher2

This is the connection to the flat file generated in step 2 from the job called Get Conflicts. You will need one of these for each publisher you plan on collecting data for.

Central Server Connection

This the connection to the database for the central server.

Once you have your connections created, continue to create the package. On the control flow create a SQL task to truncate the tables with the following two lines:

truncate table [dbo].[PublisherConflict_counts_stage]
truncate table [dbo].[PublisherConflicts_counts]

I like to start fresh with every run of data since data changes daily, there is no point in retaining the data. If you only have a single publisher, then above is what your package will look like. If you have multiple publishers like me, then you will need a set of these branches for each publisher plus one additional SQL Task that truncate table [dbo].[PublisherConflict_counts_stage] so that you do not re-insert duplicates. This step follows the Add DB and Server Names and move to final table or basically it goes between each set of branches(publishers).

Next create a data flow task to move data from the generated flat files into the PublisherConflict_counts_stage table.

Add a flat file source and point it to your flat file connection. 

Create a data conversion task. Make sure you change the conflicts_ucount and conflicts_dcount data type to string/length 25 so it matches our table definition as created in step one.

Finally create the OLE Destination task. This will move the data into our table.

The final step on the control flow is to add a sql task that will add the database and server name.

INSERT INTO [SQL_Overview].[dbo].[PublisherConflicts_counts]
([article]
,[source_object]
,[conflict_table]
,[guidcolname]
,[centralized_conflicts]
,[conflicts_ucount]
,[conflicts_dcount]
,[servername]
,[databasename])
(SELECT [article]
,[source_object]
,[conflict_table]
,[guidcolname]
,[centralized_conflicts]
,[conflicts_ucount]
,[conflicts_dcount],
'<your server>',
'<your database>'
FROM [SQL_Overview].[dbo].[PublisherConflict_counts_stage])

When this package is completed and saved, import it into SSIS.

Conclusion

Now you have a way to capture replication conficts and receive an alert of the replication publisher conflicts automatically.

Included in this package, the following will be created:

Tables:

- dbo.PublisherConflict_counts_stage

- dbo.PublisherConflicts_counts

Jobs

- DBA-Reports - Get Replication Publisher Conflicts

- DBA-Reports - Report Get Replication Publisher Conflicts

- DBA-Reports - Get Conflicts

SSIS Package

- Get Replication Conflicts