MSSQL Replication with Read Intent | Perficient Digital

MSSQL Replication With Read Intent

My team recently had a requirement in one of our Windows projects where the development team wanted to offload the website’s read request load from the database server, which can be done only if there is another database server to take the read request load on. To accomplish this, I had to configure database replication with two database servers so that all write requests would be forwarded to the primary server and all read requests would be forwarded to the secondary server. Thus, I was looking for a MSSQL replication method that replicates data to the secondary server as readable copy.

Log Shipping

First, I configured log shipping on the database servers. In Log shipping, a full backup is taken from the primary server and restored on the secondary server in no-recovery mode at the very first step. The transactional log is then backed up from the primary server every five minutes (or at a different interval, depending on the configuration), copied, and restored on the secondary server. This process keeps running continuously.

I had set up log shipping in standby mode, which allows the database on the secondary server to be accessed in Read-Only format. The development team made changes in the application and routed all read requests to the secondary database server. They found that it was working fine initially but later identified that they were intermittently receiving request timeouts.

I checked the log shipping process and tried to open a table on the secondary server from the management studio and found it was responding correctly. On further investigation of this issue, I found that each time transactional logs were restored on the secondary server, it was locking the access to the secondary server’s database. At the same time, if anyone made a read-only request to the secondary server, the database server was not responding. Because of this, the application was receiving RTOs for read requests.

Since this is the way log shipping works, I could see that log shipping was not going to be an option. Hence I had to look for an alternative method, one that could give continuous read access to the database on the secondary server.

Always On Availability Groups

Next, I looked at the MSSQL Always On availability group method, which offers the feature of read intent. However, this method requires that the database servers be part of the domain. The database servers on which I was required to setup replication were not in the domain, so I had to look for another option.

MSSQL Replication

I had worked with the MSSQL replication method in the past, which uses distribution/publication/subscription to replicate tables, schemas, etc. However, I didn’t know whether it would work for this solution or not. So, started working on it.

The replication method does not look for the database to replicate. Instead, it works on a table basis and requires that each table we want to be part of the replication has a primary key defined. When I started, we had few tables missing primary keys, so I was not able to select those tables for replication. The database administration team added a primary key to the tables, and eventually, I was able to add all of them for replication.

I selected the transactional snapshot method. The development team made changes at the website level so that all read requests would route to the secondary server only and found that it was all working as expected.

So, if you want to have MSSQL replication with read intent configured and your servers are not in the domain, then you can use the MSSQL replication method.

The Three Phases of MSSQL Replication Process

Phase 1:

The MSSQL Snapshot Agent, as its name implies, takes a snapshot of the schema and tables selected for replication and stores it on the server.

Phase 2:

The Log Reader Agent tracks when transactions happen on the primary database tables and syncs those changes to the secondary database tables.

Phase 3:

The Distribution Agent copies snapshot files from the primary server to the secondary server, along with any changes that have taken place in the distribution database tables.

Steps to Set Up Transactional Replication

Prerequisites

Source MSSQL server – This will be acting as a publisher so that it can have any SQL version other than Express and Compact.

Destination MSSQL server – This will be acting as a subscriber so that it can have any SQL version other than Compact.

Configuration of the Distributor

The role of the Distribution Agent is to move the MSSQL snapshot and transactions stored at the distributor to the subscribing servers. It has two methods, push and pull. In the push method, the distributor is placed on the publication server and takes data from the publisher and sends it to the subscriber. In the pull method, the distributor is placed on the subscription server and takes data from the publisher and waits for a request for updated data from the subscriber.

Now, which method to select totally depends on the traffic coming on the server and the type of traffic (e.g., read, write).

As already described, the publication database is the main point of contact for the website, and if heavy traffic is expected on the publication server, then you should not keep the distributor on the publication server. This will lower the processing power needed by the distributor from the publication server. In the case of our project, the pull method needed to be selected.

If you have more than one subscriber, then you should go with push method and keep the distributor on the publication server. If you expect a heavy load of reporting requests for the subscriber server, then the distributor should be set up on publication with the push method.

In my case, I configured replication with the push method and set up the distributor on the publication server, as the load on the website was moderate.

Step 1: Login to the primary instance and configure distribution.

MSSQL Distribution Agent – Configure Distribution.

Step 2: Create the Snapshot folder and add its path.

MSSQL Distribution Agent – Snapshot Folder & Path.

Step 3: Add the distribution database name.

MSSQL Distribution Agent – Set Distribution Database Name.

Step 4: Click through the remaining steps until you’re finished with the distributor configuration.

MSSQL Distribution Agent – Configure Distribution Wizard Steps A.

MSSQL Distribution Agent – Configure Distribution Wizard Steps B.

