More about Azure database
- Oracle on Azure: Managed Service vs Managed Storage
- Azure SQL Database Backup & Recovery: The Abridged Guide
- Azure Backup: SQL Server Backups on Azure
- Azure Oracle: Your First Oracle Database on Azure
- Azure Database Migration Service: Automate Cloud DB Migration
- Azure Database Review: Your Guide for Database Assessment
- How to Back Up SQL Server to Azure
- Azure SQL Database: 18 Options for SQL Server on the Cloud
Microsoft SQL Server is one of the most popular relational database systems, one that’s trusted by enterprises for mission critical line of business applications. With many enterprises adopting a cloud first strategy, deployment or migration of SQL Server workloads to the cloud, and to Azure in particular, has become a crucial development. Some enterprises have even moved entirely to an Azure database model.
With deployment in the cloud comes flexibility, scalability, as well as a choice between multiple Azure SQL database deployment options. Azure, being one of the leading cloud service providers, makes it possible to run SQL databases using either a managed database-as-a-service (DBaaS) or an Infrastructure-as-a-Service (IaaS) delivery model. This blog will explore these two Azure SQL deployment models in detail and analyze the pros and cons to identify the best fit solution for specific use cases.
SQL Database on Azure
SQL databases are well capable of handling complex transactions required for business-critical applications. Be it a simple intranet application accessed by a few users or large-scale transactional applications with thousands of concurrent users like those used in the finance or banking sectors, SQL is built to cater to a wide spectrum of workloads.
Deployment models: When applications are migrated/deployed to Azure, customers can choose from the managed DBaaS (database-as-a-service) model or deploy their own SQL VMs using Azure Marketplace or custom images (the IaaS approach).
In the DBaaS approach, customers can focus on the database specific configurations as the underlying infrastructure is managed by Azure. This option is aligned with the PaaS cloud service model and mostly targets born-in-the-cloud applications or applications being migrated to the cloud with significant efforts on modernization.
There are two DBaaS offerings on Azure: Azure SQL Database and Azure SQL Database managed instances. The IaaS model on the other hand gives full control to the customer as they have access to the SQL Server on Azure and can finetune server and DB configurations, just like they could do in an on-premises SQL deployment.
Licensing: Azure offers two licensing models for SQL databases: pay-as-you-go and Azure Hybrid Benefit. In the pay-as-you-go model, the licensing cost is included in the cost of the service. This option is available for both PaaS and IaaS deployments.
While deploying Azure SQL database or Azure SQL managed instance customers can choose to select the pricing model where the license cost is included in the cost of the deployed instance and is charged on an hourly basis. This option is also available for SQL VMs deployed from Azure marketplace, where the hourly charges for compute includes SQL license plus the OS license charges.
Customers with existing SQL licenses with Microsoft Software Assurance can use their existing licenses in cloud. This bring-your-own-license model is known as Azure Hybrid benefit is available for v-core based deployment models of Azure SQL database and Azure SQL managed instances. Customers deploying SQL Server Enterprise and standard core VMs can also use the Azure Hybrid benefit in IaaS model.
Data migration: Organizations migrating SQL databases to Azure would want to do so with minimal operational overhead. Azure Database Migration Service (DMS) can facilitate this process, helping on-premises databases migrate to Azure SQL services through an online or offline migration process. The database migration assessment tool used by DMS helps to assess the source databases and highlights any compatibility issues so that customers can address them prior to the Azure migration. The target for migration can be Azure SQL DB, managed instances, or Azure SQL VMs.
Using the Azure Managed Service for SQL Server
The Azure SQL DBaaS is delivered as a fully managed service that provides the latest SQL Server capabilities of the box. With managed services, 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., which means this managed service isn’t entirely task-free.
The backend SQL engine uses the latest stable SQL enterprise edition. Since it is delivered as a managed service, the high availability of service is assured through Azure 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.
Though many DB administration activities are offloaded in Azure SQL Server, customers with diverse requirements could find them to be restrictive in certain circumstances. For example, the migration of databases from an older version of SQL to Azure SQL Server requires additional efforts in remediating compatibility issues. There is also no guarantee on the exact DB maintenance time. The DBaaS services are deployed in Azure and can be connected to resources in existing data center systems; however, there are certain limitations and specialized configurations required to do that, something which goes beyond the scope of this being a managed service and involves a higher degree of hands-on skill.
Some of the SQL features such as CLR, extended stored procedures, file streams, etc., are not supported by Azure SQL managed instances. Though backup is automated in Azure SQL Server, the schedule and frequency may not always align with your organization’s DR requirements. All of this means additional efforts will be required for on-demand backups and data copy to additional Azure regions for redundancy, making your deployment’s Azure backup challenging.
Managed services use built-in Azure storage, which again 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 administrators, there will be a learning curve before they can take up the configuration and management Azure SQL database.
Deploying SQL DB in Using Managed Storage
The alternative option to the managed service DBaaS model is to build your database using the managed cloud storage components in the IaaS deployment model.
The IaaS deployment model uses VM SKUs fine-tuned for databases and managed storage for deploying SQL VMs in Azure. Deployment of SQL DB using VMs and managed storage delivers the same end-to-end control of the DB deployment stack as on-premises deployment, all the while enabling customers to benefit from scalability and flexibility of the cloud.
Azure offers marketplace images which help to deploy SQL VMs in a matter of a few clicks. Unlike Azure SQL limitations, there is no restriction of the SQL version that can be deployed in the VMs, which helps avoid compatibility issues during migration.
Customers can choose managed disks or ultra disks for the VMs for superior performance in production deployments. The compute performance can be adjusted by scaling up VMs to high performance SKUs if required. Using Azure Hybrid benefits the cost of running SQL VMs in Azure is reduced up to around 55%. It also allows the usage of a free passive secondary replica for high availability through the Fail-Over Servers Software Assurance benefit. This effectively reduces the licensing cost of a highly available SQL deployment by half.
Unlike managed instances, in IaaS deployments customers own the configuration of high availability, backup management, patching, etc. It is up to the customers to devise a scaling strategy and configure it, as autoscaling capabilities are limited to PaaS services. However, this could also prove to be beneficial in scenarios similar to the one we discussed in the previous section, i.e., 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.
Enhance Azure SQL Deployment Using Cloud Volumes ONTAP
Finding the right fit storage service is very crucial for successful SQL deployments in Azure. Unlike DBaaS services, the managed storage deployment approach provides greater flexibility to customize the storage layer. A great help in these situations is to employ a data management component such as NetApp Cloud Volumes ONTAP.
Cloud Volumes ONTAP is an enterprise class data management solution in Azure that can help customers meet the performance, availability, and agility requirements of SQL databases. It augments the native cloud storage layer with a set of built-in storage management features and capabilities. Cloud Volumes ONTAP is built for hybrid deployments and supports consistent deployment of SQL workloads irrespective of whether they are deployed on-premises or in the cloud.
Some of the key benefits of Cloud Volumes ONTAP as managed storage for SQL deployments in Azure:
- 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.
You can see how one organization made deploying a database on managed storage with Cloud Volumes ONTAP a success. 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. This court system uses Cloud Volumes ONTAP for Azure Development and testing environments of their database applications.
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 engineering for the database a success. Next steps include expanding Cloud Volumes ONTAP’s use for the database’s disaster recovery and data archiving needs.
The different SQL Server deployment models in Azure come with their own set of advantages and disadvantages. While DBaaS helps with quick deployment and eases the management overhead, the IaaS deployment using managed storage delivers more flexibility in terms of DB configuration and control over the storage layer.
Cloud Volumes ONTAP, when used as the storage service for an IaaS deployment, provides a unified management layer for DB deployments across Azure and hybrid environments. The advanced data management features like snapshots, data replication and FlexClone data cloning help to meet the DB storage management requirements of enterprise applications.