Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

When Did Merge Replication Subscribers Last Sync?

By Kimberly Killian,

I found myself in a new situation with SQL Server despite the fact that I've been working with it for over 10 years. After starting a new job as a DBA, I found that my users were using merge replication and syncing to the publisher within a window of 30 days. Obviously this is not new technology, but in my entire career of over 16 years it is the first time I have had to deal with it.

Just to add some background to my reasons for creating this, my users each have SQL Express installed as well as a "homegrown" software package sitting on top of the database. These users generally work offsite or off the network, then return to the office every couple weeks at the latest and sync their local server to a main publisher or connect via VPN while offsite. I have approximately 20+ users that make data modifications and trying to keep up with them manually by sending emails to remind them to sync is an obvious hassle and a waste of time. In order to maintain subscriptions from expiring and save myself a lot of rework, I have put together an automated process consisting of stored procedures, jobs, a few tables plus an email reminder to do it for me. This has saved me so much time and manual effort that I wanted to share.

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 - this is absolutely key to this process

  • Enable xp_cmdShell on each remote server, so you will be able to see what servers are on line and load them into a table (this will be discussed and used in steps 2 and 3). This is also important to this working properly.
  • Depending how you are set up and what servers you are watching, you may need to create a linked server for each server. In my case, since my users are laptops with SQL Express installed on them, I had to create a linked server to each. You will need to be able to write to tempdb on each remote server from a job/stored procedure running from your DBA_Reports database.
  • 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

Once you set up the above, it's time to create the tables we will use. The "ServerList" table below will house the servers name and email address associated to it. This table will be used later for email notifications and also for verifying what servers are currently online. The server names will be populated by a job, however the email addresses will need to be manually added.

Although this takes some manual setup initially, once setup, only minimal maintenance will be required if new servers come online. I have a job that runs 3 times a day that will populate this table. If a new server is inserted, I receive an email notification so I know to go and add the email address for that server. The email address in my case belongs to the owner of the server, however it could be anyone supporting a server. This is the only manual intervention I have after initial setup. You will see this job later in this article. Copy/paste/run the script below to create the ServerList table.

