Tuesday, 18 April 2017

How to create/configure replication in SQL Server and how to monitor Replication in SQL Server?

Replication configuration is very easy. Any one can do it in 10 Minutes

Please follow below Step by Step complete Replication configuration 

We will configure replication between following servers-

1.       INHYIZLP10744 < Publisher > Server 

2.       INHYIZLP10744\INSTANCE1 <Subscriber> Server

3.       Database name is – Replication_Test_DB (Already existing on both server)

4.       Table <Article> name – Table_1 (Table must have Primary Key)

Open SSMS and connect to your Publisher Server <INHYIZLP10744> and open Replication 

Right click on the Local Replication and select New Publication, Here we can create publication- Click Next à Select Distributor Server ( We are selecting same Publisher server for distributor server) à Next 





Select Database

Select Replication Type / publication type-

Select Articles <table, View, SP etc > - we can change Article properties also-

We can put filter or condition on Article. It is like where condition in query.


After next click on security table and provide credential-


Provide Publication Name


You publication has been created in SSMS. Non Right Click on Created publication and click on New Subscription link -

Select you Publisher

Select the Agent location <Push or Pull>

Click on ADD Subscriber and select Select subscriber server

On Our demo INSATANCE1 is our subscriber --> Connect

Select database - <Replcation_test_DB> - We have created same database in subscriber OR you have to take full backup and restore it on Subscriber server<Instance1>

Click on below button for connection for Distributor and subscriber

Credential Window --> OK

Click Next (here you can schedule synchronisation agent )

Click Next (here you can schedule initial/first subscription )



Next (Your subscription created)

You can verify in you SSMS on Publisher Server

Now  All DML(Inster/update/delete) operation will be sync between  publisher server<INHYIZLP10744> to Subscriber Server<INHYIZLP10744\INSTANCE1> for Article (Table_1)

Now you can also monitor Replication, Just Right click on Publication and click on Lunch Replication Monider the below window will open and can see all live agent and history of each article and etc. 

I hope you will like my post. Please let me know and comment below- Jainendra Verma

1 comment:

  1. Hello Verma,
    Thank you for the information on Replication. I wanted to know if it is possible to use it to restore the publisher database from the subscriber database in case of failure. I backup the database once a day and I am looking at the option of replication as a bakup method