Part-2

Azure SQL Server Database Option

The Azure SQL Database option is the most modern solution from Microsoft for SQL databases, and it is available only through Microsoft Azure. Unlike the Azure SQL Server Managed Instance Option, in this implementation, you only have to manage the databases and some of the features in SQL Server like logins. 

Microsoft handles most instance management, including backups, managing instance memory, sizing data files, working tempdb, and applying patches. You are no longer a sysadmin of the instance, and you administer  database, user, and audit management only.

Key Features: 

• You get a fully-managed SQL Server Instance, including upgrades, backups, patch management, High Availability, and Disaster Recovery setup and operations. 
• Quick provisioning and service scalability
• 99.99% Uptime SLA 
• You can select between a vCore pricing model, DTU Model, or a Serverless model (we will cover these in detail in the next section). 
• You can migrate your databases from the traditional SQL Server using Microsoft’s Data Migration Service, using a .bacpac file or SQL Server Replication. 
• Automatic performance monitoring and tuning with zero intervention from the administrator. 
• Built-in intelligence that allows customers to reduce costs of enhancing security for databases.

Security Features: 

• Firewall security for database access
• Transparent Data Encryption (TDE) support is a built-in feature for Azure SQL Server Managed Instance option and Azure SQL Database, which encrypts data without changing the application. 
• Azure AD or SQL Server Logins – Azure SQL Database can be configured to allow only Azure AD logins for enhanced security. 
• Microsoft Advanced Threat Protection Support – Azure SQL Server Managed Instances support Microsoft Advanced Threat Protection out of the box, so you can configure to protect against threats in SQL servers like SQL Injection or brute force SQL password cracking. 
• In-depth auditing capabilities, including Microsoft operations, configured at Azure Portal. 
• Advanced monitoring and alerting using Microsoft Azure Monitor. 

Designing your Azure Database Model: 

When you decide to use the Azure Database Model, you have different options to enable the service. Below are the options you need to choose from when setting up the service.

Resource Type

The first option you need to select is what resource type you want to use for your Azure Database Service. You can choose between these two:

• Single Database- Select this Option if you want to deploy a single database with dedicated resources for it.  
• Elastic Pool- Select this Option if you want to deploy a pool of resources for multiple databases that share a pool of resources. It is good for shared workloads with variable usage patterns. 

Pricing Model

In Azure Database, there are two pricing models: 

VCore Model

The traditional pricing model works the same way as the Microsoft SQL Server Managed Instance Option. The available options in this model include the following:

• Gen5 (Standard) 
• Premium-Series 
• Premium-Series, Memory Optimized

You can select the number of cores required after selecting the hardware model based on the hardware requirements for your applications. 

For single databases, you have two additional options to choose from:

• Serverless- Compute resources are autoscaled as the application needs them. The price is based on the number of vCores used per second. In this option, you can select the maximum number of cores to scale up and the minimum number of cores to scale down the database. You also have an option to auto-pause the database, which will save money when the database is not in use. However, use this option carefully because it may not perform well at scale. 
• Hyperscale- This Option supports up to 100TB of data. It is reserved for huge database workloads and supports multiple read-scale replicas. Use this option only if you need to have a database server larger than 16TB. Remember you cannot change this option after the database is created. 

Distributed Transaction Unit Model (DTU)

You can select DTU as the pricing model for your single database or elastic pool using Azure SQL Database. A DTU is a measurement of these three main components of the database performance: 

• CPU
• Memory 
• Disk I/O. 

Microsoft created the concept to facilitate the sizing of a database based on this metric, enabling more precise metrics to size a database properly and scale up or down as needed. 

The service tiers available for the DTU model are the following:

• Basic- Used for less demanding workloads. It is often used only for dev and test purposes, and it can only scale up to 5 DTUs.
• Standard- Used for most applications. It is common for cloud applications, such as workgroups or web applications. You can select between 10 to 100 DTUs in this service tier. 
• Premium- Used for high-transaction applications that require high-level business continuity. DTUs can be configured between 125 up to 4000 in this service tier. 

To help you select the best option if you decide to go with the DTU model, the table below breaks down the differences between the three service tiers. 

Here is an example of how to configure the DTU tier and number of DTUs for your single database or database pool: 

Configuration of DTUs in the Azure portal.

If you selected Elastic Pool, you can also set the DTU option. However, sizing is different.

Below is a table per each tier for the elastic pool model for DTU.

DTU Frequently Asked Questions

Before we proceed, let’s address some common DTU questions.

How do I know how many DTUs and service tiers I need for my databases?

Microsoft has created a DTU calculator tool to help you determine which service tier you should use and how many DTUs you need for your database workloads. You can find the Azure SQL Database DTU Calculator here.

How to choose between VCore or DTU?

If you need more control over the VCores assigned to a database or database pool, the VCore option is ideal. However, the DTU model works better if you have pricing constraints because it allows you to purchase a smaller capacity than a full VCore.

As a general rule, 100 DTUs in the Standard tier is equivalent to 1 VCore in the same tier, and 125 DTUs in the premium tier is equal to 1 VCore in the same tier. Also, pricing in DTUs is better when it scales because it is measured in smaller metrics than a VCore.

Our general recommendations are:

• Select the VCore model if your database requires CPU allocation as the primary performance component
• Select the DTU model if your database requires a tight mix of resources between CPU, Memory, and I/O or requires less than 1 VCore.
• Always select the DTU model if you require less than 1 VCore of processing, for example, for Dev/Test databases.

SQL Server on Virtual Machines Option

The SQL Server on Virtual Machines option is the most compatible solution for on-premises migrations to the cloud. You can deploy it with the same configurations as your current on-premises SQL Server instances.

Key features:

• You have complete control of the infrastructure where SQL Server runs.
• You get all the functionality of SQL Server Standard or Enterprise, so you can migrate applications that require features not supported in Azure SQL Database to Azure.
• You get all auditing capabilities at the OS and SQL Server levels.
• You have more granular control over backup and restore operations.
• High-availability and disaster recovery from SQL Server Enterprise included.

Security Features:

• VNet and Network Security Groups (NSGs) can isolate the SQL Servers from the network.
• Azure provides infrastructure-level security.
• Administrators must configure and manage OS-level security and SQL Server security.
• Administrators are responsible for patch management.

Designing your SQL on Virtual Machines Model:

If you decide to go with SQL on the Virtual Machines Model, you will need to design a complete solution that includes:

• Virtual machines
• Managed disks
• Virtual networks
• Storage accounts
• Availability Zone design
• SQL Server high availability setup design
• OS and SQL Server Licenses

Best practices

In summary, here are our recommendations for each of the SQL Server deployment scenarios and deployment options.

Conclusion

Selecting between Azure SQL vs. SQL Server is not easy. From performance to cost to operational complexity, many factors influence the decision. Fortunately, with the information in this article, you can make an informed decision for your next SQL Server project.