One big server with multiple instances, or multiple smaller servers...
-
We're currently on SQL 2019 and will likely be looking to migrate to a newer version in a year or so (definitely before Extended Support runs out,) and I'm looking to try to save some pennies for the org. Currently, we host SQL instances for several different orgs and their applications (so multiple, often unrelated, databases per instance.) The servers are VMs hosted in Azure and while there's some push to "modernize" the applications to be more "cloud-based," the developers largely aren't putting a lot of urgency to the task AND quite a few of them have processes that from my research, will need a LOT of re-architecting to work in a SaaS environment.
So I'm looking at potentially going from one server per org to one big server and multiple instances (to ensure the security stays similar to how it is now.) Aside from some of the obvious problems (if the server goes down, everyone hosted on it is down, instead of just one org; patching will be a bit more work coordinating reboots with multiple orgs; possible resource contention with storage, RAM, and CPU (although currently their apps are very CPU-light;)) is there anything else that would argue against multiple instances?
Somehow I feel like I'm overlooking an obvious reason to now go multi-instance...
-
Make sure all your consumers are also in the cloud ( to avoid traffic costs ! )
Check bundle prices / volume prices, maybe multiple SQLVMs come better than installing multiple SQL on a single (large) VM.
+ SQLVMs are better suited to handle sql server loads ( local ssd for tempdb on non-persisted D-drive )
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Would also look at your IO patterns and see what you need, as putting everything on one VM, you're going to be limited A LOT by Azure's forced QoS policies per VM SKU and Disk SKU, where it will be better off putting things on separate VM's to mitigate QoS limitations.
-
what Ant said - while you can go with multiple vm's with small footprint those will also reduce IOPS considerably - so a higher vm (Azure constrained cpu for example (32/16) 256 GB ram) ) will perform better than 4 vm's 4cpu 32gb ram. the first you can easily put 4-8 instances on it and would get good IOPS out of it.
and try and move more db's into a single instance if possible - security wise should be easy enough, main issue is if database names are the same.
- Johan Bijnens wrote:
Make sure all your consumers are also in the cloud ( to avoid traffic costs ! )
Check bundle prices / volume prices, maybe multiple SQLVMs come better than installing multiple SQL on a single (large) VM. + SQLVMs are better suited to handle sql server loads ( local ssd for tempdb on non-persisted D-drive )
We're in a bit of a "weird" Azure world, in that we don't directly manage our subscription. So we also don't get bundle or volume discounts. The wonderful world of AzureGov...
frederico_fonseca wrote:what Ant said - while you can go with multiple vm's with small footprint those will also reduce IOPS considerably - so a higher vm (Azure constrained cpu for example (32/16) 256 GB ram) ) will perform better than 4 vm's 4cpu 32gb ram. the first you can easily put 4-8 instances on it and would get good IOPS out of it.
and try and move more db's into a single instance if possible - security wise should be easy enough, main issue is if database names are the same.
Right now we're on servers with acceptable IOPS for the databases and the disks are in a high enough performance tier that technically, we exceed the available IOPS for the VM type. But our workloads are more "bursty" and very read-heavy so typically we never hit the IOPS limit. While there's some data load tasks, those run overnight when there's very little to no activity, so we've not had problems with that, either.
So after a discussion just now with my Team Lead about some of the restrictions we operate under, I'd at least be required, if we went to one server to rule them all, to have multiple instances to maintain logical separation of some of the apps from other apps.
So I can still look at one big server and multiple SQL instances, it just comes down to, is it going to be worth the time and effort (arguably, for me, not much,) and will it generate some cost savings for us (at least on the SQL licensing side, it would, if I can consolidate down enough servers)
-
It might be. I am not a huge fan of multiple instances, especially with trying to get RAM right, but if 4 instances vs 4 VMs is affected by licensing, that might be worth looking at.
My main concern (outside of resources) is upgrades, where there may be shared components. Not many, but it could be an issue. You also might have confusion from admins that connect to multiple instance and pick the wrong one.
I prefer (for prod) to go to VMs, even if they were on the same host.
-
Just want to add that even with 1 big server, I still like to have a minimum of 2. That way you get failover. If the server/VM needs a reboot, failover to secondary, reboot and you reduce downtime. Plus with upgrades you upgrade secondary, failover and check if things come up successfully. Do all your tests, then upgrade primary and fail back.
But that's just me.
I do agree with Steve though - managing RAM with multiple instances (especially once you toss SSRS and SSIS in there) can get messy. mind you, even with 1 instance you can still get messy with SSIS/SSRS memory as those run outside of SQL Server memory.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply