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 PaaS has two main offers for the customers who use SQL Server database and want to migrate to PaaS:
- Managed Database - isolated and self-contained database service that has database scoped functionalities.
- 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" as PaaS model. As a result, some of the instance-level features in Azure SQL Database PaaS such as SQL Agent or linked servers are not supported because they are not applicable on the database level.
Currently, the only way to get the full SQL Server instance in Azure is to use Azure SQL VM that handles underlying infrastructure (e.g. disks), but still not have some SQL PaaS features as Azure SQL Database.
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 a Managed Instance with 16 cores and 500GB storage, and then increase or decrease these numbers depending on your needs. Changing CPU or storage in instance can be done via Azure portal using simple slider:
Any change that you made will be almost instant.
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.
There are also redundant passive nodes that are used to fail-over compute instance if needed.
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.
Connectivity
Azure SQL Managed Instance is not a service on public endpoint. Azure SQL Managed Instance is placed on private IP address in your VNET. It is just hosted and managed by Azure cloud.
There are several ways to connect to your Managed Instance.
- You can create VPN connection between your on-premises network and Azure VNET where Managed Instance is placed. Managed Instance will be seen as any other SQL Server in your network.
- You can connect via some Azure VM in your Azure VNET (using standard RDP or via some app that is in your Azure VM). If your VM is placed in some other AzureVNET you need to establish peering between these two networks.
- You can Connect your web application to Managed Instance using Azure Apps VNET Integration or by placing your Web App into App Service environment that is placed in VNET. If your Web App or App service Environment is placed in another VNET, you need to establish peering between these two networks.
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.