What is Azure SQL Database Managed Instance?

What is Azure SQL Database Managed Instance?

Azure SQL Database Managed Instance is a new flavor of Azure SQL Database that represents fully managed SQL Server Instance hosted in Azure cloud.

Currently, Azure SQL Database has two main offers for the customers who use SQL Server database and want to migrate to PaaS:

  1. Managed Database – isolated and self-contained database service that has database scoped functionalities.
  2. Elastic pool – a group of Azure SQL databases that share the same resource.

However, current Azure SQL Database offers don’t provide entire SQL Server “Instance as a Service” model. As a result, some of the instance-level features such as SQL Agent or linked servers are not supported because they are not applicable on the database level.

Managed Instance is a new managed database service that represents fully-managed SQL Server Instance in Azure cloud. It shares the same code with the latest version of SQL Server Database Engine and has the latest features, performance improvements, and security patches. This service is currently in public preview.

What is Managed Instance?

Azure SQL Database Managed Instance is fully managed SQL Server Database Engine instance hosted in Azure and placed in your network. It has most of the SQL Server 2017 features (excluding some on-premise Windows features such as Windows logins or potentially harmful features such as extended stored procedures) and enables you to put almost any database that you have in on-premises SQL Server instance. Every instance is fully isolated from the other customer instance and placed in your dedicated subnet with assigned private ip addresses.

Security/Isolation

Managed Instance is a resource in your network hosted by Azure cloud. You need to create Azure VNET and a dedicated subnet where the instance should be deployed. There are some networking constraints for the VNET/subnet that you need to review before you create a managed instance.

There is no public IP address dedicated to the Managed Instance. Only applications in customer network can access Managed Instance. In addition, your network administrators have the full control and can configure access to Managed Instance using standard methods such as Network security Groups and firewalls.

Features

Managed Instance enables you to use almost any feature that you have in SQL Server 2017 Database Engine. Managed Instance supports CLR, cross-database queries, linked servers, Service Broker/Query Notifications, native BACKUP/RESTORE statements, db_mail, etc. You can find feature compatibility matrix here.

Scaling up/down

Managed Instance enables you to choose how many CPU cores you want to use and how much storage you need. You can create an Instance with 16 cores and 500GB storage, and then increase or decrease these numbers depending on your needs. Changing CPU or storage is instance and can be done easily via Azure portal.

Architecture

Managed Instance has split compute and storage components. You have compute nodes with 8, 16, or 24 cores, that work with database files stored on Azure Premium disk storage. Every database file is placed on separate Azure premium disk, that guarantees that database files cannot be lost. Although Managed Instance relies on Azure Premium disks, it also has separate backup mechanism that ensures that even if something would happen with the files, platform would still be able to restore files from backups.

Migration

Managed Instance enables you to easily move your database to Azure cloud. The simplest way to migrate database is to create a backup, move it to Azure Blob storage and restore the database on Managed instance using standard RESTORE DATABASE …. FROM URL T-SQL statement. If you need minimal downtime during migration you can use Azure Database Migration Service.¬†

See more information about migration to Managed Instance here.

PaaS

Managed Instance is fully Platform as a Service database offer. High availability, automated backups, point-in-time restore, automatic plan correction, threat detection, vulnerability assessment, and other intelligent features are built-in into service without any additional charge.

OS patching and database upgrades are handled automatically and do not require any action.

In addition to built-in monitoring and maintenance features, you can use any 3rd-party tool to monitor and manage your instance, because most of the system views are exposed.

Conclusion

Azure SQL Database Managed Instance is the best destination for SQL Server databases that need to be managed by Azure cloud. Public preview for Managed Instance is already started and you can submit request for your Managed Instance via Azure portal. See more information about the Managed Instance here.

Source: Microsoft Blog – SQL Server Storage Engine – What is Azure SQL Database Managed Instance?

We provide excellence in Database Administration

Let's work together