Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SQL Server Snapshot Replication

Using Snapshot Replication in SQL Server 2014

This article discusses the steps to implementing Snapshot Replication between a SQL Server 2014 Database instance and a SQL Server 2012 database from Initializing a Publisher on a SQL 2014 database and a Subscriber on a SQL 2012 database. There is also information regarding Adding a Filter to the Publisher and what to do if a Subscription fails due to a missing User Defined Function.
The Snapshot replicated completely replaces the Subscriber table with the Publisher table with all updates, inserts, deletions..

Snapshot Replication Publisher

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

Publication Wizard

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

Publication Databsae Selector

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

Snapshot Selection Type

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.

Snapshot Article Selection

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.

Snapshot Filter for Tables

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.

Snapshot Agent Schedule

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

Snapshot Security Window

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

Create the New Publication

Click on Finish to complete the Publication Wizard.

Complete the Publication Wizard

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

Publication SQL Agent Status Window

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.

New Subscription Wizard - Snapshot Replication

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

Publication Selection - Snapshot Replication

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.

Distribution Agent Location - Snapshot Replication

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

Subscriber Selection - Snapshot Replication

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

Distribution Agent Security - Snapshot Replication

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.

Distribution Agent Security User Name - Snapshot Replication

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

Synchronization Schedule - Snapshot Replication

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

Subscription Initialization - Snapshot Replication

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

Subscription Wizard Actions - Snapshot Replication

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.

Created Subscription - Snapshot Replication

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.

Add Publication Filter Dialog Box - Snapshot Replication

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.

Locate Failure in Distributor to Subscriber History

Publication table with Computed column using User Defined Function.

Computed Variable Not Included in Publication

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

User Defined Function in Computed Column Specification

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

Publication Properties to Add User Defined Function for Subscriber