There are two primary ways to run SQL server in the Amazon cloud, each with its pros and cons:
1. Managed Database: deploying SQL server using Amazon RDSLaunching a machine instance on EC2, using an Amazon Machine Image (AMI) with SQL server already installed and configured, and storing the data in an Elastic Block Storage (EBS) instance.
In the following sections we’ll explain the details and pros and cons of each of these deployment options:
Before we get into the deployment options, here are some basic details you should know before deploying SQL Server in AWS.
Licensing options:
Migration tools:
Amazon RDS provides a simple way to deploy SQL Server (and other types of relational databases) in the AWS cloud. RDS lets you choose among SQL Server versions, including 2012, 2014, 2016, 2017, and 2019, as well as Express, Web, Standard or Enterprise editions.
The edition you choose opens two out of three possible configuration templates: Free tier, Dev/Test, or Production. These templates enable 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.
Elastic scalability
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, which means it can detect when space is running out and allocate additional storage without any intervention at all.
Pay on demand
RDS for SQL Server is priced hourly, and there is no need to make any significant upfront investment—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.
Instance limitations
You should pay close 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.
Less control
When using a managed service, you don’t have control over how the database is configured. That includes no control over where the data is located, which limits data hybridity and its availability between regions.
Vendor lock-in
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 management tasks you need to perform on your own. 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 the standards of your company.
A good alternative for some cases is to combine SQL Server with two other services offered by AWS—Amazon EC2 and Amazon Elastic Block Store (EBS). This means you need to install and deploy SQL Server on an EC2 instance, and add Amazon Elastic Block Store (EBS) as a durable and pluggable storage.
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, you can use Amazon Machine Images (AMI) to make things easier:
Cost effective
This option can be cheaper than using RDS, and it provides you with the flexibility of running SQL Server as you would do so in your own data center (i.e. without the limitations of RDS).
Licensing
Another benefit to using this method is that it enables you to bring your own license. This means you won’t need to repurchase another SQL Server licensing when moving to AWS.
Flexibility
Overall, databases built using EBS and EC2 provide 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.
Scalability
This method allows you to avoid hitting the size limitation on RDS of 16 TB by spinning up new instances and volumes as needed.
Data protection
You can configure data protection schedules to better match your specific recovery requirements.
Features
Lets you directly use the latest capabilities of the database and have full control over its management and tuning. However, you are not in control of all of the management tasks.
The downsides of self-managing SQL server on AWS are the increased complexity of managing EC2 and EBS instances, and the difficulty of managing auto scaling on your own.
NetApp Cloud Volumes ONTAP helps you get all the benefits of the self-managed deployment option—without the complexity.
Cloud Volumes ONTAP extends your control and provides more capabilities, including a number of automatic features that aren’t available using either the managed service option on RDS or the native AWS storage model.
When using NetApp Cloud Volumes ONTAP you can leverage the following features:
Case Study: How AdvancedMD Used Cloud Volumes ONTAP to Run SQL Server on AWS
AdvancedMD is a health industry service provider, which 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 extend their SQL database capabilities.
With the help of Cloud Volumes ONTAP, management became much easier thanks to automation via NetApp Cloud Manager, automatic failover and failback DR processes, instant storage snapshots for data protection, zero-cost data cloning, and reduced cloud data storage costs.
AdvancedMD found it to be a safer, more scalable way to run SQL Server with additional benefits and a lower monthly bill.
Learn more about the AdvancedMD customer story here, and about NetApp Cloud Volumes ONTAP for MS SQL Server here.