SQL Server Replication Configuration – Part 1

Hello Folks,

I think you would have a brief idea of what the Replication is all about, and what are the types of Replication and the different agents equipped with the Replication.

Well, this article-post is mainly being focused on how to create a Publisher and Distributor, first time in the system.

Creating a Publisher and Distributor:

It’s being recommended to configure the Distribution, before creating the first publication, because if there happens to be any issue, it will be quite easy to troubleshoot.

NOTE: You at-least need to use SQL Server 2008 for this.

So as to create your first distributor, go through the steps carefully:

1. Connect to the Server. Here it is “Piyush-PC\SQLSERVER2K8R2”. It will be acting as a publisher/distributor or remote distributor using SQL Server Management Studio.

2. Under the Object Explorer, right-click the Replication folder, and select the “Configure Distribution…” option.

1_SQL_Server_Configuring_the_Replication_Part1

3. Click Next on the Configure Distribution Wizard.

4. Distribution Window will flash-up. The Distributor is the server responsible for storing replication information used during synchronizations. So you can select the Server for this:

2_SQL_Server_Configuring_the_Replication_Part1

5. Snapshot Folder will show up. It is a default location where the snapshot agent deposits the snapshot. If this folder does not have an adequate space or if you want to minimize the I/O contention, then it’s better to use the different location.

To allow Distribution and Merge Agents that run at Subscribers to access the Snapshots of their publications, we must use the network path to refer to the snapshot folder.

3_SQL_Server_Configuring_the_Replication_Part1

6. Distribution Databases dialog opens up. The distribution database stores changes to transactional publications until Subscribers can be updated. It also stores historical information for snapshot and merge publications.

   

4_SQL_Server_Configuring_the_Replication_Part1

7. Now the PUBLISHER. Enable the servers to use this Distributor when they become Publishers. If this is a local publisher/distributor, your publisher will already be selected. If not, then you would have to select the Add button, if you want to enable other publishers to use this distributor.

5_SQL_Server_Configuring_the_Replication_Part1

8. So at the end of the wizard, we can also have Configure Distribution.

6_SQL_Server_Configuring_the_Replication_Part1

9. Click Finish to Complete the Wizard.

10. Click on close.

7_SQL_Server_Configuring_the_Replication_Part1

Your local distributor is now ready to use.

In the next article-post, i will be writing about Creating a Snapshot or Transactional Publication.

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

5 Comments on “SQL Server Replication Configuration – Part 1”

  1. So what access rights would I need to give to the snapshot and replication folders ? Like say I want to have my snapshot folder created on a network or on a disk placed on SAN .

  2. Hi Sachin,

    You dont have to give any access rights to the Snapshot folders. You just need to give the location of your snapshot or replication folder which is there in a network or SAN. As from above, Specify the network path in Step 5.

  3. Piyush,

    Are you sure ? Because the path you have selected is the default local path which ideally will have all the permission for any SQL job.

    Suppose my SQL Server (forget about replication for time being) runs under the Built In Network service and the network service does not have the rights to the network path where I am placing my snapshot folder will it still work ?

    The reason I am asking you is because you have posted a long series of blogs and articles on SQL Server security on sqlservergeeks.com

  4. Snapshot Agent MUST have write permission to the folder and read permission is required for Merge Agent or Distribution agent uses when accessing the folder.

    Windows/domain account must be associated with these agent in order to use a snapshot folder that is located on a remote computer.

    HTH!
    Rakesh

  5. Hi,

    I am using SQL Server 2005 Enterprise Edition, I am trying also to configure Distributor and Publisher between two instances who one of which will be publisher and another as remote distributor.

    When I created distributor in one instance, and declared publisher on other side, the merge replication is not working as reason that user distrinutor_admin failed to login on publisher database instance.

    What kind of problem is this? And how to resolve this problem please?

    Best Regards

    Nikolla

Leave a Reply

Your email address will not be published.