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

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

• 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.

• 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

Pros 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

• 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

Pros of the Self-Managed SQL Server Deployment Option

• 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

• 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

• 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 

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

• 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.