SQLBits 2023 -Day 1-A day with SQL Managed Instance

Tuesday 14.03.23 was the first day of SQLBits. This day was dedicated to the workshop, and I chose to follow “A Day with SQL Managed Instance”.
During this day, we talked about provisioning, connection and configuration, security, but also data migration and mobility, monitoring, and performance. Last year, Microsoft developers made 42 improvements to the service SQL Managed instance.

As you can see the day was interesting and we saw a lot of content. So, I am going to give you a summary of the possibilities with Azure SQL Managed instance (SQL MI). Azure SQL Managed Instance uses the most powerful SQL engine (Enterprise Edition) and uses its own VNet. As it is a service that is fully managed by Azure, the person using SQL MI only must implement their own business model. There are two levels of service: the general level for most workloads and the critical level for workloads requiring low latency, memory, or secondary reading.

There are still some limitations like backup retention, tempdb etc.
Below is the difference during a failover between the General-Purpose service and the Business-Critical service

Provisioning

Here is what needs to be mentioned to provision the managed instance:
• Azure Tenant and Subscription
• Azure VNet and Subnet
• Resource Group
• Region
• Name
• Admin login name
• Time zone
• Collation
After provisioning, it is possible to modify certain options concerning the hardware, the networking, for the maintenance of the window or during a failover. Then, it is possible to modify some options concerning the hardware, the networking, the maintenance window or during failovers.
To create a SQL MI instance in a new subnet, it tooks between 4 and 6 hours before the November 2022 update. Thanks to the 2022 update, the instance is now 30 min long due to the fact Fast provisioning is available to popular configurations that cover most of users (GP, 4 and 8 vCores, default maintenance). For the moment, this new feature is only available for test environments, but should be available this year for the production one.

Connectivity

SQL managed instance integrates into a customer – owned VNet. Traffic originates from and terminates in a customer’s subnet. The VNet-local connectivity by default, accessible only from within that VNet. SQL managed instance imposes requirementson the subnet for example:

• We cannot deploy custom resources in SQL MI’s delegated subnet
• Traffic from/to service dependencies must be able to reach with mandatory routes and must be allowed by mandatory NSG rules.
  There are many connections available like using:
• Public endpoint
• Jump server
• Bastion
• Point to site VPN
• And so on.

Security

There are many areas where you can add security when using SQL MI. Here is an example by sphere

3.1 Network security
For provide networking security, we can use:

• Network security groups = add rules for inbound/outbound security rules
• Azure Firewall

3.2 Access management
There are 3 main possibilities of authentication on SQL MI, such as SQL Authentication (password authentication), Azure AD authentication, or MFA (multifactor authentication).
In addition to Azure AD authentication, it is now possible to use Windows integrated authentication.
There is also the possibility to use windows authentication (SQL MI only). Windows authentication is additionally Single-Sign On authentication option for Azure AD users.

3.3 Information protection and customer data
There are two possibilities of data encryption: Encryption in transit (using TLS) or Encryption at Rest (using service managed key and customer managed key).

Migration

There are several tools that can be used to migrate data from an instance to SQL MI. One possibility to migrate data to SQL MI is using the service link feature. This solution is the only one that truly allows online data migration. The scenario is as follows.
1) Replicates data and offload analytics to SQL MI. Use modern Azure Services while remain running on SQL MI
2) Offload R/O application workload to SQL MI
3) Migrate when ready. This last step is particularly important. This means that if the data in your source database may have problems in SQL MI you do not have to migrate all your data.

Conclusion

I continued to improve my knowledge and learned a lot about SQL-managed instances, and this feature will be interesting when some limitations are addressed. According to some SQL Bits attendees, this feature is used a lot in test environments, and I realized it has a remarkable potential to be used in production.
I would like to explore this topic further and create more blogs on SQL managed instance in the future as it is such a fascinating topic.