I get asked about Azure SQL very often. This is a topic that I’ve written about and taught many times. To be honest, this is a rather large topic that can go in many different directions. For this post, I want to cover what I consider the basics of Azure SQL. For me, that is three primary areas. Azure SQL encompasses Azure SQL Database, Azure SQL Managed Instance, and Azure SQL Virtual Machines.
When someone comes to me and says, “We want to move our on-premises SQL to Azure SQL”, that always gets the response of, “Where are you wanting to migrate them to in Azure”. I respond with that because Azure SQL could be one of three destinations and each one of those destinations requires more questions and ultimately understanding the workload they are wanting to migrate.
Lets break this down into what I consider the main points to understand about each of the areas of Azure SQL.
Azure SQL Database
Azure SQL Database is an excellent choice for when you need a single database for an application and you want it in a fully managed environment such as Platform as a Service (PaaS). I’ve seen a lot of new development with Azure SQL Database as the intended back end. Azure SQL Database was designed with a database scoped programming model, meaning it is designed as ‘database as a service’. It works really great for those use cases. Many projects that I’ve worked on over the past 10 years, companies have thought they can move entire instances of SQL Server to Azure SQL Database. If those databases need to ‘talk’ to each other, it is not the best solution.
Azure SQL Database offers a variety of service tiers. There is the DTU model where you have a blended measure of compute consisting of memory, storage IO, and CPU. Then there is the vCore model with General Purpose and Business Critical tiers. There is also options such as Serverless where you can auto scale and Hyperscale which allows you to scale up to 100TB and have numerous read nodes. A callout here is that Serverless is available in the General Purpose tier whereas Hyperscale has the capability of exceeding the IO limits of General Purpose and Business Critical.
My experience with Azure SQL Database has been mostly positive. As mentioned above, when you have a single database need for an application, it is a great option. The challenge I’ve run into is with IO workloads. For any database that has a moderate to heavy IO need, I’ve found that the Business Critical tier is needed. I always recommend before determining your Azure destination, that an adequate baseline is needed of the existing workload.
Azure SQL Managed Instance
Where Azure SQL Database is database scoped, Azure SQL Managed Instance is Instance Scoped. The goal of Azure SQL Managed instance was 100% surface area compatibility with SQL Server. With Managed Instance you get the look and feel of a full instance of SQL Server in Azure PaaS. There are some clear differences with Managed Instance not offering OS level access. You can’t access local drives, the registry, etc, but from the instance you have SQL Server Agent, Database Mail, cross database query support, and so much more.
For organizations that wanted to move to Azure SQL Database for all the benefits of PaaS but were limited because of the database scoped configuration model, Azure SQL Managed Instance bridged that gap.
With Managed Instance, there are two performance tiers with General Purpose and Business Critical. I won’t go deep in this blog post, however similar to Azure SQL Database, there are clear performance differences between General Purpose and Business Critical, so if your workload is IO intensive, the Business Critical tier will be required. There are then limits on storage size based on vCore count.
While Azure SQL Managed Instance is a great choice for SQL Server workloads, evaluation of workloads must be done in order to determine what tier and size you’ll need before making a decision to move to it. I’ve been part of numerous migrations to both Azure SQL Database and Managed Instance as well as migrations out after a botched migration before proper due diligence was completed.
Azure SQL Virtual Machines
Running SQL Server on Azure virtual machines is one of the most straightforward paths to migrate from on-premises to Azure. Microsoft offers a wide array of Azure VM sizes and has categorized them into different families. There are templates available that already have various SQL Server components installed and configured and these images have gotten better with installation best practices as well as storage best practices.
When it comes to selecting the correct size Azure VM, it all comes back to having a baseline for your existing workload. Its much more than picking based on cpu, memory, and storage size. Azure VMs have limits on throughput that corresponds to the size of the VMs.
Microsoft has recognized that SQL Server workloads may require more throughput than the Azure VM can provide even though the vCore and memory configuration are adequate. The solution for this issue was to scale the VM to a larger size, however that meant more vCores which equaled more SQL Server license. To combat that issue, Microsoft introduced constrained cores which means you would get all the benefits of the larger size, minus the vCores. Most recently, Microsoft has released the “b” option for bandwidth which indicates the highest I/O throughput available for the VM series.
I recorded a YouTube session with Anna Hoffman for the “Data Exposed: MVP Edition” regarding Azure VM Sizing going over the importance of baselining your workload to be able to select the proper size Azure VM.
Conclusion
Azure is a great place to migrate your SQL Server workloads if you are looking to migrate to cloud. There are numerous options from Azure VMs to Platform as a Service. Regardless of where you’d like to move, you must know your current baseline, especially IO throughput as each option has limits. Once you have a proper baseline, the Azure solutions becomes much more clear.
The post What is Azure SQL appeared first on Tim Radney.