Pros and Cons
Here are few notes I took at SQLSaturday Atlanta about Azure SQL Managed Instances.
Today there are two managed db options in Azure:
- Azure SQL
- and Azure Managed SQL Instance
Azure SQL Managed Instance vs Single database
Azure SQL database
Pros:
– Simple, easy
– most things are managed like backups, but which may be a problem if you want to log ship, migrate out, or do something more advanced.
Cons:
– backups fully managed
– sql agent dont exists
– no cross db communication
– cant power down (so you cant stop billing)
– cant restore native backups
– to import into Azure SQL – you have to use Tasks -> Export -> script out all objects
Use case that works well:
– when building new app
– limitations make migrating existing apps close to impossible (that means no big clients will go here for existing heavy dbs)
Azure SQL Managed Instance
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)
– no more Win or SQL patching – thats good, but it doesnt seem like I can chose when this auto patching happens and I have heard SQL going down in mid business day because Azure figured its time to patch yours 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%)
– June/July 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. Yeah, this one is big one. Don’t remember right this sec where this will be a problem, but I am pretty sure this can be pretty big issue. Or maybe there are work around all of them now?
– currently in preview – only in General Purpose tier available. as of May 19, 2018, in limited public preview, so you cant get this yet
– cant 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 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 number of people typing passwords in is decreasing, so why not make max a default
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
Migrate to Azure SQL Managed instance options:
– restore from native backup written to Azure Blob Storeage
– Azure Migrate available for VMWare environments
– MS plans to make Azure Migrate available for on-prem and Hyper-V environments
Questions I have:
1. Noisy neighbors – we ran into weird performance problems in Amazon AWS. You buy an EC2 server and on daily basis your performance fluctuates. But nothing changed on our end. Why?
After I get AWS support cornered/escalated, they go – “well it can be what else is going on that physical server and what everyone else is doing.” Umhhh… yeah, not cool. I get I on shared box, but I also get that I bought my CPU cores, RAM, etc – don’t care about what virtual neighbors need.
It just same as if you rented an apartment, one day you come back and find couple people inside, because complex decide to airbnb your space while you went to the mall.
I bought my space – and I don’t want anyone there. Doesn’t sound nice, but that’s exactly what I want on the server.
Anyway, point is this that there will be multiple managed instances on 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. They probably thought this through, but I need data.
2. What does storage/disk IO throughput look like? Need more information about Azure SQL Managed Instance performance.
3. 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?
4. Emailing out. How do we handle emailing out from server? I like to get some reports/alerts, so yeah, I am going to need to email out.
5. Do I have any access into OS? I think the answer is No, but still would like to ask. Can I see read only?
6. What other Azure resources are needed to launch this for small setup?
7. If I need to schedule PowerShell script to run, can I do that? And no, not SQL PS, but real full feature PowerShell…
Agree? Disagree? Comment below.
2 Responses
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.
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.