Fully managed SQL Server database on Amazon RDS

Amazon RDS is a fully managed service on AWS that allows you to run relational databases seamlessly and at scale, with added features such as comprehensive updates, failover and data backups. Amazon RDS support for six different database engines, which are MYSQL, MariaDB, Amazon Aurora, PostgreSQL, Oracle, and SQL Server. Today, we will be discussing key features for SQL Server on RDS and preparing to migrate a workload from on-premises SQL server to RDS.

Why RDS?

Instead of traditional SQL Server on Amazon EC2, where the administrator will need full control over the DB instance, backups, clustering, etc., RDS offers the following features:

Easy to build – build-in monitoring, patching, and DB, OS, and hardware software included

Compliance – Data Encryption at-rest as well as in transit

BCDR – Automatically spread across Multiple Availability Zones (Multi-AZ data replication), snapshots, etc.

Performance – Built-in schema optimization as well as scaling up the compute and storage layer are easy with little downtime

Encryption – Amazon RDS supports Microsoft SQL Server Transparent Data Encryption (TDE), which transparently encrypts stored data

Key Items to Remember for SQL Server on RDS

Here are some key things to look out for when building SQL Server with RDS or doing migrations from on-premises to Amazon SQL Server on RDS:

• You can use Microsoft Windows Authentication to authenticate users when they connect to your Amazon RDS for Microsoft SQL Server DB instance with a Directory Service native in AWS called Managed Microsoft AD. Users will authenticate into the SQL Server DB that is joined to the trusted domain, then the request is forwarded to the designated domain directory for a response. 
  • Note that Amazon RDS uses mixed mode for Windows Authentication, meaning the master user can use SQL Authentication while the rest of the users utilize AD authentication.
• Once you have chosen the engine type (SQL Server, in this case) and version, keep in mind that the commercial license is included in the cost, so you don’t need to be concerned about the license management.
• Upon Database creation, the wizard also includes many templates including specific templates for Dev/Test or Production.
• Setting the Master Password, make sure to not use / @ or “
  • Also, Database names follow the usual SQL Server naming rules with the following differences: 

• • • Database names can’t start with rdsadmin. 
    • They can’t start or end with a space or a tab.
    • They can’t contain any of the characters that create a new line.
    • They can’t contain a single quote (‘)
• You can view all Amazon RDS instance events for the past 24 hour in the management console or via the AWS CLI
• You can configure CloudWatch Log integration (to view your RDS logs in CloudWatch) by enabling this feature in the “Advanced Settings” section when you create a new DB instance
• A DB parameter group acts as a container for engine configuration values that are applied to one or more DB instances.
  • If you create a DB instance without specifying a DB parameter group, the DB instance uses a default DB parameter group. Each default DB parameter group contains database engine defaults and Amazon RDS system defaults based on the engine, compute class, and allocated storage of the instance. You can’t modify the parameter settings of a default parameter group, but you can create your own parameter group and apply it to the RDS instance.
  • Database parameters are either static or dynamic. When you change a static parameter and save the DB parameter group, the parameter change takes effect after you manually reboot the DB instance. Dynamic is applied instantly.
• If you don’t specify a preferred backup window when you create the DB instance, Amazon RDS assigns a default 30-minute backup window and remember, these are based on region.
  • Note: Backing up to Microsoft Azure Blob Storage is not supported
• If your AWS account has a default VPC instead of a custom-built VPC, then your DB instance is automatically created inside the default VPC. Check your VPC configurations and subnet groups before you create any DB instances.
• Ensure that your Security Groups are set up correctly to allow the correct access for incoming and outgoing traffic, while still adhering to the policy of least-privilege
• You can’t back up to, or restore from, an Amazon S3 bucket in a different AWS Region from your Amazon RDS DB instance.
• Database names must be unique so no restoring a database with the same name as an existing database.
• Amazon S3 has a size limit of 5 TB per file. For native backups of larger databases, you can use multi file backup.
• Make sure to set up the correct IAM roles and utilize the trust relationships and permissions policies correctly before beginning the migration
• Amazon RDS now allows for Multi-AZ deployments for DB instances running Microsoft SQL Server by using SQL Server Database Mirroring (DBM) or Always On Availability Groups (AGs). 
• Amazon RDS manages failover by actively monitoring your Multi-AZ deployment and initiating a failover when an issue occurs. Failover won’t automatically activate unless the standby and primary are fully in sync. Amazon RDS actively maintains your Multi-AZ deployment by automatically repairing unhealthy DB instances and re-establishing synchronous replication.
• The list of DBA tasks can be daunting. AWS provides a standard list to help you along your journey here

Three things before you begin

1. Choosing an instance size can be tricky, especially if you are moving from on-premises. Find the instance classes here

You can also view the RDS Storage types here

Note: The maximum storage size for SQL Server DB instances on RDS is:

• • General Purpose (SSD) storage – 16 TiB for all editions 
  • Provisioned IOPS storage – 16 TiB for all editions 
  • Magnetic storage – 1 TiB for all editions 

If your architecture demands a larger amount of RDS storage, you can use sharding across multiple DB instances to work around this rule.

The minimum storage size for SQL Server DB instances is the following:

• • General Purpose (SSD) storage – 20 GiB for Enterprise, Standard, Web, and Express Editions 
  • Provisioned IOPS storage – 20 GiB for Enterprise, Standard, Web, and Express Editions
  • Magnetic storage – 20 GiB for Enterprise, Standard, Web, and Express Editions

2. SQL Server 2019 includes many new features such as accelerated database recovery, or ADR, which reduces crash recovery time after a restart or a long-running transaction rollback, Intelligent Query Processing (IQP), Intelligent performance, Monitoring improvements, Resumable online index creation and more. 

Be sure to check the feature set you are interesting in as well as SQL versions supported currently in RDS:

3. There are numerous ways to do migrations from on-premises SQL Server to Amazon SQL Server on RDS

To do migrations from SQL Server to RDS, you must set up the RDS DB Instance, then create a replication instance in AWS DMS. Next, you create source and target endpoints for your database migration, configure the replication task in AWS DMS, and finish up with the migration and clean up resources.

One way is to create a full backup from your local server, store it on S3, and then restore it onto an existing Amazon RDS DB instance. You can also make backups from RDS, store them on S3, and then restore them wherever you want (SQL Server database backup and restore for initial data load, then utilize DMS for ongoing changes)

If you would like more information on mechanisms for migration, architectural review before or after migration to Amazon RDS or any other AWS database related services, please contact Cloudreach, where we would be happy to assist you on your cloud journey.