A MyBase for SQL Server Always On instance is built on top of the Always On availability groups feature and decouples computing and storage. By default, a MyBase for SQL Server Always On instance consists of a primary instance and a read-only instance. You can increase the number of read-only instances as needed. A MyBase for SQL Server Always On instance enables read/write splitting. If you need to split read and write requests, configure a read/write splitting endpoint. This allows ApsaraDB for MyBase to offload read requests from the primary instance to read-only instances. This improves the read performance of the MyBase for SQL Server Always On instance. This topic describes how to use a MyBase for SQL Server Always On instance.

Note

This edition is in public preview. To use this edition, search for and join the DingTalk group 32484333. Then, you can contact technical support to add your account to the whitelist. You can also scan the following QR code to join the DingTalk group.

Always on

Benefits

  • Scale out a MyBase for SQL Server Always On instance to improve the read performance
    You can increase the number of read-only instances. This improves the read performance of your MyBase for SQL Server Always On instance in a linear way. The specifications of the read-only instances can differ from those of the primary instance. Therefore, you can create read-only instances that have higher specifications than the primary instance. This improves the read performance of your MyBase for SQL Server Always On instance.
    Note By default, a MyBase for SQL Server Always On instance contains a read-only instance. Therefore, the read-only instance does not ensure high availability. To ensure high availability, create at least two read-only instances.
  • Flexibly control costs

    The read-only instances are cost-effective and belong to the general-purpose instance family. You can increase the number of read-only instances to offload read requests from the primary instance. This helps you optimize the configuration of your MyBase for SQL Server Always On instance. The read-only instances can have lower specifications than the primary instance. You can use read-only instances of low specifications to process read requests from backend applications. The applications include intelligent analytics applications. This reduces costs.

Scenarios

  • Use read-only instances to offload read requests from the primary instance during peak hours

    MyBase for SQL Server Always On instances are suitable for new retail enterprises. During peak hours, you can increase the number of read-only instances of high specifications to handle traffic spikes. For example, you can use read-only instances to process read requests during Double 11. A MyBase for SQL Server Always On instance enables read/write splitting and traffic throttling. The amount of traffic that a MyBase for SQL Server Always On instance can process increases by multiple times.

  • Offload tasks for data analytics from the primary instance to read-only instances

    Enterprises need to analyze data in an intelligent way. A MyBase for SQL Server Always On instance provides an independent read-only instance for data analytics. This reduces the probability of blocked requests on the primary instance, increases the concurrency of requests, and decreases interruptions to crucial workloads. This makes the service stable.

Prerequisites

A MyBase for SQL Server dedicated cluster is created. For more information, see Create a MyBase for SQL Server dedicated cluster.

A host is added to a MyBase for SQL Server dedicated cluster. For more information, see Add a host to a MyBase for SQL Server dedicated cluster.

Create a MyBase for SQL Server dedicated cluster

Procedure

  1. Log on to the ApsaraDB for MyBase console.
  2. In the top navigation bar, select the region in which you want to create the dedicated cluster.
    Select a region
  3. On the Clusters page, click Create Dedicated Cluster.
    Create a dedicated cluster
  4. In the Create Dedicated Cluster panel, configure the following parameters.
    Create a MyBase for SQL Server dedicated cluster
    Parameter Description
    Engine

    The database engine of the dedicated cluster. Select SQLServer.

    Dedicated Cluster Name The name of the dedicated cluster.
    VPC The virtual private cloud (VPC) to which the dedicated cluster is connected. If you need to create a VPC, log on to the VPC console.
    Note Make sure that you select the desired VPC. After you create a dedicated cluster, you cannot change the VPC to which the dedicated cluster is connected. To connect to another VPC, delete the dedicated cluster and create another dedicated cluster.
    Resource Allocation Policy The policy that defines how to allocate database instances in the dedicated cluster.
    Host Troubleshooting Policy The policy that defines how to handle host failures in the dedicated cluster.
    Grant OS Permissions Specifies whether to enable the feature that allows you to have OS permissions on the host.
    Note If you enable this feature for a MyBase for SQL Server dedicated cluster, you can log on to a host in the dedicated cluster to perform operations. For example, you can upload and download data and install software. Make sure that you select the desired option. After you create a dedicated cluster, you cannot change the setting of Grant OS Permissions.
  5. Grant the permissions on cloud resources to the user that you use to create the dedicated cluster. This step is required only when you create a dedicated cluster for the first time.

    When you create an ApsaraDB for MyBase dedicated cluster for the first time, a message appears in the lower part of the panel. The message prompts you to grant the user the permissions on the elastic network interfaces (ENIs) of Elastic Compute Service (ECS) instances and security groups. Click Authorize Now. On the Cloud Resource Access Authorization page, click Confirm Authorization Policy.

    Confirm authorization
    Note After you authorize the user, the AliyunRDSDedicatedHostGroupRole role appears on the RAM Roles page in the Resource Access Management (RAM) console. If you need to use ApsaraDB for MyBase dedicated clusters, retain the role. If the role is deleted, you must authorize the user again to create a dedicated cluster.
  6. Click OK.

Add hosts to the MyBase for SQL Server dedicated cluster

Before you create a MyBase for SQL Server Always On instance, add at least two hosts that use the same storage type.

Procedure

  1. Find the dedicated cluster that you want to manage and click Details in the Actions column.
  2. In the left-side navigation tree, click Hosts. Then, click Add Host.
  3. Configure the following parameters.
    Parameter Description
    Product Type Select Dedicated Cluster.
    Note When you purchase hosts that use enhanced SSDs (ESSDs), you pay only for the computing fees. Therefore, when you create an instance on a host, you must pay for the used storage of ESSDs. A storage plan is a subscription plan for storage resources. If you subscribe to a storage plan in a region, you can use the storage plan to offset the used storage of the pay-as-you-go instances in the same region. This helps you reduce storage costs.
    Region The region in which the host is added. Select the region in which the dedicated cluster is deployed.
    Dedicated Cluster The dedicated cluster to which the host is added.
    Zone The zone in which the host is added. We recommend that you add hosts across zones to ensure high availability.
    Host Image The OS image of the host. Select Windows (SQL Server EE Alwayson).
    Storage Type The storage type of the host. Only Enhanced SSD (ESSD) is supported.
    Host Instance Type The instance type of the host.
    Network Type The network type of the host. By default, the value is VPC.
    VSwitch The vSwitch to which the host is connected. If no option is available, create a vSwitch as instructed.
    Quantity The number of hosts that you want to add. For more information, see Requirements for the number of hosts.
    Duration The subscription duration of the host. You can select Auto-renewal. The auto-renewal feature prevents the host from being released due to expiration. If this feature is enabled, the system automatically renews the host based on the specified subscription duration. For example, if you specify a three-month subscription duration, the system renews the host once every three months.
  4. Click Buy Now. On the Confirm Order page, read and agree to the terms of service, and click Pay to complete the payment.

Create a MyBase for SQL Server Always On instance

Procedure

  1. Find the dedicated cluster that you want to manage and click Details in the Actions column.
  2. In the left-side navigation tree, click Instances. Then, click Create Instance.
    Create an instance
  3. Configure the following parameters.
    Parameter Description
    Database Engine The database engine on which the instance runs. Select SQLServer. After an instance is created, you cannot change the database engine for the instance.
    Current Version The version of the database engine on which the instance runs. Select 2019 EE Always On or 2017 EE Always On.
    Edition The edition of the instance. Select Always On.
    Storage Type The storage type of the instance.
    Instance Type The type of the instance.
    Storage Capacity The storage of the instance.
    Deployment Method You can select Default Policy or Specified Host.
    Note
    • If you select Default Policy, Distribution Policy is set to Balance. This indicates that new database instances are preferentially created on hosts that have the most available resources.
    • If you select Specified Host, configure Primary Host and Secondary Host.
      • The primary instance of a MyBase for SQL Server Always On instance is deployed on the primary host.
      • The read-only instances of a MyBase for SQL Server Always On instance are deployed on the secondary host.
    VSwitch The vSwitch to which the host is connected. If no option is available, create a vSwitch as instructed.
    Note This parameter is available only if you specify Default Policy as Deployment Method.
    Billing Method Select Pay-As-You-Go. This specifies that you are charged after you use the service.
    Note When you purchase hosts that use ESSDs, you pay only for the computing fees. Therefore, when you create an instance on a host, you must pay for the used storage of ESSDs. A storage plan is a subscription plan for storage resources. If you subscribe to a storage plan in a region, you can use the storage plan to offset the used storage of the pay-as-you-go instances in the same region. This helps you reduce storage costs.
  4. Click Create Now. On the page that appears, complete the payment.

View the private and public endpoints and port numbers

Procedure

  1. Find the dedicated cluster that you want to manage and click Details in the Actions column.
  2. In the left-side navigation tree, click Instances.
  3. Find the instance that you want to manage and click Details in the Actions column. You are redirected to the details page of the instance in the ApsaraDB RDS console.
  4. In the left-side navigation pane, click Database Connection. On the right side of the page, you can view the private endpoint and the public endpoint of the primary instance, the read/write splitting endpoint, and port numbers.
    Note
  5. Optional. Apply for a public endpoint for the primary instance.

    In the left-side navigation pane, click Database Connection. On the right side of the page, click Apply for Public Endpoint. In the dialog box that appears, click OK.

  6. Optional. Modify the endpoint of the primary instance.
    1. In the left-side navigation pane, click Database Connection. On the right side of the page, click Change Endpoint. In the dialog box that appears, configure Connection Type.
      Note The Change Endpoint button is available only after you apply for a public endpoint.
    2. Configure Connection Type and Port.
      Note
      • The prefix of an endpoint must be 8 to 64 characters in length and can contain only letters, digits, and hyphens (-). The prefix must start with a lowercase letter.
      • The port number must be within the range of 1000 to 5999.
    3. Click OK.
  7. Optional. Release the public endpoint of the primary instance.

    In the left-side navigation pane, click Database Connection. On the right side of the page, click Release Public Endpoint. In the dialog box that appears, click OK.

Configure a read/write splitting endpoint to enable read/write splitting

If you need to split read and write requests, configure a read/write splitting endpoint. The read/write splitting feature allows ApsaraDB for MyBase to route write requests to the primary instance and read requests to the read-only instances. The read requests are routed based on the read weights of the read-only instances. You can increase the number of read-only instances for a MyBase for SQL Server Always On instance. For more information, see Add a read-only instance.

Procedure

  1. Find the dedicated cluster that you want to manage and click Details in the Actions column.
  2. In the left-side navigation tree, click Instances.
  3. Find the instance that you want to manage and click Details in the Actions column. You are redirected to the details page of the instance in the ApsaraDB RDS console.
  4. Click Cluster management. On the right side of the page, click Enable now.
  5. In the Configure Readonly Splitting Address dialog box, configure the following parameters.
    Parameter Description
    Network Type
    • Intranet address (VPC): SQL Server 2017 Enterprise Edition Always On and SQL Server 2019 Enterprise Edition Always On support only VPCs.
    • Internet Address: the public endpoint that is used to connect to the instance over the Internet. However, the Internet is prone to fluctuations. We recommend that you use a private endpoint to connect to the instance.
    Weight Distribution

    The method that is used to assign read weights. A higher read weight indicates more read requests to process. For example, a MyBase for SQL Server Always On instance has three read-only instances whose read weights are 100, 200, and 200. In this case, the read requests are routed to the three read-only instances at the ratio of 1:2:2.

    • Automatic Distribution: ApsaraDB for MyBase assigns a read weight to each read-only instance based on the instance specifications. After you add a read-only instance, ApsaraDB for MyBase automatically assigns a read weight to the read-only instance and routes read requests to the instance based on the assigned read weight.
    • Customized Distribution: You can customize the read weight of a read-only instance. The weight can range from 0 to 10000 and must be a multiple of 100. After you add a read-only instance, ApsaraDB for MyBase sets the read weight of the created read-only instance to 0. You must manually modify the read weight of the created read-only instance.
    Note
    • If the primary instance fails or the synchronization between the primary instance and read-only instances times out, the read weights automatically become invalid. After you restore the primary instance, the read weights become valid again.
    • After you release the primary instance, the read weights of the read-only instances become invalid.

Add a read-only instance

If you need to process few write requests and a large number of read requests, you can add read-only instances. This improves the read performance of your MyBase for SQL Server Always On instance.

For more information about read-only instances, see Overview of read-only ApsaraDB RDS for SQL Server instances.

Procedure

  1. Find the dedicated cluster that you want to manage and click Details in the Actions column.
  2. In the left-side navigation tree, click Instances.
  3. Find the instance that you want to manage and click Details in the Actions column. You are redirected to the details page of the instance in the ApsaraDB RDS console.
  4. In the left-side navigation pane, click Basic Information. On the page that appears, find the Distributed by Instance Role section and click Add Read-only Instance next to Read-only Instance.
  5. Configure the following parameters.
    Parameter Description
    Storage Type

    The storage type of the read-only instance.

    • Standard SSD: A standard SSD is an elastic block storage device that is designed based on the distributed storage architecture. You can store data on standard SSDs. This decouples computing and storage.
    • Enhanced SSD: An ESSD is an ultra-high performance disk that is developed by Alibaba Cloud based on the next-generation distributed block storage architecture. An ESSD uses 25 GE and remote direct memory access (RDMA) technologies. This reduces the one-way latency and delivers up to 1,000,000 random IOPS. Supported ESSDs come in the following three performance levels (PLs):
      • Enhanced SSD: An ESSD of PL1 is a standard ESSD.
      • ESSD PL2: An ESSD of PL2 delivers the IOPS and throughput that are about twice higher than those delivered by an ESSD of PL1.
      • ESSD PL3: An ESSD of PL3 delivers the IOPS that is 20 times higher than the IOPS delivered by an ESSD of PL1. An ESSD of PL3 also delivers the throughput that is 11 times higher than the throughput delivered by an ESSD of PL1. If you need to process concurrent I/O requests and reduce the read/write latency, use ESSDs of PL3. This ensures high performance and makes the service stable.

    For more information, see Storage types.

    Instance Type The type of the read-only instance.
    Note To ensure I/O performance for data synchronization, we recommend that you select at least the same instance type as the primary instance for the read-only instance. In this scenario, the instance type indicates the memory specifications.
    Storage Capacity The storage of the read-only instance. Configure this parameter based on the used storage of the MyBase for SQL Server Always On instance.
    Network Type By default, the value is VPC. SQL Server 2017 Enterprise Edition Always On and SQL Server 2019 Enterprise Edition Always On support only VPCs. No other options are available.
    Zone The zone in which the read-only instance is deployed. Select the zone in which the primary instance is deployed. No other options are available.
    VPC The VPC to which the read-only instance is connected. Select the VPC to which the primary instance is connected. No other options are available.
    VSwitch The vSwitch to which the read-only instance is connected. Select the vSwitch to which the primary instance is connected.
    Note You can also create a vSwitch.
  6. Click OK.
    Note For information about how to view read-only instances, see View a read-only RDS instance.

References