AWS Database Blog

Implement high availability in Amazon RDS for SQL Server Web Edition using block-level replication

Amazon Relational Database Service (Amazon RDS) for SQL Server has enhanced SQL Server 2022 Web Edition by introducing high availability through block-level replication in Multi-AZ deployments. Previously, high availability features were limited to Enterprise and Standard Editions through technologies like Always On availability groups or Database Mirroring. This new capability brings enterprise-grade availability to SQL Server Web Edition, which is designed for Web hosters and Web value-added providers (VAPs) to host public and Internet-accessible web pages, websites, web applications, and web services.

With this release, you can quickly set up and maintain highly available databases while significantly reducing operational overhead. In this post, we discuss the benefits of block-level replication and how to get started. For more information, see Licensing Microsoft SQL Server on Amazon RDS.

Solution overview

Multi-AZ deployments in Amazon RDS achieve high availability by maintaining a synchronized standby database instance. While Amazon RDS for SQL Server has historically supported various replication technologies, this new solution specifically leverages block-level replication to synchronize storage between primary and standby instances. This approach provides a more streamlined and efficient method for maintaining database redundancy.

Supported versions for Multi-AZ on SQL Web edition:

  • SQL Server 2022: Web Edition 16.00.4215.2.v1 and above

Note that for Microsoft SQL Server Web Edition, only new DB instances created or upgraded to 16.00.4215.2.v1 or higher versions are eligible for Multi-AZ deployments using block level replication.

Refer to the RDS documentation to stay updated with the latest version support.

In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. The time it takes for a failover to complete depends on the database activity and other conditions at the time the primary DB instance became unavailable. Failover times are typically 60 –120 seconds. However, large transactions or a lengthy recovery process can increase failover time.

The primary instance’s data is synchronously replicated at the block level to the standby instance, providing complete data redundancy including server-level objects and configurations. During an unplanned service disruption, Amazon RDS automatically fails over to the standby instance. Your database operations can resume with minimal interruption because the same DNS endpoint is maintained throughout the failover process, alleviating the need to reconfigure application connection strings.

You can create a new RDS SQL Server Web Edition database instance with Multi-AZ option or modify an existing Web Edition database instance from Single-AZ to Multi-AZ, by using the AWS Management Console, AWS Command Line Interface (AWS CLI), or AWS SDKs

Prerequisites

To get started, you must have the following resources:

This solution requires new AWS resources and will incur costs. Review AWS Pricing before implementation.

We strongly recommend testing this setup in a non-production environment and performing complete validation before production deployment.

Create Multi-AZ DB instance

Before you create your DB instance, verify the AWS Region and version availability to make sure the combination of SQL Server editions and versions are enabled for you in the Region where you want to host the RDS instance.

To create your Multi-AZ Amazon RDS instance, complete the following steps:

  1. On the Amazon RDS console, choose Databases in the navigation pane.
  2. Choose Create database.
  3. Choose Standard create.
  4. Configure the basic settings:
  5. For Engine type, choose Microsoft SQL Server.
  6. For Database management type, choose Amazon RDS.
  7. For Edition, choose SQL Server Web Edition.
  8. For Version, choose 16.00.4215.2.v1
  9. For Instance identifier, enter a name for your instance.
  10. Under Settings, configure the following:
  11. Enter your primary user name and password.
  12. Specify the DB instance class as needed.
  13. Set the storage type and size.
  14. Under Availability & durability, for Multi-AZ deployment, select Yes (Block Level Replication).
  15. Under Configure connectivity, choose your virtual private cloud (VPC) and subnet group.
  16. For Public access, select No.
  17. For VPC security group, choose existing and choose the security group you created.
  18. Choose Create database.

Wait for Amazon RDS to provision the Multi-AZ Amazon RDS instance.

Alternatively, you can deploy a Multi-AZ instance using the AWS CLI.Use the following command to create an RDS for SQL Server Web edition instance using the AWS CLI on Windows:

aws rds create-db-instance ^
--db-instance-identifier sqlserver-web-demo ^
--engine sqlserver-web ^
--engine-version 16.00.4215.2.v1 ^
--license-model license-included ^
--master-username master ^
--master-user-password password ^
--db-instance-class db.m5d.4xlarge 
--allocated-storage 16000 ^
--storage-type io2 
--iops 64000 ^
--multi-az ^
--storage-encrypted ^
--region us-east-1

Use the following command to create an RDS for SQL Server Web edition instance using the AWS CLI on MacOS/Linux:

aws rds create-db-instance \
 --db-instance-identifier sqlserver-web-demo \
--engine sqlserver-web \
--engine-version 16.00.4215.2.v1 \
--license-model license-included \
--master-username master \
--master-user-password password \
--db-instance-class db.m5d.4xlarge \
--allocated-storage 16000 \
--storage-type io2 \
--iops 64000 \
--multi-az \
--storage-encrypted \
--region us-east-1

Connect and verify configuration

Complete the following steps to verify your configuration:

  1. Connect to your Amazon EC2 instance using Session Manager
  2. Launch SSMS and connect with the following information:
    1. Server name: Your Amazon RDS for SQL Server instance endpoint
    2. Authentication: SQL Server authentication
    3. Login credentials: As specified during creation
  3. Open a new query window and run the following query to create a database and table: 
    USE master;
GO

CREATE DATABASE MAZDB;
GO

USE MAZDB;
GO

CREATE TABLE dbo.test(
ID int identity(1,1) primary key,
[Desc] char(100)
)
GO

INSERT INTO dbo.test VALUES('RDS MAZ')
GO 50

SELECT COUNT (*) FROM dbo.test;
GO

You will see the database being created and data inserted into the table with 50 records.

Perform a failover

You can test the failover functionality using the console. The failover process promotes the secondary node as the new primary instance. You will notice the change of the primary Availability Zone as well. Complete the following steps:

  1. On the Amazon RDS console, select your instance.
  2. On the Actions menu, choose Reboot.
  3. Choose Reboot with failover.
  4. Confirm the reboot.

The reboot with failover operation will cause a brief downtime. Schedule this during off-peak hours to minimize impact on your applications.

Alternatively, you can initiate a failover using the AWS CLI:

aws rds reboot-db-instance \
--db-instance-identifier sqlserver-web-demo \
--region us-east-1 \
--force-failover

Verify the post-failover status

After you complete the failover process, you can connect to the new primary instance to verify the database that was created earlier is in the available state and you can query it. Complete the following steps:

  1. Remote Desktop to your Windows EC2 instance (if the previous connection closed).
  2. In the search window, search for SSMS, choose Connect, and choose Database Engine.
  3. For Server name, enter the RDS for SQL Server endpoint.
  4. Enter the login and password details you specified when you created the RDS for SQL Server instance.
  5. Choose Connect.
  6. Open a new query window and verify data accessibility: 
    USE MAZDB;
GO
SELECT COUNT (*) FROM dbo.test;
GO

You should see 50 rows returned.

It takes a few minutes for the console to reflect the new Availability Zone.

Limitations

For more information about the limitations of this solution, refer to Limitations for Microsoft SQL Server DB instances.

Clean up

To avoid ongoing charges, delete the resources you created as part of this post:

  1. Delete your RDS instance.
  2. Delete your EC2 instance.

Conclusion

In this post, we showed how to set up a Multi-AZ RDS for SQL Server Web Edition instance and validate its high availability capabilities through failover testing.

This new feature simplifies the process of implementing high availability for your databases while reducing operational overhead. By minimizing the need for manual configuration and maintenance, you can better meet your business continuity and application availability requirements.

We encourage you to implement this solution in your AWS account. If you have questions or want to share your experience, Leave a comment.

About the Authors

Ram Yellapragada

Ram Yellapragada

Ram is a Senior Database Engineer in the Amazon RDS team. He has been with AWS for over 5 years. He works on RDS product development and among other things, is focused on Multi-AZ and Durability features. Prior to this, he has extensive experience in consulting with customers in various verticals to architect, develop and deploy complex database solutions in AWS cloud.

Nirupam Datta

Nirupam Datta

Nirupam is a Sr. Technical Account Manager at AWS. He has been with AWS for over 5 years. With over 13 years of experience in database engineering and infra-architecture, Nirupam is also a subject matter expert in the Amazon RDS core systems and Amazon RDS for SQL Server. He provides technical assistance to customers, guiding them to migrate, optimize, and navigate their journey in the AWS Cloud.

Shirin Ali

Shirin Ali

Shirin is a Senior Database Specialist Solutions Architect at Amazon Web Services. She helps customers architect and migrate their database solutions to AWS. Prior to joining AWS, she supported production and mission-critical database implementation across energy and education industry segments.