Microsoft Azure – Azure SQL Managed Instance

We will get an overview of Azure SQL Managed Instance. Managed instances combine leading security features with SQL Server compatibility and a business model designed for on-premises developers and clients. There are many reasons that developers want to migrate to Azure SQL Managed Instance. The main one is, that they want to migrate to Azure, and they want to remove some of that management overhead and stress with regards to some of the PaaS capabilities. But they might have restrictions. Maybe they need instance-scoped features. 

When we talk about instance-scoped features, we really mean features that are scoped to the server as opposed to the database, and this includes things like Service Broker, SQL Server Agent, CLR, machine learning services, etc. These are just a few of the reasons why developers move towards Azure SQL Managed Instance. 

Key Features and Capabilities of Azure SQL Managed Instance

Azure SQL Managed Instance is a fully managed database service provided by Microsoft Azure, offering a range of capabilities for deploying, managing, and scaling SQL Server databases. Some of its key features include:

1. Fully Managed Service: Since Azure SQL Managed Instance is a fully managed platform, you are relieved of many administrative responsibilities as Microsoft takes on maintenance tasks like patching, backups, and improvements.
2. Compatibility: It provides a simple to transfer current SQL Server databases to the cloud by providing compatibility with the most current SQL Server features as well as supporting nearly every one of SQL Server’s features and functionalities.
3. High Availability: With embedded disaster recovery capabilities, automated patching, automatic backups, and high availability, managed instances provide all of these advantages. It ensures the high reliability and robustness of your databases against failures.
4. Security: Comprehensive safety features are accessible with Azure SQL Managed Instance, including network isolation with Virtual Network (VNet) service endpoints, data encryption during transit and at rest, and authentication connection to Azure Active Directory.
5. Scalability: It allows you to quickly and without any downtime scale compute and storage resources up or down according to your workload requirements. For handling varying workloads, you may benefit from capabilities like computerized scaling.
6. Performance: With features such as provided compute and storage resources, advanced query processing, and automatic tuning to maximize database performance, Managed Instance offers predictable and consistent performance.
7. Integration with Azure Services: It provides an entire platform for developing and managing your apps through integration with other Azure services such as Azure Virtual Machines, Azure Active Directory, Azure Monitor, and Azure Backup.
8. Cost-Effective: Pay-as-you-go and reserved capacity pricing were merely two of the many pricing choices Microsoft Azure SQL Managed Instance offers, allowing you to customize demands to your usage patterns and budget.
9. Developer Productivity: With built-in support for renowned programming platforms and languages, integrated development environments (IDEs) like Azure Data Studio, and support for agile methodologies like continuous integration and deployment (CI/CD), it provides features and tools that boost developer productivity.
10. Advanced Analytics: For complicated analytics and data processing activities, Managed Instance links with Azure services like Azure Machine Learning and Azure Data Lake Storage, enabling you to obtain insightful information from your data.

The Surface Area SQL Server

Formerly as the Cloud lifter project, it was eventually renamed Managed Instance since, although it has managed functionality like built-in availability groups and security, you are actually interacting with a full SQL Server instance with that feature set outside of the control of just a database. Therefore, you get a SQL Server instance with managed PaaS capabilities.

There is a fairly significant difference. Initial online migrations are one of the best features of using Managed Instance to help you in migrating to the cloud. It enables you move from your premises environment to an online environment instead smoothly through the use of log shipping technology and database backups and repairs. Already stated, Azure will offer comprehensive manage continuity for business availability, with capabilities like failover groups and replicas which almost replicate a self-sufficient availability group across regions. While Azure will be administering the virtual machines’ core construction, you can still use Management Studio, that will look and behave just like a SQL Server. However, this time behind the scenes with those.

Thus, Azure SQL Managed Instance can be the ideal option for you if you want to swiftly move to the cloud and get benefits like reduced expenses or if you need more IaaS includes while maintaining an enormous SQL Server surface area.

Features of Azure Managed Instance

Supported SQL features

Many SQL Server operates that are frequently utilized for database building, management, and optimization are offered by Azure SQL Managed Instance. Amongst the SQL features which are supported were:

1. Transact-SQL (T-SQL): The primary language used for managing and query SQL Server databases.
2. Stored Procedures: sets of one or more precompiled SQL statements which are repeated.
3. Triggers: Database objects that, on specific events, such as INSERT, UPDATE, or DELETE measures on a table, start computerized operation.
4. Views: Virtual tables that offer simpler and more secure access to data via relying on the result of a SELECT query.
5. Indexes: Structures which offer rapid access to data stored in tables, consequently speed up data retrieval processes.
6. Constraints: Data integrity rules, such as primary key, foreign key, unique, and check constraints, have been applied on the data in a table.
7. Data Types: Different data types, including dates, integers, writings, and binary data, are used to store various types of data.
8. Transactions: work units which ensure data integrity and consistency through carrying it performed as a single, undivided behavior.
9. User-defined Functions (UDFs): T-SQL functions specifically created for use in stored procedures and queries.
10. Dynamic Management Views (DMVs): System views providing data on the resources and show the the state of the database server.
11. Full-text Search: The capacity to search text data found within the database utilizing full text.
12. Common Table Expressions (CTEs): Impermanent collections of results that can be utilized as reference in operations such SELECT, INSERT, UPDATE, and DELETE.
13. Window Functions: Analytic functions that calculate across an assortment of rows related to the current row.
14. In-Memory OLTP: High-performance transaction processing is supported using natively developed stored procedures and in-memory tables.
15. JSON Support: Operators and functions for dealing alongside and querying JSON data.
16. Columnstore Indexes: Specialised index type built with workloads in analytics and data warehousing in consideration.
17. Temporal Tables: Tables that store historical data automatically develop over time.

vCore-based purchasing model

A few benefits of the vCore-based purchase method for an Azure SQL Managed Instance are flexibility, control, transparency, and possible cost savings via the Azure Hybrid Benefit for SQL Server. The vCore idea enables you to choose hardware configurations in the following methods:

1. Standard Series (Gen5):
   • CPU: Based on Intel® E5-2673 v4 (Broadwell) 2.3 GHz, Intel® SP-8160 (Skylake), and Intel® 8272CL (Cascade Lake) 2.5-GHz processors.
   • RAM: 5.1 GB of RAM per CPU vCore.
   • Storage: Fast NVMe SSD.
   • Other Features: Hyper-threaded logical core.
   • Compute Sizes: Available between 4 and 80 cores.
2. Premium Series:
   • CPU: Based on Intel® 8370C (Ice Lake) 2.8-GHz processors.
   • RAM: 7 GB of RAM per CPU vCore (up to 128 vCores).
   • Storage: Fast NVMe SSD.
   • Other Features: Hyper-threaded logical core.
   • Compute Sizes: Available between 4 and 128 cores.
3. Premium Series Memory-Optimized:
   • CPU: Based on Intel® 8370C (Ice Lake) 2.8-GHz processors.
   • RAM: 13.6 GB of RAM per CPU vCore (up to 64 vCores).
   • Storage: Fast NVMe SSD.
   • Other Features: Hyper-threaded logical core.
   • Compute Sizes: Available between 4 and 64 cores.

Service tiers

Varying service tiers are accessible for Azure SQL Managed Instance to meet differed performance, availability, and feature needs. Among these tiers are as follows:

1. General Purpose:
   • Designed to maintain cost and performance for typical business workloads.
   • Offers high availability with automated patching and backups.
   • Suitable for an array of analytical and transactional workloads.
   • Available through the DTU (Database Transaction Unit) and vCore purchasing models.
2. Business Critical:
   • Optimized for mission-critical, high-performance workloads requiring low latency and high availability.
   • Provides automatic failover, faster recuperation times, and high availability with zero data loss.
   • Utilizing SSD storage via Azure Premium for improved I/O performance.
   • Suitable for workloads involving OLTP (Online Transaction Processing) and data warehousing.
   • Available via the vCore purchasing model.

Advanced security and compliance

Advanced security and compliance abilities are available in Azure SQL Managed Instance to help protect your data and comply to legal requirements. Amongst the main features are as follows:

1. Data Encryption: By default, data in transit and at rest is protected using Azure SQL Managed Instance. While data in transit is encrypted using SSL/TLS, Transparent Data Encryption (TDE) encrypts data files, backups, and transaction log files.
2. Dynamic Data Masking: This function guarantees that only authorized users may view every bit of data, helping to prevent unwanted access to important data by immediately masking it.
3. Row-Level Security (RLS): RLS provides fine-grained access control over data through allowing you to restrict access to rows in a database table based on the characteristics of the user performing a query.
4. Always Encrypted: The Always Encrypted function ensures that confidential data, like credit card numbers as well as personally identifiable information (PII), stays encrypted even when accessed by privileged users or database administrators.
5. Azure Active Directory Integration: Managed Instance provides centralized identity management and access control for your databases through integration with Azure Active Directory (Azure AD) for authentication and authorization.
6. Advanced Threat Protection (ATP): By continuously monitoring database activities, determining patterns, and alerting administrators about questionable behavior, ATP helps in the real-time detection and response to potential threats.
7. Vulnerability Assessment: For SQL databases, Azure Security Center provides comprehensive vulnerability assessment instruments that help with locating and fixing possible security vulnerabilities and set up errors.
8. Auditing and Compliance: You can track and monitor the operation of databases, access attempts, and configuration changes using Managed Instance’s auditing characteristics. In addition, it aids in proving that you adhere to regulations including GDPR, HIPAA, and PCI DSS.
9. Data Classification: Sensitive data can be more effectively determined, tracked, and safeguarded if it is divided according to its impact on the business and its degree of sensitivity.
10. Regulatory Compliance: Azure SQL Managed Instance provides a secure and compliant platform for your apps and data by adhering to a variety of industry-specific and regional demands, including GDPR, HIPAA, SOC 1/2/3, ISO 27001, and PCI DSS.