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