Enable read/write splitting (read-only routing endpoint)

How it works

After you create read-only instances for your RDS for SQL Server primary instance, you can enable read/write splitting. By configuring the primary instance endpoint and the read-only routing endpoint in your application, write requests are sent to the primary instance, and read requests are sent to the read-only routing endpoint. The read-only routing endpoint then automatically distributes the read requests among the secondary and read-only instances based on their assigned read weights.

Benefits

• Unified endpoint for easy maintenance

  By connecting to a single read-only routing endpoint, you can perform read operations on your read-only instances, which reduces maintenance costs. You can also scale read capacity by adding more read-only instances without modifying your application code.

• Customizable weights for various use cases

  You can customize the read weight of each read-only instance to meet the demands of different application scenarios.

• Automatic health checks for high availability

  The read/write splitting feature automatically performs a health check on all read-only instances. If an instance fails or if replication latency exceeds a specified threshold, the system stops routing read requests to that instance. Instead, it distributes the requests among the remaining healthy instances. This process ensures that a single instance failure does not disrupt your application. When the instance is restored, RDS automatically adds it back to the request distribution pool.

  Note

  To avoid a single point of failure, we recommend that you create at least two read-only instances for a primary instance.

Read-only and network endpoints

Prerequisites

Usage notes

• You cannot manually modify the read-only routing endpoint.

• Enabling and using read-only endpoints is free of charge. You are only charged for the read-only instances.

• Read-only routing endpoints do not support the classic network.

• If the primary instance becomes unavailable due to a network failure or other reasons, the system automatically triggers a failover. The system promotes the secondary instance to the primary role to ensure service availability. During this process, the read and write weights of the primary and secondary instances are automatically switched so that the original secondary instance can process read and write requests and ensure service continuity.

  Meanwhile, the system automatically repairs the original primary instance. During the repair process, its status changes to Instance in Maintenance. During this time, you cannot perform operations such as upgrading its specifications or changing read weights. After the repair is complete, the system initiates another failover to switch the roles back. The read weights are also reverted, and the instance status returns to Running.

Enable the unified read-only routing endpoint

Enable the unified read-only routing endpoint and configure the read weights. The system then processes read requests based on the configured weights.

1. Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the instance ID.

2. In the navigation pane on the left, click Cluster Management. Then, click Open the unified read-only network address.

3. In the dialog box that appears, set the endpoint type and configure the read weight for each node (primary, secondary, and read-only instances). Then, click OK.

   The primary and secondary nodes are part of the RDS Cluster Edition architecture. You create read-only instances manually. For more information, see RDS Cluster Edition architecture.

   Parameter	Description
   Endpoint Type:	Internal (VPC): Used for internal connections from other Alibaba Cloud services, such as ECS instances, within the same VPC. Internet Address: Used when your application is deployed in a different VPC or on an on-premises machine and needs to access the instance over the internet. Note Due to the potential for network instability over the public network, we recommend using an internal endpoint for your application. For more information, see Differences between internal and public endpoints.
   Weight Distribution	The roles that participate in weight allocation include the primary node, the secondary node, and read-only instances (if they exist). A higher read weight means the instance processes more read requests. For example, if a primary instance has three read-only instances with read weights of 100, 200, and 200, the read requests are distributed among them at a ratio of 1:2:2. Automatic Distribution: The system automatically assigns a read weight to each read-only instance based on its specifications. The system also automatically sets the weights for new read-only instances. For more information, see System weight allocation rules. Customized Distribution: Manually set the read weight for each node. The value can range from 0 to 10,000. The read weight of a new read-only instance defaults to 0 and must be modified manually. Important If a read-only instance in the cluster is released, the system automatically removes its weight allocation.

Enable the secondary instance endpoint

1. Go to the RDS Instances page. In the top navigation bar, select the region where your target instance is located. Then, click the ID of the instance.

2. In the navigation pane on the left, click Cluster Management. Then, click Open backup instance address.

3. Select an Endpoint Type::

   • Internal (VPC): Used for internal connections from other Alibaba Cloud services, such as ECS instances, within the same VPC.

   • Internet Address: Used when your application is deployed in a different VPC or on an on-premises machine and needs to access the instance over the internet.

4. Click OK.

Cluster information

On the Cluster Management page, you can view the basic information and configuration list of the cluster.

Related operations

• If your business workload increases, you can modify the read weight distribution for the unified read-only routing endpoint. The endpoint then automatically forwards read requests to the secondary instance and all configured read-only instances based on the new weights. For more information, see Modify weight distribution.

• If you no longer need the read/write splitting feature, you can disable it. For more information, see Disable read/write splitting (read-only routing endpoint).