BlueXP Blog

Azure SQL Server: Managed Service vs Self Managed

Written by Yifat Perry, Technical Content Manager | Jul 19, 2020 12:24:14 PM

How Can You Deploy SQL Server on Azure?

There are two primary ways to deploy SQL server on Azure: 

  1. Fully managed - a database as a service (DBaaS) model. There are two DBaaS offerings on Azure for SQL Server: Azure SQL Database and Azure SQL Database Managed Instances. 
  2. Self managed - running an Azure virtual machine and deploying a SQL Server image from the Azure Marketplace, or your own custom image.

The fully managed option is aligned with the PaaS cloud service model and is mostly applicable for cloud-native applications or re-architected applications.

The self-managed IaaS model on the other hand gives full control to the customer, as they have access to the SQL Server on Azure and can fine tune server and DB configurations, just like they could do in an on-premises SQL deployment.

In this article, we’ll cover each of these Azure database options in more depth and help you decide which is right for you.

In this article, you will learn:

Licensing and Migration Considerations

Depending on the deployment model you choose - fully managed or self managed, you will have different licensing options, but the migration path will probably be the same.

Licensing
Licensing will differ depending on your deployment model:

  1. Fully managed with Azure SQL Database - license cost is included in the cost of the deployed instance and is charged on an hourly basis. A second license model is Azure Hybrid Benefit, which lets you bring your own license (BYOL). Hybrid Benefit is available for v-core based deployment models of Azure SQL database and Azure SQL managed instances. 
  2. Self managed with Azure VM - license can be “pay as you go”, where the hourly charges for compute includes the SQL Server license plus the OS license charges. Azure Hybrid benefit is available for customers deploying SQL Server Enterprise and standard core VMs.

Data migration
Azure Database Migration Service (DMS) supports migrations to the Azure SQL Database, Azure SQL Database managed instances, or Azure SQL VMs. So it is suitable for both deployment options outlined in this article.

DMS helps you migrate on-premises databases to Azure SQL services through an online or offline migration process. The database migration assessment tool, offered as part of DMS, helps to assess the source databases and highlights any compatibility issues so that you can address them prior to the Azure migration. 

Option #1: Fully Managed with Azure SQL Database

Azure SQL Database is delivered as a fully managed service that provides the latest SQL Server capabilities of the box. The backend SQL engine uses the latest stable SQL Server enterprise edition. 

The database engine, operating system, and underlying hardware are all managed by Azure. Note that customers are still responsible for managing the database properties, logins, security, auditing, query tuning, etc.

Pros of Azure SQL Database
Azure provides an SLA of 99.99%. The databases are protected through automated Azure database backups and can be configured for long term retention of up to 10 years. 

Other administrative tasks like patching and upgrades are also managed without user intervention. Scalability needs of the database can be handled automatically through elastic pools, which are very useful in multi-tenant usage scenarios with unpredictable usage patterns.

Cons of Azure SQL Database
Though many DB administration activities are offloaded in Azure SQL Server, customers could find them to be restrictive in certain circumstances. For example:

  • Azure SQL Database only supports the latest version of SQL Server, so you will need to migrate databases using an older version
  • There is also no guarantee of the exact DB maintenance time. 
  • The DBaaS services are deployed in Azure and can be connected to local data centers, however, this requires specialized configuration, which requires specialized technical expertise.
  • Some SQL features such as CLR, extended stored procedures, and file streams, are not supported by Azure SQL Database managed instances. 
  • Azure SQL Database backup is automated, but the schedule and frequency may not always align with your organization’s DR requirements. This means additional efforts will be needed to perform backups manually or using custom scripts, and copying data to additional Azure regions for redundancy.
  • Managed services use built-in Azure storage, which offers limited opportunity for customization outside the fixed SKUs. In enterprise deployments, the storage layer needs additional flexibility and data management features compared to what is supported by native cloud storage. 
  • For SQL Server administrators, there will be a learning curve before they can take up the configuration and management of Azure SQL Database.

Option #2: Self Managed - Deploying SQL Server in an Azure VM with Managed Storage

The alternative option to the managed service model is to build your database in an Azure VM. Azure offers marketplace images which help to deploy VMs with SQL Server in a matter of a few clicks. Unlike Azure SQL Database, there is no restriction of the SQL version that can be deployed in the VMs, which helps avoid compatibility issues during migration. 

Most Marketplace images use Azure VMs fine-tuned for databases. In order to manage storage for the SQL Server database, you will need to use a managed storage service provided by Azure or a third party.

You can choose Azure managed disks, with a Premium or Ultra disk option for superior performance in production deployments. 

Compute performance can be adjusted by scaling up VMs to high performance SKUs if required. 

Pros of self-managed SQL Server in an Azure VM

  • Provides the same end-to-end control as you would have in an on-premises deployment, while providing the scalability and flexibility of the cloud.
  • Using Azure Hybrid reduces the cost of running SQL VMs in Azure by around 55%. 
  • Running SQL Server on an Azure VM lets you use a free passive secondary replica for high availability through the Fail-Over Servers Software Assurance benefit. If you need a highly available SQL deployment, this reduces licensing cost by half.

Cons of self-managed SQL Server in an Azure VM

  • When running in an Azure VM, you own the configuration of high availability, backup management, patching, etc. This requires additional effort.
  • It is up to you to devise a scaling strategy and configure it using autoscaling features available to Azure VMs. This can be challenging, while in Azure SQL Database scalability is built in. 
  • More difficult to manage storage, which relies on Azure managed disks.

These cons could also be advantages, when you need to customize your deployment, or when the backup schedule needs to be aligned with organizational DR strategy.

What this deployment option boils down to is more control over the database, its optimization, how you can use that data, and how much you can save by avoiding the costs of premium services.

SQL Server on Azure with Cloud Volumes ONTAP: The Best of Both Worlds

For organizations that need to customize their SQL Server deployments, and need to overcome the limitations of the Azure SQL Database managed service, the self-managed Azure VM option is the most appropriate. However, this option comes with greater complexity and difficulty of managing storage.

NetApp Cloud Volumes ONTAP can help customers meet the performance, availability, and agility requirements of SQL Server in a self-managed environment. It augments the Azure cloud storage layer with a set of storage management features and capabilities, uniquely suited to enterprise deployments, including:

  • Hybrid deployments, supporting consistent deployment of SQL Server workloads irrespective of whether they are deployed on-premises or in any cloud.
  • Storage efficiency through deduplication, compression, and thin provisioning, which reduce storage footprint and monthly Azure storage costs.
  • NetApp Snapshot™ copies for point-in-time, application-consistent database snapshots to recover the database in case of disaster or data corruption.
  • Easy migration across hybrid environments through NetApp Cloud Sync service or SnapMirror® data replication. 
  • The Azure high availability architecture configuration assures zero data loss (RPO=0) and minimal recovery times (RTO< less than 60 seconds).
  • Full support for other database engines, including Oracle, MySQL, MariaDB, MongoDB, and more.
  • High performance through intelligent caching technology that reduces data access latencies.
  • FlexClone® data cloning technology creates storage-efficient, writable clones of database volumes that can be used in testing and development scenarios.

Case study: how NetApp helped a self-managed SQL Server deployment in Azure
The court system of a major US state government, which has jurisdiction over civil cases statewide and over criminal cases within the state’s major metropolis, chose to deploy SQL Server databases for development and testing environments on Azure VMs in a self-managed model. The organization uses Cloud Volumes ONTAP to manage storage for these self-managed SQL Server instances.

The court system had been running applications based on Oracle and SQL Server databases on-prem, but they wanted a more flexible and simple solution for their move to Azure. Getting to the cloud was easy via SnapMirror, and the data management functionality made the migration project a success. The next steps include expanding the use of Cloud Volumes ONTAP for disaster recovery and data archiving.