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

How to Configure Transaction Replication with step by step Demo of Transaction Replication

Source: Publisher

Destination: Subscriber

Configure Distribution:

Here you can see Snapshot folder should be share other wise you will get error

You can see distribution database  created after configured Distribution

You can see the sample table as see below

you can see sample view,Stored Procedures as see below

Test login as show below

Configure Publication

In transaction Replication Primary Key is important on tables we cant replicate table without primary key

You can see employee table do not have primary key so it shows error mark as below

After we have added primary key into existing table as see below

Now you can see primary key added so red mark of error is hidden

Publisher Name as see below

We configured Publication

Publisher database info as see below on publisher 

Now we can see subscriber as see below

Configure Subscription:

We need to choose push/pull subscriptions type

table records are not created as see below

table info @ publisher 

Jobs info in Publisher as see below

Job info @ subscriber as see below

It shows error it causes data not be replicated on subscriber

error says some tables missing reference key

we can see subscriber view synchronization status

you can see job history as see below

we can also see through replication monitor and we can reinitialize subscription as see below

reinitialize  subscription

we need to drop some view/Sp which need reference

Now we need to start sync on subscriber immediately or we need to wait for schedule

now we can modify some records and see how it replicate

Now modified on Publisher

you can see modified records as see below publisher

still in subscriber it shows old records on subscriber

after log reader runs you can see 2 transactions 

you can see records reflected on subscriber

that is it 

Happy blogging and reading