Azure SQL Managed Instance

If you’re looking for a new and better deployment option, I’d like to tell you about a relatively new offering from Microsoft, Azure SQL Managed Instance. It has nearly 100% feature compatibility with the latest on premise SQL Server Enterprise Edition database engine.

Azure SQL Managed Instance allows relatively easy lift and shift of on premise applications to the cloud, with minimal application and database changes. Required management overhead and total cost of ownership is drastically reduced with Platform as a Service capabilities such as automatic patching, version updates, backups and high availability.

For those of you running older versions of SQL Server, Azure SQL Managed Instance supports backward compatibility to SQL 2008 databases. Also, it supports direct migration from SQL 2005 database servers, however, these migrated databases will have their compatibility level updated to SQL 2008.

Two major benefits to choosing a Managed Instance deployment option is it will always be up to date as patches and upgrades are automatically applied immediately upon release, as well as the fact that you don’t have to purchase, manage or secure physical hardware.

Managed Instance has high availability built in and preconfigured, using a technology similar to the always-on availability groups (which you know about from regular SQL Server). This will provide a 99.99% uptime SLA. It also has automated backups and point in time restore.

Another huge time saving element is that you will spend less time on administrative tasks as the service will either perform them for you or greatly simplify them compared to the on premise version. Managed Instance supports Azure Active Directory authentication as a cloud alternative to Windows authentication.

It also automatically manages XTP file group and files for databases containing in-memory OLTP objects, plus it supports SQL Server Integration Services and can host the SSIS catalog that stores those packages. The SSIS packages must be executed on the Azure SSIS integration runtime in Azure Data Factory. However, in contrast to the regular Azure SQL database you may be used to, the Managed Instance allows cross database queries, making it more like your on premise versions of SQL.

There are two service tiers available:

• General Purpose - designed for applications with typical performance and IO latency requirements.
• Business Critical – designed for applications with low IO latency requirements and minimal impacts of underlying maintenance operations on the workload.

Other things to note:

• Service sizes can range from 8 to 80 virtual cores depending on the service tier.
• Memory is about 7 gigabytes per virtual core.
• The maximum storage is 8 terabytes for general purpose and up to 4 terabytes for business critical.

Some recent good news as of April 2019 is that Managed Instance now has a public endpoint. Before this you could only connect via VPN or something other from on prem to there, but now we can connect through the public endpoint. But note, the Managed Instance public endpoint can only be enabled using PowerShell. The script retrieves the Azure SQL Instance then sets the public data endpoint enabled to ‘true’ for the Azure SQL Instance.

You’ll also need to create a network security group with port 3342 open for inbound traffic. Currently this is the only port that Managed Instance can use for public endpoints and so it cannot be customized at this date. To access Managed Instance through the internet, use the host name in the following: managedinstancename.public.yourdnsname.database.windows.net,3342.

If you’d like more information about Azure Managed Instance as a better deployment option, we’d be happy to help. Click the link below or contact us about this or any Azure product or service—no matter where you are on your Azure journey, we can help.