All Products
Search
Document Center

ApsaraDB RDS:Create a read-only ApsaraDB RDS for SQL Server instance

Last Updated:Mar 21, 2024

If your database system receives a large number of read requests, you can create one or more read-only RDS instances to increase the read capability of your database system and the throughput of your application. ApsaraDB RDS for SQL Server uses the Always On architecture of native SQL Server. In ApsaraDB RDS for SQL Server, physical replication is implemented to replicate a primary RDS instance and generate a read-only RDS instance. The data on the read-only RDS instance is the same as the data on the primary RDS instance. If the data on the primary RDS instance is updated, the updates are automatically synchronized to the read-only RDS instance.

Note

Prerequisites

A primary RDS instance that meets the following requirements is created:

  • The primary RDS instance runs SQL Server 2017 EE on RDS Cluster Edition, SQL Server 2019 EE on RDS Cluster Edition, or SQL Server 2022 EE on RDS Cluster Edition.

  • The primary RDS instance runs RDS Cluster Edition.

  • The primary RDS instance uses enhanced SSDs (ESSDs) or general ESSDs.

  • The RDS instance uses the subscription or pay-as-you-go billing method. Serverless RDS instances do not support the creation of read-only RDS instances.

Note
  • Before you create a read-only RDS instance for a primary RDS instance, you need to check the major engine version or RDS edition of the primary RDS instance on the Basic Information page of the primary RDS instance.

  • If you want to create a read-only RDS instance for the primary RDS instance that runs RDS Basic Edition or RDS High-availability Edition, you need to upgrade the primary RDS instance to SQL Server EE (Always On) and then create a read-only RDS instance. For more information, see Upgrade the major version.

Usage notes

  • You can create read-only RDS instances for a primary RDS instance. You cannot convert existing primary RDS instances to read-only RDS instances.

  • When you create a read-only RDS instance, the system replicates data from the secondary RDS instance to the read-only RDS instance. This prevents interruptions to your workloads on the primary RDS instance.

  • Number of read-only RDS instances: You can create up to seven read-only RDS instances for a primary RDS instance.

  • Instance backup: You cannot configure backup policies or manually create backups for read-only RDS instances. These are configured and created on the primary RDS instance. You cannot create temporary RDS instances from backup files or any point in time. You cannot overwrite RDS instances by using backup sets. After a read-only RDS instance is created, you cannot use backup sets to overwrite the primary RDS instance to restore data.

  • Data migration: You cannot migrate data to read-only RDS instances.

  • Database management: You cannot create or delete databases on read-only RDS instances.

  • Account management: You cannot create or delete accounts, grant permissions to accounts, or change the passwords of accounts on read-only RDS instances.

  • The period of time that is required to create a read-only RDS instance is positively correlated with the data volume and the I/O performance of the primary RDS instance. Before you create a read-only RDS instance, we recommend that you evaluate and optimize the data volume and I/O performance of the primary RDS instance to shorten the creation time.

  • The storage capacity of a read-only RDS instance cannot be less than the storage capacity of the primary RDS instance. For more information about the storage capacity range of a read-only RDS instance, see Read-only ApsaraDB RDS for SQL Server instance types.

Billing rules

You are charged for the read-only RDS instances that you create based on the subscription billing method or the pay-as-you-go billing method. For more information, see Pricing.

