Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 29 June 2017

How to Configure/Step by Step Peer to Peer Replication in SQL Server

How to Configure/Step by Step Peer to Peer Replication in SQL Server 


  • Peer to Peer Replication configuration is  Bi Directional replication(read operations)
  • Peer to Peer Replication publisher and subscriber should be configured distribution on all node
  • Peer to Peer Replication can not configure Standard edition as node that is we can't configure peer to peer replication Enterprise to standard.


Source  Server is enterprise edition on SQL Server




Here source server TestP2P database is have table see below

Now we need to take Backup of database


Destination database is having Standard Edition



DB restored to successfully


TestP2P is now showing tables



Now we are going to start on source server to create publication


Here we choose database for peer to peer replication


choose publication type as peer to peer replication


Here also table should have primary key here below shows red mark as it did not have primary key


so now we need to create add primary key to existing table

or

we can add primary key on GUI (Graphical User Interface)


after created Table we have now articles is showing


we need to give proxy account for agent security



create publication


give the publication name



now publication success



Now we need to configure distribution on all server (it should be mandatory for all configuring server s for configured distribution)


Here it gives snapshot folder it should be share option


we configured share path


Configured distribution


Now we configure peer to peer

choose publication

configured


now we add peer subscriber


you can see we can't configure standard edition on peer to peer


now we are going to configure Destination SQL (Enterprise edition) but before that we need to restore database from source




restored success


Here you can see destination(enterprise edition) has the restored db

so now we resumed from we stop so we need to configure subscriber


here it gives error as destination also need to configured on distribution


After configured distribution  now we can configure destination

configure database


you can see 101 as originator  ID now it will win in conflict from SQL 2012 onward


here you can see green arrow mark (source)  and configured subscriber


log reader agent security


we can initialize manual or we can do now

but our case we already restored so we choose first option



after give finish it went success


Here you can see on source (Enterprise edition) publication is created


table on destination


now we do some modification


here you can see source it reflected


here source view log reader agent status





now we do some modification on source


it reflected on destination



That is it