Several SharePoint farms using the same clustered SQL Server

Question

In our current infrastructure we have several clients that have their own SharePoint farm hosted on dedicated WFE's and a dedicated SQL Server instance.

We are thinking of moving our hosting to another provider who have proposed an architecture whereby all the SharePoint Databases for our SharePoint Farms reside on a single instance of clustered SQL Server.

At the moment we have 15+ SharePoint farms and this number will grow rapidly in the near future. We envisage having 100's of clients.

For reasons specific to the nature of our application we have concluded that it is not feasible for us to have more than one client on the same WFE so each client will always have their own dedicated WFE(s).

The question I would like to ask is that is it feasible (in a production environment) to have several or 100's of SharePoint Farms to utilise a single instance of SQL server no matter how powerful it (the box) is? Would like to hear from anyone who has seen things implemented in this way and what their experience was?

I have concerns over performance. In addition to this there is the obvious risk of having all your clients depending on a single instance of SQL Server.

I will be grateful if you can advise on the pro's and cons and based on that your opinion on this approach. Does Microsoft have anything to say on this? I have researched this a bit and have not found anything.

Thanks for your help in advance.

Answer 1

It is possible to host multiple SharePoint farms within one SQL instance, but there are a few things to keep in mind.

1. Unless you already used a shortened SQL alias in creating your existing farms, you will need to use an alias of the old [Server][Instance] name to allow the existing farms to communicate with the new SQL instance using the new [Server][Instance] name. Or rebuild the existing farms.
2. The easiest way to keep track which databases goes with which SharePoint farm will be to use a naming convention for the databases. (You might have already built your farms this way, not enough information to determine.)
3. If you allow customers access to their own Central Admin sites and they create databases from there, the default name for content databases will always start with "WSS" and service applications will always start with terms specific to that service application if those are created via "next-next-finish". The only differentiation will be via the GUIDs at the end of database name.
4. When using a single SQL instance, the security context for all of them could be interesting. Granting the farm account from one farm "sa" can open you up to issues since all of your client databases are now in a single SQL security model.

As to best practice guidance from Microsoft, I'm not sure it's on paper anywhere in regards to how many farms can share a single SQL instance. You might want to look at SQL best practices for number of databases and I/O though to ensure the backend will tolerate what the current plan is.