Setting up Transactional Replication in SQL Server 2008 R2.

Setting
up Transactional Replication in SQL Server 2008 R2.

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:

         

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.

Continues…

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |