Its Day 63 of my 100 Days of Cloud journey, and today I’m looking at SQL services in Azure and the different options we have for hosting SQL in Azure.
As we discussed in the previous post, SQL is an example of a Relational Database Management System (RDBMS), which follows a traditional model of storing data using 2-dimensional tables where data is stored in columns and rows in a pre-defined schema.
On-premise installations of Microsoft SQL Server would follow the traditional IAAS model, where we would install a Windows Server operating which provides the platform for the SQL Server Database to run on.
In Azure, we have 3 options for migrating and hosting our SQL Databases.
SQL Server on Azure VM
SQL Server on Azure VM is an IaaS offering and allows you to run SQL Server inside a fully managed virtual machine (VM) in Azure.
SQL virtual machines are a good option for migrating on-premises SQL Server databases and applications without any database change.
This option is best suited where OS-level access is required. SQL virtual machines in Azure are lift-and-shift ready for existing applications that require fast migration to the cloud with minimal changes or no changes. SQL virtual machines offer full administrative control over the SQL Server instance and underlying OS for migration to Azure.
SQL Server on Azure Virtual Machines allows full control over the database engine. You can choose when to start maintenance/patching, change the recovery model to simple or bulk-logged, pause or start the service when needed, and you can fully customize the SQL Server database engine. With this additional control comes the added responsibility to manage the virtual machine.
Azure SQL Managed Instance
Azure SQL Managed Instance is a Platform-as-a-Service (PaaS) offering, and is best for most migrations to the cloud. SQL Managed Instance is a collection of system and user databases with a shared set of resources that is lift-and-shift ready.
This option is best suited to new applications or existing on-premises applications that want to use the latest stable SQL Server features and that are migrated to the cloud with minimal changes. An instance of SQL Managed Instance is similar to an instance of the Microsoft SQL Server database engine offering shared resources for databases and additional instance-scoped features.
SQL Managed Instance supports database migration from on-premises with minimal to no database change. This option provides all of the PaaS benefits of Azure SQL Database but adds capabilities that were previously only available in SQL Server VMs. This includes a native virtual network and near 100% compatibility with on-premises SQL Server. Instances of SQL Managed Instance provide full SQL Server access and feature compatibility for migrating SQL Servers to Azure.
Azure SQL Database
Azure SQL Database is a relational database-as-a-service (DBaaS) hosted in Azure that falls into the category of a PaaS offering.
This is best for modern cloud applications that want to use the latest stable SQL Server features and have time constraints in development and marketing.
A fully managed SQL Server database engine, based on the latest stable Enterprise Edition of SQL Server. SQL Database has two deployment options:
- As a single database with its own set of resources managed via a logical SQL server. A single database is similar to a contained database in SQL Server. This option is optimized for modern application development of new cloud-born applications. Hyperscale and serverless options are available.
- An elastic pool, which is a collection of databases with a shared set of resources managed via a logical SQL server. Single databases can be moved into and out of an elastic pool. This option is optimized for modern application development of new cloud-born applications using the multi-tenant SaaS application pattern. Elastic pools provide a cost-effective solution for managing the performance of multiple databases that have variable usage patterns.
Overall Comparisons
Both Azure SQL Database and Azure SQL Managed Instance are optimized to reduce overall management costs since you do not have to manage any virtual machines, operating system, or database software. You do not have to manage upgrades, high availability, or backups.
Both options can dramatically increase the number of databases managed by a single IT or development resource. Elastic pools also support SaaS multi-tenant application architectures with features including tenant isolation and the ability to scale to reduce costs by sharing resources across databases. SQL Managed Instance provides support for instance-scoped features enabling easy migration of existing applications, as well as sharing resources among databases.
Finally, the database software is automatically configured, patched, and upgraded by Azure, which reduces your administration
The alternative is SQL Server on Azure VMs which provides DBAs with an experience most similar to the on-premises environment they’re familiar with. You can use any of the platform-provided SQL Server images (which includes a license) or bring your SQL Server license. All the supported SQL Server versions (2008R2, 2012, 2014, 2016, 2017, 2019) and editions (Developer, Express, Web, Standard, Enterprise) are available. However, as this is a VM, it’s up to you to update/upgrade the operating system and database software and when to install any additional software such as anti-virus.
Management
All of the above options can be managed from the Azure SQL page in the Azure Portal.
Migration
In order to migrate from existing SQL Workloads, in all cases you would use an Azure Migrate Project with the Data Migration Assistant. You can find all of the scenarios relating to migrations options here.
Conclusion
And thats a look at the different options for hosting SQL on Azure. Hope you enjoyed this post, until next time – I feel like going bowling now!
Michael Durkan 