AWS Database Blog

Post-migration steps and best practices for Amazon RDS for SQL Server

Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks, such as hardware provisioning, database setup, patching, and backups. It frees you to focus on your applications, so you can give them the fast performance, high availability, security, and compatibility that they need.

You have migrated SQL Server database to Amazon RDS for SQL Server, a managed database service. Now what’s next?

In this post, we talk about some of the post-migration steps and best practices for Amazon RDS for SQL Server.

Prerequisites

You should have the following prerequisites:

Architecture overview

The following diagram illustrates the post-migration architecture of Amazon RDS for SQL Server. Amazon RDS for SQL Server Multi-AZ deployments provide enhanced availability and durability for RDS database instances. You can use Amazon CloudWatch to monitor RDS SQL Server and send notification using Amazon Simple Notification Service.

After you migrate from your on-premises or Amazon Elastic Compute Cloud (Amazon EC2) SQL Server to Amazon RDS for SQL Server, you need to take steps to make it production ready.

In the following sections, we look at the details of configuration, backup, and monitoring.

Configuration

In this section, we discuss different post configuration options for Amazon RDS for SQL Server.

High availability

Amazon RDS for SQL Server Multi-AZ deployments provide enhanced availability and durability for RDS database (DB) instances, making them a natural fit for production database workloads. When you provision a Multi-AZ DB Instance, Amazon RDS automatically creates a primary DB instance and synchronously replicates the data to a standby instance in a different Availability Zone. Each Availability Zone runs on its own physically distinct, independent infrastructure, and is engineered to be highly reliable.

Based on Recovery Point Objective (RPO) and Recovery Time Objective (RTO), you can configure the high availability for your Amazon RDS for SQL Server.

Tempdb

R5d and M5d instance types provide up to 3,600 GiB of Non-Volatile Memory Express (NVMe) SSD-based instance storage optimized for low latency, very high random I/O performance, and high sequential read throughput. When provisioning either instance type, Amazon RDS for SQL Server automatically places tempdb files on the locally attached NVMe disks, achieving low storage latencies and improving performance for certain workloads by up to 30%.

Make sure to select the instance type based on your need to help you with faster performance.

Parameter groups

For on-premises SQL Server and SQL Server running on EC2 instances, you use sp_configure to make changes to server configuration options such as max server memory and MAXDOP, and trace flags. For Amazon RDS for SQL Server, we can make these changes using DB parameter groups.

You should create custom DB parameter groups and attach them to Amazon RDS for SQL Server. Common parameters to consider include max server memory, optimize for ad hoc workloads, max degree of parallelism, cost threshold for parallelism, and common trace flags like 1211 for lock escalation and 1204 and 1222 for deadlock monitoring.

Storage Auto Scaling

You should monitor IOPS for Amazon RDS for SQL Server and provision storage according to your IOPS requirements. Amazon RDS for SQL Server Auto Scaling automatically scales storage capacity in response to growing database workloads, with zero downtime. If you choose GP2, you can scale up to 16,000 IOPS; and if you choose provisioned IOPS, you can scale up to 64,000 IOPS.

Make sure you’re setting up the appropriate Amazon RDS for SQL Server storage option for your storage. You can scale storage to up to 16 TB.

Compute scaling

You can modify the DB instance to change size and instance type. Size modifications are available within minutes. There is minimal downtime when you’re scaling up on a Multi-AZ environment because the standby RDS for SQL Server instance gets upgraded first, then a failover occurs to the newly sized RDS for SQL Server instance. A Single-AZ instance is unavailable during the scale operation. For more information about scaling, see Scaling Your Amazon RDS Instance Vertically and Horizontally.

SQL Agent jobs

Amazon RDS for SQL Server supports running SQL Agent jobs. It doesn’t support maintenance plans, even though there are alternative solutions such as Ola Hallengren and SentryOne.

Update statistics, run DBCC CHECKDB, and rebuild indexes

You should update statistics, run DBCC CHECKDB, and rebuild indexes prior to first use

Updating statistics can make sure queries are compiled with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance trade-off between improving query plans and the time it takes to recompile queries. The specific trade-offs depend on your application. When running update statistics with full scan, increasing MAXDOP may be required because this is an expensive operation.

SQL Server database corruption can be a major problem, and you need to run DBCC CHECKDB to detect any corruption issues with the database. It’s important to run DBCC CHECKDB first after you migrated to Amazon RDS for SQL Server.

For the query optimizer to generate the best possible query plans and use the right indexes, the indexes shouldn’t be fragmented. Indexes become fragmented over time based on the update, insert, or delete rate. Make sure that tables are re-indexed on a regular basis.

Amazon RDS for SQL Server read replicas

RDS for SQL Server read replicas are available for Amazon RDS for SQL Server running Enterprise Edition for SQL versions 2016–2019. The source instance must also have automated backups turned on to enable RDS for SQL Server read replicas.

You can offload read operations to an RDS for SQL Server read replica. This is set up as a distributed availability group feature, and you can actually use a different instance type than the production instance. You can go up to fifteen replicas per source instance. The source instance must be set up as Multi-AZ. These RDS for SQL Server read replicas can be promoted as new standalone databases.

