Migration & Architecture Options for Microsoft SQL Server on AWS

June 4, 2021

Since the public release of AWS’s Elastic Compute Cloud (EC2) service, Cloudnexa has assisted customers with migrating their SQL Server infrastructures into the AWS cloud. Over the last decade, we’ve participated in countless projects of varying degree and difficulty and watched as the AWS ecosystem continuously evolves to provide increased compatibility for different SQL Server deployment models and migration methods.

Migration Methodologies

AWS provides a lot of flexibility when it comes to deployment options and target architectures for SQL Server workloads, but they can all be categorized as belonging to one of three general methodologies: Rehosting, Replatforming, and Refactoring.

Rehosting (Lift and Shift) is the relatively simple method of copying application and data bits from outside AWS into AWS. Classic examples include copying SQL Server database backups into a pre-provisioned AWS EC2 instance or moving self-contained virtual machines into a pre-configured target AWS account. Rehosting is probably the most common migration method due to its speed and relative simplicity. Most rehosting can be automated, although you may prefer to do this manually as you learn how to lift-and-shift your current SQL Server deployments to AWS.

Replatforming (Lift, Shift & Shape or Lift & Shape) is the compromise between Rehosting and Refactoring. When a SQL Server database is Replatformed in the cloud, it is modified to be more cloud-compatible, but without incurring too much change and risk. Replatforming to AWS RDS for SQL Server gives you a fully managed solution, decreased management overhead, single-click high availability, automated backups, the possibility for auto-scaled storage, and a number of easy migration options. Alternately, replatforming Microsoft SQL Server from Windows to Linux will save you on Windows licensing costs, while providing the same enterprise-ready platform with great performance and nearly the same number of supported features.

Refactoring is the most complex method of migrating to the cloud, the outcome being a fully cloud-native database. The effort and technical skills required to do this vary greatly but it is generally accepted that to successfully refactor you will need more advanced database and cloud aptitude than with any other migration method. An example refactoring target would be the AWS RDS Aurora platform. The target architectures in the remainder of this post specifically pertain to available options when following the Rehosting and Replatforming methodologies.

Target SQL Server Architectures on Amazon EC2

SQL Server Instance: A single implementation of SQL Server running on EC2 without any high availability capability.
SQL Server with Log Shipping: Log shipping lets you automatically send transaction log backups from a primary database instance to one or more secondary databases (also known as warm standby) on separate DB instances running on separate EC2 instances. Log shipping is often used as a disaster recovery solution but also can be used as a high availability solution.
SQL Server Transactional Replication: Transactional replication is a SQL Server technology that is used to replicate changes between two databases, including database objects like tables, stored procedures, views, and so on, as well as data. The replication process involves a publisher, a subscriber, and a distributor, all running on SQL instances on EC2.
SQL Server with Database Mirroring: Database mirroring takes a database located on an EC2 instance and provides a complete or almost complete read-only copy (mirror) of it on a separate DB instance, typically in a different availability zone (AZ). Note that although database mirroring is still available in SQL Server 2019, it is a deprecated feature which means it is no longer under active development and may be removed from a future version.
SQL Server Linked Servers: Linked servers allow you to join tables between database servers and distribute queries through stored procedures and views across servers, without needing to change your application source code or manage multiple connection strings in your web tier. Linked servers’ connectivity can be set up using combinations of on-premise SQL instances, SQL instances running on EC2, and AWS RDS SQL Server instances.
SQL Server on Linux: Starting with SQL Server 2017, SQL Server is available to run on Linux operating systems. Moving your SQL Server workloads to Linux provides both cost savings and performance improvements. SQL Server is currently supported on Red Hat Enterprise Server, SUSE Linux Enterprise Server, Ubuntu, and running in a container with Docker.
SQL Server Always On Availability Groups (AG)
- Always On Basic Availability Groups: Starting with SQL Server 2016 SP1, SQL Server Standard edition provides basic high availability for a single, non-readable secondary database and listener per availability group. It also supports a maximum of two nodes per availability group with each node residing in a separate availability zone (AZ). Always On Basic Availability Groups replaces the deprecated Database Mirroring feature and provides a similar level of feature support.
- Always On Availability Groups: SQL Server Always On availability groups is an advanced, enterprise-level feature to provide high availability and disaster recovery solutions. This feature is available if you are using SQL Server 2012 and later versions. It includes support for up to nine availability replicas, both asynchronous- and synchronous-commit modes, automatic and manual failover, readable secondary replicas, etc.
- Distributed Availability Groups: A distributed availability group spans two separate availability groups. You can think of it as an availability group of availability groups. The underlying availability groups are configured on two different WSFC clusters. The availability groups that participate in a distributed availability group can be deployed across AWS regions.
SQL Server Always On Failover Cluster Instances (FCIs): An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering nodes to provide high availability for the entire installation of SQL Server. If the underlying node experiences hardware, operating system, application, or service failures, everything inside the SQL Server instance is moved to another WSFC node. FCIs require some form of shared storage—disks on a storage area network (SAN), file shares on Server Message Blocks (SMBs), or locally attached storage with Storage Spaces Direct (S2D), SIOS Datakeeper, and most recently Amazon FSx—to provide resiliency and high availability. We will review the configuration and use of Amazon FSx for FCIs in an upcoming post.

