Continuing on SQL Server on Amazon Web Services RDS journey that I started on previous post. In that post I covered basics of RDS, creating SQL Server instance in RDS and connecting to it. In this post I would like to cover additional topics that are near and dear to any RDBMS DBA, including backups\restores, HADR and talk about limitations of SQL Server on RDS.
Limitations for SQL Server in Amazon Web Services RDS. When you are setting up SQL Server in RDS you need to be aware of following:
- You only get SQL Server basic RDBMS Engine Services. SQL Server comes in with large number of components in addition to basic database services, These include Analysis, Reporting and Integration Services, Master Data Services, Distributed Relay, etc. As you setup SQL Server on premises you pick components that you need. With RDS all you can host is DB Engine Service, So if your application architecture involves running SQL Server instances with SSAS or SSRS, those components will have to be hosted elsewhere: this can be either an on-premise server within your network or an EC2 instance in the Amazon cloud. As an architectural best practice, it would make sense to host them in EC2.
- Although you can login from SQL Management Studio (SSMS) there is no Remote Desktop option available that I could find.
- Very limited file access.
- Size limitations. AFAIK the minimum size of a SQL Server RDS instance for Standard or Enterprise Edition is 200 GB.and max is 4 TB. Beyond 4 TB you would have create another instance and probably implement some sort of sharding methodology.
- Another limitation to be aware of is the number of SQL Server databases an RDS instance can host. It’s only 30 per instance.
- There are no high availability options available. So no replication, no log shipping, no AlwaysOn and no manual configuration of database mirroring. Mirroring is enabled for all databases if you are opting for a Multi-AZ rollout. The secondary replica hosts the mirrored databases.
- No distributed transaction support with MSDTC
- No FileStream, CDC, SQL Audit , Policy Based Management, etc.
So if I have no AlwaysOn Availability Groups or cluster how would HADR work?
Amazon RDS provides high availability and failover support for DB instances using Multi-AZ deployments. Multi-AZ deployments for Oracle, PostgreSQL, MySQL, and MariaDB DB instances use Amazon technology, while SQL Server DB instances use SQL Server Mirroring. In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. The primary DB instance is synchronously replicated across Availability Zones to a standby replica to provide data redundancy, eliminate I/O freezes, and minimize latency spikes during system backups.
The RDS console shows the Availability Zone of the standby replica (called the secondary AZ), or you can use the command rds-describe-db-instances or the API action DescribeDBInstances to find the secondary AZ. When using the BYOL licensing model, you must have a license for both the primary instance and the standby replica.
In the event of a planned or unplanned outage of your DB instance, Amazon RDS automatically switches to a standby replica in another Availability Zone if you have enabled Multi-AZ. The time it takes for the failover to complete depends on the database activity and other conditions at the time the primary DB instance became unavailable. Failover times are typically 60-120 seconds. However, large transactions or a lengthy recovery process can increase failover time. The failover mechanism automatically changes the DNS record of the DB instance to point to the standby DB instance.
Backups and Restores. Backup and restore has always been the easiest way of migrating SQL Server databases. Unfortunately this option is not available for RDS so DBAs have to fall back on the manual process of creating database schemas and importing data. You can also use the Database Import Export Wizard in SQL Server to move your data. In general Amazon defines has more on that procedure here – http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html
Interestingly you can also use SQLAzureMW tool http://sqlazuremw.codeplex.com/ to move schema and data from SQL Server to RDS.
That allows you to pick source objects to migrate:
Then you connect to destination and transfer schema and data: To me that’s much easier than scripting and transferring schema and using bcp separately on data.
For more info see – http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.html, https://youtu.be/7t2-95NDfBU, http://www.nimbo.com/blog/microsoft-sql-server-high-availability-ha-cloud/