MSSQL Distribution Agent – Configure Distribution Wizard Steps C.

Configuration of the Publisher

The publisher instance will act as the primary server and will send transactional logs to the secondary server.

Step 1: Login to the Primary instance.

MSSQL Distribution Agent – Configure Publication Login.

Step 2: Select the database you want to make a part of replication.

MSSQL Distribution Agent – Configure Publication Database.

Step 3: Specify the type of replication you want to set up. In our case, per the project’s requirement, I selected the Transactional publication method. You can learn more about the different types of replication at C-sharpcorner.com.

MSSQL Distribution Agent – Configure Publication Type.

Note: The red mark tables cannot set for replication. To include them in replication, the primary key needs to be added in the table, which can be done by a database administrator.

MSSQL Distribution Agent – Configure Publication Articles.

Step 4: Click through the remaining steps until you’re finished with the publisher configuration.

Configuration of the Subscriber

Step 1: Login to the secondary instance.

MSSQL Distribution Agent – Configure Subscription Login.

Step 2: Select and log in to the publisher.

MSSQL Distribution Agent – Configure Subscription Publication A.

MSSQL Distribution Agent – Configure Subscription Publication B.

Step 3: Select the database.

MSSQL Distribution Agent – Configure Subscription Database A.

Step 4: Select the subscription method.

MSSQL Distribution Agent – Configure Subscription Agent Location.

Step 5: The database that has been added for publication also needs to be added for subscription.

MSSQL Distribution Agent – Configure Subscription Database B.

Step 6: Select the account.

MSSQL Distribution Agent – Configure Subscription Security A.

MSSQL Distribution Agent – Configure Subscription Security B.

MSSQL Distribution Agent – Configure Subscription Security C.

MSSQL Distribution Agent – Configure Subscription Security D.

Step 7: Click through the remaining steps until you’re finished with the subscriber configuration.

Steps to Set Up Alerting and Monitoring

Monitoring

Step 1: MSSQL monitoring can always be done on the primary server.

MSSQL Monitoring & Alerts – Launch.

Step 2: Check the replication status for the databases.

MSSQL Monitoring & Alerts – Status.

Step 3: Set the threshold value for latency.

MSSQL Monitoring & Alerts – Latency Threshold.

Alerting

Step 1: Enable the jobs for which alerting is needed.

MSSQL Monitoring & Alerts – Alert Jobs.

Step 2: Configure the job and Response as below, ensure that the Database mail profile is already created.

MSSQL Monitoring & Alerts – Alert Jobs & Response.

Visit Microsoft.com for more information about configuring replication.

Comparison between Log Shipping/Mirroring/Replication

 Log ShippingMirroringReplication
ServersPrimary server, secondary server, and monitor server (Optional)Principal server, mirror server, and witness server (Optional)Publisher, subscribers, distributor (Optional)
Data TransferTransaction logs are backed up and transferred to the secondary server.Individual transaction log records are transferred using TCP endpoints.Replication works by tracking/detecting changes (either by triggers or by scanning the log) and shipping the changes.
Failover TypesManualAutomatic or manualManual
DB AccessYou can use a secondary database for reporting purposes when the secondary database restores in STANDBY mode.The mirrored database can only be accessed using the snapshot database.The subscriber database is open to reads and writes.
Recovery ModelBulk and full FullFull
Restoring StateThe restore can be completed using either the NORECOVERY or STANDBY option.The restore can be completed using WITH NORECOVERY.The restore can be completed using WITH RECOVERY.
Server TypesAll servers should be SQL servers.All servers should be SQL servers.Publisher can be an Oracle server.
SQL Server Agent Dependency/JobsYes. Log shipping involves four jobs, which are handled by dedicated SQL Server Agent jobs. These jobs include the backup job, copy job, restore job, and alert job.Independent on SQL server agent.Yes. Snapshot agent, log reader agent and Distribution agent (transactional replication)
Merge agent (merge replication)
Using with Other FeaturesLog shipping can be used with database mirroring, replication.Database mirroring can be used with log shipping, database snapshots, and replication.Replication can be used with log shipping, database mirroring.
LimitNo limitIt’s generally good to have 10 databases for one server.No limit
Commit/UncommitBoth committed and uncommitted transactions are synced and updated to the secondary database.Only committed transactions are synced and updated to the mirror database.Only committed transactions are synced and updated to the secondary database.
DB Name on Both the ServersCan be different Should be the sameShould be the same

Additional Resources:

https://lalitkale.wordpress.com/2014/09/11/difference-between-log-shipping-and-database-mirroring/

https://nilebride.wordpress.com/2011/07/24/log-shipping-vs-mirroring-vs-replication/

Leave a Reply