SQL Server Replication Configuration – Part 2

Hello Folks,

You might have seen my last article-post which was about the Creation of a Publisher and Distributor. If you want to refer it again, then just click on the link;

In this article-post, I would like to show you how to create a Snapshot Publication.

Creating a Snapshot Publication:

If you would have followed-up my last article-post, then the Distributor is being set-up for the Server and now we can create our Publication.

NOTE: Publication is a collection of articles, where an article is an item to be published. The Article can be a table, view, indexed view, a user-defined function, or a Stored Procedure or its execution.

Now, follow the steps to create a Publication:

Step 1: Connect to Publisher using SQL Server Management Studio. Expand the Replication folder and then Right-Click the Local Publication folder:


Step 2: “New Publication Wizard” window opens up, click the Next button.


Step 3: “Publication Database” – Choose the database that contains the data or objects you want to publish.


Step 4: Choose the Publication type that best supports the requirement of your application:


Here, we have chosen Snapshot Publication. In this, the Publisher sends a snapshot of the published data to Subscribers at Scheduled interval.

Step 5: Expand each object tree and select the articles you wish to replicate.

  • If you wish to replicate tables, then expand the table tree and select the individual tables.
  • We can also have the option to select only a subset of columns.
  • If the table doesn’t have a Primary Key, then we will be unable to replicate it in a transactional publication.
  • While Snapshot and Merge replication allow you to replicate tables without a Primary-Key.


Step 6: Now set the Article Properties on “Set Properties of Highlighted Table Article”. Then Click the Next button.


Step 7: Article Issues – Tables referenced by views are required.

  • SQL Server requires that all tables referenced by published views and indexed views be available at the Subscriber.
  • If the referenced tables are not published as articles in the publication, they must be created at the Subscriber manually.
  • Here, the following views and indexed views are published in the publication:-
[dbo].[vStud] [dbo].[vStudents]


Step 8: Filter Table Rows – Add filters to exclude unwanted rows from published tables. Click the Add button.


Step 9: Now the Add filter window shows-up. So we can add a filter statement here:



This would ensure that the Subsciber only receives data and changes from Std when the SID equals to 1.

Step 10: Now click the Next button.


Step 11: Snapshot Agent: Specify when to run the Snapshot agent. Subscriptions are initialized with a snapshot of publication schema and data. The Snapshot agent creates the Snapshot. So there are two options:

  • Create a Snapshot immediately and keep the snapshot available to initialize the subscription. Every replicated change that occurs in the publication is not only replicated to the subscriber, but also added to the snapshot files.
  • Schedule the Snapshot Agent to run at the following times. This is a great option, if we have to add a lot of Snapshots frequently, but it also adds up a constant load to our Publisher.
  • Changes which are not in the Snapshot have to be stored in the Distribution agent, which may mean extra storage requirements in the Distributor.


Step 12: Agent Security: This option allows you to select the security context you wish your replication agents to run under. By default, SQL Server runs the replication agents under the same account under which the SQL Server accounts runs.


Step 13: Now, you have to specify the domain or machine account under which the Snapshot Agent process will run:


Step 14: Now click on the Next;


Step 15: Now select the items that you want to see:


Step 16: Now you have to specify the properties of the Script file to create at the end of the wizard:


Step 17: Once you have given the Publication Name, click finish to create it.


Step 18: After turning all the files to a Success, clicks the Close button.


Well this was all about “How to Create Snapshot Publication”.

And also comments on this!!



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 →

4 Comments on “SQL Server Replication Configuration – Part 2”

  1. Hello geeks!

    I have a problem with the account under which the Distributor Agent runs. I created a Snapshot Replication in one server (A) and configured a push subscription in a different server (B). I define a Windows service account to run the Distribution Agent in A, with minimum privileges and assigned to it permissions as MSDN suggest here http://msdn.microsoft.com/en-us/library/ms151868.aspx.

    However when I execute the job it doesn’t finish. It returns the following lines (I put the last two):

    2011-09-12 19:54:33.815 Connecting to Distributor ‘Server A’
    2011-09-12 19:54:33.855 Agent message code 20084. The process could not connect to Distributor ‘Server A’.

    I tried the same with the Administrator account and it works perfectly!!! But I don’t want to use the Admin account to run the Distributor Agent. What can I do? What should I check?

    Thanks in advance!

  2. Thanks for your reply Rakesh

    The account is registered in the PAL, has read & execute permission on the Share folder, is db_owner of the distribution db and the replication db as well.

  3. Hello,

    I am looking to implement transactional replication to scale out my database and planning to use 1 master with read-write and 2 or 3 subscribers as read only node.

    How do i configure my web applications/web servers to connect to read only subscriber nodes? Basically from application point of view how to split read load to the subscribers databases.

    Any pointers would be really helpful.


Leave a Reply

Your email address will not be published.