16th September 2019
Let’s assume your organisation has a “cloud-first strategy” and let’s assume that you are using Microsoft Azure as your cloud provider. You have SQL Server on-premises and your next major project is moving some of those databases and servers to the cloud and Azure in particular. If this is the situation you find yourself in there will still be some key decisions to make. Are you going to go IaaS, Managed Instance, PaaS or select a combination to use? In this post, we will provide an overview of the services available to you for running SQL Server and SQL Server databases in Azure.
Infrastructure as a Service (IaaS) – SQL Server Virtual Machines
When we talk about SQL Server and Infrastructure as a Service (IaaS) what we are really talking about is running SQL Server on a Virtual Machine in Microsoft data centre. You will maintain control of the operating system and full control of the SQL Server instance. A big advantage of using this approach is you can “lift and shift” existing servers that meet certain criteria to Azure with minimum fuss. There may only need to be minimal changes to the application, maybe even no changes at all to your business-critical applications when moving the database to Azure.
You will maintain full administrative control over the SQL Server instance and the underlying operating system. From your DBA or system administrator perspective, this option will offer the path of least resistance… there is less change involved, you are not losing control. Ensure your SQL Server is backed up, index maintenance runs, consistency checks run, security is set up, and check you have appropriate indexes to satisfy your most import queries. You maintain responsibility for managing the VM and everything that runs on it, this can be an easy route into the cloud without necessarily requiring a complete re-architecting of your application.
Platform as a Service (PaaS) Azure SQL Database
Azure SQL Database is Microsoft Platform as a Service (PaaS) offering. For me moving to this service option if you have a traditional N-Tier on-premises architecture will involve the most change as you will likely need to re-architect your application. The service offers you a relational database platform built on SQL Server technology that should allow you to easily set it up and configure it, it is easy to provide a highly available database or databases. You can do all this without the need to manage and administer a virtual machine and certain aspects of your database itself, such as backups. That aspect is done for you by Microsoft as your cloud service provider.
Is this service for everybody? Well it depends on you and your application, in their official documentation around Azure SQL Database, Microsoft suggest:
“SQL Database can be the right choice for a variety of modern cloud applications because it enables you to use powerful functionalities for processing both relational data and non-relational structures”
The debate about whether this is the right feature for your environment I will leave for another post, however if you do make this choice you have some additional choices to make
What purchasing model to use? What metric will you use to measure your Compute usage? And, therefore, how much will you pay? There are two purchase models on offer:
- vCore Purchasing Model
This is a relatively new purchasing model which allows you to choose the exact amount of compute resources that are always provisioned for your workload. There are three tiers in the vCore purchasing model:
- General purpose
- Hyperscale
- Business-critical
- DTU Purchasing Model
This is a bundled as a set of compute and storage services that can be used to match to different workloads. Again, you will get a choice around service tier, your options are:
- Basic
- Standard
- Premium
I’ll save a detailed look and discussion around the pricing model for a future blog, but what you chose here will affect the features that are available to you and how much the solution will cost. So, the decision you make here is an important one. It is the cloud and it is meant to be flexible so you can switch between tiers too, but we want to get the tier right first time to avoid any unnecessary costs.
Then you need to decide how are you going to deploy your Azure SQL Database(s). The choices you have include:
Single database
A Single Azure SQL database will have its own set of resources. You can think of a single database to be like a contained database in SQL Server. Contained database were introduced to SQL Server in 2012 as a way for you to try and replicate an Azure SQL Database environment in your on-premises servers. I’m uncertain if it has ever been a like for comparison, but an Azure SQL database will have no dependency on the SQL Server instance it runs on and elements such as SQL Agent jobs and server logins are not available. You can login direct to the Azure SQL database.
Elastic Pool database
This is a reasonably new feature that has been created to allow multiple Azure SQL Databases to share resources. If you have multiple databases that vary in terms of workload and utilisation. For example, if one database is very busy during the day processing transactions whilst another is very busy overnight populating a report, then you can pool them together so each database receives resources at the time they are busiest. This way you don’t waste money and resources that are unused.
SQL Managed Instances
This is the newest and perhaps most exciting feature in running SQL Server in Azure. It tries to provide you with all the benefits of PaaS with some of the control and flexibility offered by running and managing the virtual machine yourself. Microsoft suggests this is the best of cloud migrations for you get the flexibility of the IaaS offering with the services of an Azure SQL Database. In this way, you can migrate your traditional applications to Azure with minimal changes but still get a lot of the PaaS benefits. They have close to 100% compatibility compared to on-premises SQL Server instances, however, these can be quite costly so be sure to factor this into decision making.
Conclusion
There is a number of benefits in businesses looking to utilise and deploy cloud technology. Hopefully, if you’re looking after a SQL environment and you find yourself in this position then take the time to evaluate the options you have available. Use tools such as the data migration assistant to help you understand what features you can make use of in each service offering and conclude what compatibility issues you may encounter. Keep in mind you can always ask Northdoor for help too, we have an experienced team of SQL Server consultants that have undertaken many Azure-based SQL migrations who can help you on your path to the cloud.
