Strategy for Deploying Azure Managed Instances

David C 191 Reputation points
2021-11-10T19:36:08.327+00:00

Hi. My team supports multiple standalone SQL servers and AlwaysOn clusters to host our customer DBs. Our on-prem standalone instances are typically dedicated to DB(s) for one customer app. Our on-prem clusters host multiple SQL instances. Clustered instances each host one or more availability groups. Some of the clustered instance are shared by multiple unrelated application DBs, and some are dedicated to specific application DBs.

What strategy do you use to deploy managed instances in your environment? I mean, how do you distribute multiple DBs to MIs? Let's assume initially a single subnet with max IP address space (which I think is 256 minus 5.) Also say that you support DBs for multiple unrelated client apps. Do you share one MI for multiple unrelated DBs? Do you dedicate one MI for each app DB? I guess, bottom line is how do you decide to deploy a new MI?

As far as architecting the subnets, do you deploy:

  • One MI in one subnet (in one vnet)?
  • Multiple MIs in one subnet (in one vnet)?
  • Multiple MIs in multiple subnets (in one vnet)?
  • Multiple vnets? (We're not likely to get allocated multiple vnets.)

We're aware of the MS articles that give calculation for subnet IP address space. However it is the deployment strategy I'm asking about.

Azure SQL Database
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2021-11-10T22:38:33.267+00:00

    The following are two deployment models:

    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.

    If you are going with the Shared Deployment model, try to balance write-intensive databases and read-intensive databases. Do not add to the mix those databases that are affected by deadlocks/blocking.

    To avoid CPU spikes make you eradicate missing indexes and establish index and statistics maintenance plans. Managed instances are always consuming all RAM as the max memory setting is not available.

    Very important, before migrating databases is to do the experiment explained here on your current SQL Server instance(s) to check what are the instance log limits that the current workload have and determine if that workload can run on the Managed Instance with the same performance.