The first time a read replica is started and accesses an area of disk, the process can take longer than all subsequent accesses to the same disk area. This is known as the first touch penalty. After an area of disk has incurred the first touch penalty, that area of disk doesn’t incur the penalty again for the life of the instance, even if the instance is rebooted or restarted, or the instance class changes. Note that a DB instance created from a snapshot, a point-in-time restore, or an RDS for SQL Server read replica is a new instance and does incur this first touch penalty.

In addition, data is available in the buffer cache after an initial query is run on a new instance. Subsequent queries don’t incur this penalty, but are likely reading the data directly from memory instead of disk.

Set the database compatibility level

When a database is restored or created in Amazon RDS for SQL Server, the current compatibility level may not be the one that is required to run the workload. You should change the database compatibility level to support the workload, instead of using the default Amazon RDS for SQL Server version.

Backup

In this section, we discuss best practices for configuring your backup options.

Database recovery model set to full recovery

To take advantage of point-in-time recovery (PITR), a database has to be in full recovery mode. The model database is already in full recovery on Amazon RDS for SQL Server, so any new databases, whether in a Multi-AZ or Single-AZ deployment, are created with full recovery. When restoring your databases to Amazon RDS for SQL Server, you need to make sure the source database is in full recovery to take advantage of these features.

Configure backups

Amazon RDS for SQL Server takes automatic Amazon Elastic Block Store (Amazon EBS) volume snapshots daily and transactional log backups every 5 minutes. However, database administrators should configure how many days of daily backups need to be kept according to their organizational requirements (default 7 days when deploying the instance through the console with a maximum of 35 days). For more information about automated backups, see Working with backups.

You can take EBS snapshots manually as well, and the 35-day retention policy doesn’t apply to these. Just like with automated EBS snapshots, you can restore these to another instance.

In addition, you can perform SQL Server native backup and restore by enabling the SQLServer_Backup_Restore option group in your Amazon RDS configuration.

Amazon RDS for SQL Server also supports cross-Region PITR, so if clients require their instances to be restored in a different Region to a specific point in time, that is now an option.

Monitor and audit Amazon RDS for SQL Server

Before you start running your database in production, you need to make sure monitoring is set up properly.

Amazon RDS for SQL Server has a few different levels of monitoring that you could set up. You can monitor using CloudWatch, Amazon RDS Performance Insights, and Enhanced Monitoring.

Monitoring is an important part of maintaining the reliability, availability, and performance of Amazon RDS and your AWS solutions. Amazon RDS provides metrics in real time to CloudWatch. CloudWatch is a monitoring service for AWS Cloud resources and the applications you run on AWS. Standard CloudWatch monitoring includes built-in metrics for your DB instance that are visible using the Amazon Management Console, AWS Command Line Interface (AWS CLI), or API for no additional charge.

AWS also gives you the option to enable Enhanced Monitoring for your RDS for SQL Server instance. Enhanced Monitoring is an agent installed directly on the instance and provides additional metrics, increased granularity, and per-process information. The option is available when you create or modify your instance, and doesn’t require a restart. It also differentiates from CloudWatch because it gathers its metrics directly from the instance.

Performance Insights is a database performance tuning and monitoring feature that helps you assess the load on your database. It’s a lightweight tool that doesn’t affect performance and you can choose the time you want to keep the information, with the default value being 7 days. It captures the waits and load on the database and helps you determine what queries are slowing down your system.

Capture events using Amazon RDS event notification and extended events

Amazon RDS for SQL Server uses Amazon Simple Notification Service (Amazon SNS) to provide notification when an Amazon RDS event occurs. These notifications can be in any notification form supported by Amazon SNS for an AWS Region, such as an email, a text message, or a call to an HTTP endpoint.

Amazon RDS event groups can include DB instance events, DB parameter group events, DB security group events, DB snapshot events, and categories such as creation, failure, configuration change, notification, recovery, and security.

For more information about event notifications, see Using Amazon RDS event notification and Setting up Amazon SNS notifications.

You can also set up extended events in Amazon RDS for SQL Server.

Audit using CloudTrail and SQL Server Audit

You can also integrate with AWS CloudTrail to audit API calls. CloudTrail provides a record of actions taken by a user, role, or AWS service in Amazon RDS. For more information, see Working with AWS CloudTrail and Amazon RDS.

SQL Server Audit on Amazon RDS is also available and can be added as an option group. SQL Server Audit uses the built-in SQL Server auditing mechanism, and you can create audits and audit specifications the same way you do on premises.

Conclusion

In this post, we showed you how to set up post-migration optimization for Amazon RDS for SQL Server using best practices and a post-migration checklist.

For more information about administrative migration tasks for Amazon RDS for SQL Server, see

Part 1 – Role of the DBA When Moving to Amazon RDS: Responsibilities.

About the Authors

Yogi Barot is Microsoft Specialist Senior Solution Architect at AWS, she has 22 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth AWS knowledge and expertise in running Microsoft workload on AWS.

Gene Mays is a Database Specialist Solutions Architect with expertise in SQL Server. He has worked with SQL Server for over 15 years and has extensive experience in various industries. He has helped many customers architect high availability and disaster recovery solutions for SQL Server as well as orchestrate large-scale migrations in AWS. Prior to working with AWS, Gene has experience supporting enterprise customers in the financial and health industries.