10 Key Questions Determining SharePoint SQL Server Count

One of the tough questions you will ask yourself when building out the SQL environment for SharePoint for your enterprise is what do you need?  Those determining the license may be asked this question far before the SharePoint consultants are engaged.  These questions apply to both SharePoint 2003, 2007, and 2010.

I see this as a set of questions rather than a magical formula that could be added into a spreadsheet.  Determining whether these servers are reallocated existing, virtual or physical is another conversation.

1. Organizations and Organizational Politics – Will more than 1 IT organization be managing their own SharePoint environment?  Frequently one departmental IT group will setup their own environment separate from another.  As an example, HR IT may setup their own WCM publishing portal environment and do their own deployment, while central IT does an enterprise portal, and finance sets up a third environment focused on calculation.  You’ve gone from 1 to 2 to 3 complete separate farms.  While you could consolidate into a common SQL environment, politics often dictate end to end farms be managed by separate groups.  While not a best practice, I’ve seen this often when combined with unique requirements that require custom development resources.

2. Pre-production – Development/Test/Stage – Will you have preproduction validation and/a development team or development resources and test resources working with this farm? Even if you don’t you should plan for a second for testing service packs and change control. Definitely when an organizational development team is setup, they’ll want a dev environment.  Sure you might have a dev box for each developer, there needs to be a common environment where the code comes together.  Each of these environments will require a SQL environment.  SQL express might be the choice for the dev, but for consistency you’ll find most IT departments will drive some level of consistency between dev, test and production.  Staging itself in best practices mimics the production environment. 

Add a second for sure for validation, but more based on development process and change control requirements.

3. High Availability – What are your availability requirements for uptime?  Common solutions to higher availability is clustering, log shipping, and mirroring.  Alone only clustering can provide higher uptime without requiring additional human intervention or scripting solution.

4. Disaster Recovery – What is your recovery point objective and your recovery time objective?  Don’t make the mistake that high availability requirements address your disaster recovery.  Often the simple backup requirements or a high availability solution does NOT address offsite recovery and especially those with strict recovery time requirements less than 24 hours, but realistically 72 hours.  How long does it take to acquire hardware if your datacenter is damaged one way or another?  Plan on a secondary farm if you require the entire farm be fault tolerant from a disaster recovery perspective.  It’s true you may not need 2 redundant clustered SQL servers in a fail over datacenter, but I’ve definitely seen that.

5. Dedicated Application or Isolation – Will you dedicate a separate farm to collaboration vs. your portal? While you can put your my sites, your collaboration, and your portal on the same farm, I find it common for enterprises to put the enterprise portal on it’s own dedicated hardware.  The collaboration and my sites may be separate web applications or fully separate farms.  Dedicating SQL servers vs. keeping these common is definitely another question.  They can be consolidated and the footprint of read vs. write may focus the dedication to drive vs. entirely different servers.  Indexing requirements may push out the number of SQL servers as the environment grows.  It is the ultimate isolation to put these on different servers which would help to provide top to bottom separate performance.  Coming up with a hosting model for SQL to support consolidation.

6. Performance – While performance is often addressed by bigger hardware, more CPU, more RAM, the strategy of scaling out vs. up can often times provide better memory and process isolation than continuing to add memory and CPU.  You have to figure out your economies of scale around big iron vs. additional images or additional blades for example.

7. Locations – Will you be deploying more farms in more than one location?  Plan on putting SQL servers in each location.  Three distributed datacenters equals at least 3 SQL deployments, 3 servers at a minimum.

8. Service Definition Branching – Application Hosting (Customization) vs. Out of box (Vanilla) – Do you have hosting plans for custom hosting vs. out of the box or vanilla farms? As companies determine how to divide up their SharePoint farms, some will choose for the SQL storage to be divided up when those driving the application with additional database requirements such as adding additional databases, or adding reporting

9.  TB Storage footprint – How many Terra bytes do you plan to support?  Plan for 5TB per server.  While that’s an easy answer, I’ve definitely recommended 2 TB and 1 TB per SQL server based on the operational levels and backup types.  I did a whole blog post “How many SQL servers for my X TBs” on this one question a while back.  It’s often the question people focus on.

10. Database Count – How many databases are you planning to support? While you may have heard 100 databases per SQL instance in the past, with 64 bit SQL server I haven’t heard of new numbers, but database mirroring and count when combined do push out the number of servers based on somewhere around 80 mirrored databases (or 10 or 30 depending on which doc you read), but ultimately it comes down to memory in the server as each mirrored database requires a dedicated.