Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Saturday, 17 June 2017

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