use DBA_Reports
GO
CREATE TABLE [dbo].[ServerList](
[Server] [varchar](128) NOT NULL,
[Server_email] [varchar](50) NULL,
CONSTRAINT [PK_ServerList] PRIMARY KEY CLUSTERED
(
[Server] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
)
ON [PRIMARY]

The "DBA_Reports - Get New Servers" job will populate the ServerList_Stage and ServerList tables. This job uses xp_cmdshell to call the ''sqlcmd /Lc'' command. This command searches the network looking for servers where SQL Server is installed. Once it finds a server, it will insert the server name into a temp table called #Server, then as long as the servername is not null, it will create and insert into the ServerList_Stage table. The ServerList_Stage is just a staging table where we will use it to compare to our ServerList table later, so it is, as it says just a "staging" table. Lastly, the job will compare the server name from the ServerList_Stage table to the ServerList table. If the record does not already exist, then it will insert it. If a new record is inserted, it will send an email listing out the newly added records with the subject of ''SQL Server - New Servers Found Today".

After you create this job, schedule it to run every day. I have mine running 3 times a day because I have users in multiple timezones as well as logging in at all different times. Once this job is setup and an initial run has occurred edit the ServerList table and enter an email address for each server. This will be used later in the sendmail_Sync_Needed stored procedure in step 3. Make sure you change <ADD YOUR INFO HERE> 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 New Servers',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Gets the new servers that are not in the table and sends out an email for new servers',
@category_name=N'DBA Monitoring',
@owner_login_name=N'sa',
@notify_email_operator_name=N'<ADD YOUR INFO HERE>', @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'step1',
@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'USE [DBA_Reports]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[ServerList_Stage]'') AND type in (N''U''))
DROP TABLE [dbo].[ServerList_Stage]
GO
USE [DBA_Reports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerList_Stage](
[Server] [varchar](128) NOT NULL,
CONSTRAINT [PK_ServerList_Stage] PRIMARY KEY CLUSTERED 
(
[Server] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-- Load table with server names
-- This will only work if XP_CMDSHELL is Enabled
USE DBA_Reports
GO
Create table #Server ( [Server] [varchar](128) )
Insert Into #Server
Exec xp_cmdshell ''sqlcmd /Lc''
Insert Into ServerList_Stage ([Server])
select [Server] from #Server where [Server] is not null
DROP Table #Server
GO
--Send email to with new servers found
SET nocount ON
--
DECLARE @Subject VARCHAR (100)
SET @Subject=''SQL Server - New Servers Found Today''
DECLARE @Count AS INT
SELECT @Count=COUNT(*) FROM DBA_Reports.dbo.ServerList_Stage where [Server] not in (select [Server] from [ServerList])
PRINT @Count
IF @Count > 0
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N''<table border="1">'' +
N''<tr>'' +
N''<th>Server</th>'' +
N''</tr>'' +
CAST ( ( SELECT td=[server],''''
FROM [ServerList_Stage]
where [Server] not in (select [Server] from [ServerList])
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''<ADD YOUR INFO HERE>'',
@recipients = ''<ADD YOUR INFO HERE>'',
@subject = @Subject,
@body = @tableHTML,
@body_format = ''HTML'' ;
END
INSERT INTO [DBA_Reports].[dbo].[ServerList]
([Server])
SELECT [Server]
FROM [DBA_Reports].[dbo].[ServerList_Stage]
where [Server] not in (select [Server] from [DBA_Reports].[dbo].[ServerList])
GO
',
@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_jobschedule @job_id=@jobId, @name=N'Daily 3 times',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=3,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110512,
@active_end_date=99991231,
@active_start_time=63000,
@active_end_time=220000
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:
GO

This table will house the last time a server synced and will also be populated by the stored procedure CheckLastSync discussed later in step 2. Also included in this table is the database name, sync status and summary details. I usually only care about records with a last_sync_status of "2" which is successful. Copy/Paste/Run the script below to create the table.

use DBA_Reports
CREATE TABLE [dbo].[LastSynced](
[subscriber_server] [sysname] NULL,
[db_name] [sysname] NOT NULL,
[last_sync_date] [datetime] NULL,
[last_sync_status] [int] NULL,
[last_sync_summary] [sysname] NULL
) ON [PRIMARY]

Step 2: Creating the CheckLastSync stored procedure

This stored procedure uses similar logic to search the network using the xp_cmdshell to see what servers are online as we did above in step 1. Once we have a current list of servers, it will create a table in tempdb on each server that will be populated at the remote server and later be accessed from the main database. Next it queries the last synced data into a temp table for each db except the system databases or databases you choose to exclude. It then loops on the tables, inserts sync information into a temporary table. Lastly, it will insert sync information including server name and database name into the remote table created in tempdb. Finally, it inserts the lastsynced details to the centralized main database table. Copy/Paste/Run the script to create it. Make sure you change the highlighted area to your server name and database name.

use DBA_Reports
go
CREATE procedure [dbo].[CheckLastSync]
as
declare
@err int,
@cmd varchar(8000),
@cmd2 varchar(8000),
@ServerName sysname
set nocount on
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServerList_Sync]') AND type in (N'U'))
DROP TABLE [dbo].[ServerList_Sync]
CREATE TABLE [dbo].[ServerList_Sync](
[Server] [varchar](128) NOT NULL,
CONSTRAINT [PK_ServerList_Sync] PRIMARY KEY CLUSTERED
( [Server] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]
-- Load table with server names
-- This will only work if XP_CMDSHELL is Enabled
Create table #Server ( [Server] [varchar](128) )
Insert Into #Server
Exec xp_cmdshell 'sqlcmd /Lc'
Insert Into ServerList_Sync ([Server])
select [Server] from #Server
DROP Table #Server
--Start fresh with every run
Truncate Table dbo.LastSynced
-- Loop on Server list and find last sync'd data
declare SrvCursSync cursor for
select Server from dbo.ServerList_sync
open SrvCursSync
fetch SrvCursSync into @ServerName
while @@FETCH_STATUS = 0
BEGIN
-- Create a table in tempdb on each remote server
select @cmd = 'if exists (select 1 from tempdb.dbo.sysobjects ' +
'where type = ''U'' AND NAME = ''lastsynced'') ' +
'drop table tempdb.dbo.lastsynced;' +
'CREATE TABLE tempdb.dbo.lastsynced (' +
'subscriber_server sysname,' +
'db_name sysname,' +
'last_sync_date date,' +
'last_sync_status INT,' +
'last_sync_summary varchar(500)) '
select @cmd2 = 'sqlcmd -E -S"' + @ServerName + '" -Q "' + @cmd + '"' 
exec master..xp_cmdshell @cmd2
select @err = @@error
IF @err <> 0
begin
select 'ERROR Unable to create the table in tempdb for: ' + @ServerName
return -1
end
/* Query the last synced data into remote table.
In the remote server, for each database, except for the system databases (feel free to add additional db's, in my case, I only have 1 or 2 db's on each remote server).
Loop on the tables, insert sync information into a temporary table.
Insert sync information including server name and database name into the remote table created in tempdb.
This table will be later read from the central database.*/
select @cmd =' 
exec [' + @ServerName + '].master.dbo.sp_msforeachdb ' +
'''use [?]; print ''''?'''';
if ''''?'''' in (''''tempdb'''',''''msdb'''',''''master'''',''''distribution'''',
''''model'''',''''Northwind5'''',''''pubs'''')
return
insert into tempdb.dbo.lastsynced SELECT [subscriber_server], [db_name], [last_sync_date], [last_sync_status], [last_sync_summary] FROM [sysmergesubscriptions];'''
exec (@cmd)
select @err = @@error
IF @err <> 0
begin
select 'ERROR Collecting last synced information for: ' + @ServerName
return -1
end
-- Insert the lastsynced details to the DBA_Reports server LastSynced table
select @cmd = 'insert into <YourServerName>.DBA_Reports.dbo.LastSynced select * from [' + @ServerName + '].tempdb.dbo.lastsynced'
exec (@cmd)
select @err = @@error
IF @err <> 0
begin
select 'ERROR selecting data from remote table for: ' + @ServerName
return -1
end
fetch SrvCursSync into @ServerName
END
close SrvCursSync
DEALLOCATE SrvCursSync

Step 3: Create 'sendmail_Sync_Needed' stored procedure.


The sendemail_Sync_Need stored procedure will read from the lastSynced table and compare the last_sync_date to 25 days back. If the db has not been synced in at least 25 days, then an email will be sent to the owner of the server remind them to sync.

Copy/Paste/Run the script below to create the stored procedure. Make sure you change the areas that show <ADD YOUR INFO HERE>. This stored will send an email to users that have not synced in at least 25 days.

Use [DBA_Reports]
Go
CREATE PROCEDURE [dbo].[sendemail_Sync_Needed]
AS
SET NOCOUNT ON;
Declare @ServerName varchar(100)
Declare synccursor CURSOR FORWARD_ONLY FOR
Select ([subscriber_server]) FROM [DBA_Reports].[dbo].[LastSynced]
group by [subscriber_server], last_sync_status
having last_sync_status = 2 and max(last_sync_date) <= GETDATE() -25
Open synccursor
fetch Next from synccursor into @ServerName
While @@FETCH_STATUS = 0
BEGIN
Declare @email varchar(25)
select @email = Server_email from dbo.ServerList where [Server] = @ServerName
Declare @body varchar (1000)
SET @body = '<html><body><font face=verdana size=2>
<p><strong>' + @ServerName + '<br> <br>You have not synced your database in at least 25 days, to avoid issues and reinstallation please do so ASAP. </strong><br></p>
<p><i>Thank you,</i></p>
<p><strong>
<ADD YOUR INFO (name) HERE></strong>
<br><ADD YOUR INFO (title) HERE>
<br><ADD YOUR INFO (company) HERE>
<br><ADD YOUR INFO (phone/email) HERE>
</p>
</body></html>'
--send email reminder to laptops
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '<ADD YOUR INFO HERE>',
@recipients =@email,
@copy_recipients = '<ADD YOUR INFO HERE>',
@subject ='AUTOMATED SEND: Please Sync your Database',
@body =@body,
@body_format ='HTML';
fetch Next from synccursor into @ServerName
END
--clean up
close synccursor
deallocate synccursor

Step 4: Create the job 'DBA_Reports - Laptop Sync History and Reminder'.

This job will kick off the stored procedure to collect the sync information, send an email to the DBA with the data collected then send an email to the users that need to sync. My subscriptions are set to expire in 30 days, so I use 25 days as my threshold for notification. The threshold is set in the sendemail_Sync_Needed stored procedure. I have this job scheduled everyday, so that users will get a reminder each day until they sync. Nothing like a constant reminder, to get users to listen. Copy/Paste this script to create the job. Remember to change areas that have <ADD YOUR INFO HERE> with 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 - Laptop Sync History and Reminder',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Reports last time laptops synced and the status',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'<ADD YOUR INFO HERE>', @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'Call the SPROC',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec CheckLastSync',
@database_name=N'DBA_Reports',
@output_file_name=N'<ADD YOUR INFO HERE>',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send the email',
@step_id=2,
@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'SET nocount ON
--
DECLARE @Subject VARCHAR (100)
SET @Subject=''DBA_Reports - Laptop Sync History''
DECLARE @Count AS INT
SELECT @Count=COUNT(*) FROM [DBA_Reports].[dbo].[LastSynced] WHERE last_sync_status = 2
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>Last Sync Date</th>'' +
N''<th>Sync Summary</th>'' +
N''</tr>'' +
CAST ( ( SELECT td=[subscriber_server],''''
,td=[db_name],''''
,td=convert(varchar(10), [last_sync_date],110),''''
,td=[last_sync_summary],''''
FROM [LastSynced]
group by [subscriber_server], [db_name],[last_sync_date], [last_sync_status], [last_sync_summary]
having last_sync_status = 2
order by last_sync_date asc
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''<ADD YOUR INFO HERE>'',
@recipients = ''<ADD YOUR INFO HERE>'',
@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_add_jobstep @job_id=@jobId, @step_name=N'Send warning email to users who have not synced in 25 days',
@step_id=3,
@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 sendemail_Sync_Needed',
@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_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=20110603,
@active_end_date=99991231,
@active_start_time=133000,
@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:

Schedule this job to run once per day. I have mine running at 1PM which ensures me the most people online, however this may not work for you.

Conclusion

There you have it, a way to tell when merge replication subscribers have synced last and also notify them when they need to sync again.

Included in this package, the following will be created:

Tables:

  • Tables ServerList_Stage
  • ServerList
  • ServerList_Sync
  • LastSynced

Stored Procedures:

  • CheckLastSync
  • Sendemail_Sync_Needed

Jobs

  • DBA_Reports - Laptop Sync History and Reminder
  • DBA_Reports - Get New Servers

Resources:

Scripts.zip
Total article views: 1566 | Views in the last 30 days: 2
 
Related Articles
FORUM

Best way to create a weekly email report for SQL Server

SQL Server, reports, statistics, email

FORUM

Email Automation

Automate EMail process from SQL Server

FORUM

Getting Error when create File Share or Email Subscription

Getting Error when create File Share or Email Subscription

FORUM

Sending email alert when SQL Server restarts

Email alerts in SQL Server 2005

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones