High Availability and Disaster Recovery (HADR) in SQL Server on AWS

High Availability and Disaster Recovery (HADR) play a vital role in maintaining the integrity of data, reducing downtime, and safeguarding against data loss in enterprise database systems. AWS offers a range of HADR options for SQL Server, which leverage the powerful capabilities of SQL Server along with the scalability and flexibility of AWS. In this article, we will guide you through the process of configuring HADR for SQL Server on AWS, providing practical code examples for setting up and recovering a SQL Server database.

1. Setting up SQL Server on AWS

Launch an EC2 Instance With SQL Server

1. Initiate the deployment of an EC2 instance: Utilize the AWS Management Console to commence the deployment of an EC2 instance by selecting the suitable SQL Server AMI (Amazon Machine Image). Opt for the instance type that aligns with your specific performance criteria.
2. Set up security groups: Guarantee that the security groups permit the essential inbound and outbound traffic to facilitate seamless SQL Server communication.

aws ec2 run-instances \

    --image-id ami-0abcdef1234567890 \

    --instance-type t3.large \

    --key-name MyKeyPair \

    --security-group-ids sg-0123456789abcdef0 \

    --subnet-id subnet-6e7f829e

Please note that AWS provides a free tier and I am using a free tier here.

Install and Configure SQL Server

1. Connect to the EC2 instance: Use SSH to connect to your EC2 instance.

ssh -i MyKeyPair.pem ec2-user@ec2-12-34-56-78.compute-1.amazonaws.com

2. Install SQL Server: If not pre-installed, download and install SQL Server.

sudo yum install -y https://packages.microsoft.com/config/rhel/7/prod.repo

sudo yum install -y mssql-server

3. Configure SQL Server: Run the setup to configure the SQL Server.

sudo /opt/mssql/bin/mssql-conf setup

4. Verify installation: Ensure SQL Server is running.

systemctl status mssql-server

2. High Availability (Always On Availability Groups)

What Is High Availability?

SQL Server's High Availability (HA) feature guarantees the continuous accessibility and functionality of databases, even in the face of hardware or software failures. This is particularly crucial for critical applications where any downtime can significantly disrupt business operations. Always On Availability Groups (AG) in SQL Server provide a robust HA solution that offers enterprise-level protection and automated failover capabilities.

Utilizing Always On Availability Groups allows a group of databases to failover together, ensuring consistency across related databases. Each availability group comprises primary and secondary replicas. The primary replica handles all read-write operations, while secondary replicas can be configured to support read-only operations and backup jobs, thereby optimizing resource utilization.

In AWS, HA is further enhanced by deploying SQL Server instances across multiple Availability Zones (AZs). This multi-AZ deployment guarantees that even if an entire data center experiences an outage, the secondary replicas in other AZs can seamlessly take over with minimal downtime. To facilitate smooth failover, AWS Elastic Load Balancer (ELB) can be employed to redirect traffic to the new primary replica.

To implement HA, it is necessary to enable Always On Availability Groups in SQL Server, configure the required endpoints, and set up the replicas. Regular monitoring and maintenance of the AGs, combined with AWS's resilient infrastructure, ensure that your SQL Server databases achieve high availability, thereby minimizing the risk of downtime and data loss.

Step 1: Enable Always On Availability Groups

1. Open SQL Server Configuration Manager and enable Always On Availability Groups.

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'availability groups', 1;

RECONFIGURE;

2. Restart the SQL Server to apply the changes.

sudo systemctl restart mssql-server

Step 2: Create and Configure Availability Groups

Note: After launching EC2 and installing SQL Server, you are able to utilize any Integrated Development Environment (IDE) to operate the system and establish a database.

1. Create a database for the availability group.

CREATE DATABASE TestDB;

2. Back up the database and transaction log.

BACKUP DATABASE TestDB TO DISK = '/var/opt/mssql/data/TestDB.bak';

BACKUP LOG TestDB TO DISK = '/var/opt/mssql/data/TestDB_Log.bak';

3. Create the availability group.

CREATE AVAILABILITY GROUP AG_TestDB

    FOR DATABASE TestDB

    REPLICA ON 

        'PrimaryReplica' WITH (

            ENDPOINT_URL = 'TCP://PrimaryReplica:5022',

            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

            FAILOVER_MODE = AUTOMATIC

        ),

        'SecondaryReplica' WITH (

            ENDPOINT_URL = 'TCP://SecondaryReplica:5022',

            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

            FAILOVER_MODE = AUTOMATIC

        )

    LISTENER 'AGListener' (WITH IP (('10.0.0.10', 1433)));

Step 3: Add Secondary Replicas

1. Join the secondary replicas to the availability group.

ALTER AVAILABILITY GROUP AG_TestDB JOIN;

2. Restore the database on secondary replicas.

RESTORE DATABASE TestDB FROM DISK = '/var/opt/mssql/data/TestDB.bak' WITH NORECOVERY;

RESTORE LOG TestDB FROM DISK = '/var/opt/mssql/data/TestDB_Log.bak' WITH NORECOVERY;

3. Join the database of the availability group.

ALTER DATABASE TestDB SET HADR AVAILABILITY GROUP = AG_TestDB;

Disaster Recovery (Log Shipping)

Step 1: Configure Log Shipping

1. Enable log shipping on the primary database.

EXEC sp_add_log_shipping_primary_database

    @database = N'TestDB',

    @backup_directory = N'/var/opt/mssql/data/',

    @backup_retention_period = 4320,

    @monitor_server = N'MonitorServer';

2. Add a secondary database to log shipping.

EXEC sp_add_log_shipping_secondary_database

    @primary_server = N'PrimaryServer',

    @primary_database = N'TestDB',

    @secondary_server = N'SecondaryServer',

    @restore_delay = 0,

    @restore_mode = 1,

    @disconnect_users = 0,

    @block_size = 65536,

    @buffer_count = 5,

    @max_transfer_size = 0,

    @load_delay = 0,

    @copy_delay = 0,

    @copy_job_id = NULL,

    @restore_job_id = NULL;

Step 2: Monitor and Manage Log Shipping

1. Monitor log shipping status.

EXEC sp_help_log_shipping_monitor;

2. Test the log shipping by performing a failover.

RESTORE DATABASE TestDB WITH RECOVERY;

4. Recovery Process

Step 1: Failover To Secondary Replica

1. Initiate failover to the secondary replica in case of primary failure.

ALTER AVAILABILITY GROUP AG_TestDB FAILOVER;

Step 2: Restore Backups

1. Restore the database from the latest backup on a new server.

RESTORE DATABASE TestDB FROM DISK = '/var/opt/mssql/data/TestDB.bak' WITH RECOVERY;

2. Reconfigure the new server as part of the availability group.

ALTER AVAILABILITY GROUP AG_TestDB ADD REPLICA ON 'NewPrimaryReplica'

    WITH (ENDPOINT_URL = 'TCP://NewPrimaryReplica:5022');

These are the optimal methods I adhere to to establish a connection and recover in the event of failover. While I am providing an example here, this is based on actual real-time discoveries I made during my development process.

To establish and oversee High Availability and Disaster Recovery for SQL Server on AWS, it is essential to follow these procedures. This will guarantee the resilience and accessibility of your databases, even in the event of unforeseen failures.

In conclusion, the implementation of High Availability and Disaster Recovery (HADR) in SQL Server on AWS necessitates the configuration of Always Availability Groups for high availability and log shipping for disaster recovery. By utilizing these powerful features, you can guarantee that your SQL Server databases are readily available and can swiftly recover from disasters, thereby reducing downtime and minimizing data loss.