Cloud Amazon_Web_Services

Published on June 25th, 2015 | by Warner Chaves

20

DBA Guide to running SQL Server on Amazon RDS

UPDATE: I have published a course on Pluralsight on implementing SQL Server on RDS, including how to build, configure, setup HA and replication to RDS. The course can be found HERE.

In case you didn’t notice, the cloud wars are here. I feel like we’re back in the Netscape vs Internet Explorer days of the 90’s here except the battle is not for the desktop but for the development and infrastructure platform. Just like any other major database (SQL or NoSQL), SQL Server now has many options and variations to run on the different clouds:

  • Amazon Infrastructure as a Service: run SQL on a Windows VM.
  • Azure Infrastructure as a Service: run SQL on a Windows VM.
  • Amazon SQL Server RDS: run SQL Server as a DaaS (Database as a Service) platform.
  • Azure SQL Database: run a very-close-to-SQL Server variation of your database on Microsoft’s DaaS platform.

Now each one of those comes with it’s own set of caveats, pros and cons. We already covered Azure SQL Database here. And I want to eventually cover my recommendations and best practices for the other ones on different blog posts here at SQLTurbo. For today though, we’re going to focus on SQL Server on Amazon RDS and run through what the service offers and what our recommendations are.

First things first, RDS is a Database as a Service offering, so you give up fine grained control and some features of the product in exchange for a managed service where Amazon takes care of the infrastructure, high availability and backups. You can connect to your SQL instance but you have no access to the underlying host, nor will you get sysadmin access. We’ll go into detail for each feature on the different sections below.

Versions and licensing

Currently Amazon RDS supports SQL 2008 R2, SQL 2012 and SQL 2014 (this last one up to Standard Edition). The editions can go from Express to Enterprise, however, if you want to run Enterprise then you’ll need to provide your own license. Amazon does NOT offer pay-as-you-go billing for SQL Enterprise edition.

Managed Activities

There are several main activities that Amazon will take off your hands as part of providing the database service:

  1. Patching: as part of your configuration you can specify a maintenance window and sign up to get patches applied. Note however that you have no control over what patch level Amazon wants to certify and bring RDS up to. For example, SQL 2008 R2 is currently offered on the SP1 + CU3 version, dating back to October 2011! SQL Server 2012 does NOT do much better, the current version offered is the very first RTM launch version, dating back to March 2012!
  2. Upgrade: you can, if you want, have the service do a major version upgrade from 2008 R2 to 2012.
  3. Backups: you can specify a retention period of up to 35 days. Amazon’s documentation offers an RPO of 5 minutes, however, I’m not sure how (or if) this is enforced on an official SLA. Another thing worth noting is that RDS does NOT support changing a databases recovery model. If you change from FULL to SIMPLE then Amazon will change it back. If you remove backup retention then Amazon will just set it to SIMPLE. Database snapshots can also be taken and up to 50 can be kept by Amazon.
  4. High Availability: to provide high availability, RDS can automatically setup a database mirroring secondary on another Availability Zone (another Amazon data center in the same region). As part of their service, Amazon will also keep logins and their permissions in sync between the primary and secondary. Unfortunately, jobs are not kept in sync, this will be your responsibility.

Performance and Scalability

Performance for SQL Server on Amazon RDS is very similar to specifying your hardware requirements on Infrastructure as a Service virtual machines.

You have a choice of different numbers of vCpus and RAM as well as storage from General SSD (middle ground in terms of performance), Provisioned IOPS SSD (highest performance, highest cost) and magnetic (lowest cost, lowest performance). Some of the instance classes will also say if they are EBS optimized. This means that Amazon will have a dedicated connection and bandwidth just for storage.

Of all the instance types, the T2 one is of special interest. This type is Burst Capable. This means that the instance has lower cost than the higher fixed performance ones but also has the capability of bursting CPU to meet periodic high demands. This is ideal for workloads where there are significant ups and downs on load during the day.

As part of being a cloud service, you can scale from one instance type to another to modify CPU and RAM capacity at any time. This however, cannot be said for storage!

