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.

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:

Full article...