Microsoft SQL Server on AWS: Managed service, self-managed, or managed storage?

Microsoft SQL Server on AWS: Managed service, self-managed, or managed storage?

Microsoft SQL Server is a foundational part of enterprise IT, providing a reliable database that can scale to meet your most demanding mission-critical requirements. This post examines the two database deployment models—managed and self-managed—and explains the pros and cons of each option. Plus, it shows how AWS and NetApp have partnered to create a third, 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

Published Jun 25, 2024
Last Modified Jul 11, 2024
Microsoft SQL Server is a foundational part of enterprise IT, providing a reliable database that can scale to meet your most demanding mission-critical requirements. That criticality extends to SQL Server on AWS deployments. But if you’re running SQL Server on AWS there is one major difference: You have two unique database deployment models to choose from.
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.
It’s a big choice to make. Let’s take a look at how these two models differ and one way you might be able to bridge the gap.

Managed or self-managed SQL Server? What’s the difference?

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 is a fully managed database service that works with several different SQL Server editions, including Express, Web, Standard, and Enterprise. The maximum storage size 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 create your own infrastructure on AWS. It’s on you to launch Amazon EC2 instances, choose the storage solution or service, and configure the networking and other elements so that it all works together. This level of control provides a lot more flexibility.

Certain use cases prefer one option over the other.

How can you know which is the right model for you to choose? Mainly, it comes down to size and complexity:
  • In general, smaller, streamlined applications can rely on the fully managed Amazon RDS to meet all their needs. On the other hand, enterprise-level applications are generally more complex and need the flexibility, scalability, and configurability that comes with a self-managed database.
  • Applications that migrate from on-premises systems and/or those that are part of a hybrid environment need the flexibility and data mobility of a self-managed database.
  • High-performance and storage-demanding workloads (e.g., artificial intelligence, machine learning, analytics, electronic design automation) need a self-managed database’s cost effectiveness and higher levels of elasticity and agility.
  • Business-critical workloads such as Software-as-a-Service applications, media and entertainment services, and customer-facing apps need the strict controls that self-managed databases provide for security settings, data protection, and meeting recovery point objectives (RPOs) and recovery time objectives (RTOs) targets.
  • Your IT team’s skillset will also be a factor. If you have a team that knows what they’re doing, the self-managed route is probably best. Less experienced teams can lean into the managed service. 
  • Legacy databases migrating to AWS will likely have an easier time fitting into a flexible self-managed database than the one-size managed database option.

Managed deployment with SQL Server on Amazon RDS

The managed option has its benefits and certain considerations to keep in mind.

Pros of the Managed SQL Server Deployment Option

Let’s take a look at the highlights of 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 flexibility for significant discounts.

Cons of the Managed SQL Server Deployment Option

There are some considerations to keep in mind with the fully managed option for SQL Server:
  • Instance size
    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 max out to 16TB of storage capacity.
  • Handing over the reigns
    For the most part, when you use a managed service, 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.
    AWS has done some work to address that with the Amazon RDS Custom for SQL Server service. 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.

Self-managed deployment of SQL Server with Amazon EC2

Self-managed databases come with their own benefits and considerations to keep in mind.

Pros of the Self-Managed SQL Server Deployment Option

Now let’s look at what the self-managed SQL Server deployment option entails:
  • Cost-effectiveness
    The self-managed option offers a number of ways to reduce the total cost of running your database in comparison to using a managed service. This varies between the storage type used, but can include the ability to use less expensive licenses, highly efficient snapshot and replication techniques, and storage efficiencies.
  • 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 RPO and 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.

Cons of the Self-Managed SQL Server Deployment Option

There are some considerations to using the self-managed option for SQL Server model:
  • It’s up to you
    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.

Here’s the solution: Using fully managed storage for self-managed SQL Server

Each option has pros and cons, but there is a way you can bridge the gaps between the two approaches: fully managed storage to support self-managed databases.
It’s a combined approach that gives enterprise-scale deployments the benefits of the self-managed option with some of the added values that come with a managed service.
A fully managed storage service should offer benefits that can make a big difference for a database:
  • Flexible and consistent high performance with high throughput, low latency, and intelligent NVMe caching
  • Efficient dataset clone copies that can be used for database refreshes and to spin up new environments quickly, speeding up database refreshes, development testing (DevTest), and continuous integration and continuous deployment (CI/CD) pipelines  
  • Cross-Region and application-aware data protection that helps keep your database safe from local failures, regional disasters, outages, accidental deletion, and ransomware
  • Cross-zone high availability that’s designed to preserve your storage layer uptime if an entire AWS Availability Zone (AZ) fails, supporting minimal RPO and RTO
  • Cost-reduction capabilities that won’t compromise performance
  • Scalability that goes well beyond 16TB to accommodate even the largest databases 
Let’s take a look at an example of how it all works in practice.

How S&P Global Market Intelligence enhanced data resilience and reduced costs with Amazon FSx for NetApp ONTAP

S&P Global Market Intelligence provides customers around the world with business insights into the global financial industry and markets. They’re running self-managed SQL Server databases on AWS using a fully managed storage service, Amazon FSx for NetApp ONTAP. FSx for ONTAP is a fully managed AWS storage service based on NetApp® ONTAP® technology.
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 in a few ways:
  • Data protection across regions. The multi-region architecture protects the database if a disaster strikes, and increases availability to preserve business continuity.
  • Optimized sizing and lower costs with FCI. FSx for ONTAP has the ability to share LUNs between instances. That enables the use of the SQL Server failover cluster instance (FCI) capability which allows:
    • High availability across AZs, using various performance tiers.
    • Offloading the heavy lifting from the database service to the storage service, allowing more efficient server usage and better cost-performance.
  • Instant, optimized snapshot copies. Highly efficient, instantly created snapshots facilitate backups and prevent data loss.
  • Thin clone copies. Writable, thin clone copies are instantly created and consume near-zero capacity, so database refreshes and spinning up new environments takes seconds instead of hours, speeding up DevTest and CI/CD processes for a reduced time to market.
  • Cost-efficient DR environment. NetApp SnapMirror® replication technology replicates data at the storage level instead of using SQL’s built-in replication feature. This allows users to use FCI with smaller instance types and the less-expensive Standard SQL Server license instead of the Enterprise license.
  • Additional reduced costs. NetApp built-in storage-efficiency features, including data deduplication, compression, and data tiering, reduce the data footprint as well as the storage and data transfer costs.
For more information about FSx for ONTAP and the S&P Global Market Intelligence story, visit the Amazon FSx for NetApp ONTAP customers’ page.
Are you running an SQL Server database on AWS? Which of the two options are you using and how is that working out for you?
If you’re looking for a way to bridge the two options, consider the fully managed storage option with a service like FSx for ONTAP.
To learn more about how to deploy your MS SQL Server on AWS with FSx for ONTAP, visit our GitHub repository.