Create a read-only RDS instance

  1. 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 ID of the instance.
  2. In the Instance Distribution section of the page that appears, click add to the right of Read-only Instance.

    image.png

  3. Configure basic information, including the billing method, storage type, and zone. Then, click Next: Instance Configuration.

    1. Configure the Billing Method parameter.

      Billing method

      Description

      Benefit

      Subscription

      A subscription RDS instance is an instance for which you pay an upfront fee. If you want to use an RDS instance for a long period of time, we recommend that you select the Subscription billing method. If you select the subscription billing method, you must configure the Subscription Duration parameter in the lower-left corner of the page.

      In most cases, the subscription billing method is more cost-effective than the pay-as-you-go billing method for long-term usage. Alibaba Cloud provides lower prices for longer subscription durations.

      Pay-as-you-go

      You are charged on an hourly basis for a pay-as-you-go instance based on your actual resource usage. If you want to use an instance for a short period of time, we recommend that you select the Pay-as-you-go billing method.

      You can create a pay-as-you-go RDS instance. After you confirm that the created RDS instance meets your business requirements, you can change the billing method of the RDS instance to subscription.

      You can release a pay-as-you-go RDS instance at any time. The billing of a pay-as-you-go RDS instance stops immediately after you release the instance.

    2. Configure the Storage Type parameter.

      ESSDs and general ESSDs are supported. For more information, see Storage types.

    3. Configure the Zone parameter.

      Note
      • Each zone is an independent physical location within a region. No substantive differences exist between zones in the same region.

      • If the RDS instance resides in the same zone as the ECS instance on which your application is deployed, these instances can deliver optimal performance. If the RDS instance and the ECS instance reside in different zones in the same region, the performance of the RDS instance and the ECS instance is slightly lower than the performance of the RDS instance and the ECS instance that reside in the same zone.

    4. Configure the Instance Type parameter.

      Specify the category (dedicated or general-purpose instance family). Then, specify the CPU cores, memory capacity, and maximum number of connections.

      Instance type

      Description

      Benefit

      General-purpose Instance Types

      A general-purpose RDS instance exclusively occupies the allocated memory and I/O resources.

      A general-purpose RDS instance shares CPU and storage resources with other general-purpose RDS instances that are deployed on the same host.

      General-purpose instances are cost-effective.

      Dedicated Instance Types

      A dedicated RDS instance exclusively occupies the allocated CPU, memory, storage, and I/O resources.

      Note

      The dedicated host instance family is the highest configuration of the dedicated instance family. A dedicated host RDS instance occupies all CPU, memory, storage, and I/O resources of the host on which the RDS instance is deployed.

      A dedicated instance provides better performance and stability.

      Note
      • Select the instance type to which you want to upgrade. Each instance type supports a specific number of CPU cores, memory capacity, maximum number of connections, and maximum IOPS. For more information, see Instance types for read-only ApsaraDB RDS for SQL Server instances.

      • To ensure I/O performance for data synchronization, we recommend that you make sure that the memory capacity of the read-only RDS instance is higher than or equal to the memory capacity of the primary RDS instance.

    5. Configure the Storage Capacity parameter.

      The storage capacity of the RDS instance. The storage capacity is used to store data files, system files, archived log files, and transaction files. The valid values of the Storage Capacity parameter vary based on the values of the Storage Type and Instance Type parameters that you specify. You can adjust the storage capacity at a step size of 5 GB.

      Note
      • The storage capacity of a read-only RDS instance cannot be less than the storage capacity of the primary RDS instance.

      • If you select the local SSD storage type, the storage capacity of the RDS instance may vary based on the instance type. If you select the standard SSD or ESSD storage type, the storage capacity of the RDS instance does not vary based on the instance type. For more information, see Primary ApsaraDB RDS instance types.

  4. Configure instance resources, such as the network type, vSwitch, and resource group. Then, click Next: Confirm Order.

    1. The Network Type parameter is fixed as VPC.

    2. Configure the VPC and vSwitch of Primary Node parameters.

      We recommend that you select the VPC in which your ECS instance resides for the RDS instance. If the RDS instance and the ECS instance reside in different VPCs, these instances cannot communicate over an internal network.

      Note

      You can connect the RDS instance and the ECS instance over an internal network even if the instances use different vSwitches in the same VPC.

    3. Optional. If you set the Billing Method parameter to Pay-as-you-go, enable Release Protection to prevent the instance from being accidentally released. For more information, see Enable or disable the release protection feature.

    4. Configure the Resource Group parameter. You can use the default resource group or select a custom resource group based on your business requirements.

    5. Enter a description and specify tags for the instance.

  5. Confirm the configuration of the RDS instance in the Parameters section, configure the Purchase Plan and Duration parameters, read and select Terms of Service, and then click Pay Now to complete the payment. You must configure the Duration parameter only if you select the subscription billing method for the RDS instance.

    Note
    • If you select the subscription billing method for the RDS instance, we recommend that you select Auto-Renew Enabled. This way, you can prevent interruptions on your workloads even if you forget to renew the RDS instance.

    • It requires 1 to 10 minutes to create an RDS instance. You can refresh the page to view the RDS instance that you created.

View a read-only RDS instance

You can view a read-only RDS instance on the Instances page, the Cluster Management page of the required RDS instance, or the Basic Information page of the required RDS instance.

View a read-only RDS instance on the Instances page

  1. Log on to the ApsaraDB RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region in which the read-only RDS instance resides.

  2. Find the read-only RDS instance and click the ID of the instance.

View a read-only RDS instance on the Basic Information page

