You want to move to the cloud. You have SQL Server, and now you need to choose a database platform in the cloud. Which do you choose? This is one of the first questions I hear when we recommend moving a client’s database environment to the cloud. Most of our clients are moving their SQL Server environments to the cloud or are seriously considering the move in the near future. Clients want to continuing leveraging their investment in the platform, and a good enterprise architecture will always have a tried-and-true Relational Database Management System (RDBMS) as a critical component. Granted, when you move to the cloud, you have additional options to consider with your data platform. A data lake, Document Databases, noSQL databases, and MPP databases such as Azure Synapse, Redshift, and Snowflake will not make the architecture choices easy. Quite literally you can have one of each of these types in a composite scenario. It will all depend on your requirements and budget.
Let’s take one at a time. We’ll begin by focusing on getting your SQL Server to the cloud. Whether Azure or AWS, the options for migrating your SQL Server database are similar, with Azure providing the Azure SQL Database as an additional option.
- Azure SQL Database
- Azure SQL Database in Elastic pool
- SQL Server instance on Azure VM
- SQL Server Managed Instance
- SQL Server on EC2 (VM)
- SQL Server on RDS (managed instance)
Evaluating which platform(s) you will need to implement is a balancing act between business needs, application needs, and support needs. An example scenario might be that the business requires 24×7 availability, the application requires SSRS, SSIS, and SQL Server established, and there is minimal staff for infrastructure support. How to best cover all needs? The goal is to upgrade your business to a modern cloud architecture, with all the capabilities that entails, but success often means leveraging a phased approach to have a minimal impact on business operations and on the resources available.
To begin weighing your available choices—and which cloud you’ll implement—start going down your checklist for selection. It’s a short list, but questions and choices often boil down to the following areas:
- IaaS or PaaS
- Platform Price
- High Availability
Uplift existing or building new
Building new allows you to be unbridled in your choices and lets you follow modern cloud architecture patterns from the beginning. This is great but requires you to uplift your existing data and applications in some form. You might be in a position to mirror the on-premises VM and uplift to an Azure VM or EC2 just to get it there. In other scenarios, you might have to modify an application to utilize the cloud resources provisioned or for the application to be modified to work in the cloud. The challenge is keeping the business operating, but also moving towards a more modern architecture and capabilities.
Enterprise scope or application-level scope/limited targeted functionality
Your application requirements will also indicate which platform choices are relevant. This can depend on the scope of the application: whether it’s at an enterprise level, or more targeted in use. If you have a data warehouse or mission-critical transactional database, you may be looking at Managed Instance (or RDS) to support them. For example, if you have a transactional application, or applications leveraging containers, you may look to use Azure SQL database to support or uplift an on-premises VM directed to Azure VM or EC2.
IaaS or PaaS
Infrastructure as a Service (IaaS): a cloud provider maintains the OS and you manage everything else.
Platform as a Service (PaaS): a cloud provider maintains the OS, and DB software.
In my career I’ve been a DBA and maintained my own servers, installed SQL Servers, performed patch management/version upgrades with the joy of having to schedule downtime, perform the upgrade, smoke-test the upgrade, rollback in some cases, and release the platform for access. If I have an option of PaaS (where someone else is doing the OS and DB platform maintenance) without losing downtime, this would be a most desired capability. One often overlooked item is the Total Cost of Ownership (TCO) where the support costs and potential business costs are required and significant, though not entirely visible. PaaS offers additional baked-in benefits provided by the cloud provider service (e.g., a stronger security framework, SLAs, HA, and other default aspects managed by the cloud provider).
|Azure SQL DB||✔︎|
|Azure SQL DB Elastic Pool||✔︎|
|Azure SQL VM||✔︎|
|SQL on EC2 (AWS)||✔︎|
Here are some options you must consider (these vary based on selection): OS, Service Tier, Backup Tier, Instance Type, Instance (Cores), Generation, Compute, SQL License, Storage, Backup storage, Support, and others. Many Cloud vendors provide online price calculators, simply plug in the numbers to get a clearer picture of the capabilities and costs associated with the relevant scenarios.
The SQL Server feature set will vary based on the choices available. SQL Server on a VM will support a complete feature set, where Managed Instance or RDS supports most features/capabilities at an instance level, but not at the OS level, and Azure SQL DB supports features at a database level, but not at the OS or instance level. Decide which functionality is absolutely required and match that to the choices available. Sometimes you have to split between multiple choices (e.g., migrating an on-premises server with DB, SSIS, and SSRS to Azure might be split between a managed instance for the database platform (with its HA capabilities) and SSIS and SSRS on a dedicated Azure SQL VM, or EC2).
If your applications require High Availability (HA), PaaS includes HA as part of the implementation. Managed Services and RDS includes HA as a core capability in the platform. IaaS implementations must have the HA functionality designed, added and maintained manually. The caveat to IaaS is that while it offers full control over the platform, you must manage it entirely on your end. Your Cloud provider will only manage the OS for IaaS. Costs may be higher for a Managed Instance, or RDS, but when you calculate the TCO for an IaaS HA scenario, PaaS may be more enticing.
Can you auto-scale? In most cases, auto-scaling is not provided. AWS RDS includes the ability for storage to auto scale, and Azure SQL DB with Elastic Pools will provide auto-scaling. Other options, scaling is a dial-up activity on the subscription. Scaling-up takes place in a matter of minutes after being requested (but must be requested by the cloud subscription owner).
A bit of advice. Any effort to adopt the cloud should be part of your overall corporate effort. While initially, implementation might be small in scope, it should be a subset of your overall strategy; that strategy must be well-defined before moving to the cloud. Most importantly, the cloud infrastructure/security must be defined and in place to support any effort. Avoid the stove-type implementations you’ve seen in on-premises data centers in the past and fill in your platforms as components in the overall framework. Cloud subscription owners should be the gatekeepers for creating and overseeing the resources in the cloud. They will be the coordinator for the largest ongoing event your company will engage in. Cloud costs will creep up on you very quickly if not managed correctly or implemented piecemeal.
For the resources to be implemented, you will most likely use a composite of SQL Server platforms in your cloud architecture. You may have Managed Instance or RDS supporting mission critical applications. You may use Azure VM or EC2 to support special-purpose platforms (e.g., SSRS) or you may need to uplift an on-premises VM because you don’t have the time or resources to re-engineer. You may need Azure SQL databases to support specific applications or processes leveraging containers. You may even need Azure SQL Edge (recent release) to support IOT devices. Adding other platforms and components can create quite a large environment very quickly.
The criteria listed above is the short list, but it does focus on key items to evaluate when selecting the platform you need spun-up to support your SQL Server in the cloud. Be diligent in your architecture and the cloud will be an extremely valuable asset for your business. How can we help you migrate your SQL Server to the cloud? Please feel free to reach out to us with any questions. We’d love to help you get started.
Business Intelligence Architect