SQLSkills blogspot

Setting up Transactional Replication in SQL Server 2008 R2.

Click Part 1: SQL Server Replication Step by Step
Replication is one of the High Availability features available in SQL Server. Transactional Replication is used when DML or DDL schema changes performed on an object of a database on one server needs to be reflected on the database residing on another server. This change happens almost in real time (i.e. within seconds). In this article, I will demonstrate the step by step approach to configuring transactional replication in SQL Server 2008 R2.

Scenario: An Address table which belongs to the Person schema in the Adventureworks Database is replicated to the Adventureworks_Replication database residing on the same server. The Adventureworks_Replication database acts as a subscriber. The subscriber is normally present on a separate database server.

Before we start with the configuration, we need to understand three important terms:

1.       Publisher
2.       Subscriber
3.       Distributor Database

Let’s discuss each these in detail.

Publisher:

The Publisher can be referred to as a database on which the DML or DDL schema changes are going to be performed.

Subscriber:

The Subscriberis the database which is going to receive the DML as well as DDL schema changes which are performed on the publisher. The subscriber database normally resides on a different server in another location.

Distribution Database:

A database which contains all the Replication commands. Whenever any DML or DDL schema changes are performed on the publisher, the corresponding commands generated by SQL Server are stored in the Distribution database. This database can reside on the same server as the publisher, but it is always recommended to keep it on a separate server for better performance. Normally, I have observed that if you keep the distributoion database on the same machine as that of the publisher database and if there are many publishers then it always has an impact on the performance of the system. This is because for each publisher, one distrib.exe file gets created.

Let us now begin with the Configuring of the Transactional Replication.

There are 3 steps involved for Configuring the Transactional Replication:

1. Configuring the Distribution Database.

2. Creating the publisher.

3. Creating the subscriber.

1.Configuring the Distribution Database.

2.Creating the publisher.

The Publisher can be referred to as a database on which the DML or DDL schema changes are going to be performed.

3.Creating the subscriber.

The Subscribers the database which is going to receive the DML as well as DDL schema changes which are performed on the publisher. The subscriber database normally resides on a different server in another location.

How it works

Transactional replication is implemented by the Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.

The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database. The Distribution Agent moves the initial snapshot jobs and the transactions held in the distribution database tables to Subscribers.

Configuring the Distribution Database

1. Connect to the Microsoft SQL Server 2008 R2 Management Studio.

2. Right Click on the Replication node and Select Configure Distribution as shown in the screen capture below:

3. A new window appears on the screen as shown in the screen capture below:

4. Click the Next> button and a new window appears on the screen as shown in the screen capture below:

5. As you can see in the above screen capture, it gives the user two choices. The first choice says that whether the server on which the Replication will be configured will be Hosting the distribution database. The second choice asks the user whether some other server will be Hosting the distribution database. The user can select any one of the either choices are per his/her requirements. I decide to use the First option, i.e. the server on which the Replication is configured will itself be holding the distribution database. Then Click on the Next> button as shown in the screen capture above.

6. A new window appears as shown in the screen capture below:

7. Select the first option, i.e. Yes, configure the SQL Server Agent service to start automatically and click on the Next> button as shown in the screen capture above.

8. A new window appears on the screen as shown in the screen capture below:

As you can see in the above screen capture, you are asked where the Snapshot folder should reside on the Server. Let us first understand what the Snapshot folder exactly is.

The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder. This folder should never be placed on the C drive of the server i.e. the drive which is hosting the Operating System.

Create a folder on any other drive to hold the Snapshot folder and Click on the Next> button as shown in the screen capture above.

9. A new window appears as shown in the screen capture below:

As you can see in the above screen capture, it displays information such as what will be the distribution database name, the location where the data and the log file will reside. Click on the Next> button as shown in the screen capture above.

10. A new window appears as shown in the screen capture below:

11. Click on the Next> button.

12. Click on the Next> button as shown in the screen capture below:

13. Click on the Finish button as shown in the screen capture below:

14. Once done, a new database named distribution gets created. In order to confirm it just expand the System Database node and you shall be able to view the distribution database, please refer the screen capture below:

Creating the Publisher

The following steps need to be followed while creating the publisher.

1. Right Click on Local Publications and select New Publications, please refer the screen capture below:

2. Click on the Next> button as shown in the screen capture below.

3. Select the database which is going to act as a publisher. In our case, I select the AdventureWorks database. Please refer the screen capture below and Click on the Next> button.

4. Select Transactional Replication from the available publication type and Click on the Next> button as shown in the screen capture below:

5. Select the Objects that you want to publish. In this example, we will select a table named Person which we need to Replicate. Select the table as shown in the screen capture below and Click on the Next> button. One important point to note is that Only those tables can be replicated in Transaction Replication which has a Primary Key column in it.

6. Since there are no filtering conditions, Click on the Next> button as shown in the screen capture below:

7. Check the first radio button as shown in the screen capture below and Click on the Next> button.

8. Click on the Security Settings tab as shown in the screen capture below.

A new window appears as shown in the screen capture below.

Select Run under the SQL Server Agent service account as the account under which the Snapshot Agent process will run and Connect to the publisher By impersonating the process account as shown in the screen capture below and then Click on the OK button.

Click on the Next> button as shown in the screen capture below.

9. Click on the Next> button as shown in the screen capture below.

10. Give a suitable name to the publisher ad Click on the Finish button as shown in the screen capture below.

Creating the Subscriber

Once the publisher is created the next step is to create the subscriber for it.

The following steps needs to be performed for creating the subscriber.

1. Right Click on the publisher created and select New Subscriptions as shown in the screen capture below.

2. Click on the Next> button as shown in the screen capture below.

3. Click on the Next> button as shown in the screen capture below.

4. Click on the Next> button as shown in the screen capture below.

5. As shown in the screen capture below, it asks for the Subscriber name as well as the subscription database. The subscriber database can be created by restoring the publisher database at the start itself or by creating a new database as shown in the screen capture below.

If you have already restored the backup of the database which is a publisher, then the database name will appear in the dropdown as shown in the screen capture below:

If we wan’t to now create the subscriber database then it can be done as follows:

Click on New Database as shown in the screen capture below.

A new window appears as shown below. Give a suitable database name as well as the path where the data and log file are going to reside.

Click on the OK button.

If the subscriber is some other server, then the following steps need to be performed.

Click on the down arrow available on the Add Subscriber button as shown in the screen capture below.

Click on Add SQL Server Subscriber as shown in the screen capture above.

A new window appears which asks for the SQL Server Name as well as the Authentication neeed to connect to the SQL Server, please refer the screen capture below.

6. Click on the Next> button as shown in the screen capture below.

7. Click on the button as shown in the screen capture below. Here we need to specify

Process account as well as the connection options for the distribution agent.

8. A new window appears as shown in the screen capture below.

9. Specify the distribution agent to run under the SQL Server Agent Service Account. Also connect to the distributor as well as the subscriber by impersonating the process account. Please refer the screen capture below.