Microsoft SQL Server in AWS: Managed or Self-Managed?

Microsoft SQL Server is one of the foundations of enterprise IT, providing a reliable database that can scale to meet even the most demanding mission-critical requirements. However, there are two ways to run SQL Server on AWS, and they’re fundamentally different.

The difference comes down to either managed or self-managed. You can run a managed database with Amazon Relational Database Server (Amazon RDS), or you can manage your own database that’s deployed on Amazon Elastic Compute Cloud (Amazon EC2) instances.

This blog takes a closer look at these two deployment models and explains the pros and cons of each option in detail. Plus, you find out how AWS and NetApp have partnered to create an innovative approach to self-managed SQL Server on AWS that supports many of the advantages of managed SQL Server deployment: Amazon FSx for NetApp ONTAP.

To start off, take a look at the two options for SQL Server deployment:

• The managed database option: Deploying SQL Server by using Amazon RDS

  Amazon RDS enables you to deploy different editions of SQL Server, including Express, Web, Standard, and Enterprise, by using the License Included service model. The maximum storage is 16TB.

  Another managed service option is Amazon RDS Custom for SQL Server. This option offers the same availability, performance, and security capabilities of the general Amazon RDS for SQL Server, but it gives you more control over the underlying database and operating system.

• The self-managed database option: Deploying SQL Server on Amazon EC2

  With this option, you must create your own infrastructure on AWS. You have to launch Amazon EC2 instances, choose the storage solution or service, and configure the networking and other elements to make sure that it all works together.

There are several licensing options for the base SQL Server that you decide to run, regardless of whether you choose to have it managed for you or manage it yourself. These licensing options include:

• Standard or Enterprise. SQL Server’s main commercial licensing options are the Standard and Enterprise editions. These options 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 licenses. SQL Server also has Express, Developer, and Web licenses that are targeted at development or specific small-scale scenarios.
• License Included. The managed Amazon RDS service supports SQL Server’s License Included mode, in which you don’t have to worry about purchasing a license. Instead, the cost of the license is included as part of the underlying compute resources in a pay-as-you-go pricing model.
• BYOL. If you’re deploying in a self-managed SQL Server database with Amazon EC2, you have the option to bring your own license (BYOL) as part of the License Mobility agreement. This option can be useful if you already have a SQL Server license.

If you’re looking to move an existing SQL Server database to AWS, you need to get the data to the cloud first. Following are some tools that can help:

• AWS Data Migration Service (DMS). Whether it’s a one-time migration or an ongoing synchronization process, DMS can replicate data from your data center to AWS.
• AWS Schema Conversion Tool (AWS SCT). This tool can help you migrate an existing SQL Server database to a different database technology, such as PostgreSQL or MySQL.
• AWS Snowball, Snowball Edge, and Snowmobile. These AWS services are designed to physically ship huge amounts of data in less time than it would typically take to migrate over a network.

Amazon RDS provides a simple way to deploy SQL Server (and other types of relational databases) in the AWS Cloud. Amazon RDS lets you choose among SQL Server versions, including 2012, 2014, 2016, 2017, and 2019, as well as the Express, Web, Standard, or Enterprise edition.

The edition that 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.

Amazon RDS for SQL Server normally operates only under the License Included service model. Therefore, when you set up an instance, the software license is included as part of the instance price, and you don’t have to bring your own license. However, if you want to bring your own license, the Amazon RDS Custom for SQL Server service gives you that option.

When it comes to storage, both service options are based solely on SSDs (either 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 they are created. You also get to choose which Amazon Virtual Private Cloud (VPC) your Amazon RDS instance runs in, and you can set up Windows authentication if you need to.

Let’s take a look at the major benefits that you get with the managed SQL Server deployment option:

• Elastic scalability

  You can effortlessly change the resources that are allocated to your SQL Server in AWS by tweaking a few settings, and the changes are rolled out within minutes. Amazon RDS also supports storage autoscaling, which means that it can detect when space is running out and allocate more storage without any intervention at all.

• Pay on demand

  Amazon RDS for SQL Server is priced by the hour, and there is no need to make any significant up-front investment—you pay for what you use. This approach, combined with the ability to increase and to decrease the allocated resources as needed, can help you optimize costs.

  You can even quickly spin up Amazon RDS instances for occasional jobs and destroy them immediately afterward. If your workloads are more predictable, you can take advantage of Reserved Instances, trading the aforementioned flexibility for significant discounts.

There are some considerations to keep in mind with the fully managed option for SQL Server:

• Instance limitations

  You should pay close attention to the limitations of SQL Server on Amazon RDS. For example, each Amazon RDS instance has a maximum number of SQL Server databases that it can support, ranging from 30 to 100, depending on the instance type that you use. Individual databases can also be no larger than 16TB.

• Less control

  When you use a managed service such as Amazon RDS, you don’t have control over how the database is configured or any access to the underlying instance and infrastructure. That lack of control also limits the locations where data resides and the options regarding data hybridity across environments.

  With the introduction of the newer Amazon RDS Custom for SQL Server service, those limitations were lifted to some degree. Amazon RDS Custom still has some inherent considerations, such as region availability and the type of instance classes that are available. But overall, it offers a similar level of performance, scalability, and security as traditional Amazon RDS provides. The ability to control the underlying operating system and database configuration makes it a good option if your organization needs more control but you don’t want to fully manage SQL Server by yourself.

A good alternative for some cases is to combine SQL Server with other services that AWS offers—Amazon EC2 and Amazon Elastic Block Store (Amazon EBS)—as 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 data center. However, you can use Amazon Machine Images (AMIs) to make things easier:

• You can create an AMI and use it as a template for new Amazon EC2 instances.
• AMIs are already available with SQL Server preinstalled and with a lot of configurations already in place (for example, the configuration that allows remote access).
• AWS Marketplace has several paid AMI options.

Now let’s look at the major benefits of the self-managed SQL Server deployment option:

• Cost-effectiveness

  The self-managed option is often less expensive than Amazon RDS is. Also, it gives you the flexibility of running SQL Server the way that you would in your own data center, without the data center costs.

• Flexibility

  Overall, databases that are built by using Amazon EC2 provide more flexibility. You have complete control over how you configure the database and where its data is located, and you gain access to features that make it easier to implement complex hybrid architecture deployments.

• Scalability

  With the ability to spin up new instances and volumes as you need them, the self-managed method doesn’t have the 16TB size limitation of Amazon RDS.

• Ability to configure your RPO and RTO

  You can configure data protection schedules to better match your specific recovery point objective (RPO) and recovery time objective (RTO) requirements.

• Access to the latest features

  You can directly use the latest capabilities of the database and have full control over its management and tuning.

There are some considerations to using the self-managed option for SQL Server model:

• Not fully managed

  Basically, the downside of self-managing SQL Server on AWS is that you aren’t getting a fully managed service. You have more control but that comes with management overhead.

  For example, it’s up to you to manage Amazon EC2 instances and storage and to scale them to meet your demands. It’s worth keeping in mind that without a managed service, all management and operational tasks—such as backups, disaster recovery, and snapshots—are things that you need to implement on your own. That’s not a factor for every user, but it may be for some.

• Licensing

  Another aspect to take into account is the software license. With the self-managed option, you must bring your own license instead of using one that is packaged as part of the managed service instance billing.

Each option has pros and cons, so which do you choose, managed or self-managed? With Amazon FSx for NetApp ONTAP, you can bridge the gaps between the managed and self-managed approaches to SQL Server deployment.

FSx for ONTAP is a fully managed, AWS-native storage service that’s based on NetApp® ONTAP® technology. It helps you get all the benefits of the self-managed deployment option and many of the advantages of a managed service that handles the complexity of key management and operational tasks.

FSx for ONTAP extends your control and provides more capabilities for your databases. The major benefits are:

• Up to 65% cost reduction for your database storage, without compromising on performance
• Zero-capacity, zero-cost, instant, and writable data clone copies that can speed up your database refresh, development and testing (dev/test), and continuous integration and continuous deployment (CI/CD) processes
• Cross-region, application-aware data protection that helps keep your database and data safe from regional disasters, outages, accidental deletion, and ransomware
• Built-in multi-AZ high availability that’s designed to preserve your storage layer uptime if an entire AWS Availability Zone fails, supporting RPO 0 and RTO < 60 seconds

The following FSx for ONTAP features further enhance self-managed SQL Server deployments:

• Consistent high performance and throughput with low latency and intelligent NVMe caching
• Flexible sizing so that capacity, throughput, and IOPS can be scaled up or down on the fly
• Multiprotocol accessibility, including iSCSI, as well as NFS and SMB protocols
• Reduced backup windows from hours to minutes for optimized RTO

With self-managed deployment in Amazon EC2, you can use FSx for ONTAP to simplify operational tasks and to unlock these new capabilities. Let’s take a look at an example of how it all works in practice.

S&P Global Market Intelligence provides customers around the world with business insights into the global financial industry and markets. Amazon FSx for NetApp ONTAP helps this organization make that happen by supporting their SQL Server deployments.

S&P Global Market Intelligence runs hundreds of SQL Server databases and needs them to be highly available across AWS Regions to support disaster recovery and to preserve uptime.

FSx for ONTAP helps the organization achieve those goals and much more:

• The multi-AWS Region architecture protects data if a disaster strikes, and it preserves business continuity.
• Failover Cluster Instances (FCI) integrate with the FSx for ONTAP storage.
• Multiprotocol support for block-based iSCSI and for NFS and SMB is provided.
• Cross-region replication for disaster recovery by way of SnapMirror is more efficient than database-level replication.
• Reduced costs, thanks to built-in deduplication and compression features, help S&P Global Market Intelligence do more with less.

Find out more in the S&P Global Market Intelligence case study.

From reducing costs to protecting data and providing better business continuity on AWS, SQL Server users who self-manage their databases have a better option for the storage layer. Amazon FSx for NetApp ONTAP adds enhanced data management that will make your self-managed SQL Server deployments easier, faster, and more cost-effective.