Configure Azure SQL managed instances for scale and performance
We will learn how to scale and conduct Azure SQL-managed instances in this article.
The intelligent, scalable cloud database service Azure SQL Managed Instance offers the broadest SQL Server database engine compatibility with all of the benefits of a fully managed and evergreen platform as a service. SQL Managed Instance is nearly 100 percent compatible with SQL Server (Enterprise Edition), with a native virtual network (VNet) solution that addresses major security problems and a commercial model that appeals to current SQL Server customers.
The following diagram outlines key features of SQL Managed Instance:
Customers that want to migrate a large number of apps from an on-premises or IaaS, self-built, or ISV-provided environment to a fully managed PaaS cloud environment with as little work as possible should use Azure SQL Managed Instance. Customers may lift and transfer their existing SQL Server instance to SQL Managed Instance utilising the fully automated Azure Data Migration Service, which delivers SQL Server compatibility and total isolation of client instances with native VNet support.
vCore-based purchasing model
The vCore-based SQL Managed Instance purchase approach provides you with flexibility, control, transparency, and a simple method to adapt on-premises workload needs to the cloud. Furthermore, depending on your workload requirements, this paradigm allows you to adjust computation, memory, and storage. The Azure Hybrid Benefit for SQL Server allows you to save up to 55 percent on the vCore model.
However, in the vCore model, you can choose between generations of hardware.
- Firstly, Gen4 logical CPUs are based on Intel E5-2673 v3 (Haswell) 2.4 GHz processors, attached SSD, physical cores, 7-GB RAM per core, and compute sizes between 8 and 24 vCores.
- Secondly, Gen5 logical CPUs are based on Intel E5-2673 v4 (Broadwell) 2.3 GHz, Intel SP-8160 (Skylake), and Intel 8272CL (Cascade Lake) 2.5 GHz processors, fast NVMe SSD, hyper-threaded logical core, and compute sizes between 4 and 80 cores.
Service tiers
SQL Managed Instance is available in two service tiers:
- Firstly, General purpose: Designed for applications with typical performance and I/O latency requirements.
- Secondly, Business critical: Designed for applications with low I/O latency requirements and minimal impact of underlying maintenance operations on the workload.
General Purpose service tier
The following list describes key characteristics of the General Purpose service tier:
- Firstly, designed for the majority of business applications with typical performance requirements
- Secondly, high-performance Azure Blob storage (8 TB)
- Lastly, built-in high availability based on reliable Azure Blob storage and Azure Service Fabric
Business Critical service tier
The Business Critical service tier is built for applications with high I/O requirements. It offers the highest resilience to failures using several isolated replicas.
The following list outlines the key characteristics of the Business Critical service tier:
- Firstly, designed for business applications with highest performance and HA requirements
- Secondly, comes with super-fast local SSD storage (up to 1 TB on Gen4 and up to 4 TB on Gen5)
- Thirdly, built-in high availability based on Always On availability groups and Azure Service Fabric
- Then, built-in additional read-only database replica that can be used for reporting and other read-only workloads
- Lastly, in-Memory OLTP that can be used for workload with high-performance requirements
Security features
Azure SQL Managed Instance provides a set of advanced security features that can be used to protect your data.
- Firstly, SQL Managed Instance auditing tracks database events and writes them to an audit log file placed in your Azure storage account. Auditing can help you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies.
- Secondly, Data encryption in motion – SQL Managed Instance secures your data by providing encryption for data in motion using Transport Layer Security. In addition to Transport Layer Security, SQL Managed Instance offers protection of sensitive data in flight, at rest, and during query processing with Always Encrypted. Always Encrypted offers data security against breaches involving the theft of critical data.
- Thirdly, Advanced Threat Protection complements auditing by providing an additional layer of security intelligence built into the service that detects unusual and potentially harmful attempts to access or exploit databases.
- Next, Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling you to designate how much of the sensitive data to reveal with minimal impact on the application layer.
- After that, Row-level security (RLS) enables you to control access to rows in a database table based on the characteristics of the user executing a query (such as by group membership or execution context).
- Lastly, Transparent data encryption (TDE) encrypts SQL Managed Instance data files, known as encrypting data at rest. TDE performs real-time I/O encryption and decryption of the data and log files.
Dynamically scale database resources with minimal downtime
With SQL Managed Instance and Azure SQL Database, you can dynamically add extra resources to your database with little downtime. However, there is a brief period during which connectivity to the database is lost, which may be addressed by employing retry logic. When the number of devices and consumers using your app rises to millions, Azure SQL Database and SQL Managed Instance scale on the fly with low downtime. One of the most essential features of the platform as a service (PaaS) is its scalability, which allows you to dynamically add extra resources to your service as needed. You may quickly adjust the resources (CPU power, memory, IO throughput, and storage) allotted to your databases with Azure SQL Database.
Further, Azure SQL Database offers the DTU-based purchasing model and the vCore-based purchasing model, while Azure SQL Managed Instance offers just the vCore-based purchasing model.
- Firstly, the DTU-based purchasing model offers a blend of compute, memory, and I/O resources in three service tiers to support lightweight to heavyweight database workloads: Basic, Standard, and Premium. Performance levels within each tier provide a different mix of these resources, to which you can add additional storage resources.
- Secondly, the vCore-based purchasing model lets you choose the number of vCores, the amount or memory, and the amount and speed of storage. This purchasing model offers three service tiers: General Purpose, Business Critical, and Hyperscale.
Single databases in Azure SQL Database, on the other hand, offer manual dynamic scalability but not auto scalability. Consider adopting elastic pools, which allow databases to share resources in a pool depending on individual database demands for a more automated experience. However, there are scripts in Azure SQL Database that can help automate scaling for a single database.
Azure SQL Database offers the ability to dynamically scale your databases:
- Firstly, with a single database, you can use either DTU or vCore models to define maximum amount of resources that will assign to each database.
- Secondly, elastic pools enable you to define maximum resource limit per group of databases in the pool.
Azure SQL Managed Instance allows you to scale as well:
- SQL Managed Instance uses vCores mode and enables you to define maximum CPU cores and maximum of storage allocated to your instance. All databases within the managed instance will share the resources allocated to the instance.
Reference: Microsoft Documentation, Documentation 2