Launching AWS SQL Server RDS
Business Needs:
Our goal is to identify whether Amazon SQL Server RDS Service provides elastic , highly available , Scalable and operationally efficient solution for our use case. We are evaluating options to migrate our read/write heavy production SQL Server database to amazon SQL Server RDS. We have pretty high throughput needs for few hours a day for few months in a year ,which is mission critical for our business success. Any downtown during peak usuage would be catastrophic for our business. We are evaluating pros and cons of moving to amazon RDS with provisioned IOPS.
Caveats of AWS RDS SQL Server:
These information we gathered while working with SQL Server RDS.
| Feature Name | Yes/No | Description |
|---|---|---|
| SQL Server 2016 Support | No | Microsoft says SQL Server 2016 comes with very rich feature set and ton on OLTP Enhancements |
| Native Backup Restore | Yes | AWS RDS Released this feature a week ago , which makes moving databases across environments lot more easier. |
| Elastic IOPS | No | Storage and IOPS needs to be incremented linearly for higher performance. You can’t get higher IOPS without increasing storage. |
| Elastic Storage | No | Scaling Storage is not an option after launching an instance |
| RAID Support | No | We usually have RAID 10 for production workload and RDS doesn't have options to configure RAID |
| Point in Time Restore on Same Instance | No | You can't do Point in Time Restore on the existing database. You have to spin up new Instance |
| AlwaysOn Availability Groups | No | This provides ability to failover group of databases to your secondary instance |
| Mirroring | Yes | Mirroring is Deprecated feature and its replaced with AlwaysOn Availability Groups |
| Linked Servers from RDS | No | But Linked Servers to RDS is Allowed. |
| Service Broker | No | Comes handy for services |
| Extended Events | No | Microsoft deprecated Profiler and pushing towards Extended events because its lightweight and better functionality. |
| Local Date Time | No | GETDATE is engineered to return GETUTCDATE |
| SAFE CLR | Yes | SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. |
| Across Region Encrypted Snapshot | No | Encrypted Snapshot can’t be copied to different Region |
| Read Replicas | No | If you want to offload your Read workload to replicated database, you are out of luck. You can’t setup replication from RDS to on-premise database but the on-premise to RDS Replication is allowed. |
No Admin privileges.
You can’t execute normal sql server system stored procedures and you need to work with options group and parameters group to modify configuration. You can’t execute sp_configure to change configurations.
No backup and Restore Option:
You cannot use the BACKUP and RESTORE commands to import data into a DB instance because Amazon RDS does not allow OS-level access that would enable you to place files in a physical location that the database engine could access. You also cannot import data using the Copy Database Wizard in SQL Server Management Studio because the tool requires sysadmin privilege on the source and destination servers and this permission is not available to the master user account for a DB instance.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html
Native Backup and Restore:
http://ramblingsofraju.com/sql-server/aws-sql-server-rds-native-backup-restore/
Now Native Backup and Restore option is available for AWS SQL Server RDS
This is really big deal because without this option moving medium to large size database is operationally inefficient and time consuming without providing any value.
July 27 2016 Annoucement : https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/
Database Migration Wizard:
We used codeplex SQL Database Migration wizard. Even though its time consuming process to migrate medium to large size database to RDS, this tool worked well for our use case.
SQL Database Migration Wizard Download link
https://sqlazuremw.codeplex.com/
Point in Time Restore:
You don’t have the option to do point in time restore on individual database level. You need to spin up new instance with different instance name for point in time restore.
You don’t operate on Database level anymore:
Traditional database backups are replaced with snapshots which are system level backups. If you want to restore a single database, you need to restore the entire snapshot which brings entire machine with all databases. You lose the ability to control individual database restores.
Linked Servers from RDS is not Allowed
You can’t create linked server from RDS instance but you can create Linked Server to RDS. That means , if you need to have linked server connection to on-premise database, you can’t do that.
No Server Level Triggers
No Service Broker
Currently Service Broker is not supported on AWS SQL Server RDS.
Extended Events
Currently Extended Events are not supported either.
System Time is fixed to UTC.
Its probably good thing, so you can always make sense out of the date time data regardless of time zone. But it does bring overhead of converting it to local time zone for application consumption especially doing the daylight savings offset.
Only Safe CLR is supported.
Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.
Encrypted Snapshot can’t be copied to different Region
You can’t copy the encrypted snapshot to different Region and if you like to spin up new instance , you probably need to engineer solution to copy the database.
No Database Mail Support
No File Stream Support
Read Replicas: ( RDS to On-Premise Replication )
If you want to offload your Read workload to replicated database, you are out of luck. You can’t setup replication from RDS to on-premise database but the on-premise to RDS Replication is allowed.
Over Provisioning
We initially launched r3.8x large instance with 20,000 Provisioned IOPS on Standard SQL Server Edition. But Standard SQL Server Max memory for Standard Edition is 128 Gig. So pay close attention to options you choose .
Renaming a Database on a DB Instance in a SQL Server Multi-AZ with Mirroring Deployment
You can’t rename a database on a SQL Server DB instance that is in a SQL Server Multi-AZ with Mirroring deployment. If you need to rename a database on such an instance, first turn off Multi-AZ with Mirroring for the DB instance, then rename the database, and finally turn Multi-AZ with Mirroring back on for the DB instance.
EXEC rdsadmin.dbo.rds_modify_db_name ‘OldDBName’, ‘newDBName’
RAID Configuration
This is very critical for high throughput. Usually we use RAID 10 configuration for our databases but we couldn’t do RAID storage with Amazon RDS which handicaps the Read / Write throughput.
Scaling Storage is not an option after launching an instance
Scaling storage after launching a DB Instance is currently not supported for SQL Server. You may want to provision storage based on anticipated future storage growth.
If you want to modify the storage , you will get error similar to this.
AllocatedStorage cannot be modified for DB Instances with this engine: sqlserver-se (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue; Request ID: 4a1e3675-4bb0-11e6-825a-33cd0a0d4544)
Storage and IOPS needs to be incremented linearly for higher performance. You can’t get higher IOPS without increasing storage.
RDS SQL Server SE requires storage increments of 100 GB
RDS SQL Server SE requires 3000-10000 IOPS for 1000 GB storage
General Purpose (SSD) storage is suitable for a broad range of database workloads. Provides baseline of 3 IOPS/GB and ability to burst to 3,000 IOPS.
Provisioned IOPS (SSD) storage is suitable for I/O-intensive database workloads. Provides flexibility to provision I/O ranging from 1,000 to 30,000 IOPS.
IOPS Calculation:
Each page read or write constitutes one I/O operation. Database operations that read or write more than a single page will use multiple I/O operations for each database operation.SQL Sever pages are 8 KB in size.
If you want to modify IOPS for the snapshot , you will get message similar to this.
Magnetic Cannot restore this instance based in Windows OS because the request has a different iops than the backup.
(Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue; Request ID: 0fc22e3e-5411-11e6-8436-3dff9c356ac5)torage may be used for small database workloads where data is accessed less frequently.
Instant File Initialization: Not Enabled
In SQL Server, data files can be initialized instantaneously. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.
https://msdn.microsoft.com/en-us/library/ms175935.aspx.
This means auto growth events need to be avoided otherwise you will notice performance degradation when you most need it.
Mirroring:
Deprecated feature in SQL Server 2016 and its replaced with Always On Availability Groups. AWS RDS uses Mirroring and its deprecated featured in SQL Server 2016. Its replaced with Always On Availability groups .
| High availability | database mirroring | Always On Availability Groups. If your edition of SQL Server does not support Always On Availability Groups, use log shipping. |
Synchronous Mirroring
No Read Replicas
You can’t configure the standby to accept database read activity.
Failover is not immediate
Failover times are typically 60-120 seconds. However, large transactions or a lengthy recovery process can increase failover time. When the failover is complete, it can take additional time for the RDS console UI to reflect the new Availability Zone.
With a Multi-AZ deployment using Mirroring, Amazon RDS manages failover by actively monitoring your Multi-AZ deployment and initiating a failover when a problem with your primary occurs. Failover doesn’t occur unless the standby and primary are fully in sync.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_SQLServerMultiAZ.html#USER_SQLServerMultiAZ.Location
You don’t have control over which AZ you want to connect.
You cannot select the Availability Zone (AZ) for the standby instance, so when you deploy application hosts, take this into account. Your database could fail over to another AZ, and the application hosts might not be in the same AZ as the database. For this reason, it is a best practice to balance your application hosts across all AZs in the region. We usually spin up database instance first and obtain primary and secondary database instances Availability zones and launch our app servers in those Availability zones.
Configure DB Subnet to have at-least two AZs.
To use SQL Server Multi-AZ with Mirroring with a SQL Server DB instance in a VPC, you first create a DB subnet group that has subnets in at least two distinct Availability Zones. You then assign the DB subnet group to the SQL Server DB instance that is being mirrored.
Multi AZ Deployments
Multi-AZ with Mirroring is not supported for instances with dedicated tenancy.
Cross-region Multi-AZ is not currently supported.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_SQLServerMultiAZ.html
AWS Notes on working with Multi-AZ deployments
Multi-AZ Failover is all or nothing. You don’t have individual database level control.
Multi-AZ deployments maintain all databases on the same node. If a database on the primary host fails over, all your SQL Server databases fail over as one atomic unit to your standby host. Multi-AZ with Mirroring supports one standby mirror.
AWS Forum post about increasing IOPS and storage after launching an instance.
The storage will need to be scaled at the same time as the IOPS according to:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/RDSFAQ.PIOPS.html
https://forums.aws.amazon.com/message.jspa?messageID=423906
Once I create a Provisioned IOPS volume, can I scale my IOPS up?
Yes, you can scale your IOPS up in increments of 1,000 IOPS up to the maximum for your DB Engine. However, you must scale your storage linearly as well. For example, if you have a DB instance with 100GB of storage and 1,000 IOPS, you could scale to 200 GB and 2,000 IOPS, but you could not scale to 200GB and 1,000 IOPS, for example. You can make these changes using the console, API, or command line. Please note that scale storage and IOPS operations are only supported for the MySQL and Oracle DB Engines.
SQL Server 2016 is not available on AWS RDS.
SQL Server Versions
http://ramblingsofraju.com/sql-server/sql-server-internal-versions/
RDS SLA Link https://aws.amazon.com/rds/sla/
The RDS SLA affirms that AWS will use commercially reasonable efforts to make Multi-AZ instances of Amazon RDS available with a Monthly Uptime Percentage of at least 99.95% during any monthly billing cycle. In the event Amazon RDS does not meet the Monthly Uptime Percentage commitment, affected customers will be eligible to receive a service credit.
Test Multi-AZ Failover carefully for your use case and throughly understand the implications for your workload.
SLA level of 99.95 %
DownTime for 99.95 % SLA
Weekly: 5m 2.4s
Monthly: 21m 54.9s
Yearly: 4h 22m 58.5s
Service Credits
Service Credits are calculated as a percentage of the charges paid by you for the Multi-AZ instances that did not meet the Monthly Uptime Percentage commitment in a billing cycle .
Less than 99.95% but equal to or greater than 99.0% – 10% Credit
Less than 99.0% – 25% Credit
RDS Specific Commands
-- Rename database EXEC rdsadmin.dbo.rds_modify_db_name N'Olddbname', N'NewDBName' -- Read Error Log EXEC rdsadmin.dbo.rds_read_error_log @index = 0, @type = 2; -- Delete SQL Agent Job EXEC msdb..sp_delete_job @job_name = '';
Error Encountered :
Permission lock-down
Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15 [Batch Start Line 0]
User does not have permission to perform this action.
Msg 262, Level 14, State 1, Line 1
BACKUP LOG permission denied in database ”.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Lack of 2016 Support
Magnetic Cannot restore this instance based in Windows OS because the request has a different iops than the backup.
(Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue; Request ID: 0fc22e3e-5411-11e6-8436-3dff9c356ac5)torage may be used for small database workloads where data is accessed less frequently.
Lack of Storage Elasticity
AllocatedStorage cannot be modified for DB Instances with this engine: sqlserver-se (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue; Request ID: 4a1e3675-4bb0-11e6-825a-33cd0a0d4544)
References:
SQL Database Migration Wizard Download link :
https://sqlazuremw.codeplex.com/
AWS White Paper :
https://d0.awsstatic.com/whitepapers/rdbms-in-the-cloud-sql-server-on-aws.pdf
AWS SQL Server RDS User Guide :
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html
AWS Simple Monthly Calculator :
https://calculator.s3.amazonaws.com/index.html
Safe CLR Description:
https://msdn.microsoft.com/en-us/library/ms345101.aspx
SQL Server Max Memory Reference :
https://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx
Provisioned IOPS Info
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/RDSFAQ.PIOPS.html
https://forums.aws.amazon.com/message.jspa?messageID=423906
Multi AZ Deployments
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_SQLServerMultiAZ.html
RDS SLA Link
https://aws.amazon.com/rds/sla/
RDS Rename Database
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.html#Appendix.SQLServer.CommonDBATasks.RenamingDB
Native backup Restore support Annoucement
https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/
Awesome Article!!