A database is the backbone of an application, storing, organising, and managing the data your application needs. There comes a point in the development of your application where the critical nature necessitates that your SQL databases remain available at all times. When it comes to availability, most fingers point to the cloud. After all, they can quote impressive availability figures and have the capacity to build your infrastructure almost anywhere in the world. So, what should you look for when selecting a high-availability database solution?
In this insight, we will examine the pros and cons of running SQL Server in cloud-specific services and why you might consider a self-managed SQL Server Always On Availability Groups (AGs) solution. 
Also, see our previous post SQL Server: Cloud Options? where we break down what is available in the Cloud.
Managed Cloud Database Services
The general idea and advantage of Managed Cloud Databases is that less management is required on a system level. This is because these services are essentially ‘serverless’. The database/system admin doesn’t have to care for the SQL Server host because its management has been automated as part of the service, leaving them to focus on SQL itself. High-availability is often built in, and scaling is simple, giving you fewer things to worry about overall.
Definition: A serverless SQL database lets you process distributed data using SQL language without operating the servers, managing maintenance, and capacity planning, etc.
NOTE: You may see alternate descriptions/interpretations of the term ‘serverless’ from Cloud providers or others, meaning a compute tier that doesn’t include provisioned resources but instead scales based on workload.
The Good Stuff
Automation: As a whole, serverless solutions integrate well with automation and IaC tools, such as Terraform, as all the service configurations can be easily defined in code. This leaves the databases themselves as the only thing needed to maintain continuity. You can redeploy your serverless solution halfway across the world, and as long as you restore up-to-date copies of your databases, things will continue to run exactly as they did previously. No messing around with SQL installations and reconfiguring user permissions. It’s hard to beat the speed and consistency of deployments with Managed Cloud Database Services.
Availability / Redundancy: Managed Cloud Databases are designed with availability and redundancy in mind out of the box. Options are often HA by default, and those that aren’t can be configured as HA with as little as ticking a simple checkbox. Redundancy doesn’t stop at the hardware level but can also span multiple data centres within a single region to multiple regions themselves. All without the effort of setting up expensive site-to-site interconnects, which can be required for on-premise multi-region solutions.
Scalability: If you find yourself short on resources and lacking in performance, you can quickly scale up your Managed Cloud Databases with little to no downtime. You can either scale your solution to a set threshold or make use of auto-scaling capabilities to scale your solution depending on its usage.
Keep updated with the latest from Pipe Ten by subscribing below.
The Not So Good Stuff
Variable Costs: While cloud options can reduce costs by scaling with demand, unpredictable usage patterns can lead to higher-than-expected costs. In some cases, these higher costs can be massively more than budgeted, so setting up cost and usage alerts to minimise these risks is essential.
Inconsistent Performance: Serverless models can exhibit variability in response times due to shared infrastructure and on-demand resource allocation. It’s a subtle but critical downside that is often overlooked. While you can select your cloud computing tier, you don’t (without paying excessively) get a say in what hardware is hosting that compute and what neighbours you may be sharing it with. If you draw the short straw and your resources are allocated to a particularly high-load host, this can lead to poor performance.
Troubleshooting: Unfortunately, monitoring performance and debugging issues can be more challenging in a serverless environment due to its dynamic nature. There are unique variables that don’t usually present themselves in traditional environments, some of which are outside the user’s control.
The Abstract
Digging into the features and capabilities of Managed Cloud Database Services highlights some of the concerns associated with serverless SQL products. Taking a closer look at Azure’s offering, note that Azure SQL Database drops the SQL Agent service and SSRS (SQL Server Reporting Services) and does not allow cross-database transactions. SQL Managed Instance (SQL MI) fairs a little better but still needs consideration in these areas.
While this is a direction that the technology is taking and is a sign of things to come to standard SQL Server installations, it does mean that you need to adapt your applications to suit the serverless offerings. For companies running legacy applications, this can mean massive redevelopment from both an application and internal team perspective to adopt the new required methods, not to mention potential periods of disruption that any issues may cause following the migration to the new solution.
For new application development, things start easier; however, you run into a different problem. While you may be able to develop your application specifically for a Cloud serverless solution, you have no control over the version of SQL the solution uses or the features it introduces or removes. This could leave you in a position where you have developed part of your solution around a specific feature that Microsoft chooses to remove. Traditionally, you had the option of simply not upgrading your SQL Version until you have a workaround for the new version, but this is not an option in managed Cloud solutions.
What happens if you discover the managed Cloud solution isn’t for you? While data can be exported from these solutions, they can only be exported to a Bacpac file rather than the standard .Bak. While Bacpac backups certainly have their place, they are not suited for more sizable databases (greater than 200GB) as they can take a long time to build. They also are not transactionally consistent, so they cannot be taken on live data without potentially missing some data changes. Before making any commitments, it is worth considering how well your databases are suited to the Bacpac format.
The final consideration with managed cloud services, in general, is that price changes are non-negotiable. If Azure or AWS wants to increase their prices, that’s what will happen. You aren’t going to get a customised contract open to discussion and negotiation like you are with smaller On-Premise providers. With any Large Cloud platform, you are simply a number on a page and not a unique customer with unique requirements to form a relationship.
All things considered, this often leads us to conclude that for most (not all) businesses requiring a highly available database solution, SQL Server Always on Availability Groups (SQL AGs) is the way to go.
Why Availability Groups?
SQL Server Always on Availability Groups (SQL AGs) is a high-availability solution that provides a modernised approach to database mirroring. AGs maximise the availability and redundancy of a set of user databases and, interestingly, form the framework of most serverless solutions, including those in Azure.
The advantage of self-managed AGs over Managed Cloud SQL is that it can be hosted as a Cloud, Hybrid Cloud or On-Premise solution. This flexibility makes moving between providers and locations easy and, importantly, (if done correctly) without downtime.
While Azure explicitly offers database migration tools for moving to their serverless cloud platforms, depending on what version of SQL you are moving from and what features you require from your solution, it can be anything but a smooth process.
SQL AGs allow for SQL replicas (SQL server nodes) to be seamlessly added and removed, so scaling your redundancy and availability is never a problem. Replicas can be run in synchronous or asynchronous replication modes, which can be helpful for slower connections between sites or if you have many SQL replicas across many regions.
Definition: Asynchronous is a non-blocking architecture, so the execution of one task isn’t dependent on another, meaning that functions can run simultaneously. Synchronous, on the other hand, is a blocking architecture, so the execution of each operation depends on completing the one before it.
Conclusion
Despite requiring more manual work and maintenance, SQL AGs give you a greater balance of availability and business continuity than cloud-based, managed SQL server solutions. You can move your data to wherever is most convenient and work around version and feature changes on your timetable.
Running with AGs in a hybrid or Cloud environment also leaves the option of moving to serverless SQL-managed services on the table with fewer concerns than going straight from On-Premise, so you can keep your options open.
Pipe Ten has experience hosting SQL AGs in a variety of environments and has designed bespoke solutions for a diverse selection of customers with precise requirements. We pride ourselves on working with our customers to ensure a smooth migration and a stable platform into the future. We are platform agnostic and won’t force you down any particular direction; we simply offer our best advice.
For more information or to start your journey with high-availability databases, please contact us!
Author: Jack Jones Jack has been an integral part of Pipe Ten’s engineering team in Sheffield, Yorkshire for over 5 years. With a long history of being immersed in Microsoft’s ecosystem, Jack embodies Pipe Ten’s provider agnostic approach and has lead the evolution of many customer solutions to integrate the benefits of public cloud, specialising in Azure and AWS. The wealth and sheer depth of Jack’s cutting edge technical knowledge and skillset has been crucial to the success and growth of many customers’ businesses.