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

{"value":"Customers running end of support (EOS) Microsoft SQL Server workloads on premises often ask us how they can confidently migrate those workloads to Amazon Web Services fully managed database services like [Amazon Relational Database Service (Amazon RDS) for SQL Server](https://aws.amazon.com/rds/sqlserver/). Amazon RDS for SQL Server makes it simple to set up, operate, and scale SQL Server deployment in the cloud.\n\nAmazon 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.\n\nSQL Server database [compatibility level](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16) 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](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16#backwardCompat).\n\nIn 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.\n\n### **Lifecycle dates**\n\nEach 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](https://docs.microsoft.com/en-us/sql/sql-server/end-of-support/sql-server-end-of-support-overview?view=sql-server-ver16) indicates a product has reached end of its lifecycle; servicing and support is no longer available. Amazon Web Services maintains a separate [deprecation schedule](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.Deprecated-Versions) for major engine versions of Microsoft SQL Server on Amazon RDS.\n\n\n\n### **Migration overview**\n\nThe database migration process consists of the following high-level steps:\n\n1. Check the compatibility matrix and choose your target Amazon RDS for SQL Server version.\n2. Run an assessment using [Amazon Web Services Schema Conversion Tool](https://aws.amazon.com/dms/schema-conversion-tool/?nc=sn&loc=2) (Amazon Web Services SCT). The Amazon Web Services SCT [assessment report](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServer.ToRDSSQLServer.html) provides server and database metrics for the conversion.\n3. Right-size your RDS for SQL Server instance.\n4. Optimize your costs.\n5. Migrate the database.\n6. Optionally, migrate business intelligence (BI) components.\n\n### **Check the compatibility matrix**\n\nAs the first step towards your migration to Amazon RDS for SQL Server, check your supported [compatibility level](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16#arguments) using the following table and choose your target Amazon RDS for SQL Server version accordingly.\n\nFor 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).\n\n\n\n### **Run an assessment with Amazon Web Services SCT**\n\nAs the second step towards your migration to Amazon RDS for SQL Server, run an assessment using Amazon Web Services SCT. The metrics about your SQL Server instance included in the assessment report include the following:\n\n- Database storage object compatibility\n- Database code object compatibility\n- Enterprise edition feature usage\n- Database mirroring is used\n- SQL Server Log shipping is configured\n- Failover cluster is used\n- Database Mail is configured\n- Full Text Search Service is used (Amazon RDS for SQL Server has a limited full text search, and doesn’t support semantic search)\n- Data Quality Service (DQS) is installed\n- SQL Server Service Broker is used\n- Linked Server is used (Amazon RDS for SQL Server has limited support for linked servers)\n\nThe following are some sample Amazon Web Services SCT assessment reports run at the database level.\n\n\n\n\n\nWe recommend also checking [Limitations for Microsoft SQL Server DB instances](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.FeatureSupport.Limits). Refer to [Features not supported and features with limited support](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.FeatureNonSupport) to learn more on Amazon RDS for SQL Server.\n\nAs 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/working-with-custom-sqlserver.html). [Amazon RDS Custom](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-custom.html) 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 Amazon Web ServicesCloud while granting you access to the database and underlying operating system.\n\nWith Amazon RDS Custom, you get the automation of Amazon RDS and the flexibility of [Amazon Elastic Compute Cloud](http://aws.amazon.com/ec2) (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.\n\nThe following table shows the shared responsibility model for Amazon RDS Custom.\n\n\n\n### **Right-size the instance**\n\nAs 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:\n\n- 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.\n- 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).\n- Amazon Web Services handles the Amazon RDS for SQL Server Microsoft licensing for you, and you pay as you go.\n- 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.\n- Consider a [memory optimized](https://aws.amazon.com/rds/instance-types/) instance for production workloads.\n\nBased on the on-premises SQL Server utilization and how scaling works for Amazon RDS for SQL Server, choose the [instance type](https://aws.amazon.com/rds/sqlserver/instance-types/) 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.\n\n### **Optimize costs**\nBefore you create the RDS for SQL Server instance, optimize your cost using the following methods:\n\n- 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)](https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16#Cross-BoxScaleLimits).\n\n\n\n- Run the following command at your on-premises SQL Server to find out if any Enterprise edition features are used in your application:\n\n```\nIF OBJECT_ID('tempdb.dbo.##enterprise_feature_list') IS NOT NULL\n DROP TABLE ##enterprise_feature_list\n \nCREATE TABLE ##enterprise_feature_list\n(\n dbname SYSNAME,\n feature_name VARCHAR(100),\n feature_id INT\n)\n \nEXEC sp_MSforeachdb\nN' USE [?] \n INSERT INTO ##enterprise_feature_list \n SELECT dbname=DB_NAME(),feature_name,feature_id \n FROM sys.dm_db_persisted_sku_features \n'\nSELECT *\nFROM ##enterprise_feature_list\n\n```\n- 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.\n- 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.\n- 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.\n- Consider using [reserved instances](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithReservedDBInstances.html) to help further reduce costs.\n\n### **Migrate the database**\n\nTo migrate the database from on-premises to Amazon RDS for SQL Server using backward compatibility, consider native [backup and restore](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html). If you have a large number of SQL Server instances to migrate, consider automating the migration using [custom log shipping](https://aws.amazon.com/blogs/database/automate-on-premises-or-amazon-ec2-sql-server-to-amazon-rds-for-sql-server-migration-using-custom-log-shipping/).\n\nThe 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.\n\n\n\n### **Migrate BI components (optional)**\n\nIf 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.\n](https://aws.amazon.com/getting-started/hands-on/enable-business-intelligence-features-amazon-rds-sql-server/)\n\nFor more information about the limitations of SSIS, SSRS, and SSAS in Amazon RDS for SQL Server, refer to the following resources:\n\n- [SSIS in Amazon RDS for SQL Server Limitations](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.SSIS.html#SSIS.Limitations)\n- [SSRS in Amazon RDS for SQL Server Limitations](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.SSRS.html#SSRS.Limitations)\n- [SSAS in Amazon RDS for SQL Server Limitations](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.SSAS.html#SSAS.Limitations)\n\nIf 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.\n\n### **Validate backward compatibility**\n\nTo validate that backward compatibility maintains the old [cardinality estimation](https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-ver16), 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.\n\nAlso 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```.\n\nTo check the compatibility of the databases on the RDS for SQL Server instance, run the following command:\n\n```\nSELECT @@version as DatabaseEngineVersion;\n\nSELECT * FROM sys.databases;\n```\n\n\nThe ```AdventureWorks2012```database restored using backward compatibility maintains ```CardinalityEstimationModelVersion```70 like it was running on-premises.\n\n\n\nThe ```AdventureWorks2019```database created using database engine compatibility has ```CardinalityEstimationModelVersion```150 to match SQL Server 2019 default cardinality estimation.\n\n\n\n### **Best practices**\n\nConsider the following best practices:\n\n- 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.\n\n\n\n- You may download the latest [Microsoft ODBC](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16) and [Microsoft JDBC](https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16) drivers to use in your application if required.\n- If you have an application with [tempdb heavy workloads, consider using R5d instance](https://aws.amazon.com/about-aws/whats-new/2021/01/amazon-rds-for-sql-server-now-supports-tempdb-on-local-instance-store-with-r5d-and-m5d-instance-types/) 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.\n- Always consider running your production workload in a [Multi-AZ](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_SQLServerMultiAZ.html) setup to ensure high availability and help prevent data loss.\n- Consider running UPDATE STATISTICS after the cutover on all the tables using the output from the following code:\n\n```\nUSE <database_name> --repeat for each database\ngo\nSELECT 'UPDATE STATISTICS ' + s.name + '.' + o.name + ' WITH ALL;'\nFROM sys.objects o\ninner join sys.schemas s\non s.schema_id = o.schema_id\nWHERE o.type = 'U'\nORDER BY o.name\n```\n\n### **Summary**\n\nIn 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 Amazon Web Services with confidence.\n\nTo determine your options when the database is already running in Amazon Web Services, refer to [Best practices for upgrading SQL Server 2008 R2 to SQL Server 2016 on Amazon RDS for SQL Server.](https://aws.amazon.com/blogs/database/best-practices-for-upgrading-sql-server-2008-r2-to-sql-server-2016-on-amazon-rds-for-sql-server/)\n\nIf you have any comments or feedback, please leave them in the comments section.\n\n#### **About the authors**\n\n\n\n**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 Amazon Web Services. Prior to joining Amazon Web Services, he supported production and mission-critical database implementation across financial and travel and hospitality industry segments.\n\n\n\n**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 Amazon Web Services Cloud database solutions.","render":"<p>Customers running end of support (EOS) Microsoft SQL Server workloads on premises often ask us how they can confidently migrate those workloads to Amazon Web Services fully managed database services like <a href=\"https://aws.amazon.com/rds/sqlserver/\" target=\"_blank\">Amazon Relational Database Service (Amazon RDS) for SQL Server</a>. Amazon RDS for SQL Server makes it simple to set up, operate, and scale SQL Server deployment in the cloud.</p>\n<p>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.</p>\n<p>SQL Server database <a href=\"https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16\" target=\"_blank\">compatibility level</a> 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 <a href=\"https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16#backwardCompat\" target=\"_blank\">backward compatibility</a>.</p>\n<p>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.</p>\n<h3><a id=\"Lifecycle_dates_8\"></a><strong>Lifecycle dates</strong></h3>\n<p>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. <a href=\"https://docs.microsoft.com/en-us/sql/sql-server/end-of-support/sql-server-end-of-support-overview?view=sql-server-ver16\" target=\"_blank\">End of support</a> indicates a product has reached end of its lifecycle; servicing and support is no longer available. Amazon Web Services maintains a separate <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.Deprecated-Versions\" target=\"_blank\">deprecation schedule</a> for major engine versions of Microsoft SQL Server on Amazon RDS.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/b1d311e9ae7b4496ab870aefcff53d3b_image.png\" alt=\"image.png\" /></p>\n<h3><a id=\"Migration_overview_14\"></a><strong>Migration overview</strong></h3>\n<p>The database migration process consists of the following high-level steps:</p>\n<ol>\n<li>Check the compatibility matrix and choose your target Amazon RDS for SQL Server version.</li>\n<li>Run an assessment using <a href=\"https://aws.amazon.com/dms/schema-conversion-tool/?nc=sn&amp;loc=2\" target=\"_blank\">Amazon Web Services Schema Conversion Tool</a> (Amazon Web Services SCT). The Amazon Web Services SCT <a href=\"https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServer.ToRDSSQLServer.html\" target=\"_blank\">assessment report</a> provides server and database metrics for the conversion.</li>\n<li>Right-size your RDS for SQL Server instance.</li>\n<li>Optimize your costs.</li>\n<li>Migrate the database.</li>\n<li>Optionally, migrate business intelligence (BI) components.</li>\n</ol>\n<h3><a id=\"Check_the_compatibility_matrix_25\"></a><strong>Check the compatibility matrix</strong></h3>\n<p>As the first step towards your migration to Amazon RDS for SQL Server, check your supported <a href=\"https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16#arguments\" target=\"_blank\">compatibility level</a> using the following table and choose your target Amazon RDS for SQL Server version accordingly.</p>\n<p>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).</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/fb84c4597ae04ac885b79d65dff1d960_image.png\" alt=\"image.png\" /></p>\n<h3><a id=\"Run_an_assessment_with_Amazon_Web_Services_SCT_33\"></a><strong>Run an assessment with Amazon Web Services SCT</strong></h3>\n<p>As the second step towards your migration to Amazon RDS for SQL Server, run an assessment using Amazon Web Services SCT. The metrics about your SQL Server instance included in the assessment report include the following:</p>\n<ul>\n<li>Database storage object compatibility</li>\n<li>Database code object compatibility</li>\n<li>Enterprise edition feature usage</li>\n<li>Database mirroring is used</li>\n<li>SQL Server Log shipping is configured</li>\n<li>Failover cluster is used</li>\n<li>Database Mail is configured</li>\n<li>Full Text Search Service is used (Amazon RDS for SQL Server has a limited full text search, and doesn’t support semantic search)</li>\n<li>Data Quality Service (DQS) is installed</li>\n<li>SQL Server Service Broker is used</li>\n<li>Linked Server is used (Amazon RDS for SQL Server has limited support for linked servers)</li>\n</ul>\n<p>The following are some sample Amazon Web Services SCT assessment reports run at the database level.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/538863ceaec9446680673896463d80ae_image.png\" alt=\"image.png\" /></p>\n<p><img src=\"https://dev-media.amazoncloud.cn/de46d399173c4371a2408aa420a5dbb5_image.png\" alt=\"image.png\" /></p>\n<p>We recommend also checking <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.FeatureSupport.Limits\" target=\"_blank\">Limitations for Microsoft SQL Server DB instances</a>. Refer to <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.FeatureNonSupport\" target=\"_blank\">Features not supported and features with limited support</a> to learn more on Amazon RDS for SQL Server.</p>\n<p>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 <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/working-with-custom-sqlserver.html\" target=\"_blank\">Amazon RDS Custom for SQL Server</a>. <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-custom.html\" target=\"_blank\">Amazon RDS Custom</a> 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 Amazon Web ServicesCloud while granting you access to the database and underlying operating system.</p>\n<p>With Amazon RDS Custom, you get the automation of Amazon RDS and the flexibility of <a href=\"http://aws.amazon.com/ec2\" target=\"_blank\">Amazon Elastic Compute Cloud</a> (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.</p>\n<p>The following table shows the shared responsibility model for Amazon RDS Custom.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/a95c798f5917452cb59940f8d660d6b9_image.png\" alt=\"image.png\" /></p>\n<h3><a id=\"Rightsize_the_instance_65\"></a><strong>Right-size the instance</strong></h3>\n<p>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:</p>\n<ul>\n<li>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.</li>\n<li>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).</li>\n<li>Amazon Web Services handles the Amazon RDS for SQL Server Microsoft licensing for you, and you pay as you go.</li>\n<li>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.</li>\n<li>Consider a <a href=\"https://aws.amazon.com/rds/instance-types/\" target=\"_blank\">memory optimized</a> instance for production workloads.</li>\n</ul>\n<p>Based on the on-premises SQL Server utilization and how scaling works for Amazon RDS for SQL Server, choose the <a href=\"https://aws.amazon.com/rds/sqlserver/instance-types/\" target=\"_blank\">instance type</a> 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.</p>\n<h3><a id=\"Optimize_costs_77\"></a><strong>Optimize costs</strong></h3>\n<p>Before you create the RDS for SQL Server instance, optimize your cost using the following methods:</p>\n<ul>\n<li>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 <a href=\"https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16#Cross-BoxScaleLimits\" target=\"_blank\">Editions and supported features of SQL Server 2019 (15.x)</a>.</li>\n</ul>\n<p><img src=\"https://dev-media.amazoncloud.cn/46ad134b4ffe48f0bee1ad4ac868a3c8_image.png\" alt=\"image.png\" /></p>\n<ul>\n<li>Run the following command at your on-premises SQL Server to find out if any Enterprise edition features are used in your application:</li>\n</ul>\n<pre><code class=\"lang-\">IF OBJECT_ID('tempdb.dbo.##enterprise_feature_list') IS NOT NULL\n DROP TABLE ##enterprise_feature_list\n \nCREATE TABLE ##enterprise_feature_list\n(\n dbname SYSNAME,\n feature_name VARCHAR(100),\n feature_id INT\n)\n \nEXEC sp_MSforeachdb\nN' USE [?] \n INSERT INTO ##enterprise_feature_list \n SELECT dbname=DB_NAME(),feature_name,feature_id \n FROM sys.dm_db_persisted_sku_features \n'\nSELECT *\nFROM ##enterprise_feature_list\n\n</code></pre>\n<ul>\n<li>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.</li>\n<li>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.</li>\n<li>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.</li>\n<li>Consider using <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithReservedDBInstances.html\" target=\"_blank\">reserved instances</a> to help further reduce costs.</li>\n</ul>\n<h3><a id=\"Migrate_the_database_112\"></a><strong>Migrate the database</strong></h3>\n<p>To migrate the database from on-premises to Amazon RDS for SQL Server using backward compatibility, consider native <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html\" target=\"_blank\">backup and restore</a>. If you have a large number of SQL Server instances to migrate, consider automating the migration using <a href=\"https://aws.amazon.com/blogs/database/automate-on-premises-or-amazon-ec2-sql-server-to-amazon-rds-for-sql-server-migration-using-custom-log-shipping/\" target=\"_blank\">custom log shipping</a>.</p>\n<p>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.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/2a000d29f78841e28a2b0a9a5fc91cab_image.png\" alt=\"image.png\" /></p>\n<h3><a id=\"Migrate_BI_components_optional_120\"></a><strong>Migrate BI components (optional)</strong></h3>\n<p>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 <a href=\"https://aws.amazon.com/getting-started/hands-on/enable-business-intelligence-features-amazon-rds-sql-server/\" target=\"_blank\">enabling Microsoft SQL Server BI features on Amazon RDS for SQL Server.<br />\n</a></p>\n<p>For more information about the limitations of SSIS, SSRS, and SSAS in Amazon RDS for SQL Server, refer to the following resources:</p>\n<ul>\n<li><a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.SSIS.html#SSIS.Limitations\" target=\"_blank\">SSIS in Amazon RDS for SQL Server Limitations</a></li>\n<li><a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.SSRS.html#SSRS.Limitations\" target=\"_blank\">SSRS in Amazon RDS for SQL Server Limitations</a></li>\n<li><a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.SSAS.html#SSAS.Limitations\" target=\"_blank\">SSAS in Amazon RDS for SQL Server Limitations</a></li>\n</ul>\n<p>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.</p>\n<h3><a id=\"Validate_backward_compatibility_133\"></a><strong>Validate backward compatibility</strong></h3>\n<p>To validate that backward compatibility maintains the old <a href=\"https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-ver16\" target=\"_blank\">cardinality estimation</a>, use one of the migration methods outlined earlier to migrate the database (<code>AdventureWorks2012</code>) from on-premises SQL Server 2012 to Amazon RDS for SQL Server 2019.</p>\n<p>Also create a new database (<code>AdventureWorks2019</code>) on the RDS for SQL Server instance matching the database engine compatibility. We use this database to compare the <code>CardinalityEstimationModelVersion</code>values between <code>AdventureWorks2012</code>and<code>AdventureWorks2019</code>.</p>\n<p>To check the compatibility of the databases on the RDS for SQL Server instance, run the following command:</p>\n<pre><code class=\"lang-\">SELECT @@version as DatabaseEngineVersion;\n\nSELECT * FROM sys.databases;\n</code></pre>\n<p><img src=\"https://dev-media.amazoncloud.cn/c829c717d89e427fa71f928cb798fbee_image.png\" alt=\"image.png\" /></p>\n<p>The <code>AdventureWorks2012</code>database restored using backward compatibility maintains <code>CardinalityEstimationModelVersion</code>70 like it was running on-premises.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/a331d38094d34ae8a2bcd1810757eb22_image.png\" alt=\"image.png\" /></p>\n<p>The <code>AdventureWorks2019</code>database created using database engine compatibility has <code>CardinalityEstimationModelVersion</code>150 to match SQL Server 2019 default cardinality estimation.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/44a2961ba23e4af99b3946d823436ac8_image.png\" alt=\"image.png\" /></p>\n<h3><a id=\"Best_practices_156\"></a><strong>Best practices</strong></h3>\n<p>Consider the following best practices:</p>\n<ul>\n<li>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.</li>\n</ul>\n<p><img src=\"https://dev-media.amazoncloud.cn/a13a55526f064a6494e57e41e84c1c23_image.png\" alt=\"image.png\" /></p>\n<ul>\n<li>You may download the latest <a href=\"https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16\" target=\"_blank\">Microsoft ODBC</a> and <a href=\"https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16\" target=\"_blank\">Microsoft JDBC</a> drivers to use in your application if required.</li>\n<li>If you have an application with <a href=\"https://aws.amazon.com/about-aws/whats-new/2021/01/amazon-rds-for-sql-server-now-supports-tempdb-on-local-instance-store-with-r5d-and-m5d-instance-types/\" target=\"_blank\">tempdb heavy workloads, consider using R5d instance</a> types with <code>tempdb</code>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.</li>\n<li>Always consider running your production workload in a <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_SQLServerMultiAZ.html\" target=\"_blank\">Multi-AZ</a> setup to ensure high availability and help prevent data loss.</li>\n<li>Consider running UPDATE STATISTICS after the cutover on all the tables using the output from the following code:</li>\n</ul>\n<pre><code class=\"lang-\">USE &lt;database_name&gt; --repeat for each database\ngo\nSELECT 'UPDATE STATISTICS ' + s.name + '.' + o.name + ' WITH ALL;'\nFROM sys.objects o\ninner join sys.schemas s\non s.schema_id = o.schema_id\nWHERE o.type = 'U'\nORDER BY o.name\n</code></pre>\n<h3><a id=\"Summary_180\"></a><strong>Summary</strong></h3>\n<p>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 Amazon Web Services with confidence.</p>\n<p>To determine your options when the database is already running in Amazon Web Services, refer to <a href=\"https://aws.amazon.com/blogs/database/best-practices-for-upgrading-sql-server-2008-r2-to-sql-server-2016-on-amazon-rds-for-sql-server/\" target=\"_blank\">Best practices for upgrading SQL Server 2008 R2 to SQL Server 2016 on Amazon RDS for SQL Server.</a></p>\n<p>If you have any comments or feedback, please leave them in the comments section.</p>\n<h4><a id=\"About_the_authors_188\"></a><strong>About the authors</strong></h4>\n<p><img src=\"https://dev-media.amazoncloud.cn/80bff009d0de4c9ca4d696a9650dcb14_image.png\" alt=\"image.png\" /></p>\n<p><strong>Rajib Sadhu</strong> 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 Amazon Web Services. Prior to joining Amazon Web Services, he supported production and mission-critical database implementation across financial and travel and hospitality industry segments.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/1dfc0865f3b74997a636939f197b855d_image.png\" alt=\"image.png\" /></p>\n<p><strong>Shirin Ali</strong> 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 Amazon Web Services Cloud database solutions.</p>\n"}