Migrate end of support Microsoft SQL Server databases to Amazon RDS for SQL Server confidently

Customers running end of support (EOS) Microsoft SQL Server workloads on premises often ask us how they can confidently migrate those workloads to AWS fully managed database services like Amazon Relational Database Service (Amazon RDS) for SQL Server. Amazon RDS for SQL Server makes it simple to set up, operate, and scale SQL Server deployment in the cloud.

Amazon RDS for SQL Server doesn’t allow you to provision any new EOS SQL Server instances. Application owners may find it challenging to upgrade the database engine when moving to the cloud because it might change the database engine optimizer and break the application. In most cases, applications need to go through extensive functionality and performance testing for such major version upgrades.

SQL Server database compatibility level is a feature to help with database modernization by allowing the database engine to be upgraded while keeping the same functional status for connecting applications by maintaining the same pre-upgrade database compatibility level. This means that you can upgrade from an older version of SQL Server (such as SQL Server 2008 or 2012) to Amazon RDS for SQL Server 2014 or higher with minimal to no application changes (except for database connectivity). Running a database in a lower compatibility level than the host database engine is known as backward compatibility.

In this post, we examine a use case in which you migrate your on-premises EOS Microsoft SQL Server 2012 databases to Amazon RDS for SQL Server 2019 using backward compatibility.

Lifecycle dates

Each version of SQL Server from SQL Server 2012 forward typically includes 5 years of mainstream support and 5 years in extended support. Mainstream support includes functional, performance, scalability, and security updates. Extended support includes only security updates. End of support indicates a product has reached end of its lifecycle; servicing and support is no longer available. AWS maintains a separate deprecation schedule for major engine versions of Microsoft SQL Server on Amazon RDS.

Migration overview

The database migration process consists of the following high-level steps:

1. Check the compatibility matrix and choose your target Amazon RDS for SQL Server version.
2. Run an assessment using AWS Schema Conversion Tool (AWS SCT). The AWS SCT assessment report provides server and database metrics for the conversion.
3. Right-size your RDS for SQL Server instance.
4. Optimize your costs.
5. Migrate the database.
6. Optionally, migrate business intelligence (BI) components.

Check the compatibility matrix

As the first step towards your migration to Amazon RDS for SQL Server, check your supported compatibility level using the following table and choose your target Amazon RDS for SQL Server version accordingly.

For example, if you choose to create an RDS for SQL Server 2019 instance, the default engine version is 15 and any new database created on this instance will be with default compatibility 150. But you have the option to restore databases with the lowest backward compatibility of level 100 (SQL Server 2008).

Run an assessment with AWS SCT

As the second step towards your migration to Amazon RDS for SQL Server, run an assessment using AWS SCT. The metrics about your SQL Server instance included in the assessment report include the following:

• Database storage object compatibility
• Database code object compatibility
• Enterprise edition feature usage
• Database mirroring is used
• SQL Server Log shipping is configured
• Failover cluster is used
• Database Mail is configured
• Full Text Search Service is used (Amazon RDS for SQL Server has a limited full text search, and doesn’t support semantic search)
• Data Quality Service (DQS) is installed
• SQL Server Service Broker is used
• Linked Server is used (Amazon RDS for SQL Server has limited support for linked servers)

The following are some sample AWS SCT assessment reports run at the database level.

We recommend also checking Limitations for Microsoft SQL Server DB instances. Refer to Features not supported and features with limited support to learn more on Amazon RDS for SQL Server.

As part of your assessment, if you find that your Amazon RDS for SQL Server migration is blocked due to limited or no support for a critical feature your application is using, consider Amazon RDS Custom for SQL Server. Amazon RDS Custom is a managed database service for legacy, custom, and packaged applications that require access to the underlying operating system and database environment. Amazon RDS Custom for SQL Server automates setup, operation, scaling, and patching of databases in the AWS Cloud while granting you access to the database and underlying operating system.

With Amazon RDS Custom, you get the automation of Amazon RDS and the flexibility of Amazon Elastic Compute Cloud (Amazon EC2). By taking on additional database management responsibilities beyond what you do in Amazon RDS, you can benefit from Amazon RDS automation and the deeper customization of Amazon EC2.

The following table shows the shared responsibility model for Amazon RDS Custom.

Right-size the instance

As the third step towards your migration to Amazon RDS for SQL Server, you need to right-size the RDS for SQL Server instance. Let’s understand how scaling works in Amazon RDS for SQL Server. You can scale your instances by adjusting memory or compute power up or down as performance and capacity requirements change. The following are some key items to consider when scaling a database instance:

• Storage and instance type are decoupled. When you scale your database instance up or down, your storage size remains the same and is not affected by the change.
• You can separately modify your RDS DB instance to increase the allocated storage space or improve the performance by changing the storage type (such as General Purpose SSD to Provisioned IOPS SSD).
• AWS handles the Amazon RDS for SQL Server Microsoft licensing for you, and you pay as you go.
• Determine when you want to apply the change. You have the option to apply it immediately or during the maintenance window specified for the instance.
• Consider a memory optimized instance for production workloads.

Based on the on-premises SQL Server utilization and how scaling works for Amazon RDS for SQL Server, choose the instance type with the CPU and memory needed for your workload. You can always scale up your instance based on forecasts and pay for the additional compute only for that duration.

Optimize costs

Before you create the RDS for SQL Server instance, optimize your cost using the following methods:

• Consider SQL Server 2019 Standard edition (SE) as your target RDS for SQL Server instance. SQL Server 2019 SE includes most of the critical Enterprise edition (EE) features. The following table summarizes the differences between Enterprise and Standard editions. For a detailed comparison, refer to Editions and supported features of SQL Server 2019 (15.x).

• Run the following command at your on-premises SQL Server to find out if any Enterprise edition features are used in your application:

  IF OBJECT_ID('tempdb.dbo.##enterprise_feature_list') IS NOT NULL
    DROP TABLE ##enterprise_feature_list
   
  CREATE TABLE ##enterprise_feature_list
  (
      dbname       SYSNAME,
      feature_name VARCHAR(100),
      feature_id   INT
  )
   
  EXEC sp_MSforeachdb
  N' USE [?] 
      INSERT INTO ##enterprise_feature_list 
      SELECT dbname=DB_NAME(),feature_name,feature_id 
      FROM sys.dm_db_persisted_sku_features 
  '
  SELECT *
  FROM   ##enterprise_feature_list

• If your application fits into SQL Server 2019 SE features except the number of vCPU required by your workloads is higher than 24, consider breaking your target RDS for SQL Server instance into more than one instance and adopt a microservice-based architecture to keep your cost lower.
• Consider grouping multiple smaller RDS for SQL Server instances into one (provided it stays within 24 vCPU and 128 GB memory) to maximize the use of the resources.
• Consider EE only when your workload requires more than 24 vCPU / 128 GB memory on a single SQL Server instance, has a requirement of setting up read replicas or an EE feature used by your application that can’t be removed.
• Consider using reserved instances to help further reduce costs.

Migrate the database

To migrate the database from on-premises to Amazon RDS for SQL Server using backward compatibility, consider native backup and restore. If you have a large number of SQL Server instances to migrate, consider automating the migration using custom log shipping.

The custom log shipping solution uses the following architecture, where it copies the on-premises full and transaction log backups, and restores them on Amazon RDS for SQL Server using backward compatibility.

Migrate BI components (optional)

If your on-premises SQL Server workloads use any BI components like SQL Server Integration services (SSIS), SQL Server Reporting Services (SSRS), or SQL Server Analysis Services (SSAS), you can help reduce your costs by enabling Microsoft SQL Server BI features on Amazon RDS for SQL Server.

For more information about the limitations of SSIS, SSRS, and SSAS in Amazon RDS for SQL Server, refer to the following resources:

• SSIS in Amazon RDS for SQL Server Limitations
• SSRS in Amazon RDS for SQL Server Limitations
• SSAS in Amazon RDS for SQL Server Limitations

If your workload is using any BI features that aren’t supported by Amazon RDS for SQL Server, consider using Amazon RDS Custom for SQL Server.

Validate backward compatibility

To validate that backward compatibility maintains the old cardinality estimation, use one of the migration methods outlined earlier to migrate the database (AdventureWorks2012) from on-premises SQL Server 2012 to Amazon RDS for SQL Server 2019.

Also create a new database (AdventureWorks2019) on the RDS for SQL Server instance matching the database engine compatibility. We use this database to compare the CardinalityEstimationModelVersion values between AdventureWorks2012 and AdventureWorks2019.

To check the compatibility of the databases on the RDS for SQL Server instance, run the following command:

SELECT @@version as DatabaseEngineVersion;

SELECT * FROM sys.databases;

The AdventureWorks2012 database restored using backward compatibility maintains CardinalityEstimationModelVersion 70 like it was running on-premises.

The AdventureWorks2019 database created using database engine compatibility has CardinalityEstimationModelVersion 150 to match SQL Server 2019 default cardinality estimation.

Best practices

Consider the following best practices:

• In most cases, application driver change isn’t needed as you run your database in backward compatible mode. But if you receive a handshake error while connecting your application to the RDS for SQL Server instance, consider creating a new parameter group on the Amazon RDS console for your SQL Server instance and enabling TLS 1.1 and 1.0.
  
• You may download the latest Microsoft ODBC and Microsoft JDBC drivers to use in your application if required.
• If you have an application with tempdb heavy workloads, consider using R5d instance types with tempdb configured to use local instance storage. By placing tempdb data files and log files locally, you can achieve lower read and write latencies when compared to the standard EBS-based offerings.
• Always consider running your production workload in a Multi-AZ setup to ensure high availability and help prevent data loss.
• Consider running UPDATE STATISTICS after the cutover on all the tables using the output from the following code:

USE <database_name> --repeat for each database
go
SELECT 'UPDATE STATISTICS ' + s.name + '.' + o.name + ' WITH ALL;'
FROM sys.objects o
inner join sys.schemas s
on s.schema_id = o.schema_id
WHERE o.type = 'U'
ORDER BY o.name

Summary

In this post, we demonstrated how to migrate any older version (and EOS) of Microsoft SQL Server databases to Amazon RDS for SQL Server 2019 using backward compatibility. Try out Amazon RDS for SQL Server and migrate your EOS SQL Server workload to AWS with confidence.

To determine your options when the database is already running in AWS, refer to Best practices for upgrading SQL Server 2008 R2 to SQL Server 2016 on Amazon RDS for SQL Server.

If you have any comments or feedback, please leave them in the comments section.

About the authors

Rajib Sadhu is Senior Database Specialist Solutions Architect with over 15 years of experience in Microsoft SQL Server and other database technologies. He helps customers architect and migrate their database solutions to AWS. Prior to joining AWS, he supported production and mission-critical database implementation across financial and travel and hospitality industry segments.

Shirin Ali is a Database Consultant with the Professional Services team at Amazon Web Services. She works as a database migration specialist to help Amazon customers migrate their on-premises database environments to AWS Cloud database solutions.