SQL Server Snapshot Replication – Part 1

In this blog I will focus on snapshot replication and how to configure and implement snapshot replication using the GUI method.

The SQL Server products that support snapshot replication are as below.

SQL Server Workgroup Editionà Subscriber only

SQL Server Enterprise Edition àEnabled

SQL Server Standard Edition àEnabled

SQL Server Web Edition àSubscriber only

SQL Server Express Editionà Subscriber only

After reading of this blog, you will be able to do the following using the GUI:\

• Configure snapshot replication.
• Configure publication.
• Configure both push and pull subscriptions.
• Use Windows Synchronization Manager (WSM), or Sync Center on Vista, to pull subscriptions.

I will divide this blog into parts. In the first part, we will see on how to configuring the Publication.

Configuring Publication 

To begin, open the Microsoft SQL Server Management Studio (SSMS). I have already configured the publication, so it is listed under the Local Publications object, as you can see in Figure 1. Since you haven’t done so yet, your local publications list will be blank in the Object Explorer, although the Publisher server has been configured. If you expand the Jobs object under the SQL Server Agent, you will notice that the Snapshot Agent is not in the list of agents.

This makes sense, since we have not yet configured snapshot replication.

Figure 1. Viewing local publications in the SSMS

Right-click the Local Publications object under the Replication folder in the left pane, and select New Publication from the menu. When the wizard for configuring new publications opens, click Next.

This wizard guides you through the creation of the publication. You will see the page in Figure 2. Choose the database that contains the data or objects you want to publish, and click Next.

Figure 2. Selecting the database whose data and objects you want to publish

Shown in Figure 3, the wizard lists the different types of publications, such as snapshot publication, transactional publication, transactional publication with updatable  subscriptions, and merge publication. At the bottom of the page, it describes the different publication types. Select Snapshot publication, and click Next.

Figure 3. Selecting snapshot publication

The next page displays the list of objects in the database that you can publish as articles. This can be seen in Figure 4. Select the tables that you want to publish, and click next.

Figure 4. Selecting the articles that you want to publish

Shown in Figure 5, the wizard asks whether you want to filter the rows horizontally for the articles. You can click Add to choose horizontal partitioning; otherwise click Next.

Figure 5. Choosing whether you want to exclude unwanted rows from published tables

We will ignore horizontal filtering in this page for now. Click Next, and you will see the Snapshot Agent page shown in Figure 6. As you can see, I have specified that the Snapshot Agent should create a snapshot for the publications immediately, and also that it should run at the scheduled times.

Figure 6. Specifying the tasks and the scheduling of the Snapshot Agent

To set up the Snapshot Agent’s schedule, click the Change button, as shown in Figure 6. Doing so will display the page shown in Figure 7. In this example, I have scheduled the Snapshot Agent to run once every week at midnight on Monday—the wizard summarizes the schedule at the bottom of the screen.

Figure 7. Scheduling the frequency and the duration of the execution of the Snapshot Agent

Once you have set the schedule, click OK to return to the Snapshot Agent page (shown earlier in Figure 6), and click Next to move on to the Agent Security page, shown in Figure 8.

Figure 8. The security account under which the Snapshot Agent will run

You need to specify the account under which the Snapshot Agent will run, and to do this you need to specify the security settings of the agent. This can be done by clicking the Security Settings button. Figure 9 shows the options you can choose to set Snapshot Agent security. Although it is possible to connect using the SQL Server Agent’s services permissions, it is recommended, for security purposes, that you configure the connection to the Distributor server using a domain account, such as the SQL Server Agent service account. This setting is shown in Figure 9.

Figure 9. Specifying Snapshot Agent security settings

The Snapshot Agent resides on the Distributor server and also establishes a connection to the Publisher server, so you need to have a second connection setting for the Snapshot Agent. As you can see in the lower part of the page in Figure 9, the Snapshot Agent connects to the Publisher server by impersonating the process account, which is essentially the Windows account.

Once you have set the security settings, click OK to return to the Agent Security page of the wizard (shown earlier in Figure 8), and click Next. You’ll see the Wizard Actions page, shown in Figure 10.

Figure 10. Choosing to create a publication

Select the Create the publication check box option, as shown in the figure. You can also choose to generate a script file for the publication. If you set up the publication in a test environment, you can then use this script to load it on a production environment by selecting File ➤Open ➤ File in SSMS.

When you’re done, click Next to go to the last page of the wizard, where you can give a name to the publication, as shown in Figure 11

Figure 11. Setting a name for the publication

The wizard asks you to verify the list of tables that are being published, the account under which the Snapshot Agent will run, and the type of replication that you want to publish. Ensure that the right articles for publication, the domain account for the Snapshot Agent, and the type of publication are correct before you click the Finish button. Otherwise, you will have to drop

the publication and re-create it. If you need to change any of the properties, you can use the Back button to return to previous pages in the wizard. After checking that you have made the right selections, click the Finish button. The next wizard page you will see confirms the success in creating the publication, as shown in Figure 12.

As you can see, the New Publication Wizard lists its success in creating the publication, the number of articles created for the publication, and its success in executing the Snapshot Agent. In case you want to interrupt the wizard while the publication and articles are being created, you can do so by clicking the Stop button. The Stop button is disabled once the process is

Successful, as shown in Figure 12. Should any of the actions listed in Figure 12 fail, you can click the Report button to check

whether there are any error messages. Figure 13 shows the report for the successful creation of the publication.

Figure 12. The successful creation of the publication and the articles

Figure 13. The report for the publications created can be saved in a file.

In Next blog, we will see configuring of a Subscription. Stay connected

About Author:

Bhanu Prakash is budding technology geek, who actively contributes to Systems Plus with his creativity and research on technology. He can be contacted at bhanu.p@spluspl.com