Administration of Transactional Replication

There are 3 types of Replication Types in SQL Server:

1. Snapshot Replication
2. Transactional Replication
3. Merge Replication

Replication of data can be configured between same server or can be different servers connected by Network. SQL Server replication uses a publisher, distributor and subscriber for replication to work
.
Publisher is the database that sends its data to another database which can be located in same or another server.

Subscriber is the database that receives data from another database which can be located in same or another server.

Distributor is the server that manages the flow of data through the replication system. This server contains the distribution database.

There are 3 steps involved for Configuring the Transactional Replication:

1. Configuring the Distribution Database.

2. Creating the publisher.

3. Creating the subscriber.

Administration of Transactional Replication:

1. For the Administration of Transaction Replication we can use Replication Monitor which will show all relavant information on Replication Status.

2. SQL Commands Commonly used for Dealing with various situation in Transactional Replication administration

Command	Description
DBCC OPENTRAN	to find out whether If your log reader is keeping up with what’s in the transaction log which displays the ‘oldest non-distributed LSN’
DBCC INPUTBUFFER(SPID)	To find which transactions are pending where SPID is found from DBCC Opentran
sp_repldone	sp_repldone is used by the log reader process to track which transactions have been distributed. With sp_repldone, you can manually tell the server that a transaction has been replicated (sent to the Distributor). It also allows you to change the transaction marked as the next one awaiting replication. You can move forward or backward in the list of replicated transactions. (All transactions less than or equal to that transaction are marked as distributed.) The required parameters xactid and xact_seqno can be obtained by using sp_repltrans or sp_replcmds.
sp_removedbreplication	This stored procedure is executed at the Publisher on the publication database or at the Subscriber on the subscription database. The procedure removes all replication objects from the database in which it is executed, but it does not remove objects from other databases, such as the distribution database.
sp_replcmds	Returns the commands for transactions marked for replication. This stored procedure is executed at the Publisher on the publication database.
sp_replcounters	Returns replication statistics about latency, throughput, and transaction count for each published database. This stored procedure is executed at the Publisher on any database.
sp_repltrans	Returns a result set of all the transactions in the publication database transaction log that are marked for replication but have not been marked as distributed. This stored procedure is executed at the Publisher on a publication database.