SQL Server Replication - Case Study & Implementation

This post discuss of evaluating a business case, with designing and implementing SQL Server replication in a step-by-step tutorial to address the requirement.

Business Case
There are two SQL Servers. One hosted on Server A and one hosted on Server B. These two servers are stand alone server on a similar network but not joined to the same domain. Each SQL Server has a database with similar list of tables. Within this list of tables, a set of tables (A_Tbl*) have read/write transactions performed on the database hosted on SQL Server A, with its data also available on SQL Server B for read only purpose. Similarly, there are another set of tables (B_Tbl*) in the database that have read/write transactions performed on SQL Server B, with the data also available on SQL Server A for read only purpose. Any data change occurred on any table of that database on one SQL Server should be promptly reflected on the other SQL Server.

SQL Server replication stand out to be a good solution to address this business and technical requirement. Replication topology could includes servers that are not in the same domain. The servers do not need to be in a cluster environment or require any domain account. SQL Server replication also provides option for replicating selected objects (tables) to another SQL Server. The receiving database on the other SQL Server is available for use even when new data is being replicated over.

Design & Implementation
Transactional replication is chosen to allows data to be replicated in a continuous manner. The replication topology design in this case have each publisher utilize its own distributor for its publication. The distributor is hosted on the same server as the publisher. Push subscription method is chosen on each server to push the publication articles to another server. A local Windows account will be created and used for replication agent process account as well as connecting to SQL Server.

SQL Server transactional replication is implemented by multiple agents, there are snapshot agent, log reader agent and distribution agent. Here are some description and requirement of each agent.

Snapshot agent
- Prepare snapshot files (schema, script, indexes, data, etc), and record synchronization status in distribution database. Snapshot files are used to initiate subscriber for transactional replication and also used for other replication
- Run at distributor
- Connect to publisher either with a Windows account or SQL account. Connecting account at least db_owner database role in publication database
- Connect to distributor with a Windows account (process account). Process account at least db_owner database role in distribution database. Process account has write permission to snapshot share

Log reader agent
- Monitor transaction log on publication database and copy transaction marked for replication to distribution database
- Run at distributor
- Connect to Publisher either with a Windows account or SQL account. Windows account will be used in this case. The same Windows account (connecting account) with at least db_owner database role in publication database
- Connect to Distributor with a Windows account (process account). Process account with at least db_owner database role in distribution database

Distributor agent (Push subscription)
- Move snapshot in transaction stored in distribution database to the subscriber
- Run at distributor
- Connect to distributor with a Windows account (process account). Process account at least db_owner database role in distribution database. Process account has read permission to snapshot share. Process account a member of PAL (Publication Access List)
- Connect to subscriber with either with a Windows account or SQL account. Windows account will be used in this case. The same Windows account (connecting account) with at least db_owner database role in subscription database

This is the design layout,

As the distributor and publisher of each publication are hosted on the same server, for simplicity, one local Windows account is used for snapshot agent, log reader agent and distributor agent on each server. As both servers are not on the same domain, one Windows account with similar name and password will be used for authentication purpose.

Preparation
In this example, we will create a database called TEST on each SQL Server. The actual server name in the example is SQL2008R2 (acted as Server A) and SQL2008R2A (acted as Server B). Each server only have one default SQL Server instance.

The SQL Server agent service startup type on each server has been changed to automatic. A firewall rule to allow TCP port 1433 has been created on each server for SQL Server communication.

Lets create the database and tables. Connect to SQL Server A and run the database and table creation script. Insert some data into A_tbl tables

USE master;
GO
CREATE DATABASE TEST;
GO
USE TEST;
GO
CREATE TABLE [dbo].[A_tbl1] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[A_tbl2] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[A_tbl3] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl1] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl2] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl3] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
INSERT INTO dbo.A_tbl1 VALUES ('Hello'), ('Hi');
INSERT INTO dbo.A_tbl2 VALUES ('Morning'), ('Night');
INSERT INTO dbo.A_tbl3 VALUES ('SQL'), ('ORACLE');
GO

On SQL Server B, create the database and same tables. Insert some data into B_tbl tables

USE master;
GO
CREATE DATABASE TEST;
GO
USE TEST;
GO
CREATE TABLE [dbo].[A_tbl1] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[A_tbl2] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[A_tbl3] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl1] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl2] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl3] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
INSERT INTO dbo.B_tbl1 VALUES ('Yes'), ('No');
INSERT INTO dbo.B_tbl2 VALUES ('Food'), ('Drink');
INSERT INTO dbo.B_tbl3 VALUES ('See ya'), ('Bye');
GO

On Server A,
Creates a Windows account for replication agents through computer management
Account: sqlreplication

Grant the Windows account created read / write access to its snapshot replication folder (on its server). In this example, the default location is used. The replication folder is designated during replication setup.

eg. C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata

Create the account in the SQL Server A for the Windows account just created. Grant the local Windows account db_owner role on the publication database (in this case also a subscription database for other publication), TEST database.

On Server B,
Perform the same action like Server A.

Creates a Windows account with the same name and same password like Server A for replication agents through computer management
Account: sqlreplication

Grant the Windows account just created with read / write access to its snapshot replication folder (on its server). In this example, the default location is used. The replication folder is designated during replication setup.

eg. C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata

Create the account in the SQL Server B for the Windows account just created. Grant the local Windows account db_owner role on the publication database (in this case also a subscription database for other publication), TEST database.

SQL Replication Implementation - Publication A
Now we are ready to setup the replication. Let's setup the publication A (PUB_A) on SQL Server A for the three articles (three tables A_tbl1, A_tbl2, A_tbl3). Keep in mind that we will be setting up with push subscription to SQL Server B.

This example mainly use the SSMS GUI for all the replication tasks. So, ready for the screenshot?

Distributor hosted on the same server.

This is where we define the snapshot folder that we grant the sqlreplication Windows account the read write permission.

 Use transactional replication

Selecting the A_tbl1, A_tbl2, and A_tbl3 for this publication

We will initiate the snapshot after we finish all the configuration

Using sqlreplication account on Server A for snapshot agent. We have previously configured the account with db_owner role in the TEST database.

Both snapshot and log reader agent using the same local Windows account.

Name this publication as PUB_A

There is one more thing to do here. The sqlreplication Windows account needs to be granted the db_owner role in the newly created distribution database.

Now that the publication has been setup on SQL Server A, lets setup the subscriber (SQL Server B) for this publication. On SQL Server A, creates new subscription

Run all agents at distributor for push subscription.

Add SQL Server B as subscriber to this publication (PUB_A)

Run distribution agent with the sqlreplication local Windows account. Connect to subscriber (SQL Server B) with the Server B sqlreplication local Windows account. This will only work when both local Windows account has the same name and password.

Synchronization (or replication) run continuously

Select initialize when to immediately. This will start the Snapshot agent when the subscription is setup. The snapshot agent will generate the snapshot and synchronization will follow after.

Now with both the publication and subscriber setup.

Go to SQL Server B and verify the three A_tbl tables have updated with the data. Data show up. Great! Let's insert a record at SQL Server A.

USE TEST;
INSERT INTO A_tbl2 VALUES ('Noon');

Check the synchronization status if the new record has sent to the subscriber.

Synchronization completed

Go to SQL Server B. Check the table A_tbl2, new record 'noon' show up.

SQL Replication Implementation - Publication B
The first publication (PUB_A) has been completed. Now we just need to setup the other publication (PUB_B) at the SQL Server B. The steps are similar like the one above.

Here are some of the difference,

Select the B_tbl tables only for this publication

The local Windows account used is the sqlreplication Windows on Server B (NOT Server A), although the name is the same. See the sql2008r2a (with the a)?

The publication named as PUB_B

On subscriber setup, here are some of the difference.

Connecting to SQL Server A to add it as subscriber

Similarly, setup the Server B sqlreplication local Windows account (NOT the server A) as the process account.

Insert a record to B_tbl1 on SQL Server B,

USE TEST;
INSERT INTO B_tbl1 VALUES ('MayBe');

Verify the synchronization status,

Go to SQL Server A, check the B_tbl1 tables.

It has been verified that two publications successfully replicated to each other. Mission accomplished.