Local Replication Case Study 2

RecoverPoint Case Study, How to troubleshooting remote IP replication high load issue

Introduction

 

This article introduces a case study of how to troubleshooting remote IP replication high load issue.

Detailed Information

 

EMC RecoverPoint is an enterprise-scale solution designed to protect application data on heterogeneous SAN-attached servers and storage arrays. RecoverPoint runs on an out-of-band appliance and combines industry-leading continuous data protection technology with a bandwidth efficient, no-data-loss replication technology.

EMC RecoverPoint provides local and remote data protection, enabling reliable replication of data over any distance; that is, locally within the same Site, and remotely to another Site. Specifically, RecoverPoint protects and supports replication of data that applications are writing over Fibre Channel to local SAN-attached storage. RecoverPoint uses an existing Fibre Channel infrastructure to integrate seamlessly with existing host applications and data storage subsystems. For long distances, RecoverPoint uses an existing IP network to send replication data over a WAN.

 

RecoverPoint utilizes write splitters that monitor writes and ensure that a copy of all writes to a protected volume are tracked and sent to the local RecoverPoint appliance. RecoverPoint supports four different types of write-splitters. They are fabric-based write splitter, host-based write splitter, array-based write splitter and VPLEX write splitter.

 

 

 

RecoverPoint CRR (Continuous Remote Replication) provides bidirectional, heterogeneous block-level replication across any distance using asynchronous, synchronous, and snapshot technologies over FC and IP networks.

 

 

A consistency group consists of one or more replication sets. Each replication set consists of a production volume and the replica volumes to which it is replicating. The consistency group ensures that updates to the replicas are always consistent and in correct write order; that is, the replicas can always be used to continue working or to restore the production source, in case it is damaged.

 

 

 

Replication High Load

 

With several of resources limitation, replication will have high load issue. High load issue occurs too frequently causes customer experiences reduction. Common high load issue will caused replication turn to “temporary high load”. Following four scenarios are the reasons of “temporary high load”

 

1.     In rare cases, long time overloaded writes IO workloads.

2.     Replication Volume & Journal Volumes are too slow.

3.     There is Insufficient WAN bandwidth.

4.     There are improper compression settings.

 

Overloaded write IO workloads and improper compression settings are easy to be detected. But Replication Volume & Journal Volumes are too slow issue is hidden. Below is the troubleshooting example about them.

 

WAN bandwidth issue detection:

 

Ensure the point in time of high load issue first, it can be found in CLI folder of RPA log.

 

High load event:

 

Time:             Wed Aug  7 10:41:24 2013

  Topic:            GROUP

  Scope:            DETAILED

  Level:            WARNING

  Event ID:         4019

  Site:             7DaysInn_KXC

  Links:            [WFO-04_CG, WFO-04_KXC -> WFO-04_RMZ]

  Summary:          Group in high load -- transfer to be paused temporarily  Service Request info:N/A

 

Checking the incoming write rate before point in time, the rate is performance metrics which can be found in performance logs. We found before the event occurs, the troughput average are 10-20MB/s

 

2013/08/07 02:32:07.910 GMT    2013/08/07 02:33:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    Incoming writes rate for link                                      15.2306 Megabytes/sec

2013/08/07 02:33:07.910 GMT    2013/08/07 02:34:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    Incoming writes rate for link                                      12.8331 Megabytes/sec

2013/08/07 02:34:07.910 GMT    2013/08/07 02:35:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    Incoming writes rate for link                                      5.81344 Megabytes/sec

2013/08/07 02:35:07.910 GMT    2013/08/07 02:36:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    Incoming writes rate for link                                      17.8956 Megabytes/sec

2013/08/07 02:36:07.910 GMT    2013/08/07 02:37:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    Incoming writes rate for link                                      20.43     Megabytes/sec

2013/08/07 02:37:07.910 GMT    2013/08/07 02:38:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    Incoming writes rate for link                                      19.557   Megabytes/sec

2013/08/07 02:38:07.910 GMT    2013/08/07 02:39:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    Incoming writes rate for link                                      21.2274Megabytes/sec

2013/08/07 02:39:07.910 GMT    2013/08/07 02:40:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    Incoming writes rate for link                                      11.2537 Megabytes/sec

2013/08/07 02:40:07.910 GMT    2013/08/07 02:41:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    Incoming writes rate for link                                      16.1114 Megabytes/sec

2013/08/07 02:41:07.910 GMT    2013/08/07 02:42:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    Incoming writes rate for link                                      17.0141 Megabytes/sec

 

Which cause log increasing from 100M to 7G

 

2013/08/07 02:31:07.910 GMT    2013/08/07 02:32:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    RPO - lag in data between replicas during transfer after init          106.659                Megabytes

2013/08/07 02:32:07.910 GMT    2013/08/07 02:33:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    RPO - lag in data between replicas during transfer after init          673.536                Megabytes

2013/08/07 02:33:07.910 GMT    2013/08/07 02:34:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    RPO - lag in data between replicas during transfer after init          1405.1                Megabytes

2013/08/07 02:34:07.910 GMT    2013/08/07 02:35:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    RPO - lag in data between replicas during transfer after init          1745.29                Megabytes

2013/08/07 02:35:07.910 GMT    2013/08/07 02:36:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    RPO - lag in data between replicas during transfer after init          2469.8                Megabytes

2013/08/07 02:36:07.910 GMT    2013/08/07 02:37:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    RPO - lag in data between replicas during transfer after init          3581.55                Megabytes

2013/08/07 02:37:07.910 GMT    2013/08/07 02:38:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    RPO - lag in data between replicas during transfer after init          4447.67                Megabytes

2013/08/07 02:38:07.910 GMT    2013/08/07 02:39:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    RPO - lag in data between replicas during transfer after init          5613.29                Megabytes

2013/08/07 02:39:07.910 GMT    2013/08/07 02:40:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    RPO - lag in data between replicas during transfer after init          6738.1                Megabytes

2013/08/07 02:40:07.910 GMT    2013/08/07 02:41:07.910 GMT    long term            7DaysInn_KXC   RPA1     WFO-04_CG                WFO-04_KXC     WFO-04_RMZ                    RPO - lag in data between replicas during transfer after init          7099.98                Megabytes

 

Based on above two evidences, High load issue might be caused by:

 

There is insufficient WAN bandwidth which unable to transfer the data timely.

Slow storage which cause write IO high response time and queued.

 

At this time, if customer confirm there one of two bottleneck in their environments, we can locate the cause. If not, additional data are required to breaking down the root cause. Further analysis shows the average transfer time during peak time is only 3Mbps.

 

2013/08/06 20:04:07.910 GMT    2013/08/06 20:05:07.910 GMT    long term            7DaysInn_KXC                   WAN throughput from site              1.50716 Megabits/sec    

2013/08/06 20:05:07.910 GMT    2013/08/06 20:06:07.910 GMT    long term            7DaysInn_KXC                   WAN throughput from site              3.18644 Megabits/sec    

2013/08/06 20:06:07.910 GMT    2013/08/06 20:07:07.910 GMT    long term            7DaysInn_KXC                   WAN throughput from site              3.35739 Megabits/sec    

2013/08/06 20:07:07.910 GMT    2013/08/06 20:08:07.910 GMT    long term            7DaysInn_KXC                   WAN throughput from site              3.32743 Megabits/sec    

2013/08/06 20:08:07.910 GMT    2013/08/06 20:09:07.910 GMT    long term            7DaysInn_KXC                   WAN throughput from site              5.50845 Megabits/sec    

2013/08/06 20:09:07.910 GMT    2013/08/06 20:10:07.910 GMT    long term            7DaysInn_KXC                   WAN throughput from site              7.36306 Megabits/sec    

2013/08/06 20:10:07.910 GMT    2013/08/06 20:11:07.910 GMT    long term            7DaysInn_KXC                   WAN throughput from site              6.83884 Megabits/sec    

2013/08/06 20:11:07.910 GMT    2013/08/06 20:12:07.910 GMT    long term            7DaysInn_KXC                   WAN throughput from site              6.1095   Megabits/sec    

2013/08/06 20:12:07.910 GMT    2013/08/06 20:13:07.910 GMT    long term            7DaysInn_KXC                   WAN throughput from site              3.18375 Megabits/sec    

2013/08/06 20:13:07.910 GMT    2013/08/06 20:14:07.910 GMT    long term            7DaysInn_KXC                   WAN throughput from site              0.375969              Megabits/sec  

 

At this time, we can confirm the lack of WAN bandwidth because of customer’s networking configuration which cause 40Mbps bandwidth utilization is only 3Mbps.

 

Journal Volumes is too slow issue:

 

Ensure the point in time of high load issue first in CLI folder.

 

