Thursday, August 18, 2022
No menu items!
HomeDatabase ManagementMigrate end of support Microsoft SQL Server databases to Amazon RDS for...

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.

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

Migration overview

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
Application optimization
Customer
Customer
Customer
Scaling
Customer
AWS
Shared
High availability
Customer
AWS
Customer
Database backups
Customer
AWS
Shared
Database software patching
Customer
AWS
AWS
Database software install
Customer
AWS
AWS
OS patching
Customer
AWS
AWS
OS installation
Customer
AWS
AWS
Server maintenance
AWS
AWS
AWS
Hardware lifecycle
AWS
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.

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).
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 indexing
Yes
No
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)
Contained database
Yes
Yes
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.

Read MoreAWS Database Blog

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments