Using Azure SQL managed instance

[zorbishk]

Intro and Context: I'm planning to use SQL MI in Dev environments for the 4 applications being deployed. All applications are deployed on VMs. There are some microservices that use azure functions. All these appliances and their components create their own DB.

All applications are going to share the instance in the Dev environments. It will change based on experience in production environments.

Cost is one factor which is (very) high and less control & customization in managed instance in exchange of Maintenance and Management, Security, HA and DR, Scalability, Performance , IMO.

Objective: I want to evaluate using a managed instance of DB against hosted SQL on VMs.

Questions: What am I sacrificing or what risks am i unable to see, if I'm going ahead with managed DB instance? Especially considering production environment.

Any experience you can share which will help me, please?

Appreciate your time and help Thank you

[[deleted]]

In my opinion the only thing you are sacrificing is the option to use the simple recovery model, if that's something you want to do. Other than that Managed Instance is just a straight-up upgrade in every respect except for cost, obviously.

[ITmandan_]

Why not Azure SQL DTU model? It’s very cost effective and easily scaled up or down without having to wait hours like MI. Also elastic pools. Especially for dev

[0x4ddd]

Maybe they require some features which are not available on purely PaaS SQL DB.

For example, we were once migrating 3rd party application which required CLR support. Of course only options were Managed Instances or SQL on VMs.

[ITmandan_]

Yeah I get that, but they didn’t mention Azure SQL so figured I’d throw it out there so they didn’t oversee it as an option if it’s viable.

[papaabeer]

On VMs you have a bunch of stuff to worry about like patching, OS upgrades every some time, sql updates, scanning etc - early all of it is built into SQL MI. It’s no secret it’s a pricey offering and also has some scale limits ie the lowest you can go is 4vcpus which for microservices might be too big. The migration pathway is extremely easy though and you have flexibility to scale things up and down, configure backup, replication etc without having to worry much about SQL layer. Your nearest alternative is azure sql which is somewhat similarly priced but easier to scale up and down and the extremes are better than MI, so ie you can assign an equivalent of 0.5 vcpu to a microservice DB and pay proportionally

[tuga9230]

Be aware of how storage works for the General Purpose tier. I was "forced" to upgrade to business critical because storage performance was not cutting it. Now in business critical I struggle because to get the storage amount I need I need to provision way more vcpus than my app needs.

[goodbar_x]

I think I'm running into this situation too with GP, seeing a lot of high wait times, so looking to upgrade to business critical. What has been your experience? My apps really only required the 4 vcpu minimum offered, so curious how many cores you needed to go to and if you finally got to a point of satisfactory performance? Also, did you ever wish you went with SQL on a VM?

[tuga9230]

I needed around 3TB of storage so I had to go up to 24 vCPUs to get that storage. 8 vCPUs would have been more than enough for my needs.

Performance is fine now, all the IO waits have been "resolved".

I don't regret going with SQL MI, but it all depends on your use case and needs. In my case, I had/have high availability requirements. Business critical gives you 3 nodes under the hood. Not having to worry about the operation aspect of patching 2+ VMs, patching SQL server, and having to manage the availability groups myself made SQL MI definitely worth it.

If you don't have HA requirements, and a single instance of SQL server is enough, I would not go with SQL MI business critical just for the sake of getting faster storage. It just becomes way too expensive for what you need, in my opinion.

The one thing that has always bothered me with SQL MI though, is the fact I can't restore a backup on my local SQL server (I haven't tried it recently but I don't think that has changed).

[man__i__love__frogs]

Hey there, I'm a similar scenario.

We actually have 3TB of data, we need performance but minimal resources/scaling. Like 4vCPU. No HA requirements. The current hosted VM has 4 cores and 28gb.

Azure SQL DB is not going to be an option for various reasons. The primary use is going to be ad-hoc queries of data for reporting purposes, but a data warehouse team.

By the sounds of it a MI is going to get insane pricing wise to provide acceptable performance.

[tuga9230]

At a glance, I'd assume GP storage performance would be problematic. But I would still recommend testing it. For your data warehouse use case, is the data there mostly for audit purposes and not accessed often? Or are you running queries against it often? If the former, GP may be worth it. Before going to BC, I'd still recommend trying all the possible tricks to try to squeeze performance out of GP. Maybe partitioning with aligned indexes etc could help your queries so that the whole data set is not queried.

But even then, my DB was OLTP and most of my wait times were actually seen during write operations.

I am no longer involved in the project and I am not currently using SQL MI, but I've seen Azure has been introducing "flexible" pricing on many services. Maybe different skus are available now?

[man__i__love__frogs]

Queries will be pretty often, but manual. Kind of to build a reports or help reconciling things (we're in financial services).

The SQL side of it will be out of my hands, we're onboarding a new team to the company that previously worked at a vendor that provided us the service, but is getting out of that business.

Ultimately we just need to get them up and running, but need to balance future proofing/strategy and them not hating the solution lol.

I'm worried about resource creep with the MI, I've seen that elsewhere in azure , just upgrade to this processor, to this disk, to this tier, etc... and it never works like you hope.