SQL Server Migrations & Upgrades

[Pros & Cons] Azure SQL Managed Instances

Updated
7 min read
Written by
Mark Varnas
Azure SQL managed instance

As of this writing this, we don’t have a single client here at Red9 where we are using SQL Managed Instances. We do have some clients that have Azure SQL though.

Pros and cons

Here are a few notes I took at SQLSaturday Atlanta about Azure SQL Managed Instances.

Currently there are two managed DB options in Azure:

  1. Azure SQL, and
  2. Azure Managed SQL Instance

Azure SQL Managed Instance vs single database

Azure SQL Database

Pros

  • Simple and easy
  • Most things are managed like backups. This is a problem though, if you want to log ship, migrate out, or do something more advanced – this gets tricky

Cons

  • Backups are fully managed
  • SQL Server Agents don’t exist… ummh…ok…
  • No cross-DB communication
  • Can’t power down (so you can’t stop billing)
  • Can’t restore native backups
  • To import into Azure SQL – you have to use Tasks -> Export -> script out all objects

Use case that works well

Azure SQL Managed Instance – PROS & CONS

Pros

  • Provides the best of both of the past offerings
  • Infrastructure managed by Azure (not sure this is good, because now I don’t know if Azure set Lock pages in memory, maintenance volume GPOs on, or power savings are set to Balanced mode. All I can do is to just trust them. And I am not there… yet)
  • No more Win or SQL patching – nice! But it doesn’t seem like I can choose WHEN patching happens, and I have heard SQL going down mid-business day because Azure autodecided its time to patch your instance up
  • Supports multiple DBs
  • Azure SQL Managed Instance cross-database query  (cross-DB communication supported)
  • SQL Agent available
  • Lift & shift migrations possible
  • Backups managed by Azure
  • COPY_ONLY backups can be run by ad hoc
  • 98% of features there (not convinced its really 98%, or closer to like 85%). Also, the missing 2% needs to be identified, as one missing item could quickly become a deal breaker
  • June/July 2018 when Managed Instance should become available
  • If you have core licenses already, you can migrate them (kind of) to Azure
  • If you have MS contact/manager, you can let them know and he can help expedite this
  • Azure SQL Managed Instance High Availability and Disaster recovery (many options available)

Cons

  • Some features not available – Azure SQL Managed Instance limitations
  • Limited to SQL Authentication. No Active Directory. This one is a big one! Workarounds?
  • Currently in preview – only in General Purpose tier available. As of May 19, 2018, in limited public preview, so you can’t get this yet
  • Can’t shut this down (so billing keeps on ticking)
  • In memory file groups must be empty in preview
  • Initial build currently takes ~24h – additional builds in the same subnet takes 3min. Not super bad, compared to what usually takes to get a new server provisioned
  • HA is built in and cannot be controlled
  • Password has to be min 16 characters long 🙂 – Microsoft should make this 25, because at this point the number of people typing passwords in is decreasing, so why not make max a default?

Azure SQL Managed Instances – requirements

  • Azure VNet – I like this one. Makes me want to do this everywhere to just separate traffic from Netflix vs. stuff I care about
  • Subnet and routing list
  • Additional subnet for Azure VM connecting to Managed Instance

How do you migrate to Azure SQL Managed Instances?

Here are a few options:

  • Restore from native backup written to Azure Blob Storage
  • Azure Migrate available for VMWare environments
  • MS plans to make Azure Migrate available for on-premises and Hyper-V environments

My questions

  1. Noisy neighbors – we ran into weird performance problems in Amazon AWS. You buy an EC2 server and on your performance fluctuates a daily basis. But nothing has changed on our end. Why?

After I got AWS support cornered/escalated, they go – “Well it can be the neighbors on the same physical server.” Umhhh… yeah, not cool. I get that I am on a shared server, but I also get that I bought my CPU cores, RAM, etc. – so, no, I don’t really care about what my neighbors’ needs may be.

It’s just the same as if you rented an apartment, one day you come back and find a couple people inside, because the apartment complex decided to Airbnb your space while you went to get the mall. NOT COOL.

There will be multiple Managed Instances on the same physical server. So having a high fence around my server to prevent my resources being sucked out by other knuckleheads is important.

I would like to see more data on how Microsoft Azure will handle this noisy neighbors issue. They probably thought this through, but I need data.

  1. What does storage/disk IO throughput look like? I need more information about Azure SQL Managed Instance performance.
  1. Does it make sense to keep splitting DB into multiple filegroups, files to get more throughput? Does it make sense to have multiple DB files in blob storage and how does that affect performance?
  1. Emailing out. How do we handle emailing out from the server? I like to get some reports/alerts, so yeah, I am going to need to email out.
  1. Do I have any access into OS? I think the answer is No, but I would still like to ask. Can I at least see OS in read-only?
  1. What other Azure resources are needed to launch this for small setup?
  1. If I need to schedule PowerShell script to run, can I do that? And no, not SQL PS, but real full featured PowerShell…

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide 
operational peace of mind

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

1 thought on “[Pros & Cons] Azure SQL Managed Instances”

  1. Thanks for nice comparison.
    I will try to answer some questions related to managed instance.
    Instead of AD there are AAD users (same as in azure sql database). Not identical to AD logins (i.e. scoped only to database), but they are some kind of replacement.
    Noisy neighbors don’t exist. If you want 16 cores you will always get them. In GP IO depends on Azure Premium disk performance, it might be better to have less bigger files because io perf is proportional to size of file in GP,
    db_mail is available.
    Also, pros of Azure SQ DB is that it guarantees performance per each individual database (for example if you want 8 cores for DB7 you will always get 8 cores.) In Managed Instance, you are giving cores to entire instance, so databases are competing (same as on sql server on-premises)

    Also, note that we are keep working on the instance and improving some features.

    Reply

Leave a Comment

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials