Hi Y'll
I'm currently hosting in our cloud for customers. Our application uses 5 databases pr customer it's a POS application and we have to comply with gov. regulations so there are constrains.
we decided to use a named instance on the SQL2014 server per customer, thinking about separation safety upgrading and recovery. Working with this model for about 2 years now we are getting up to 40+ hosted installations (instances).
The Sql server 2014 is on a virtualized W2012R2 server . We don't have any problems with performance. We use VEEAM for our backups. basically I'm happy today but we keep expanding so I need to plan for the future.
So some questions to you experts:
- Any comments or thoughts about the model named instances V loads of databases in on instance?
- I want to start clustering and I'm very happy with hyper-V and would like to use that, thought's
- Failover or failover with AG ??
- Shared storage on a NAS with different RAID sets?
- Failover the Hyper-V servers versus just the SQL instances?
- In clustering they talk about instances, do they mean the same named instances that have a limit of 50 pr enterprise server.
Looking forward to you comment's and yes I'm prepared for some corrections from Y'll I'm not new to this but I know there's a lot to learn and what's right today might kill you in the future.
R
Pretty sure @TaraKizer dslikes the many-instances model, but I can't remember why, but I expect she will have some good answer for you 
1 Like
Resource management will be a big problem with multiple instances.
1 Like
Thx.
Getting more and more aware.
I do dislike instance stacking! It makes it hard to manage memory, CPU, etc. What happens if a database is designed horribly and has tons of scans which lead to high CPU? Why should another instance suffer? Sure you can resource govern things, but that gets complicated. With most companies using virtual machines these days, just do the one VM to one SQL instance model and be done with it.
3 Likes
Thanks for your response. I've been getting the picture that what I set out on was the wrong track. But we live we learn. I'm going to move all this now.
The thought is as follows.
I got 35 customers and growing, wich are sepperate company's taht use our POS system (we write it our selv's have done for over 30 years 
)
Each customer has 5 databases. we have different release versjons.
So following the advice given here i start clustering, using 2 physical servers and a shared storage with different raid types (for data and logs)
I virtualize on each host 2 SQL servers that have each just one instance (instance per server to seperate our release levels that)
So SQLSRV01 on host 1 is clustered with SQLSRV02 on host 2 and sharing storage.
and SQLSRV03 on host 1 is clustered with SQLSRV04 on host 2 and sharing storage.
That way I've redundance
Multiple instances do require a bit more work to manage - but they solve a lot of issues and cost a lot less than individual systems. For each VM you spin up for a single instance of SQL Server - you have to license that instance.
For each instance you spin up in a multi-instance server you only have to license the server one time - and then have as many instances as you want and the system can support.
If you do have a runaway instance then you have options to control that one instance. Move it to its own dedicated server, restrict it to a specific set of CPUs, restrict the memory (should already be done), etc...
For any business critical system I would not put that in a shared environment - it should have dedicated hardware anyways. For department level applications with just a few users or multi-tenant situations where you know the performance profiles - multiple instances work well and reduce overall costs.
You can also co-locate databases in a single instance but you still have the same issues as multi-instance. If you have one user in one database in a shared environment that runs a horribly bad query - then all users can and will be affected...and even more negatively consider plan cache and buffer cache are shared across databases.
As soon as you start having systems with different version requirements - you are going to run into management issues if they are all on the same SQL Server instance. If your new version requires SQL Server 2014 - but your older versions are not compatible then you have to upgrade every customer at the same time...not really a good option.
1 Like
Who is paying for all of this hosting on the cloud?
I see there are different thoughts about what's right and wrong and a lot depends on preferences and specifics of use.
like I said in the earlier piece the databases are in a controlled environment and nobody except our developers are running queries. All reports and writes are controlled by our programs and user interfaces.
It's just that I'm reaching the 50 instances sealing soon and I have to move to a cluster that first priority.
My first thought is to create this.
So following the advice given here i start clustering, using 2 physical servers and a shared storage with different raid types (for data and logs)
I virtualize on each host 2 SQL servers that have each just one instance (instance per server to seperate our release levels that)
So SQLSRV01 on host 1 is clustered with SQLSRV02 on host 2 and sharing storage.
and SQLSRV03 on host 1 is clustered with SQLSRV04 on host 2 and sharing storage.
That way I've redundancy and freedom.
If anybody see any big mistakes or dis advantages plz feel free to point them out
Rene
Thanks all for sharing your thoughts
If I am reading your response correctly - you are stating you have to move to a cluster because you are reaching the 50 instance limitation?
If so - be very careful with that assumption because SQL Server only supports 25 instances in an FCI cluster. It will support up to 50 instances in an AOAG cluster.
Clustering is not going to help you get over 50 instances...a separate licensed system where you can install more instances is going to be required.
In your situation - moving to a single instance containing all customer databases is a possibility, but that really depends on how each database is set up. If only one of the 5 databases has actual customer data - and the other 4 are just lookup type databases then you could have a single set of lookup databases for all customers. If each database contains actual customer data - then you have to replicate every database...
In a single instance scenario - database names become an issue because you have to have separate names for every customer - whereas separate instances don't change the database names just the instance names. To manage that in a single instance means you have to modify all code to use synonyms (if not already done - this is recommended anyways) or views (not ideal because you have to edit the views for each customer) just to get to those other databases.
There is a lot more to consider than just moving the databases...
1 Like
Thank you.
This kind of confirms what i've been researching. One of my worry's was the 50 instances limit and needing to splitt it up and start with 6 or 8 servers and grow from there.
Our database model has luckily already taken in to account that we can have or need to have all databases in one instance.
Our database design forces us to have seperate instance's for hour database (software) versions.
Thanks for your insight .
Rene