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.
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.
|Version||Release Year||Mainstream Support End Year||Extended Support End Date|
|SQL Server 2019||2019||2025||2030-01-08|
|SQL Server 2017||2017||2022||2027-10-12|
|SQL Server 2016||2016||2021||2026-07-14|
|SQL Server 2014||2014||2019||2024-07-09|
|SQL Server 2012||2012||2017||2022-07-12|
|SQL Server 2008 R2||2010||2012||2019-07-09|
|SQL Server 2008||2008||2012||2019-07-09|
|SQL Server 2005||2006||2011||2016-04-12|
|SQL Server 2000||2000||2005||2013-04-09|
The database migration process consists of the following high-level steps:
- Check the compatibility matrix and choose your target Amazon RDS for SQL Server version.
- Run an assessment using AWS Schema Conversion Tool (AWS SCT). The AWS SCT assessment report provides server and database metrics for the conversion.
- Right-size your RDS for SQL Server instance.
- Optimize your costs.
- Migrate the database.
- 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).
|Product||Database Engine Version||Default Compatibility Level Designation||Supported Compatibility Level Values|
|SQL Server 2019 (15.x)||15||150||150, 140, 130, 120, 110, 100|
|SQL Server 2017 (14.x)||14||140||140, 130, 120, 110, 100|
|SQL Server 2016 (13.x)||13||130||130, 120, 110, 100|
|SQL Server 2014 (12.x)||12||120||120, 110, 100|
|SQL Server 2012 (11.x)||11||110||110, 100, 90|
|SQL Server 2008 R2||10.5||100||100, 90, 80|
|SQL Server 2008||10||100||100, 90, 80|
|SQL Server 2005 (9.x)||9||90||90, 80|
|SQL Server 2000 (8.x)||8||80||80|
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.
|Feature||Amazon EC2 Responsibility||Amazon RDS Responsibility||Amazon RDS Custom for SQL Server Responsibility|
|Database software patching||Customer||AWS||AWS|
|Database software install||Customer||AWS||AWS|
|Power, network, and cooling||AWS||AWS||AWS|
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.
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).
|Functional Area||Feature||Enterprise Edition||Standard Edition|
|Scale limits||Maximum number of processors or cores per instance||OS maximum||4 sockets or 24 cores|
|Maximum memory used per instance||OS maximum||128 GB (SQL Server 2016 and later)|
|Maximum memory-optimized data size per database||Unlimited memory (SQL Server 2014 and later)||32 GB (SQL Server 2016 and later)|
|High availability||Always On availability groups||Yes||Basic availability groups (SQL Server 2016 and later)|
|Online schema change||Yes||No|
|Resumable online index rebuilds||Yes (SQL Server 2017 and later)||No|
|Accelerated database recovery (ADR)||Yes (SQL Server 2019)||Yes (SQL Server 2019)|
|Scalability and performance||Table and index partitioning||Yes||Yes (SQL Server 2016 SP1 and later)|
|Data compression||Yes||Yes (SQL Server 2016 SP1 and later)|
|Partition table parallelism||Yes||Yes (SQL Server 2016 SP1 and later)|
|In-Memory OLTP||Yes (SQL 2014 and later)||Yes (SQL Server 2016 SP1 and later)|
|Delayed durability||Yes (SQL 2014 and later)||Yes (SQL 2014 and later)|
|Columnstore||Yes (SQL Server 2016 SP1 and later)||Yes (SQL Server 2016 SP1 and later)|
|Interleaved execution for multi-statement table valued functions||Yes (SQL Server 2017 and later)||Yes (SQL Server 2017 and later)|
|Intelligent Database: batch mode on rowstore||Yes (SQL Server 2019)||No|
|Intelligent Database: approximate count distinct||Yes (SQL Server 2019)||Yes (SQL Server 2019)|
|Intelligent Database: table variable deferred compilation||Yes (SQL Server 2019)||Yes (SQL Server 2019)|
|Intelligent Database: scalar UDF inlining||Yes (SQL Server 2019)||Yes (SQL Server 2019)|
|Security||Transparent database encryption (TDE)||Yes||Yes (SQL Server 2019)|
- Run the following command at your on-premises SQL Server to find out if any Enterprise edition features are used in your application:
- 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
To check the compatibility of the databases on the RDS for SQL Server instance, run the following command:
AdventureWorks2012 database restored using backward compatibility maintains
CardinalityEstimationModelVersion 70 like it was running on-premises.
AdventureWorks2019 database created using database engine compatibility has
CardinalityEstimationModelVersion 150 to match SQL Server 2019 default cardinality estimation.
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
tempdbconfigured 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:
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.