Naming Convention for SharePoint Databases

Naming conventions are obviously important to SharePoint farms.  Having a naming convention drastically improves efficiency related to configuration tasks, troubleshooting, documentation, and knowledge transfer.  While I use naming conventions for various aspects of a SharePoint farm, this post explains a naming convention I use for SharePoint databases.

Here it is:
FarmAbbreviation_DatabaseCategory_Component_InstanceNumber_DatabaseType_InstanceNumber

Farm Abbreviation:  A consistent abbreviation used to prefix all objects in a farm including IIS Application Pool, IIS Web Site, IIS Web Site directory, and databases.
Database Category:  WebApp for Web Application and SvcApp for Service Application.
Component:  Name of the Web Application or name of the Service Application.
Instance Number:  Two digit, incremental number of component instance.
Database Type:  For Web Applications, this is ContentDB.  Some service applications use multiple databases so Database Type describes which one it is.
Instance Number:  Two digit, incremental number of Database Type.

Examples:

The first content database for a Web Application named "Intranet", in a Farm abbreviated as "SPF01" would read as follows:
SPF01_WebApp_Intranet_01_ContentDB_01

An additional content database added to the "Intranet" Web Application would be incremented by 1, as follows:
SPF01_WebApp_Intranet_01_ContentDB_02

The Reporting database of a Web Analytics Service Application would read like this:
SPF01_SvcApp_WebAnalytics_01_ReportingDB_01

Sample Sizing Estimate for SharePoint Server 2010 Search Environment

Below is a sample search storage estimate for environment with 500GB content databases. This was calculated by adding up the sum of MDF files. There are so many considerations for storage, and this is just a rough sketch of the storage aspects related to the Search Service Application. This does not consider base installation overhead nor does it factor in other SharePoint Service Applications (SSA), Office integrations, User Profiles, Usage Database, PowerPivot, etc..

1.
ContentDBSum = Measure the size on disk of MDF files. For this example, 500 GB.

2.
IndexTotalIndexSize = ContentDBSum * .035 or 17.5 GB
QueryComponentIndexSize = TotalIndexSize / Number of Index Partitions
StorageQueryComponentIndexSize * 3 single query component with space for index merging
QueryComponentIndexSize * 4 single query component with space for index repartioning or 70 GB

3.
TotalPropertyDBSize = ContentDBSum *.015 or 8 GB
TotalPropertyDBLogSize = ContentDBSum *.0031 or 2 GB

4.
TempDBSize = TempDBSizeContentDBSum * .00034 or 1 GB

5.
TotalCrawlDBSize = ContentDBSum *.046 or 23 GB
TotalCrawlDBLogSize = ContentDBSum *.011 or 6 GB
ContentDBSum *.011 = TempDBSize = 6 GB

6.
SearchAdminDBSize = # items in index * .3 or override as 5 GB
In this case, I don't know how many items there will be in the index so I am going to allocate 5GB.

7.
Backup Size = basically, add all of the database sizes together.

Reference

Microsoft Technet (2010). Performance and capacity test results and recommendations. Retrieved April 27, 2010 from http://technet.microsoft.com/en-us/library/ff608068(office.14).aspx.

Microsoft's Position on Changing SharePoint Databases

I caught a Twitter message from a colleague, Mauro, today passing along a Microsoft Help and Support article which discusses Microsoft's position on database modifications for SharePoint Products. In short, the article breaks down to Microsoft doesn't support changes to SharePoint databases.

The article, "Support for changes to the databases that are used by Office server products and by Windows SharePoint Services (http://support.microsoft.com/default.aspx?scid=kb;EN-US;841057 )" includes a list of unsupported database changes, shown below:

" Examples of such database changes include, but are not limited to, the following:

• Adding database triggers
• Adding new indexes or changing existing indexes within tables
• Adding, changing, or deleting any primary or foreign key relationships
• Changing or deleting existing stored procedures
• Adding new stored procedures
• Adding, changing, or deleting any data in any table of any of the databases for the products that are listed in the "Applies to" section unless Microsoft protocol documentation is followed exactly
• Adding, changing, or deleting any columns in any table of any of the databases for the products that are listed in the "Applies to" section
• Making any modification to the database schema
• Adding tables to any of the databases for the products that are listed in the "Applies to" section Changing the database collation "

This subject comes up rather frequently during discussions and as a proponent of SQL Server in general and especially its Business Intelligence features such as Reporting Services for extending the capabilities of SharePoint solutions, I feel compelled to chime in.

I completely understand Microsoft's position. How could a software company possibly support custom, schema changes or functional modifications to their application? They cannot. Nor can Microsoft really ensure their software will function (optimally) with any type of foreign database object running on the live databses, even a view.

However, accepting Microsoft's position doesn't change two things:

1. SharePoint information is business intelligence information and often times needs to extract, transform, load, and display in other places than its native content database.
2. Where there's a will there's a way.

Exploring some scenarios...

If you need to add custom functionality to your SharePoint environment, then you might build your own custom database with triggers, functions, sprocs and all, and then use a programmatic methods to move the data you need out of the SharePoint content databases and into your custom database. Then, use Web Parts to deliver the data and functionality from the custom database throughout the SharePoint Web sites. This does not overstep any of the guidelines listed in the article.

Secondly, T-SQL queries are some times necessary for reports when using the Object Model as an XML data source reaches limitations. If you need to integrate SharePoint data with other systems or for reports you can:

1. Create a database snapshot of the SharePoint content datbase (if you have SQL 2005 / 2008 Enterprise Edition)
2. Use programmatic methods to move the data from the SharePoint content database into an intermediary database for abstraction purposes.
3. Move data into an existing data mart or data warehouse during regular intervals.

Any of these three approaches allows you to leverage Views. The benefits of compiling T-SQL queries as Views is that you can secure them, you can use them as data sources in SQL Reporting Services reports, and you can store them on the database where they are accessible, reusable, they get backed up with database backups, you can optimize query performance with Indexes, and querying Views performs better because you are not storing queries nor are you filtering or transforming data in the Web tier.