Multi-Tenancy Design

Question

Multi-Tenancy Design

David-438049

SSC Enthusiast

Points: 189
More actions
January 13, 2015 at 9:34 am

#294202

Our Service Architecture group has developed a Multi-Tenancy design where essentially there is one SQL instance per application (which is approximately 150). Based on their design, each instance would host at the most 3 database. Most of these db's are small in size (< 500mb). What are your thoughts of this design?
TIA,
David W.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

JohnMarks Ten Centuries Points: 1253 More actions · Answer 1

So are you talking about having 150 instances of SQL Server? How many servers do you have available to host all those instances?

Are there are requirements that a tenant's data requires physical separation? Could multiple tenants share the same database?

David-438049 SSC Enthusiast Points: 189 More actions · Answer 2

Yes, I am talking about having 150 instances of SQL Server. Currently we have ~ 45 servers to support all the databases. We are using VM's and have enough license's to support the proposed hierarchy. My thoughts are that this is overkill and could pose possible management issues.

JohnMarks Ten Centuries Points: 1253 More actions · Answer 3

Personally I am not a fan of using multiple instances on production machines. I find management and troubleshooting machines with multiple instances a lot more work than single instance machines.

If each tenant does not need physical separation of data and you don't plan on making tenant specific schema changes in the database then I would think a multi-tenant three database design might work ok. It certainly would be less management work. However if you need to bill on resource usage by tenant then this route would not work well. Another potential problem with the single design is if a few tenants drive a lot of traffic then they might impact the performance of the other tenants (use of the resource governor can help here). Physically separating tenants allows you to mitigate the impact of a larger tenant.

I don't know what up time requirements you may have but if you are looking at 99%+ you will need mirroring/clustering/availability groups which will reduce your 45+ available servers in half. I don't know how that will affect your workload/design. Also in my experience the number of tenants rarely stops at what was expected so you might plan for 150 now but what happens when you get to 200, 300 etc. How will the multi-instance set up scale?

David-438049 SSC Enthusiast Points: 189 More actions · Answer 4

Thanks John for your remarks. All good comments. The proposed hierarchy would be 150 VM's with one instance on each VM. So, the issue with multiple instances on one VM is a moot point. My opinion is that it is overkill, but the points you regarding resource control is exactly why they are proposing this methodology. Also, when we migrate to a specific application (new and/or updated DML/DDL) the rick is mitigated to just that one application.

Thanks!