SQL Managed Instance is one of the latest addition to the Azure SQL Database family of PaaS offering in Azure. Azure SQL Database, besides representing the singleton SQL databases in Azure, also represents the family of managed relational databases services in Azure.
If you have not heard of Azure SQL Managed Instance before, I would highly recommend reading the documentation for Managed Instance. It has been in huge demand in enterprise world since it hit public preview in March. As part of my role, I have been working with number of Enterprise customers to assist them in architecting, planning and piloting managed instance for their production deployments.
SQL Managed Instance due to its application compatibility with on-premises SQL releases, secure hybrid channel (VNet isolation), Azure hybrid benefits for SQL Server with independent compute and storage billing and flexibility makes it unanimous choice for enterprise customers looking to migrate and modernize their SQL Server instances to Azure. The cherry on the cake is the minimal downtime migration support added by Azure Data migration service making it possible to migrate mission critical databases to SQL MI in minutes.
When I present SQL MI to customers, one of the frequent questions I get is, does that mean Azure SQL Database (singleton) services is going away and SQL MI will be replacing them. This is not true at all and SQL engineering team has no intent or motivation to kill Azure SQL DB. In fact, there are active investments to further uplift its scale and light up new features. Azure SQL DBs has its unique advantages and is extremely popular among developers, devops, BI analysts and ISVs who are looking to develop new cloud born applications in Azure. Some popular use-cases for Azure SQL DB is using it as hive metastore for big data solutions or as a caching/serving layer on top of Azure SQL Datawarehouse. With the modern microservices based design and architecture, developers really don’t need an SQL Instance and customers leverage the Azure ecosystem (elastic database queries, Azure functions, logic apps) as lego blocks to build their solutions.
SQL MI on the other hand is popular among Enterprise customers looking to lift and shift their existing applications designed on on-premises SQL Server instance to Azure. It is also popular among ISVs who do not wish to maintain multiple codebase for on-premises SQL Server and Azure SQL DB. As SQL MI provides the same T-SQL programmability surface area, it allows them to maintain single codebase for all deployments.
Enterprise DBaaS with SQL Managed Instance
For some of the enterprise customers I am engaged with, we are designing enterprise database as a service (DBaaS) solution with MI for any SQL database requirement within the organization. The decision of using SQL MI as enterprise DBaaS is easy owing to its app compat (application team & DBAs are happy), full secure VNet isolation suitable for hybrid deployments (Infosec guys are happy) and favorable business model (procurement team is happy).
Large enterprises usually have tiered database workload requirement with many tier 1 mission critical application requiring dedicated beefy servers while tier 2 and 3 applications which can be hosted in a shared environment. The following two deployment model are being considered in the design based on the access pattern, isolation, security, cost and efficient utilization of IT resources.
Shared Deployment Model (Multi-tenant Model) – In the shared deployment model, the database server and instances are shared by multiple applications. The applications is given access to the databaseschema and the scope of the access is limited to the application databases alone. The instance is owned and governed by IT teams. The shared multi-tenant model is cost efficient, allows central governance of resources. The shared deployment model can lack isolation and is vulnerable to noisy neighbor issues which can lead to sporadic performance issues. This makes it more suitable for tier 2 and 3 applications in the enterprise environment. For instance – A visitor checkin application can just reside in shared instances since the concurrency requirement is generally not too high. To achieve predictable performance, resource governance in SQL MI can be implemented to avoid runaway queries or single application hogging the database resources.
Dedicated Deployment Model (Single-tenant Model) – In the dedicated deployment model, the database server and instance are dedicated for the application. The application enjoys the full access of the dedicated resources and can thereby expect predictable performance provided other variables in the system remains unchanged. The DBAs still control the ownership and can set up role-based access (RBAC). The dedicated deployment provides predictable performance, easier ownership, internal billing chargeback, independent failover and combined with the elasticity of cloud allowing scale up and down as needed. This dedicated model is suited for Tier 1 and some tier 2 workloads. For small workloads, it can lead to inefficient utilization of server resources and may not be cost efficient.
VNet planning
The most important requirement to keep in mind for SQL MI is, it requires a subnet within the VNet dedicated for MI Instances only. A General purpose (GP) tier takes 1 IP address while Business Critical (BC) tier requires 3 IP address.
Depending on the application classification, we can decide the number and type of MI instances (GP or BC) to provision in the subscription. It is therefore important to reserve a subnet with sufficient IP addresses (16 to 256 address) reserved within the subnet. Ideally, you would want to keep MI Instances closer to your application region and in the same VNet. This requires us to reserve subnet in each of VNets in which plan to provision the MI Instances.
The above architecture requires you to reserve a subnet in each of the VNet. If your estimation on the number of MI instances required is inaccurate, it can lead to wastage of IP address or running out of IP address and requiring new subnets. In addition, It may not be practical to reserve a subnet in each of VNet where application resides in which case, we can leverage VNet peering. There is no latency or performance disadvantage of using VNet peering in local azure regions. Azure also supports global VNet peering across azure regions where the traffic between the region goes through the robust Microsoft backbone network. This allows following architecture for application or access for SQL MI instances across VNets.
Migration Planning
For migration, you can choose leverage tools like DMA for migration assessment and DEA for performance A/B testing but since most of the T-SQL surface area is supported there is not much planning required and the effort is similar to SQL Server migration exercise. However, that doesn’t discount the effort on testing which is highly recommended and should not be short circuited to avoid any surprises.
For lift and shift of databases to SQL MI, native backup/restore or Azure Data Migration service are preferred migration techniques. SQL Managed Instance doesn’t support SSAS and SSRS so those workloads would need to move to IaaS or PaaS (applicable for tabular models). System databases cannot be restored in SQL MI so all the logins, jobs would need to be scripted out and migrate to SQL MI similar to SQL Server side by side migration.
Tips (Lesson learned)
· In our pilots, we observed DBAs who had build custom scripts to manage and automate some of their tasks accessing the filesystem, network shares or using system variables and functions (SERVERNAME, SERVERPROPERTY) required some refactoring to adapt to MI but the application code just worked by changing the connection strings. Thankfully, all the T-SQL differences are documented which makes it easier for DBAs to know which code can potentially break in their scripts. Some of the functionalities like AlwaysOn, Mirroring, backup, restore are available out of the box so they do not need to worry about it. I have seen SQL Community has already started to adapt their scripts and solution for MI as well.
· SQL MI is one of the few services which supports full VNet isolation which makes it suitable for hybrid and distributed architectures including transaction replication, service broker talking to the on-premises assets without the risk of data being exposed to public internet during movement. This is very critical for enterprise who are looking to extend their datacenters with Azure. This is where understanding of VNets plays a key role when provisioning Managed Instance for your environment. The SQL Engineering team has done a great job documenting these requirements in this blog which are essential requirements before you provision Managed Instance.
· Like Azure SQL DB, SQL MI only supports UTC timezone so if you use Getdate in your application code and do not store datatime in UTC timezone, you will need to built a custom getdate function using AT TIMEZONE t-sql to return your preferred timezone.
· Always download and use latest version of SSMS when connecting to MI. SSMS is still catching up so if you encounter any errors for any operations (for instance backup/restore), try using T-SQL first since it might be a bug with SSMS.
· Although CREATE DATABASE doesn’t support file or filegroup parameters, ALTER DATABASE supports it. So modify your scripts to break CREATE DATABASE into CREATE DATABASE <dbname> followed by files parameters (SIZE, MAXSIZE etc).
· Although General Purpose tier doesn’t support memory optimized objects, it does create and support memory optimized filegroups. This allows you to restore a database with empty memory optimized filegroup to General purpose tier. As there is no support for deletion of memory optimized filegroup in SQL Server, this option allows you to migrate databases with empty memory optimized filegroups to MI.
· CLR code should be converted to binary to be able to migrate to SQL MI.
· Leverage sp_readmierrorlog developed by SQLCAT team to read the relevant error messages from the error log.
Parikshit Savjani,
Cloud Solution Architect (Data & AI), Microsoft