In scenarios where there are many read requests but only few write requests, a single instance may not be able to handle the load of read requests, which even may affect the business. To implement the auto scaling of read capabilities and mitigate the pressure on TencentDB for SQL Server, you can create one or multiple read-only instances and use them to sustain high numbers of database reads.
Unified read/write separation addresses (i.e., read and write requests are separated automatically) are not supported currently. Read-Only instances need to be accessed with separate IPs and ports.
Changes in the primary instance (source database) are synced to all read-only instances. Given the single-node architecture (with no replica) of read-only instances, repeated attempts to restore a failing read-only instance will be made. Therefore, we recommend you choose a read-only group rather than a read-only instance for higher availability.
The read-only instance backend architecture and technology slightly vary by TencentDB for SQL Server editions:
In this mode, tables without a primary key in the primary instance cannot be synced. You can use the following code to query whether there is such a table:
If you need to create read-only instances for tables without a primary key, we recommend you use 2017 Enterprise Cluster Edition.
use dbname select name from sys.sysobjects where xtype='U' and id not in(select parent_obj from sys.sysobjects where xtype='PK')
You can connect to the VIP of a read-only group to read read-only instances in it, which can reduce the maintenance costs. You can also add the number of read-only instances in the unified read-only group to continuously expand the processing capacity of the system while ensuring the high availability of read-only instances, with no need to make any changes to the application.
TencentDB for SQL Server supports adding read-only instances across AZs and regions, providing a low-latency, high-efficiency, and stable one-stop solution for nearby business access.
The cluster management module automatically checks read-only instances. When it finds that a read-only instance is down or the delay exceeds the threshold, it stops allocating read requests to the instance and instead allocates them among the remaining healthy instances. This ensures that when a single read-only instance fails, the normal access to the application will not be affected. When the failed instance is repaired, it will be automatically added back to the request distribution system.