You can view a read-only RDS instance on the Basic Information page of the primary RDS instance.

  1. Log on to the ApsaraDB RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region in which the read-only RDS instance resides.

  2. Find the primary RDS instance and click the ID of the instance.

  3. On the Basic Information page of the primary RDS instance, move the pointer over the number of read-only RDS instances and click the ID of the read-only RDS instance that you want to view.

View a read-only RDS instance on the Cluster Management page

Prerequisites

The read/write splitting feature is enabled on the Cluster Management page of the primary RDS instance to which the read-only RDS instance is attached. For more information, see Enable read/write splitting.

Procedure

  1. Log on to the ApsaraDB RDS console.

  2. Find the primary RDS instance and click the ID of the instance.

  3. In the left-side navigation pane, click Cluster Management.

  4. Find the read-only RDS instance and click the ID of the instance.

What to do next

FAQ

Can I change the billing method of my read-only RDS instance?

Yes, you can change the billing method of a read-only RDS instance. For more information, see Change the billing method from pay-as-you-go to subscription or Change the billing method from subscription to pay-as-you-go.

After I change the configuration of my read-only RDS instance, release the read-only RDS instance, or change the billing method of the read-only RDS instance, is the primary RDS instance affected?

No, the primary RDS instance is not affected.

After I create accounts on the primary RDS instance of my database system, can I manage the accounts on the read-only RDS instances?

No, you cannot manage the accounts on the read-only RDS instances. The accounts that are created on your primary RDS instance are synchronized to the read-only RDS instances and have only the read permissions on the read-only RDS instances.

If my primary RDS instance fails, can I convert a read-only RDS instance to a regular RDS instance?

No, you cannot convert a read-only RDS instance to a regular RDS instance.

Do read-only RDS instances support manual backup or automatic backup?

No, read-only RDS instances do not support manual backup or automatic backup. Backups are performed on the primary RDS instance. You cannot configure backup settings or manually create backups for read-only RDS instances.

Do read-only RDS instances support parallel replication?

Yes, read-only RDS instances support parallel replication. By default, ApsaraDB RDS for SQL Server uses parallel replication.

What mechanism is used to clear transaction logs?

The transaction logs of an RDS instance are cleared in the following phases:

  • Log truncation: Log truncation is automatically performed for each log backup. If long-running transactions are executed, synchronization waits occur, or kernel-related issues occur, log truncation cannot take effect.

  • Log shrinking: Regular backups include log shrinking. You can also shrink logs in the ApsaraDB RDS console. For more information, see Shrink the transaction logs of an ApsaraDB RDS for SQL Server instance.

How is data replicated to read-only RDS instances? What causes a replication latency and how do I resolve the issue? How do I view a replication latency?

When you create a read-only RDS instance, the system replicates data from the secondary RDS instance to the read-only RDS instance. After the read-only RDS instance is created, it has the same data as the primary RDS instance. If the data on the primary RDS instance is updated, the system automatically synchronizes the updates to all read-only RDS instances that are attached to the primary RDS instance.

The following section describes the causes and solutions:

  • Cause: The specifications of the primary RDS instance are higher than the specifications of the read-only RDS instance. In this case, the replication latency between your primary and read-only RDS instances is large.

    Solution: Upgrade the specifications of the read-only RDS instance. For more information, see Change instance specifications.

  • Cause: Large concurrent transactions exist.

    Solution: Check and process large transactions. In most cases, large transactions are caused by locks, slow queries, and uncommitted temporary queries.

  • Cause: The kernel is abnormal.

    Solution: Upgrade the major engine version or update the minor engine version of your RDS instance. For more information, see Upgrade the major engine version or Update the minor engine version.

You can use autonomy services in ApsaraDB RDS for SQL Server to identify issues and view the replication latency. You can also execute the following statements to view the replication latency. For more information, see Overview of DAS.

SELECT
    ag.name AS [availability_group_name]
    , d.name AS [database_name]
    , ar.replica_server_name AS [replica_instance_name]
    , drs.truncation_lsn
    , drs.log_send_queue_size
    , drs.redo_queue_size
FROM
    sys.availability_groups ag
    INNER JOIN sys.availability_replicas ar
        ON ar.group_id = ag.group_id
    INNER JOIN sys.dm_hadr_database_replica_states drs
        ON drs.replica_id = ar.replica_id
    INNER JOIN sys.databases d
        ON d.database_id = drs.database_id
WHERE drs.is_local=0
ORDER BY
    ag.name ASC, d.name ASC, drs.truncation_lsn ASC, ar.replica_server_name ASC