Part-1

Azure SQL vs SQL Server

Microsoft SQL Server is a popular database solution chosen by organizations because of its performance, scalability and enterprise-ready features. When you need to migrate or create a Microsoft SQL Server project to Azure, there are three different options:

• Azure SQL Server Managed Instances
• Azure SQL Database
• SQL Server on Azure Virtual Machines

Generally, the Azure SQL options help reduce complexity while the SQL Server option increases control. However, there’s no one-size-fits-all answer when choosing Azure SQL vs. SQL Server for an application. Each approach comes with trade-offs, and organizations need to weigh the pros and cons to decide what’s right for them. 

In this article, we will review all three options in detail, explain their differences, and help you decide which approach is best for your environment. 

Differences between Azure SQL Database vs. SQL Server on Virtual Machines.

Before we get into the details of each solution, let’s highlight the main feature differences between Azure SQL Server vs. SQL Server on virtual machines (VMs). We summarized information from Microsoft’s official docs (1 & 2) in the table below to create a quick reference for Azure SQL Server vs. SQL Server features.

*Lift & Shift: It means to move an application from one data center location to another.

Azure SQL Server Managed Instance Option 

The Azure SQL Server Managed Instance option is a Microsoft Azure offering where you have access to a full SQL Server instance without managing the OS and underlying services.

This option gives you the advantage of having nearly 100% compatibility with Microsoft SQL Server Enterprise Editions, which is required for most lift & shift applications.

Key features:

• Quick provisioning and service scalability
• 99.99% Uptime SLA 
• You can select between having a single instance or an instance pool
• You have almost 100% compatibility with SQL Server Enterprise, which will make Lift & Shift Migrations easier
• You get a fully-managed SQL Server Instance, including upgrades, backups, patch management, High Availability, and Disaster Recovery Setup and Operation. However, the administrator manages some operations at the instance level, like TempDB, Logins, and audit logs. 
• You can migrate your databases from any SQL Server 2008 to 2019 using Azure Database Migration Service or Log Replay Service for near-zero downtime.

Security Features: 

• VNETs and Network Security Groups (NSG) to improve security at the network level. 
• 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 Support.
• SQL Server Auditing – Requires configuration 
• 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. 

Design your Azure SQL Managed Instance

 Now, let’s look at the configuration options for an Azure SQL Managed Instance. 

VCores required

For Azure SQL Managed Instances, you will use a vCore pricing model. This model is the best option for current SQL Server deployments because you can select the number of cores, memory, and storage needs for your existing applications. 

The available options in this model include: 

• 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. 

Instance Type

You can select if you need a single instance for your managed SQL Server or if you want an instance pool. An instance pool allows you to add multiple instances to a single SQL Server managed pool.

You should use SQL Server Instance Pools to consolidate multiple SQL Server instances from different applications and manage them in a single pool in Azure. 

Here are some guidelines on how to select each option: 

Service Tiers

For the service tiers, you can select between these options: 

• General Purpose- Applications with standard performance and I/O operations.
• Business-Critical- Applications requiring low latency on I/O operations and minimal performance impact for maintenance operations. 

For each service tier, it is essential to understand the critical differences for storage and how high availability is managed, so you can select which option suits your application requirements. Here is a comparison of feature