I am trying to plan out an ideal environment to support 5-10 clients in Sharepoint 2010. I have decided to create a farm for each client but I am not sure how to deploy the SQL server architecture. I have been reading about the pros and cons of instancing and would like to know how some of you have attacked this issue (instanced or not). Currently I support a single client in a non clustered SQL infrastructure with a single server managing all databases. I know this is not ideal to support 5-10 clients and am concerned I might get questioned about separation of databases per farm and if this is even feasible. On top of that most clients want a 3 farm setup (test, stage, production).

What are the SQL database best practices when it comes to supporting multiple clients (multitenancy) in a traditional hosting scenario?

2 Answers 2


I have not setup a multi-tenancy-multi-farm setup like this, but I would attack it with multi-sql-instances. This allows each farm to be on it's own SQL server (or at least it will think it's own it's own SQL server). You can use unique permissions per instance to ensure each farm is secure and separate.



There are quite a few ways to address this beyond just SQL backend. If you need to keep full separation, then you need dedicated SQL instance on dedicated server. Otherwise based on your requirements you can start decreasing the resources.

With SharePoint you can restrict customers to dedicated site collections and provision each site collection to a dedicated database. There is some work you have to do to limit people picker, and search results, but it may be enough depending on what you need. And because they are in dedicated databases, those content databases can be provisioned across SQL instances if further separation is required. This does not separate shared data and services like Search, Metadata, etc.

SharePoint does provide multitenant support directly.. You can dedicate content databases to clients, and service databases are partitioned and each scale out as required. This, however, is much more complicated to deploy, configure, and manage so you need to do a lot of reading on the subject and understand the pro's / cons. Start here, but review everything he's posted http://www.harbar.net/articles/sp2013mt.aspx. Again, depending on your actual separation requirements will determine how far you have to take this (single SQL, multi instance, dedicated server, etc).

It may be worthwhile to investigate re-selling a hosted solution from say Rackspace, where they manage the multi-tenancy intricacies.

  • Thanks for the response. I guess this is not as cut and dry as I thought. I wanted to go this route but then I read about everything hitting the tempdb and the performance hit and that is what prompted me to ask best practice. I don't want to deploy the SQL infrastructure and realize later that it will be too big a hit to performance and have to move all the dbs for each client to their own dedicated server as that would be labor intensive. 10 SQL server on top of moving 3 farms per client to each. I'm sure you understand why I want to get this right.
    – Vazarus
    Jan 2, 2013 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.