Microsoft SQL Server Merge Replication Client Status Information

Below is a select statement which can be placed in a view, for providing information on core merge replication status information usually seen using Microsoft SQL Server Replication Monitor GUI tool.

SELECT TOP 1000 [subscriber_server]
,[db_name]
,SubStatus =
CASE [status]
WHEN 0 THEN ‘Inactive’
WHEN 1 THEN ‘Active’
WHEN 2 THEN ‘Deleted’
ELSE ‘N/A’
END
,convert(nvarchar(100), [last_sync_date], 100) as LastSyncedOn
,LastSyncStatus =
CASE [last_sync_status]
WHEN 0 THEN ‘All jobs are waiting to start’
WHEN 1 THEN ‘One or more jobs are starting’
WHEN 2 THEN ‘All jobs have executed successfully’
WHEN 3 THEN ‘At least one job is executing’
WHEN 4 THEN ‘All jobs are scheduled and idle’
WHEN 5 THEN ‘At least one job is attempting to execute after a previous failure’
WHEN 6 THEN ‘At least one job has failed to execute successfully’
ELSE ‘N/A’
END
,[last_sync_summary]
,ReplicationVersion =
CASE [replica_version]
WHEN 90 THEN ‘SQL Server 2005’
WHEN 100 THEN ‘SQL Server 2008’
ELSE ‘N/A’
END

FROM [MyDatabase].[dbo].[sysmergesubscriptions]
where subscriber_type=2
order by [last_sync_date] asc

Below might be needed as well for non-sysadmins

grant select on [sysmergesubscriptions] to public

More details on the system table used can be found by clicking here.

SQL Server Replication Options

Common configuration notes/options.

==============================

Snapshot publication: The Publisher sends a snapshot of the published data to Subscribers at scheduled intervals.

Transactional publication: The Publisher streams transactions to the Subscribers after they receive an initial snapshot of the published data.

Transactional publication with updatable subscriptions: The Publisher streams transactions to SQL Server Subscribers after they receive an initial snapshot of the published data. Transactions originating at the Subscriber are applied at the Publisher.

Merge publication: The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications

=============================

All merge articles must contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server adds a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.

Adding a new column will:      » Cause INSERT statements without column lists to fail      » Increase the size of the table      » Increase the time required to generate the first snapshot

SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.

============================================

Below shows one of the configuration wizard screens during setup of SQL Server Replication from SSMS SQL Server 2008 R2.

You have the option of configuring replication to support SQL 2008 and/or lower SQL server versions.

sql_replication