Snapshot Replication Publisher

Start the snapshot replication by right clicking Replication, Local Publications, New Publication to start the Wizard.

Select the Publication Database to push \ pull snapshots in the box and click Next to continue.

Select Snapshot publication from the Publication Type box and click Next.

Select the Articles to include in the snapshots taken. Only Tables have been selected here, but Stored Procs, Views, Indexed Views, and User Defined Functions may also be selected. Click Next.

The Filter Table Rows step allows you to filter certain data from individual tables using a Where clause. Clicking Add in this window brings up the Filtering window allowing you to select the Table and the where clause to filter information. For now, click Next to go to the next step.

The Snapshot Agent allows you to set up the schedule of when you want the Snapshot To run on the SQL Server Agent. You can click on change button to change the schedule.

Next set up the Snapshot Agent Security using the local Process account and password.

Make sure that the Create the Publication check box is checked before clicking Next.

Click on Finish to complete the Publication Wizard.

The Publication will display the steps and progress in the last window. Click Close to complete the Publication.

Snapshot Replication Subscription

Right click on the new Publication in the folder named Replications folder under Local Publications and select New Subscriptions. The New Subscription Wizard window will pop open, click Next to continue setup.

In the Publication step of the Subscription set up, select the publication name and click Next.

In the Distribution Agent Location step, select either Push Publications (Option 1) or Pull Publications (Option 2) and click Next. This tutorial uses Pull Subscriptions to retrieve the data to the Subsribing Database.

In the Subscribers window, select the Subscriber Database and select the Subscription Database to receive the data from the Publisher Database. Click Next.

In the Distribution Agent Security step, click on the ellipsis button to enter the Account name to run the Process.

Enter a Domain Name and User Name as the Process Account on the Distribution Database and leave the second Option Group to Connect to the Distributor as By Impersonating the Process Account. Click OK to close the dialog box.

In the Synchronization Schedule Step, you may select Run Continuously, Run on Demand Only, or Define Schedule. Click Next after select the Agent Schedule.

In the Initialize Subscriptions step, under the Initialize When drop down, select Immediately or select At First Synchronization. Click Next to Continue.

In the Wizard Actions dialog, click the Create the Subscription(s) box and click Next.

In the final dialog box step, click Next to get to the Complete the Wizard dialog and click Finish to create the substription. Click Close once the Subscription has been created.

General Information - Snapshot Replication

Filters - In the Filter Table Rows step during Publication set up there is an Option to set up a Filter on the rows to transfer to a Subscriber. After a Publication is set up, by Right Clicking the Publication and selecting Properties, you can open the Page for Filters in the Publication Dialog box and set a Filter after initializing a Publication. For example, only certain columns might be pushed to \ pulled by the subscriber. Also, the addition of a Where clause to limit which rows are pulled or pushed to the subscriber may be added in the Filter Statement in the dialog box.

User Defined Functions - If a User Defined Function is used in a Table being Replicated to a Subscriber, that User Defined Function must be included in the Publication to the Subsciber. If it is not included, the job will Fail when run by the SQL Agent.

Publication table with Computed column using User Defined Function.

Table Designer View with the User Defined Function name included in Computed Column Specification

Publication Dialog to Add the User Defined Functions in the Articles page to the Publication to the Subscriber