SQL 2008 r2 architecture for multiple clients(farms)

Question

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?

Answer 1

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.

HTH

Answer 2

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.