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.
Step 2: Create the Snapshot folder and add its path.
Step 3: Add the distribution database name.
Step 4: Click through the remaining steps until you’re finished with the distributor configuration.
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.
Step 2: Select the database you want to make a part of replication.
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.
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.
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.
Step 2: Select and log in to the publisher.
Step 3: Select the database.
Step 4: Select the subscription method.
Step 5: The database that has been added for publication also needs to be added for subscription.
Step 6: Select the account.
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.
Step 2: Check the replication status for the databases.
Step 3: Set the threshold value for latency.
Alerting
Step 1: Enable the jobs for which alerting is needed.
Step 2: Configure the job and Response as below, ensure that the Database mail profile is already created.
Visit Microsoft.com for more information about configuring replication.
Comparison between Log Shipping/Mirroring/Replication
Where is your organization on its blockchain journey?
| Answer | Total Number | Total % |
|---|---|---|
| Not yet started | 130 | 0.62 |
| Identifying use cases | 55 | 0.26 |
| Competing a POC | 9 | 0.04 |
| Building a prototype | 7 | 0.03 |
| Doing a pilot | 5 | 0.02 |
| Planning to scale | 3 | 0.01 |
| Total Responses: 209 of 428 (49%) |
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/