Target SQL Server Architectures on AWS RDS

Amazon RDS supports DB instances running several versions and editions of Microsoft SQL Server, starting with SQL Server 2012. SQL Server Analysis Services (SSAS), Integration Services (SSIS), and Reporting Services (SSRS) are now also available on Single-AZ or Multi-AZ instances starting with Amazon RDS for SQL Server 2016 on both the Standard and Enterprise editions.

Single Availability Zone (AZ) QL Server Instance: A single SQL Server instance running on RDS without any high availability capability. All editions are supported (Express, Web, Standard, Enterprise).
Multi-AZ SQL Server Instances: Amazon RDS supports Multi-AZ deployments for DB instances running Microsoft SQL Server by using SQL Server Database Mirroring (DBM) or Always On Availability Groups (AGs). Multi-AZ deployments provide increased availability, data durability, and fault tolerance for DB instances. Amazon RDS supports Multi-AZ with Always On AGs for the following SQL Server versions and editions:
- SQL Server 2019: Standard and Enterprise Editions
- SQL Server 2017: Enterprise Edition 14.00.3049.1 or later
- SQL Server 2016: Enterprise Edition 13.00.5216.0 or later

Amazon RDS supports Multi-AZ with DBM for the following SQL Server versions and editions, except for the versions noted previously:

SQL Server 2017: Standard and Enterprise Editions
SQL Server 2016: Standard and Enterprise Editions
SQL Server 2014: Standard and Enterprise Editions
SQL Server 2012: Standard and Enterprise Editions
SQL Server Read Replicas: SQL Server read replicase are available on the SQL Server Enterprise Edition engine for versions 2016-2019, and must be part of a multi-AZ deployment with Always On AGs. Up to five read replicas can be created from one source DB instance.

Migration Methods

Various methods are available to support migrating your SQL Server databases to AWS. Some of these most common methods and their characteristics are summarized in the following table:

In our next post, we will review the configuration and use of Amazon FSx for SQL Server FCIs, one of the more recent capabilities made available by AWS. Amazon FSx for Windows File Server provides fully managed, highly reliable, and scalable file storage that is accessible by using the Server Message Block (SMB) protocol, making it a suitable option to be used as shared storage in a Windows Server Failover Clustering node.

The content in this post references information from the following links. For additional details, please refer to these links:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server?view=sql-server-ver15

https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/ec2-sql-ha.html

https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/methods.html

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.ReadReplicas.html

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.InstanceClasses

https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/methods.html

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.html

What to know: Restoring Multiple Copies of a Database on AWS RDS SQL Server

March 5, 2018

When customers migrate application databases to Amazon Relational Database Service (RDS) SQL Server, they typically want to restore multiple copies of those database to segregate separate access domains such as production, quality assurance, testing, development, etc. Although the standard AWS recommendation is to use independent RDS instances, this can be an expensive and undesirable proposition. If you try to restore a backup file to a DB instance multiple times, you hit the following issue and troubleshooting roadblock:

Issue:

Database <database_name> cannot be restored because there is already an existing database with the same family_guid on the instance

Resolution:

You also can’t restore the same backup file to a DB instance multiple times. That is, you can’t restore a backup file to a DB instance that already contains the database that you are restoring. Instead, restore the backup file to a new DB instance (Translation: spend more $$$).

Fear not, here is one possible workaround. Using an on-premise SQL Server instance (or a SQL Server instance running on EC2), a SQL database can be exported into a BACPAC file and then imported back into that same SQL instance from that BACPAC file, thus creating “legal” copies of the same database that can now be restored into a single AWS RDS instance. For reference, a BACPAC file is a Windows file with a “.bacpac” extension that encapsulates a database’s schema and data, whose primary use case is to move a database from one server to another. These files are a variant of data-tier applications (DAC), in the family of DAC packages (DACPACs). All DAC operations are supported starting with Microsoft SQL Server 2008 R2. Note that there are some exceptions, so be sure to review DAC Support for SQL Server Objects & Versions. For more information on Data-tier Applications refer to DAC concepts.

