More about AWS database
- AWS PostgreSQL: Managed or Self-Managed?
- MySQL Database Migration: Amazon EC2-Hosted vs. Amazon RDS
- AWS MySQL: MySQL as a Service vs. Self Managed in the Cloud
- AWS ElastiCache for Redis: How to Use the AWS Redis Service
- AWS Data Analytics: Choosing the Best Option for You
- Amazon DocumentDB: Basics and Best Practices
- AWS NoSQL: Choosing the Best Option for You
- AWS Big Data: 6 Options You Should Consider
- Oracle on AWS: Managed Service vs. Managed Storage Options
- AWS Database as a Service: 8 Ways to Manage DBs in AWS
- AWS Oracle RDS: Running Your First Oracle Database on Amazon
- SQL Server in AWS: Two Deployment Options
- DynamoDB Pricing: How to Optimize Usage and Reduce Costs
- AWS Databases: The Power of Purpose-Built Database Engines
- AWS Oracle: How to Lift and Shift Your Oracle DB to Amazon
- Overcome AWS RDS instance Size Limits with Data Tiering to AWS S3
Two Options for Deploying SQL Server in AWS
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 RDS
Amazon Relational Database Service (RDS) lets you deploy different editions of SQL server including Express, Web, Standard and Enterprise Edition, using the License Included service model, with maximum storage of 16TB.
2. Self-managed: deploying SQL server on EC2 with EBS
Launching 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:
- Option 1: Managed—SQL Server on RDS
- Option 2: Self-Managed—Deploying SQL Server with EC2/EBS
- SQL Server in AWS with NetApp Cloud Volumes ONTAP: The Best of Both Worlds
Before You Begin: Licensing and Tools
Before we get into the deployment options, here are some basic details you should know before deploying SQL Server in AWS.
- Standard vs Enterprise—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.
- Specific licences—SQL Server also has Express, Developer, and Web licenses targeted at development or specific small-scale scenarios.
- Licence included—Amazon EC2 and Amazon RDS options for SQL Server support a license-included model. This means you don’t have to worry about licenses at all.
- BYOL—EC2, unlike RDS, allows you to Bring Your Own License (BYOL) as part of the AWS Microsoft License Mobility agreement, in case you already have an SQL Server license.
- AWS Data Migration Service (DMS)—whether it’s a one-time migration or an ongoing synchronization process, DMS can replicate data from your datacenter to AWS.
- Schema Conversion Tool (SCT)—can help you change your database engine.
- Snowball, Snowball Edge, and Snowmobile—can help you transfer huge amounts of data in less time than it would typically take when transferring over a network.
Option 1: Managed Deployment—SQL Server on RDS
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.
Pros of the Managed Option
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.
Cons of the Managed Option
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.
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.
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.
Option 2: Self-Managed Deployment—SQL Server with EC2 and EBS
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:
- You can create an Amazon Machine Image (AMI) and use it 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).
- There are several paid AMI options on the AWS Marketplace.
Pros of the Self-Managed Option
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).
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.
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.
This method allows you to avoid hitting the size limitation on RDS of 16 TB by spinning up new instances and volumes as needed.
You can configure data protection schedules to better match your specific recovery requirements.
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.
Cons of the Self-Managed Option
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.
SQL Server in AWS with NetApp Cloud Volumes ONTAP: The Best of Both Worlds
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:
- Point-in-time, instantly-creatable consistent snapshot copies
- Zero-cost clones
- Reduced storage consumption thanks to storage efficiencies like thin provisioning, data compression, deduplication, and automatic tiering of infrequently-used data (can save 70% of storage costs)
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.