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:

1_SQL_Server_Configuring_the_Replication_Part2

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

2_SQL_Server_Configuring_the_Replication_Part2

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

3_SQL_Server_Configuring_the_Replication_Part2

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

4_SQL_Server_Configuring_the_Replication_Part2

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.

5_SQL_Server_Configuring_the_Replication_Part2

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

6_SQL_Server_Configuring_the_Replication_Part2

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]

7_SQL_Server_Configuring_the_Replication_Part2

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

8_SQL_Server_Configuring_the_Replication_Part2

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

9_SQL_Server_Configuring_the_Replication_Part2

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.

10_SQL_Server_Configuring_the_Replication_Part2

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.

11_SQL_Server_Configuring_the_Replication_Part2

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.

12_SQL_Server_Configuring_the_Replication_Part2

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

13_SQL_Server_Configuring_the_Replication_Part2

Step 14: Now click on the Next;

14_SQL_Server_Configuring_the_Replication_Part2

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

15_SQL_Server_Configuring_the_Replication_Part2

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

16_SQL_Server_Configuring_the_Replication_Part2

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

17_SQL_Server_Configuring_the_Replication_Part2

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

18_SQL_Server_Configuring_the_Replication_Part2

Well this was all about “How to Create Snapshot 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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

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.

    Thanks
    Sen

Leave a Reply

Your email address will not be published. Required fields are marked *