As I discussed in my first part of replication SQL Server 2008 Replication: High Availability Solution Part One, replication is the process of moving data and database schema changes from one database to another server, either on the same server in different instances or on different server databases.

In this article, I will discuss setting up transactional replication on my machine on different instances. Basically transactional replication involves transferring data and database schema changes from publisher to the subscriber with the use of a snapshot agent, a log reader agent, and a distribution agent.

As we discussed in the first part, replication involves a distributor, a subscriber, a publisher, and articles. Each components need to be configured to make replication function properly.

Basic transactional replication architecture is shown in the figure below:

Configuring a Distributor:

A Distributor is a server that stores and delivers the replicated transactions of data. A single server can be a publisher, distributor, and a subscriber. In a production system, it is recommended to use separate servers to fulfil the roles of subscriber, distributor, and publisher. In order to reduce the load of data transaction on the publisher, it is recommended to use a separate distributor server. The Distributor server can be configured using SQL scripts and SQL Server Management Studio(SSMS). I am using SSMS to configure distributor server. In this example, I have one server that acts as distributor, publisher, and subscriber.

1. Connect to the SQL Server Instance.

2. Navigate to the Replication folder,

3. Right click on this folder and select Configure Distribution as shown below:

Fig 2: Configure Distributor

4. After Clicking Configure Distribution… Configure Distribution Wizard comes, and then Click on Next as shown below:

Fig 3: Configure Distribution Wizard

5. Following Window will come once Next button is clicked. Select the Distributor server that you want to use. You can select another server selecting second option, if you do not want to use the same server as a distributor. In my case, I have selected my PC as a distributor as shown below:

Fig 4: Selecting Distributor Server

6. A Snapshot folder should then be created where the snapshot of the database should be kept in order for distribution and merge agents to access the publication database. It is shown in the figure below. The network path should be used if the snapshot folder is on another server.

Fig 5: Creating Snapshot Folder

7. Click Next and the next window comes where Distribution Database configuration can be performed such as giving the name of the database, where to store the distribution data file and log file. It is shown in the figure below. Once these details are filled, click next as shown in the figure.

Fig 6: Configure Distribution Database

8. Once we click next, we can add Publisher database as shown in the screenshot below:-

Fig 7: Adding Publisher

9. In my case, I have one distributor, publisher and subscriber so I have selected ABI-PC, and the distribution database created earlier.

10. Once you click Next, following window comes where you can select Configure Distribution option as shown in the figure below. You can also choose to generate the script for configuring distributor and adding publisher.

Fig 8: Configure Distributor

11. Click Next and then click Finish to complete the distributor configuration steps as shown in the figure below:

Fig 9: Configure Distributor

12. You are then presented with the following screen:-

Fig 10: Configuring Distributor

13. Once the configuring process is completed, following window comes where it shows that distributor is completed successfully.

Fig 11: Distributor Configuration Status

14. Click Close to get out of the Distributor Configuration Wizard.

Creating Publication:

Now the server is ready for distributor and publisher databases. We can now define and configure the publication for this replication setup.

A Publication is a collection of set of articles such as tables, stored procedures, views, and user defined functions.

1. Connect to the SQL Server Instance and then click on Replication folder and then right click on Local Publications and then click on New Publication… as shown in the figure below:

Fig 12: Configuring Publication

2. After you click on New Publication as shown in the figure above, select Publication database from the database list from the New Publication Wizard. In my case, I have selected Adventure Works database as a Publisher database as shown in the figure below:

Fig 13: Configuring Publication Database

3. Click Next. You can now select Publication type from the Publication Type Window. Since this is a transactional replication demo and I have selected transactional publication as shown in the figure below.

Fig 14: Selecting Replication Type

4. After clicking Next, you can select the Article to publish. You can select all articles or one article to publish. Once you select an article to publish, you can select the properties of all or single article of that table. In my case, I have select one table (Address table) as publication. It is shown below:

Fig 15: Selecting Article for Publication

5. Click on Article Properties. There are two options either to select only the highlighted article to set the properties or select all articles of the entire table. In my case, I have selected only the highlighted article(Address Table)

Fig 16: Selecting Article Properties

6. Once you click Set Properties of Highlighted Table Article, you can set the properties as shown in the figure below:

