Relational databases have been around for a long time, and even though NoSQL storage engines have become increasingly popular in recent years, their SQL counterparts aren’t going away anytime soon. Although NoSQL systems are great for high throughput and scale, relational databases still have a fundamental role where consistency is key, which is frequently the case at the enterprise level. How does this factor when running an AWS database?
As more and more companies turn to the cloud for their infrastructural requirements, what changes is not the role of the relational database but how to administer it. Since cloud providers offer a range of managed services, maintaining a relational database on the cloud can be done in a few different ways.
In this post we’ll focus specifically on SQL Server within the context of Amazon Web Services (AWS). What are the best deployment options for SQL Server in AWS? We’ll take a look both at the managed services provided by AWS and the alternative managed storage options, such as NetApp Cloud Volumes ONTAP.
Introduction to SQL Server
Microsoft SQL Server is a relational database management system (RDBS) whose roots stretch as far back as the late 1980s. A common choice for companies already entrenched in the Microsoft technology stack, SQL Server is, as of SQL Server 2017, also able to run on Linux.
SQL Server provides a variety of services on top of its core database engine, with capabilities including analytics, reporting, and full text search among others. Developers can leverage Transact-SQL (T-SQL), SQL Server’s SQL dialect, in order to support even the most complex applications.
SQL Server in AWS
Administrators deploying and maintaining SQL Server in AWS can opt to manage their own SQL Server instances running in EC2 instances (AWS’ virtual machine offering), or delegate that responsibility to AWS’ managed database service, Amazon Relational Database Service (Amazon RDS).
SQL Server’s main commercial licensing consists of the Standard and Enterprise editions. These are targeted at different organization sizes and have different resource limitations. Both can be licensed on a per-core basis, although the Standard edition also offers the option of a Server + Client Access License model. Additionally, SQL Server also has Express, Developer, and Web licenses targeted at development or specific small-scale scenarios.
Both the Amazon EC2 (self-managed) and Amazon RDS (AWS-managed) options for SQL Server support a license-included model, which means that you don’t have to worry about licenses at all. The EC2 option (unlike RDS) also allows you to Bring Your Own License (BYOL), in case you already have an SQL Server license.
AWS provides a number of tools to help migrate data from your premises to its cloud. Whether it’s a one-time migration or an ongoing synchronization process, the AWS Data Migration Service (DMS) can be used to replicate data from your datacenter to AWS. If you’d like to change to a different database engine, AWS also provides a Schema Conversion Tool (SCT) that can help. Finally, if you’ve got huge amounts of data that would take forever to transfer over a network, AWS has got you covered with offerings such as Snowball, Snowball Edge, and Snowmobile.
Using the AWS Built-In Service for SQL Server
AWS’ managed database offering, Amazon RDS, provides an easy and hassle-free way to deploy SQL Server (and other types of relational databases) on AWS. With RDS for SQL Server, you can choose among SQL Server versions including 2012, 2014, 2016 and 2017, and editions including Express, Web, Standard or Enterprise. The edition you choose opens two out of three possible configuration templates: Free tier, Dev/Test, or Production. These templates in turn allow you to choose from a range of AWS instance types with varying levels of allocated resources which will be running your database under the hood.
RDS for SQL Server operates under the License Included service model only, meaning you cannot bring your own license. Storage options are based on SSD only (General Purpose or Provisioned IOPS) and a maximum of 16TB can be allocated. Automatic backups are enabled by default, and you can configure how frequently these are taken. You also get to choose which VPC your RDS instance runs in, and can set up Windows Authentication if you need to.
One of the biggest benefits of RDS is that it’s elastic, i.e. you can effortlessly change the resources allocated to your SQL Server in AWS by tweaking a few settings, and the changes will be rolled out within minutes. RDS also supports storage autoscaling, meaning that it can detect when space is running out and allocate additional storage without any intervention at all.
RDS for SQL Server is priced hourly, meaning that there is no need to make any significant upfront investment, and you pay for what you use. This, combined with the ability to increase and decrease the allocated resources as needed, allows you to optimize your costs, or even quickly spin up RDS instances for occasional jobs and destroy them immediately afterwards. If your workloads are more predictable, you can take advantage of Reserved DB Instances, trading the aforementioned flexibility for significant discounts.
Although there are many advantages to this approach, you should also pay attention to the limitations of SQL Server on RDS. For instance, each RDS instance has a maximum number of SQL Server databases it can support, ranging from 30 to 100 depending on the instance type used. Individual databases can also be no larger than 16 TB.
But there’s more to what makes this managed service option challenging. Most importantly, you don’t have control over how the database is configured; that includes control over where the data is located, which limits data hybridity and its availability between regions. Vendor lock-in is another obvious drawback: once you’re using RDS, you won’t be able to have that data seamlessly work with any deployment outside of AWS. The service is also something that is paid for at a premium; however, there are still plenty of tasks that you will have to complete to manage the database yourself. Also, the set limitations of the services threshold for data protection, availability, and encryption can’t be changed, even if they don’t align with your company’s standards.
Other limitations of managed database services include a lag when it comes to the latest capabilities of the database (for example, AWS just recently began to support SSIS), management restrictions such as database renaming capabilities, and more.
Before using RDS, you will need to understand the limitations involved, and determine whether any obstacles posed are insurmountable.
Deploying SQL Server in an AWS Instance Using Managed Storage
While Amazon RDS is a great managed relational database offering that can help in a wide range of scenarios, there are cases when it is not feasible to use. This could be a result of its limitations posing a significant impediment to your requirements, or it could be for budgetary reasons.
In such situations, an alternative is to install and deploy your own SQL Server instance on an EC2 instance, combined with Amazon Elastic Block Store (EBS) for durable and pluggable storage. This option is cheaper than using RDS, gives you the flexibility of running SQL Server any way you would do so in your own data center (i.e. without the limitations of RDS), and also gives you the option to bring your own license—which means that any investment you made into SQL Server licensing does not have to be duplicated when moving to AWS.
Overall, databases built using EBS and EC2 mean more flexibility. You have complete control over how you configure the database, where its data is located, and you gain the ability to expand its use beyond the AWS ecosystem, such as in hybrid and multicloud deployments. It’s also a more scalable solution, allowing you to avoid hitting the size limitation on RDS of 16 TB by spinning up new instances and volumes as needed. You can also properly schedule the data protection schedules to better match your specific recovery requirements. You can also directly use the latest capabilities of the database and have full control over the management and tuning of the database. However, it does leave all of the management of those tasks up to you.
If you manage SQL Server yourself, you have to invest effort in setting it up and maintaining it just as you would do in your own datacenter. However, since it is possible to create an Amazon Machine Image (AMI) as a template for new EC2 instances, there are AMIs already available with SQL Server preinstalled, and with a lot of configuration already in place (e.g. allowing remote access), reducing the effort needed to set up SQL Server in EC2. These AMIs can be easily selected when launching a new EC2 instance, and several are available on the AWS Marketplace, although they are more restrictive on the instance types that can be deployed.
Costs for this type of deployment can be reduced even further through the use of a managed storage platform, such as NetApp Cloud Volumes ONTAP. This adds a number of automatic features that aren’t available using either the managed service option on RDS or the native AWS storage model: point-in-time, instantly-creatable consistent snapshot copies, zero-cost clones, storage consumption reduced by 50-70% thanks to storage efficiencies like thin provisioning, data compression, deduplication, automatic tiering of infrequently-used data to lower-cost Amazon S3 object storage, and more.
You can see how the managed storage option worked in the case study of AdvancedMD’s use of Cloud Volumes ONTAP for their SQL database. This health industry service provider needed to spin off from its parent company ADP and migrate the entirety of their database to the cloud from their data center. While initially planning to rely on a combination of native AWS EBS and AWS EFS, their cloud architects realized that managed storage via Cloud Volumes ONTAP would greatly enhance their SQL database capabilities.
Running SQL Server on AWS brings a lot of flexibility, allowing you to avoid large upfront investments and paying for what you use. You can also easily scale the allocated resources, and the costs involved, depending on the resources that are needed at any given time. If you want to migrate SQL Server to AWS, or even maintain a hybrid environment, there are a lot of tools and best practices that can help.
In AWS, you can use EC2 with EBS to run your own SQL Server, or leverage Amazon RDS and let AWS do it for you. SQL Server options are a little more limited with RDS, and it’s more expensive because you’re paying AWS to manage SQL Server for you. On the other hand, a self-managed SQL Server means you are responsible for the smooth and reliable operation of your organization’s databases. With the help of Cloud Volumes ONTAP, that management becomes much easier thanks to automation via Cloud Manager, automatic failover and failback DR processes, AWS high availability to ensure business continuity, instant storage snapshots for data protection, CI/CD DevOps benefits with zero-capacity cost data cloning technology, and reduced cloud data storage costs. It’s a safer, more scalable way to run SQL Server with additional benefits and a lower monthly bill.