big big caveat when dealing with SQL Server on RDS is that unlike the other RDS services, SQL Server does not support changing storage after deployment. This basically means that whatever size and characteristics you chose at the beginning of your deployment must account for future growth. Any change in storage will require to create a new instance with the new storage config and migrate to that.

Other Major Gotchas

Due to the nature of being a cloud service, Amazon has to put some limitations on the product that will allow them to automate and easily manage the service while at the same time preventing users from killing their platform. These are some possible show stoppers that we’ve run into when exploring an RDS deployment with clients:

  • Each RDS instance can have a max of 30 Dbs.
  • Max size of the instance is 4TB.
  • You CANNOT do a backup/restore from a full SQL Server to SQL on RDS. You will have to do logical data movement through scripting, SSIS or BCP.
  • Distributed queries (linked servers and MSDTC) are NOT officially supported. We’ve had success LINKING TO RDS but NOT LINKING OUT from RDS.
  • SQL Server Replication is also NOT officially supported. Forum posts talk about different user experiences regarding this one, some got it working, others not. In the end it’s not officially supported, so even if it works today, if it stops working tomorrow you have no one to complain to. Given this situation, we can’t recommend anyone using replication on RDS.
  • Even though SQL 2012 introduced AlwaysOn Availability Groups, Amazon is NOT using it, the HA setup is still going to be database mirroring.
  • Instant file initialization is NOT enabled on RDS and you can’t enable it. This means autogrowth events need to be avoided AT ALL COST.
  • Amazon RDS instances get created with the SQL_Latin1_General_CP1_CI_AS collation. You can specify the individual database collation to be different but this can lead to issues when joining data with tempdb for example, since tempdb will inherit the default instance collation.

Previously Windows Authentication was NOT supported and it was part of our list of gotchas. This has changed recently though and it is now supported in RDS! The details can be found HERE.

Monitoring and tracing

You can monitor your databases through a regular SQL connection as long as the script doesn’t access anything blocked by RDS. For example, to monitor the SQL errorlog and the Agent log, you will need to use the SP provided by Amazon for this specific purpose (rdsadmin.dbo.rds_read_error_log). You can also access these logs from the AWS RDS console.

Amazon’s own monitoring Cloudwatch service also provides a set of metrics that can be monitored for baselining and setup for alerting. More details on Cloudwatch for RDS are here.

For tracing, you can connect SQL Server Profiler from another server or create a Profiler server-side trace. Extended events unfortunately are NOT yet supported.

Running jobs

Amazon supports running the SQL Server Agent. Some limitations apply since you don’t have access to the underlying host, so for example, command line steps won’t work. Email notifications are also NOT available.

Encryption

Both SSL connection encryption and TDE are supported on RDS. You’ll need Enterprise licensing for TDE. Amazon also provides their own encryption at rest as an instance option.

Best Practices

  • If you’re going to run production you definitely need to go with the automated Multi-Availability zone deployment. In the cloud, you never know when things might go down and you have no control of the infrastructure, you have to use this redundancy.
  • Predetermine your necessary creation config options like: will the RDS instance have a public-facing endpoint, port, VPC, etc.
  • Predetermine your configuration for the instance itself. This is done through what Amazon calls a Parameter Group. The Parameter group includes options that you would normally find under sp_configure and also some popular trace flags.
  • If you’re migrating from an existing install, make sure you’re not moving back on patch level to what RDS is offering. You could end up with a situation where you had an issue prevented by a fix that reappears after a minor version downgrade.
  • Pre-grow all your files. On-premises you can get away with having instant file initialization and autogrowth, however, without instant file initialization, autogrowth on RDS will make sessions wait and it could be enough to throw timeouts.
  • Measure the throughput and latency you get with the Multi-AZ deployment. RDS only offers synchronous replication so you will need to take this into account as part of your planning.
  • Just like on-premises SQL Server, the mirroring failover will depend on the speed of transaction replay and recovery. Make sure to test this properly.
  • For high performance production workloads we recommend an EBS optimized instance with Provisioned IOPS.
  • For medium performance workloads with lower concurrency and more in tune with a “business hours” schedule, the T2 type instances are great from a cost/benefit perspective.
  • For cheap archival type databases, a small instance type with magnetic storage is enough to provide access to the data at a very low cost. It will be slow but at least it will be available.

I hope this is a handy guide for everyone considering deploying SQL Server on RDS,.If there are any questions you want us to cover and add to the guide or any tips you want to share please leave it on the comments below!

Tags: , , , , ,


About the Author

SQL Server Certified Master and MVP. DBA for MS technologies for over 10 years.



20 Responses to DBA Guide to running SQL Server on Amazon RDS

  1. g_demetriou@hotmail.com' George D says:

    Hi Warner,

    I was wondering what your thoughts were on running a fairly large 250GB database, with a weekly insert/update ETL on a single table with 250m rows and an insert on a history table with over 1b rows. I’m new to the cloud services, and was surprised by the lack of SSIS on the Azure SQL (Data Factory instead). I was thinking of going for a VM with SQL instead, and Amazon seems to be far cheaper for this. Any advice or gotchas, in particular to SSIS, and the ability to transfer a file for processing against the db via a website?

    • Warner Chaves says:

      Hi George, I will speak from the experience of our current clients. As nice as Azure Data Factory is, most of our clients have already invested a lot of time into SSIS packages so this is still their tool of choice. As you mentioned, you can provision a small-ish VM and just use it as an SSIS package runner, you can do this easily on both AWS or Azure. The only trickier part might be connectivity to outside if any firewalls or security has to be tweaked. Other than that, if you’re running large inserts then the usual SQL tuning recommendations apply: fast transaction log with low VLF count, commit on large batches, explore bulk insert and minimal logging and if using SQL Enterprise 2014, clustered columnstores are awesome for historical tables. Cheers!

      • pamar66@gmail.com' Paul says:

        Table partitioning and compression supported (as with EE) ?

      • pamar66@gmail.com' Paul says:

        Table partitioning and data compression supported (as with EE) ?

        • Warner Chaves says:

          Hi Paul, yeah both of those are supported. The only caveat is that unlike on-premises where you can have different types of storage to target different partitions, on RDS you pick one storage type and size and that’s it. Cheers!

  2. win32nipuh@gmail.com' Oleg says:

    Hi Warner, thank you.
    Some questions:
    1. Does it support CLR – SAFE, UNSAFE?
    2. Does it support extended stored procedures?

    Regards,
    Oleg

    • Warner Chaves says:

      Hi Oleg! Extended sps and UNSAFE clr are NOT available. Reason for it is quite simple, both allow the possibility of running code that * lives * outside of SQL Server itself so Amazon needs to limit it. Otherwise think of the security and stability implications of letting people run any type of unmanaged code as part of RDS.

      If this is a requirement for you then you’ll have to go with just normal SQL running on a VM. Cheers!

      • win32nipuh@gmail.com' Oleg says:

        Hi Warner,
        thank you for the answer.
        Yes, sure, it is clear.
        We are thinking about moving our system to Amazon RDS or VM. And I am trying to find the list of SQL Server restrictions in comparison with usual SQL Server installations.
        Yes, we use Unsafe CLR procs, xp’s, Service Broker queues.
        Btw, what about Service Broker?
        What did you mean ‘VM’? is Amazon RDS?
        Regards,
        Oleg.

  3. win32nipuh@gmail.com' Oleg says:

    Hi Warner,
    how to enable Service Broker in RDS SQL Server?
    ALTER DATABASE is not allowed.

    Regards,
    Oleg.

    • Warner Chaves says:

      Hi Oleg, this hasn’t changed, Service Broker is NOT supported in SQL Server on RDS. You will need a regular Windows Virtual Machine and install SQL Server there to use Service Broker.

      • win32nipuh@gmail.com' Oleg says:

        The new version of RDS SQL Server supports it but endpoints

        • Warner Chaves says:

          It does? That’s news to me, the official docs here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

          Still say:

          Amazon RDS currently does not support the following SQL Server features:

          Maintenance Plans
          Database Mail
          Distributed Queries (i.e., Linked Servers)
          Database Log Shipping
          Windows Authentication
          Change Data Capture (CDC) – Consider using Change Tracking as an alternative to CDC.
          Replication
          The ability to run Reporting, Analysis, Integration, or Master Data Services on the same server as the DB instance. If you need to do this, we recommend that you either install SQL Server on an EC2 instance or use an on-premise SQL Server instance to act as the Reporting, Analysis, Integration, or Master Data Services server.
          Performance Data Collector
          Service Broker or additional T-SQL endpoints (all operations using CREATE ENDPOINT are unavailable)

          I don’t have experience with any type of workaround that has to be used to get it working on RDS though, sorry I can’t be of more help.

          • win32nipuh@gmail.com' Oleg says:

            Ok, thnak you. Please check this
            http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html


            In addition to supported features of SQL Server 2008 R2, Amazon RDS supports the following SQL Server 2012 features:

            Columnstore indexes (Enterprise Edition)
            Online Index Create, Rebuild and Drop for XML, varchar(max), nvarchar(max), and varbinary(max) data types (Enterprise Edition)
            Flexible Server Roles
            Service Broker (note that Service Broker Endpoints are not supported)
            Partially Contained Databases
            Sequences
            Transparent Data Encryption (Enterprise Edition only)
            THROW statement
            New and enhanced spatial types
            UTF-16 Support
            ALTER ANY SERVER ROLE server-level permission

  4. asksubir@gmail.com' Subir Das says:

    How can we deploy SSIS packages in aws

    • Warner Chaves says:

      Hi Subir, you’ll have to use a regular Windows virtual machine and deploy your SSIS packages to run from there.

  5. njohnson@collectors.com' Nate says:

    You say you’ve “had success LINKING TO RDS” — how? I am trying desperately to get that to work from an on-prem SQL instance to an AWS RDS instance, and it’s not working. What provider/connection-type did you use? Thank you for your reply!

    • Warner Chaves says:

      Hi Nate, I used the SQL Native Client just as if it was an on-premises SQL Server. 99% of the times the issue is connectivity related, did you whitelist your SQL Server from the network group of the VPC that hosts the RDS instance? A good way to test it is just to try to connect SSMS from the SQL Server machine to the RDS instance and see if it works to verify you can reach it and authenticate to it.

  6. pamar66@gmail.com' Paul says:

    - DBMail ? Is it supported if you subscribe to SES ? or not supported at all ?
    – BCP / Bulk Insert ? supported or not ? if so, what type of f/s access ?
    – Need to use their ETL Tool(s) ?
    – FTP in and out ? to folders SQL Instance ?
    – Powershell on SQL Host ? run via SQL Agent ?
    – CLRs / Assemblies ?
    – Can I reference an AWS RDS “host” from a local Host ?
    – via SQL Link ?
    – via Opendatasource
    – via SSMS on my client ?
    – Limits on data pushing / pulling ?
    – for ETLs
    – stage all data to a local Instance and push pull via SQL Link or Opendatasource query
    – Can stored procedure reference SQL Link to RDS Instance ?

    or is RDS JUST an Operational Data Store with basic functionality for data manipulation ?

    • Warner Chaves says:

      Hi Paul, that’s a long list, I would say you should refer to the Amazon documentation on exactly which feature is supported and how. However, I can definitely answer your queestion about RDS being just an ODS for data manipulation.

      We currently have clients in production running active SQL workloads on RDS. The main features that are blocked are the ones that open access to the OS and the ones that allow communication to the outside world since both of those are security and stability threats for Amazon to run the service. Connectivity from 3rd party tools, SSMS, SSIS, etc all work, you just have to run then from another machine.

      If you do require something like db mail or having linked servers out of your RDS machine then the service is probably not a good fit for your use case and you’re better off with building an EC2 VM and then you can have the entire set of features that SQL Server has.

      Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to Top ↑
  • Sign Up For Our Newsletter

    Stay up to date on the latest from SQL Server products and Features.

    Sign Up Now

    Sign up for mailing list and receive new posts directly to your inbox.

    We promise never to share or sell any of your personal information.

  • Recent Posts

  • Categories

  • Archives

  • Tags

  • Topics

  • Subscribe to our RSS Feed


    sqlturbo.com A SQL Server Web Blog
    Canada, Ontario, Ottawa.
    http://sqlturbo.com

    The Authors