SQL Server Transactional Replication
Created By: Ramu Poonjolai
April 29, 2015
Table of Contents
Introduction ........................................................................................................................................................................ 3
What is transactional replication and when to use it ....................................................................................................... 3
Setup Transactional replication ......................................................................................................................................... 3
How to troubleshoot replication ....................................................................................................................................... 3
Technique 1 ..................................................................................................................................................................... 3
Technique 2 ..................................................................................................................................................................... 3
Troubleshooting queries .................................................................................................................................................... 3
Remove replication information after restore................................................................................................................ 3
View All Outstanding Replicated Commands ................................................................................................................. 3
Delete the command ...................................................................................................................................................... 4
How to specify –skiperror parameter at distribution agent ........................................................................................... 4
Which tables/articles transaction are waiting to be replicated and how much? ........................................................... 4
How to find recent log reader agent status .................................................................................................................... 4
Catalog views to get info about Publication/Subscription/Articles ................................................................................. 4
Initialize subscriber ............................................................................................................................................................ 4
Initialize Transaction replication using backup/restore.................................................................................................. 5
Initialize the subscriber using alternate snapshot folder................................................................................................ 5
Introduction
In this document you will learn how to setup transactional replication and how to monitor it.
What is transactional replication and when to use it
Setup Transactional replication
How to troubleshoot replication
Technique 1
1) Open the replication monitor
2) Double click on the subscriber.
3) You will see the error message stating “Transaction sequence number” & “Command Id”
4)
Take the “Transaction sequence number” & “command id” from step-3
You can get some other information from the below query:
SELECT * FROM distribution .dbo.msrepl_commands
WHERE xact_seqno = 0x0000044100002D93000100000000
AND command_id = 1
5)
Get the values from step 4 and plug it here…
Execute the sp_browsereplcmds to find the actual commands
EXEC SP_BROWSEREPLCMDS
@xact_seqno_start = '0x0000044100002D930001',
@xact_seqno_end = '0x0000044100002D930001',
@publisher_database_id = 1,
@article_id = 12,
@command_id= 1
Technique 2
Add the following to agent in sql server job.
-OutputVerboseLevel 2 -Output C:\temp\ReplOutput.txt
Troubleshooting queries
Remove replication information after restore
This will disable the replication info on the database
exec sp_replicationdboption @dbname= N’MyDB’, @optname=N’publish’, @value = ‘FALSE’
View All Outstanding Replicated Commands
select * from MSrepl_commands
or
exec distribution..sp_browsereplcmds
Delete the command
Sometimes replication cannot replicate the command. In that case, you can delete the command from the
distribution.dbo.msrepl_commands table or put -skiperror at the distribution agent.
How to specify –skiperror parameter at distribution agent
Create the new agent profile for the distribution agent. Put the error/s that you wanted to skip like this:
Which tables/articles transaction are waiting to be replicated and how much?
select
count(*) as [# of commands] ,
f.publication,
c.publisher_database_id,
d.publisher_db,
c.article_id,
e.article,
entry_time
from MSrepl_commands c with (NOLOCK)
inner join MSrepl_transactions t with (NOLOCK) on c.publisher_database_id = t.publisher_database_id
and c.xact_seqno = t.xact_seqno
inner join MSpublisher_databases d with (NOLOCK) on d.id = c.publisher_database_id
inner join MSarticles e with (NOLOCK) on e.publisher_db = d.publisher_db and e.article_id =
c.article_id
inner join MSpublications f with (NOLOCK) on f.publisher_db = e.publisher_db and e.publication_id =
f.publication_id
where f.publication = '(publication_name)'
group by
c.publisher_database_id,
f.publication,
d.publisher_db,
c.article_id,
e.article,
entry_time
order by [# of commands] desc
source: https://sqlship.wordpress.com/category/sql-server-replication/
How to find recent log reader agent status
select * FROM distribution.dbo.MSlogreader_history order by time desc
Catalog views to get info about Publication/Subscription/Articles
select * from syspublications
select * from syssubscriptions
select * from sysarticles
Initialize subscriber
Initialize Transaction replication using backup/restore
If the database is huge and you are trying to setup the replication, you might want to backup & restore the database
instead of using snapshot replication. If you run the initialization (using snapshot agent) for bigger database, it might
cause blockings on the database and that is not preferred way.
1)
Make sure the "Allow initialization from backup files" setting is True for the publication.
2)
Backup the publishing database
3)
Restore the database on subscriber
4)
While you are adding(or creating) the subscription, you need to make following changes.
@sync_type = 'initialize with backup',
@backupdevicetype ='disk',
@backupdevicename = 'C:\PUB_DB.bak'
Initialize the subscriber using alternate snapshot folder
Sometimes, due to network issues snapshot files cannot be copied to subscriber. So, you can manually zip them and
copy to subscriber and initialize the subscriber from that location.
Zip the initialization (snapshot) files on distributor
Copy the zip file to subscriber
Unzip it and extract it to a folder called ‘unc’.
For example: D:\Somefolder\unc\SNAPSHOT-FOLDERNAME\*.*
Right click the subscriber, select properties. Change alternate snapshot folder location.
