SQL Server Replication - Configuring Peer-to-Peer Replication | SansSQL

Tuesday, November 19, 2013

SQL Server Replication - Configuring Peer-to-Peer Replication

The first step in configuring the Peer-to-Peer replication is to configure the distribution on all the involved SQL Server instances.
To configure the distributor, follow the steps posted here.
Once the distributor is configured on all the involved instances, we need to configure the transactional publication.
To configure the publisher, follow the steps posted here.
Once the publisher is configured, we need to enable the peer-to-peer replication for this publisher.
Go to the "Properties" of the publication --> "Subscription Options" --> Set the property "Allow peer-to-peer subscriptions" to "True" and click "ok"

Once this option is enabled, an new option is now available for creating the peers to this publication.
Right Click on the publication and choose "Configure Peer-To_Peer Topology"


Click "Next" in the welcome screen

This screen allows you to choose the publisher server and publication.

Right Click on the white space and choose "Add a new peer node"

This will allow to connect and choose the peer database 




If you mouse over on a peer node, it will give the information relating to that node.
Once the information is verified, click "Next"


You can configure the Log reader agent security in this screen.




This screen allows to set the distribution agent security. 
If the option "Use the first peer's security settings for all other peers" is checked then the same security setting will be applied to the other peer nodes.

When configuring the peer-to-peer replication, the process needs to know how the database on the new peer is initialized. This helps in proper syncing of the changes.



Once the configuration is completed, you can see the publication and subscription on the involved SQL Server instances.

No comments:

Post a Comment