A topic that came up several times at the SharePoint Fest conference related to SharePoint’s database tier—SQL Server of course.
Here are some fundamental tips to ensure it's protected, portable, and peppy.
Protected: Set Quotas On SQL Server Storage
If you’re not using quotas, you’re exposing your SQL Server storage to a malicious, or, (more often), accidental scenario in which a user uploads a ridiculous amount of data to a My Site or Team Site. This is easier than ever, now that SkyDrive is in play.
If a user is syncing to SkyDrive Pro (My Site document library) to a local computer, and accidentally drops an iTunes collection into it, I hope your SQL Server storage is ready for the “hit.”
Use quotas! On every site collection! Even if you set a high quota (say, 200GB or 500GB), set something that protects your storage tier from something worse than that.
Portable: Ensure Portability Via a SQL Server Alias
Aliases are the key, here. A SQL Server “alias” is a fake name for the database server. You define the alias in the SQL Native Client that runs on all SharePoint servers (cliconfg.exe). Open CliConfg and configure an alias for TCP/IP. Repeat and define the alias on each and every SharePoint server.
Then use that alias—that “fake name”—throughout your SharePoint configuration when you are asked to specify a database server. Have a separate alias to refer to your failover server, if you’re using mirroring to fail over SharePoint.
When the day comes to move your databases to another SQL Server, or to use clustering or another technology that effectively changes the server name, it’s no big deal. Simply redefine the alias on each SharePoint server to point to the new server name. One action per server.
Much easier than digging through your SharePoint configuration and replacing the server name on all web applications and service applications.
Peppy: Size Databases For Performance
Many SharePoint admins haven’t focused on the initial size and autogrowth rate of content databases. The bottom line is that these settings are generally terrible for SharePoint purposes—they’re appropriate for “normal” SQL applications, but not for a content-heavy service like SharePoint.
Your database “initial size” should reflect a reasonable understanding of how much storage you’ll need for the content in a database for the near future—remembering that a content database can host more than one site collection, so account for all of them.
The “autogrowth rate” is used when the database hits the size, and must expand. You want to configure the autogrowth rate so that when the database does grow, it doesn’t have to do it again right away. You have to decide what’s “right” based on your usage. I’d suggest using a fixed size (rather than a percentage growth rate) that is calculated as a percentage of the initial size.
So, for a collaboration (team site) scenario, let’s assume you’re expecting a database to need 50GB this year, but that it might grow as large as 100GB. You’d have to think about your specific usage pattern and storage model, but you might set the initial size to 50GB, and set the growth rate to 10GB (20% of the initial size).
When the size and growth rate of SharePoint databases are left at their defaults, SQL performance can be seriously penalized. Conceptually, the server is so busy just growing databases by very small increments (1MB is the default growth rate) that it can’t do anything else. Plus, the databases themselves get fragmented on the file system, further degrading performance.
You can learn more about each of the settings I mentioned here on TechNet--for example, see also "Best Practices for SQL Server in a SharePoint Server Farm" and "Considerations for the 'autogrow' and 'autoshrink' settings in SQL Server"--but I wanted to keep this short and sweet, as a reminder to be sure your storage tier is protected, portable, and peppy.