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:-

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!!

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