Need help implementing? Contact Cloudnexa today.

Tutorial: Restoring Multiple Copies of a Database on AWS RDS SQL Server

In this tutorial, we are going to duplicate the “AdventureWorks2016” sample database using SQL Server 2016. Open SQL Server Management Studio, select the database, select “Tasks”, and select “Export Data-tier Application…”.

Click Next
Select the “Save to local disk” radial button, choose a path and name for your “.bacpac” file. Click “Next”.
Review the summary. Click “Finish”.
Review the results, everything should read as a “Success”.
Back in SQL Server Management Studio, right click on “Databases”, “Import Data-tier Application…”.
Specify the BACPAC file to import. Click “Next”.
Specify settings for the new copy of the database.
Review the summary and verify the specified settings. Click “Finish”.
Review the results, everything should read as a “Success”. Create full backups of the two databases and upload the “.bak” files to S3 (this post assumes you are familiar with, and have set up the prerequisites for AWS RDS SQL Server Native Backup and Restore).
Connect to the target SQL Server RDS instance and restore the first database backup. To restore your database, you call the rds_restore_database stored procedure. The following parameters are required:
@restore_db_name – The name of the database to restore.

@s3_arn_to_restore_from – The Amazon S3 bucket that contains the backup file, and the name of the file.
- Sample TSQL:
```
exec msdb.dbo.rds_restore_database

@restore_db_name='database_name', 

@s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension';

exec msdb.dbo.rds_task_status @db_name='database_name' 
```
- Actual TSQL statement to restore our first database backup:
```
exec msdb.dbo.rds_restore_database

@restore_db_name='AdventureWorks2016',

@s3_arn_to_restore_from='arn:aws:s3:::pa-rds-test/AdventureWorks2016.bak';
```
- Actual TSQL statement to monitor the restore of our first database backup:
```
exec msdb.dbo.rds_task_status @db_name='AdventureWorks2016'
```

Once the “lifecycle” column reads “SUCCESS”, you can start the restore of the second database backup.
- Actual TSQL statement to restore our second database backup:
```
exec msdb.dbo.rds_restore_database

@restore_db_name='AdventureWorks2016Copy1',

@s3_arn_to_restore_from='arn:aws:s3:::pa-rds-test/AdventureWorks2016Copy1.bak';
```
- Actual TSQL statement to monitor the restore of our second database backup:
```
exec msdb.dbo.rds_task_status @db_name='AdventureWorks2016Copy1'
```

Watch for the “lifecycle” column to read “SUCCESS”. Congratulations, you’ve restored multiple database copies to the same, single RDS instance.

Need help implementing? Contact Cloudnexa today.

AWS Snapshot Cleanup is a Common Issue Affecting Our Customers

July 26, 2017

Today we will focus on Amazon RDS and its ability to create manual, and automated backups of your database instance. When you create a DB snapshot on your Amazon RDS database, a backup of the entire DB instance is created (not just individual databases) and stored into a snapshot volume.

Automated backups are created during the backup window configured on your DB instance. These automated backups are created during the period specified, and automatically cleaned up according to the retention period configured (currently with a max of 35 days). With managed cleanup by AWS for automated backups, no further attention is required.

RDS database instances can also be manually backed up into manual snapshots. Manual snapshots are necessary to persist backups beyond 35 days and after an RDS database is deleted. If an RDS instance is terminated, then all automated snapshots are removed at the same time by AWS. These manual snapshots are critical to a customer’s backup retention policy beyond 35 days, or the life of the RDS instance. Cloudnexa can provide both governance policies to manage retention on these manual snapshots, as well as provide an Account Review check to quickly identify outliers based on the age of the manual snapshots. Below we will discuss our Account Review check.

About the Account Review Check

Cloudnexa’s vNOC Account Review will check for any manual snapshots that exceed a certain age threshold, to keep track of lingering snapshots that might no longer be in use, or necessary to keep. Cleaning up any unnecessary manual snapshots of an RDS database instance can be a cost savings. We have seen customers save hundreds of dollars by removing long forgotten RDS manual backups. At the time of writing this the charge for excess snapshot storage is $0.095 cents per gig in us-east-1 for a MySQL RDS Database.

The flagged states for this check are all time based, with red being the oldest at 365 days since the snapshot was created.

Resolving

Login to the AWS Console and navigate to the RDS Service -> Snapshots.
Select the filter for Manual Snapshots.
Select the snapshot to be deleted, then select Snapshot Actions and click delete.