Fig 17: Article Properties

7. Click OK button to close this article properties window to go back to Articles Window. Click Next as shown below to go to another window where you can filter the table.

Fig 18: Article Properties

8. Once you set the properties of the article, Click Next button to go to the Filter Table Rows window as shown below:

Fig 19: Filter Table Rows in the Publication

If you do not want to filter the data in your publication, simply click Next. If you want to filter the data in your publication, click Add as so that you can get the Add Filter window where you can filter the data in your publication. OK button on Add Filter Window will be grayed out and it will be active once you add the column to filter in the filter statement and then Click OK to close this window to back to Filter Table Rows window, shown in the figure below:

Fig 20: Adding Columns to Filter

9. Once the data is being filtered, click Next on Filter Table Rows window to go to next window where you can schedule snapshot agent to run. There are two ways to run the snapshot agent:

a. Run immediately

b. Schedule to run on scheduled times.

In my example, I am selecting snapshot agent to run immediately and keep the snapshot data available to initialize subscriptions. If you want to change the snapshot agent to run on scheduled timings, click on Change and you can give when you want to run the agent. It is shown in the screenshot below:

Fig 21: Snapshot Agent Schedule

10. Once the selection is made on how to schedule the snapshot agent to run, click next to go to Agent Security in New Publication Wizard window:

Fig 22: Agent Security Configuration

In the agent security window, there are two security agents:

• Snapshot Agent, and

• Log Reader agent.

Agent security has two security agents to be configured, Snapshot and Log Reader agents. Snapshot agent security dialog box has two options to select security under which Snapshot Agent runs.

• Windows Account: This is an account under which the snapshot agent runs at the distributor.

• SQL Server Agent Service Account: This is another account under which snapshot agent makes connection to the publisher and this connection can be made by impersonating the windows account.

11. Click on Snapshot Security …. Tab as shown in the picture above and select the option Use the Security Settings from Snapshot Agent (shown in the picture above).

12. Snapshot Agent Security window comes once you click on Security Settings ….as shown below:

Fig 23: Snapshot Agent Security Configuration

We can use the domain account under which the snapshot agent runs, but I have selected the account under which SQL Server Agent Service is running. Also the By impersonating the process account option is selected to connect to the publisher (first option as shown in the figure). We can also use the SQL Server Login to connect to the Publisher.

13. Click OK once this setting is configure to close this window and then Click Next from Agent Security as shown below:

Fig 24: Configuring Snapshot Agent Security

14. Click Next to go to the New Publication Wizard again and then click Next again:

Fig 25: Publication Wizard to create Publication

15. Once Next button is clicked, following window comes where we can give the name of the Publication. I gave the publication name as: Abi_Pub_Test as shown in the figure below:

Fig 26: New Publication Window to Give the Publication Name

16. Click Finish Button to complete creating the publication for the replication as shown in the figure below:

Fig 27: Publication Created successfully

17. Click Close to close the New Publication Wizard Window.

Creating the Subscription:

Now the publication is configured and is ready for the data to be published or delivered to the subscriber. Data transactions can be made to the subscribers either using pull subscription or push subscription. In replication technology, these are the two types of subscriptions used.

Push Subscription: In push subscription, distributor agent runs on the distributor. Push subscription is created on the publisher and data will be delivered to the subscriber without the notice or request being sent by the subscriber.

Pull Subscription: In pull subscription, distributor agent runs on the subscriber and data will be pulled to the subscriber from the publisher.

How to create Subscription:

1. Connect to the SQL Server Instance and then Click on Replication folder and then Right Click on Local Subscriptions and then Click on New Subscriptions… as shown in the figure below:

Fig 28: Configuring Subscription for Publication

2. After you Click New Subscriptions as shown above, in the New Subscription window, publications, database and the publisher will be available as shown in the figure below, from which we can select for the setting up subscription.

Fig 29: Selecting Publication for New Subscription

3. Click Next to go to the Distribution Agent Location window where we can select to use either Push or Pull Subscriptions for this replication. I am going to select Push Subscriptions in which the distributor agent runs on my distributor(ABI-PC) as shown in the figure below:

Fig 30: Selecting Subscriptions Type

4. Click Next to select the Subscriber. Since my publisher, distributor and subscriber are on the same server, but we can add the subscriber by clicking Add Subscriber and then Add SQL Server Subscriber … as shown in the figure below:

Fig 31: Selecting Subscriber and Subscription Database

5. Click Next after selecting the subscriber and subscription database, where we can select the Distribution Agent and its security configuration as shown in the figure below:

Fig 32: Distribution Agent Security Agent Configuration

6. Click on …. Tab under Subscription Properties tab on Distribution Agent Security Window of New Subscription Wizard, and the following window comes where we can configure the security for Distribution Agent.

Fig 33: Distribution Agent Security Settings

We can use the domain account under which the distributor agent runs, but I have selected the account under which SQL Server Agent Service is running. Also the By impersonating the process account option is selected to connect to the Subscriber and distributor. We can also use the SQL Server Login to connect to the Distributor and Subscriber.

7. Click OK once this setting is configure to close this window and then Click Next to go to Distribution Agent Security as shown below:

Fig 34: Completing Distribution Agent Security Setup

8. Click Next to go to the Synchronization Schedule for this subscription as shown in the figure below:

Here we can find distribution agent to run continuously, on demand or with custom defined schedule. These options can be selected from the drop down menu, which is under Agent Schedule tab. I have selected to run continuously, which is shown in the figure below:-

Fig 35: Distribution Agent Schedule

9. Click Next to go to the Subscription Initialization page where we can set the properties to initialize the subscription. There are two ways that we can initialize subscription: Immediately and At first Synchronization. These properties can be set by selecting them from the drop down menu under Initialize When tab as shown in the figure below.

Fig 36: Initializing Subscription for this replication.

10. Click Next to Go to the Wizard Actions from which we can generate the script for creating the Subscription as well. Here we can select create subscription as we as Generate Script to create subscription as shown in the figure below:

Fig 37: Selecting options from Wizard Actions

11. Click Next to go to the window where we can verify all the choices made for setting up the subscription. In this window all the details will be given as shown in the figure below:

Fig 38: Details of the Subscription Created

12. Click Finish to complete the Subscription Set up for the publication I have created and the subscription is created successfully shown below:

Fig 39: Subscription Created Successfully

13. Click Close button to close the New Subscription Wizard to complete the subscription setup.

Replication Validation:

Validation of data transfer from a publisher to a subscriber is very important in the replication process. SQL Server Replication engine provides a mechanism to validate the data synchronization between publisher and subscriber. There are two stored procedures used to validate the data transfer between publisher and subscriber. These stored procedures are:

1. sp_publiciation_validation, and

2. sp_article_validation

These are the system stored procedures that can be used to validate the replication. These stored procedures are used to validate replicated data using two methods: Row Count and Row Count and Binary Checksum.

Row Count Validation:

Use AdventureWorks

Go

EXEC sp_publication_validation @publication = ‘Abi_Pub_Test’,

@rowcount_only = 1,

@full_or_fast = 2

We can also use Management Studio to validate the replication.

Go to the Replication Folder, Local Publication, and then Select the name of the Publication created, Right Click on Publication, and then Click on Validate Subscriptions …. shown in the figure below:

Fig 40: Validating Subscriptions

Choose Validation Subscriptions, where we can click on Validation Options and from which we can select the option we want on how to validate the subscriptions, shown in the figure below: Click OK to close Validation Subscriptions window.

Fig 41: Validating Subscriptions

Replication Monitoring:

Replication can be monitored using SSMS with the Replication Monitor and using stored procedures. Replication monitor gives the overall health of the replication set up on the server and the detailed information on the status of the publications and subscriptions that are created for the replication.

To launch replication monitor, go to the Replication folder and then right click on Replication Folder and then click on Launch Replication Monitor and the replication monitor shows the following details of the replication status:

Fig 42: Replication Monitor for Monitoring Replication Topology

Conclusion:

Transactional replication topology is mostly used in most of the organizations while using the replication technology to synchronize the databases between two different servers in two geographically different locations. This is just an overview of transactional replication and the steps to set up the transactional replication between two databases. There are several steps to be kept in mind while setting up the transactional replication, therefore, proper planning and careful set up is required to make it working smoothly.