By: Rajendra Gupta | Updated: 2022-09-12 | Comments | Related: > Amazon AWS RDS
Problem
My company is planning to move our on-premises databases to an RDS instance. What can you tell me about Amazon RDS?
Solution
Amazon Relational Database Service, aka RDS, provides managed SQL database services for Microsoft SQL Server, Oracle, PostgreSQL, MySQL, Amazon Aurora, and MariaDB.
This tip guides you through some valuable features of AWS RDS.
The Amazon RDS is a database as a service solution where you do not need to manage the infrastructure. AWS takes care of tasks such as:
- Provisioning
- Patching (OS and database)
- Backup and Recovery
- High Availability
- Disaster Recovery
- Failure Detection
AWS RDS SQL Server Features
Let's look at the RDS features for SQL Server to get some insight.
Fully Managed
AWS RDS fully manages the SQL Server relational database service. You can use AWS Console, CLIs, or API calls to provision, manage, and configure databases. By default, RDS instances use the pre-configured parameters and configurations. However, you can customize parameters as per your requirements using DB parameter groups. Additionally, AWS analyzes your configuration based on usage metrics and provides best practice guidance. Users can review the recommendation and dismiss or accept to apply those recommendations immediately or schedule for the next maintenance window.
Automatic Patching
In the on-premises environment, IT infrastructure teams apply OS and database patches so that you always have the latest security patches and hotfixes. AWS removes this overhead automatically by using software patching with user control over the patching schedule.
Automated Backups
AWS automatically configure backups once you create a new RDS instance. These automated backups provide point-in-time (PITR) recovery for SQL Server. It creates a storage volume snapshot of the RDS instance to backup the entire DB instance instead of the individual database.
Automated backup includes:
- A full backup (daily) in the configured backup window.
- T-log backups are taken every 5 minutes in the Amazon S3 bucket.
Refer to Working with backups for more details.
Performance
Amazon RDS supports General Purpose (SSD) and Provisioned IOPS (SSD) storage to deliver fast, consistent IOPS suitable for your database workloads.
- General Purpose(SSD) Storage: This is suitable for small to medium sized databases such as development and test environments. It can provide up to 3000 IOPS per volume with five nines of availability.
- Provisioned IOPS: This is suitable for fast, consistent, predictable performance with low consistency workloads. It is optimized for OLTP workloads. The provisioned IOPS can provide 1000-64000 IPS for SQL Server with 20GiB to 16 TiB storage. Refer to Amazon RDS DB instance storage for more details.
Additionally, you can auto-scale storage without any downtime. The RDS storage auto-scaling automatically increases the storage as per your desired maximum storage limit.
Single Click High Availability
AWS cloud locations combine Regions, Availability zones, and Local zones.
- Region: An AWS region is a separate geographic area. For example, US East (Ohio), US East (N. Virginia), US West (N. California)
- Availability Zone: Each region contains multiple isolated locations. These locations are availability zones.
The AWS RDS provides Multi-AZ replicas that can replicate data synchronously to a different availability zone for enhanced availability and durability. The key features are:
- Automatic database failover in the event of database instance failure
- Protect database availability
- Enhance durability using synchronous replication
You can refer to Amazon RDS Multi-AZ for more details.
Support for Various SQL Server Versions and Editions
AWS RDS supports various SQL Server editions and versions for you to choose your required version.
- It supports Enterprise, Standard, Web, and Express editions.
- AWS RDS supports SQL Server 2019, 2017, 2016, and 2014.
To get the minor version details, refer to the article Amazon RDS for Microsoft SQL Server.
Pay Only for What You Use
AWS does not ask for any up-front commitment of AWS RDS instances. You can deploy, configure and delete them as per your requirements. It also allows you to stop database instances for up to 7 days at a time for a cost-effective solution, especially for development and test purposes.
Encryption
You can encrypt databases using the AWS Key Management Services (KMS). It can encrypt data stored at rest in underlying storage, automated backups, read replicas, and snapshots. It also supports transparent data encryption for SQL Server.
Monitoring
AWS provides monitoring metrics using Amazon Cloudwatch without any additional cost. You can monitor these operational metrics, compute, storage capacity, IO activity, and database connections. You can also configure enhanced monitoring to get access to granular performance insights that can help you detect performance issues.
Local Time Zone Support
The AWS SQL Server instance default uses the Coordinated Universal Time (UTC) timezone. However, you can configure the timezone as per your requirements. For a list of supported time zones, refer to the article Local time zone for Microsoft SQL Server DB instances.
Migration Options
AWS supports various ways to migrate your database to AWS RDS SQL Server instances. These options are:
- Single and multiple native restores
- SQL Server database publishing wizard
- Import and Export
- AWS Database Migration Service
- SQL Server Replication
License Support
AWS RDS instance does not require you to purchase a software license for SQL Server. The AWS RDS instance pricing involves the following:
- Software license
- Hardware resources cost
- RDS management capabilities
Things to Know When Using RDS for SQL Server
You should know the limitations before moving any database to an AWS RDS instance. The following restrictions apply to RDS SQL Database:
- The maximum number of databases depends on the instance class type and availability mode. For example, a single availability zone (AZ) supports 30 databases for db.*.large and Multi-AZ.
- The Computation and memory capacity depends on the DB instance class. You cannot choose individual compute and memory configurations.
- AWS RDS has restrictions for maximum storage size.
- General Purpose SSD: 16 TiB
- Provisioned IPS: 16 TiB
- Magnetic storage: 1 TiB
- AWS RDS does not support the following SQL Server features:
- Data Quality Services
- Master Data Services
- FileStream
- Buffer Pool Extension
- Database backup to Azure blob storage
- Machine Learning
- Maintenance plans
- Replication
- PolyBase
- Server-level triggers
- Stretch Database
- Performance data collector
- Policy Based Management
- Importing data into the MSDB database
- AWS SQL Server instance does not support these server-level roles: bulkadmin, dbcreator, diskadmin, securityadmin, serveradmin, and sysadmin.
- You cannot use client connections from the IP range 169.254.0.0/16 as AWS uses these IP addresses for the Automatic Private IP Addressing Range (APIPA)
- AWS RDS instance creates a database called RDSADMIN and you need to use specific
functions to perform certain administrative tasks.
- Drop a SQL database: rds_drop_database function
- View error logs: rds_read_error_log
- Transition a database from offline to online: rds_set_database_online
- Backup and restore: rds_backup_database, rds_database_restore and rds_restore_log
- View SQL Server audit logs: rds_fn_get_audit _file
- SSIS: rds_drop_ssis_database and rds_sqlagent_proxy
- SSRS: rds_drop_ssrs_databases
Next Steps
- You can learn more about Amazon RDS.
- Refer to existing AWS tips.
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-09-12