Time:             Wed Dec 18 00:45:19 2013

  Topic:            GROUP

  Scope:            DETAILED

  Level:            WARNING

  Event ID:         4019

  Site:             Chai_Wan

  Links:            [IOCM, IOCM-DC -> IOCM-DR]

  Summary:          Group in high load -- transfer to be paused temporarily  Service Request info:N/A

 

Then checking the Journal Volume distributor related performance log. There are two metrics:

 

Distributor phase 2 thread load

Distributor phase 2 effective speed

 

2013/12/17 16:36:05.869 GMT    2013/12/17 16:37:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        87.272   % of time

2013/12/17 16:37:05.869 GMT    2013/12/17 16:38:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        97.4623 % of time

2013/12/17 16:38:05.869 GMT    2013/12/17 16:39:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        95.9147 % of time

2013/12/17 16:39:05.869 GMT    2013/12/17 16:40:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        90.7287 % of time

2013/12/17 16:40:05.869 GMT    2013/12/17 16:41:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        87.7186 % of time

2013/12/17 16:41:05.869 GMT    2013/12/17 16:42:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        91.9028 % of time

2013/12/17 16:42:05.869 GMT    2013/12/17 16:43:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        91.6016 % of time

2013/12/17 16:43:05.869 GMT    2013/12/17 16:44:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        92.5544 % of time

2013/12/17 16:44:05.869 GMT    2013/12/17 16:45:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        96.4623 % of time

2013/12/17 16:45:05.869 GMT    2013/12/17 16:46:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        85.6245 % of time

2013/12/17 16:46:05.869 GMT    2013/12/17 16:47:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        87.484   % of time

2013/12/17 16:47:05.869 GMT    2013/12/17 16:48:05.869 GMT                    IOCM    IOCM-DR                                             Distributor phase 2 thread load        94.5462 % of time

2013/12/17 16:38:05.869 GMT    2013/12/17 16:39:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               3.63361 Megabytes/sec

2013/12/17 16:39:05.869 GMT    2013/12/17 16:40:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               5.08614 Megabytes/sec

2013/12/17 16:40:05.869 GMT    2013/12/17 16:41:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               5.44455 Megabytes/sec

2013/12/17 16:41:05.869 GMT    2013/12/17 16:42:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               6.10622 Megabytes/sec

2013/12/17 16:42:05.869 GMT    2013/12/17 16:43:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               5.67813 Megabytes/sec

2013/12/17 16:43:05.869 GMT    2013/12/17 16:44:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               4.28963 Megabytes/sec

2013/12/17 16:44:05.869 GMT    2013/12/17 16:45:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               3.59579 Megabytes/sec

2013/12/17 16:45:05.869 GMT    2013/12/17 16:46:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               3.03741 Megabytes/sec

2013/12/17 16:46:05.869 GMT    2013/12/17 16:47:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               2.95798 Megabytes/sec

2013/12/17 16:47:05.869 GMT    2013/12/17 16:48:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               4.20823 Megabytes/sec

2013/12/17 16:48:05.869 GMT    2013/12/17 16:49:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               3.99813 Megabytes/sec

2013/12/17 16:49:05.869 GMT    2013/12/17 16:50:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               4.75266 Megabytes/sec

2013/12/17 16:50:05.869 GMT    2013/12/17 16:51:05.869 GMT    RPA1     IOCM    IOCM-DR                                             Distributor phase 2 effective speed               7.01075 Megabytes/sec

 

Through performance log, we can find the Distributor phase 2 thread is highly loaded, but the Distributor phase 2 effective speed is low. That mean the Journal Volume distributing speed is low. Possible reason is SAN problem or storage performance bottleneck.

 

Above examples introduce how to detect the root cause of two kinds of RecoverPoint high load issues. But the analysis of high load issue is much more than it. For example, the insufficient bandwidth issue may be caused by various of reasons which is beyond RecoverPoint side much than the Networking topic. Regarding the Journal Volume low speed issue, it may be caused by connectivity as well as slow storage which all need further analysis out of ReocverPoint which are not covered in the article. Please note the cause of performance is varied and extend to the rest of devices in storage stacks.

 

https://emc.my.salesforce.com/ka07000000052Rx

                                                         

Author: Fenglin Li

 

 

 

             

iEMC APJ

Please click here for for all contents shared by us.

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.

Categories: 1

0 Replies to “Local Replication Case Study 2”

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *