Setting up Transactional Replication in SQL Server 2008 R2.

up Transactional Replication in SQL Server 2008 R2.

Replication is one of the High Availability features
available in SQL Server. Transactional Replication is used when DML or DDL
schema changes performed on an object of a database on one server needs to be
reflected on the database residing on another server. This change happens
almost in real time (i.e. within seconds). In this article, I will demonstrate the step
by step approach to configuring transactional replication in SQL Server 2008 R2.

Scenario: An Address table which belongs to the Person
schema in the Adventureworks Database is replicated to the
Adventureworks_Replication database residing on the same server. The Adventureworks_Replication
database acts as a subscriber. The subscriber is normally present on a 
separate database server.

Before we start with the configuration, we need to understand three important

1.       Publisher
2.       Subscriber
3.       Distributor

Let’s discuss each these in detail.


The Publisher can be referred to as a database on
which the DML or DDL schema changes are going to be performed.


The Subscriberis the  database which
is going to receive the DML as well as DDL schema changes which are performed
on the publisher. The subscriber database normally resides on a different server
in another location.


A database which contains all the
Replication commands. Whenever any DML or DDL schema changes are performed on
the publisher, the corresponding commands generated by  SQL Server are
stored in the Distribution database. This database can reside on the same
server as the publisher, but it is always recommended to keep it on a
separate server for better performance. Normally, I have observed that if you
keep the distributoion database on the same machine as that of the publisher database and
if there are many publishers then it always has an impact on the performance of the
system. This is because for each publisher, one distrib.exe file gets created. 

Let us now begin with the Configuring of
the Transactional Replication.

There are 3 steps involved for Configuring
the Transactional Replication:

1.       Configuring
the Distribution Database.

2.       Creating
the publisher.

3.       Creating
the subscriber.

the Distribution Database

1.       Connect
to the Microsoft SQL Server 2008 R2 Management Studio.

2.        Right
Click on the Replication node and Select Configure Distribution as shown
in the screen capture below:

3.       A
new window appears on the screen as shown in the screen capture below:

4.       Click  the Next> button and a new window
appears on the screen as shown in the screen capture below:

5.       As
you can see in the above screen capture, it gives the user two choices. The
first choice says that whether the server on which the Replication will be
configured will be Hosting the distribution database. The second choice asks
the user whether some other server will be Hosting the distribution database.
The user can select any one of the either choices are per his/her requirements.
I decide to use the First option, i.e. the server on which the Replication is
configured will itself be holding the distribution database. Then Click on the
Next> button as shown in the screen capture above.

6.       A
new window appears as shown in the screen capture below:

7.       Select
the first option, i.e. Yes, configure the SQL Server Agent service to start
automatically and click on the Next> button as shown in the screen capture

8.       A
new window appears on the screen as shown in the screen capture below:

As you can see in the above
screen capture, you are asked where the Snapshot folder should reside on the Server.
Let us first understand what the Snapshot folder exactly is.

The Snapshot Agent prepares
snapshot files containing schema and data of published tables and database
objects, stores the files in the snapshot folder. This folder should never be
placed on the C drive of the server i.e. the drive which is hosting the
Operating System.

Create a folder on any other
drive to hold the Snapshot folder and Click on the Next> button as shown in
the screen capture above.

9.       A
new window appears as shown in the screen capture below:

As you can see
in the above screen capture, it displays information such as what will be the
distribution database name, the location where the data and the log file will
reside. Click on the Next> button as shown in the screen capture above.

10.   A new window appears
as shown in the screen capture below:

11.   Click on the Next>

12.   Click on the Next>
button as shown in the screen capture below:


13.   Click on the Finish
button as shown in the screen capture below:

14.   Once done, a new
database named distribution gets created. In order to confirm it just expand
the System Database node and you shall be able to view the distribution
database, please refer the screen capture below:


Creating the

The following
steps need to be followed while creating the publisher.

1.      Right
Click on Local Publications and select New Publications, please refer the
screen capture below:

2.      Click
on the Next> button as shown in the screen capture below.

3.      Select
the database which is going to act as a publisher. In our case, I select the
AdventureWorks database. Please refer the screen capture below and Click on the
Next> button.


Pages: 1 2 3


76 Responses to “Setting up Transactional Replication in SQL Server 2008 R2.”

  1. Well Done. Simple but yet descriptive.

  2. Thank you for this. Have been looking for a clean explanation of setup.

  3. Nice stuff, Walked me through a full setup for my class model.
    Thanks very much

  4. complete step by step explanation. Good job..

  5. thank you , but you didnot added this subscribtion in another server as you told us at the first of the lesson

  6. Thanks a lot This is very useful for me. Once again say thanks for this kind of assistance.

  7. Thanks a lot for the step-by-step setup!!
    Helped me very much.

  8. Thank you very much, your explanation helped me a lot :) Great job :)

  9. Thanks for your efforts and wonderful explanation!

  10. Thanks a lot. Good job.

  11. Christian Eintveit Møller Reply February 25, 2012 at 10:43 am

    Nice, helped me very much.


  12. Thank for your Article!!!!

    helped me Very Much

  13. this is really helpfull

  14. Could you please elaborate why you chose to ignore the security recommendations to choose separate accounts other than the service account?

  15. excellent job very very thanful to you for this tutorial sir .

    thanks a lot

  16. i could like to have more tutorial

  17. owesom is very easy to understand…thanks a lot

  18. Nice one..really helpful

  19. The publisher doesn’t/shouldn’t care about the exact location of the subscriber’s database files, right? For example, once the subscriber has been set up and replication has been going on for some time, if I move the database files underlying the subscriber database, will that break the transaction replication?

  20. Very Nice Job; Thanks a lot. I wonder if this procedure can be done with SQL Express? I know it’s not but maybe there is a way to tweak the application.

  21. Great tutorial, got me started real quick. Thanks Satnam!

  22. Excellent tutorial. Thank you very much.

  23. Need to Know more on “Always On High Availability ” Feature. its new to task to me in SQL Server 2012.

  24. nice article

  25. Thanks. it is good.

    can you share about clustering in SQL Server 2008 R2

  26. Superb explanation for each steps. Good work!!

  27. thanks its useful

  28. owesom is very useful to me…thanks a lot

  29. It throws me an error, when configuring the distributor.

    It says, ‘ SQL server could not be configure rajan\sqlexpress’ as a Distributor.

    So kindly help me, what mistake am i doing. And why it is coming for me.

    Exception as

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  30. Thanks its very Usefull

  31. Thanks. Its a very useful for me. Whatever i missed I recover from ur article. Thanks Again.

  32. hi , Thanks for very useful guide.
    i have question about this.
    is it possible my publisher server hosted out of my network and doesn’t have valid IP?

  33. hi…

    but i am use subscribtion in another server,in this case work only local System not network.

    can hlep me how to subscribtion in another server done Succeffuly.

  34. Hi,

    How can we add articles to existing publishers? is it with only Sp_addarticle or any GUI way? can you please reply?

  35. What happens if you add another publisher to the same database?

    Does SQL server try to add a new identity column and then bomb out?

    Must you back up the Publisher database and restore the database to the subscriber before creating a new Publisher?

    Or would you simply create a new Publisher and let the snap shots update the Subscriber database.

  36. Thank you so much for giving us very good knowledge…….

  37. Good work. Great Thanks.

  38. Thanks. it is good.

  39. Manikanda Perumal M Reply December 19, 2012 at 9:29 am

    Thanks . its very Nice

  40. Excellent Guidance !!! Thanks.

  41. The way of presentation is good..Keep it up.
    Thanks for ur time.

  42. Great Explanation, thank you so much


  43. This is very clear concept

  44. Hi,

    Need more details with steps…

    I have an two Active/Active SQL database servers 2008 with STD edition in my Lab server. Wanted to configure the Trasactional Replication and wanted to replicate the data from server A to Server B and Server B to Server A….i have tried in my lab server but facing some chanllanges… while replicatiing the data from the Server B to server A its givinf me the error through Application…below is the details.

    SqlException: Violation of PRIMARY KEY constraint ‘PK_STATE_AUDIT’. Cannot insert duplicate key in object ‘dbo.STATE_AUDIT’. The duplicate key value is (3)

    so need ur assistance to solve it. as i know it is talking about the primary key but dont know how to resolve it and wht to do…new for Database.

    With regards
    Narsingh reddy

    • Hi Narsignh, I need help urgently so kindly try to reply asap.
      As I read your comment U replicated from A to B and then from B to A.
      What steps should I take to replicate back from B to A(main server).?
      And is there any way to just get back the data in B, not the table structure.?

  45. Hi Narsignh,

    //Need more details with steps…//

    The failure cause is very clearly given in error. Please check the destination table’s(STATE_AUDIT’) column in which primary key is defined. Since Primary key column should not have any duplicate values, the reason your application is failing.

    Check for which data you are getting the error and if needed delete the particular row and try again.

    Sajal Bagchi

    • Hello Mr Sajal Bagchi,
      I got the same PK error, But i think truncate table will resolve it, But is there any way to get back just the data not the table structure to the Primary db in replication?
      my scenario is
      1) server A to B
      2) get back Data from B to A(freshly installed).

  46. John Jefferson Rosales Reply June 27, 2013 at 5:51 am

    i follow this instruction but i got this error

    The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the subscriber, publisher and distributor a still active

  47. Transactional Replication
    step by step

  48. What happen if whitout setting distribution?
    Thank’s for your help

  49. super man, helped a lot to understand transactional replication setup easily

  50. is it possible to configure transactional replication on system tables like cdc_CT tables

  51. Do you know if a mirrored database can also be replicated?



  53. Nice Article …Keep posted

  54. Really very nice Article………….

  55. how to test whether this is working or not?

  56. Here is a good understand transactional replication setup easily

    want more go :

  57. thanks buddy it is very helpful for me….

  58. What happens in transactional replication with columns that have autogenerated values (e.g. identity column, guid columns with insert/update using GETID(), datetime columns with insert/update using GETDATE())?

  59. Thanks for taking the time to prepare this. As you can see from the response it has been very usefull and appreciated by the SQL Commmunity.

  60. Great step by step setup instructions.

    Two questions

    #1 — how much is the server speed impacted on both servers using replication?

    #2 — If the replication breaks (server 1 has a hard disk failure), could we use server 2? If not, what steps on server 2 would we need to do to start using server 2?


  61. Thanks for the valuable info provided.

  62. thanx.

    can u plz tell me, how to replicate non-primary key tables..

  63. Great Article. Yet too helpful!!

  64. Many thanks for the great instructions. One thing I ran into is I had to use SSMS from 2008 R2 to make the subscriber work. SSMS 2012 worked fine for distributor and Publisher.

  65. Thank you very much. It explains transactional replication very clearly

  66. Hello! Would you mind if I share your blog with my fcebook group?
    There’s a lott of folks that Ithink would really enjoy your content.
    Please let me know. Many thanks

  67. Hello,

    My 5 cents regarding transactional replication performance:
    - set snapshot always available to false
    - set subscription expiration to 48 hours or less


  68. Eery weekend i ussed to pay a visit this web page, as
    i want enjoyment, since this this website conations genuinely good funny stuff

  69. It’s an remarkable paragraph for all the web visitors; they
    will get advantage from it I am sure.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |