A few weeks back I kicked off a survey asking whether you have SharePoint in your SQL Server environment at all. Here are the results:

The "Other" responses were:

• 3 x "Not now but I did in a previous role."
• 3 x "Yes, but the dbas do not manage their servers."
• 2 x "SharePoint 2007 and 2010."
• 1 x "My environment no but it's included in an MES environment."

Almost 75% of respondents have some form of SharePoint in their environment. Although I don't think is a statistically valid representation of the installed SQL Server user base, I think it confirms what I've been thinking - there's a *lot* of SharePoint out there. Remember that this is just a survey of people who read my blog - primarily SQL Server professionals - there is a huge amount more SharePoint installations where there is *no* SQL Server professional involved.

The reason that it's interesting whether SharePoint is involved in your SQL Server environment is that it can have an impact on your regular maintenance and your HA/DR strategy. Ordinarily you'd want to treat a SQL Server instance that's running SharePoint just like any other back-end SQL Server for an application, but SharePoint imposes some restrictions on you.

• It has it's own maintenance jobs which run regularly, which means you either need to exclude that instance from your standardized maintenance setup, or disable the SharePoint jobs completely and run your own, taking into account the SharePoint guidelines.
• There's more than one database required for SharePoint to work properly, which means that database mirroring or log shipping as HA/DR providers becomes extremely tricky. I usually recommend failover clustering with SAN replication for HA/DR to avoid complicated failover logic. Backup and restore can become more complicated too - especially when using an off-database LOB storage layer like RBS.
• You can't create additional filegroups in the large content databases and you can't use partitioning (except for the Web Analytics service), which means taking advantage of filegroup piecemeal restore and partial database availability is not possible.
• Auto-create statistics needs to be disabled for SharePoint, whereas in most situations you want it enabled - a departure from your standardized setup.
• MAXDOP=1 is strongly recommended - again a departure from your standardized setup.

Some of this guidance is unfortunate, and shows that SharePoint's use of SQL Server is not as optimal as it could be, but all of this means that you need to treat a SQL Server instance that underpins SharePoint differently from your other SQL Servers.

You can read more about SQL Server configuration and maintenance when using SharePoint at:

• Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)
• Database maintenance for SharePoint Server 2010

Enjoy!

This question came up on the #sqlhelp tag on Twitter today, and it's something I'm interested in too: what percentage of SQL Server environments include SharePoint somewhere too?

This is interesting because it has implications for standardization of SQL Server maintenance and HA/DR procedures across your company.

If you don't have SharePoint, please take 5 seconds to click No so the results make some sense.

I'll report on the results in a week or two.

Thanks!

As you may know, I've been teaching the SQL Server portion of the SharePoint MCM since it started. The old database maintenance whitepaper for SharePoint 2007 had all kinds of things wrong with it and the publishing of the updated whitepaper for SharePoint 2010 was eagerly awaited by the community. Unfortunately it too had a bunch of problems so I offered to get involved and comprehensively review and rewrite it, and did so just before the summer.

It's finally been republished with all my revisions and you can download it from: http://technet.microsoft.com/en-us/library/cc262731.aspx

Enjoy!

For about 6 months we'd bee trying to hook up online with SharePoint MVP Hilton Geisenow for an interview but unfortunately the bandwidth to South Africa is pretty limited so we decided to wait until the recent MVP Summit in Redmond, WA. We managed to hook up and record an interview for Hilton and he's just published it as part of his SharePoint podcast The MOSS Show.

The interview is around 45 minutes (we unfortunately lost the intro section due to a technical hitch, but repeated all the technical goodness) and we cover issues that you'll see with SharePoint's use of SQL Server, how to maintain databases if you're an involuntary DBA (which many SharePoint admins are forced to become) and debunk some myths along the way. Although the interview is focusing on SharePoint, everything we say is relevant to DBAs of regular relational SQL Servers.

You can listen to the interview or download it at: Episode 21 – Diving Into SQL Server With Paul Randal & Kim Tripp.

Enjoy!

After teaching some of the MCM-SharePoint class last week, one of the attendees pointed me at a blog post about measuring churn in SharePoint databases. The poster gave code to measure how large full backups are, which really only measures how much data there is in the database, not whether existing data has changed. If two successive full database backups are the same size, there's no way to tell how much changed - and of course, you need to take a full backup to be able to tell whether the size changed.

A while ago I wrote a script that would enable SQL database DBAs to tell how big the next differential backup will be. A differential backup contains everything that's changed since the last full database backup, so every new and everything changed. Although this still doesn't show whether a single piece of existing content changed multiple times, it can still show whether existing content changed at least once. Better still, you don't need to take any kind of backup to run this script.

So, to get an idea of the churn rate of your content databases, check out this script - see New script: How much of the database has changed since the last full backup?.

Enjoy!

PS If you're using SharePoint and find this useful, please let me know and if there's any other scripts that would be useful - Kimberly and I are both starting to get more into SharePoint admin from a SQL perspective.

As you may know, Kimberly and I teach (and wrote) the first week of the 3-week Microsoft Certified Master - Database qualification. From next week onwards, we'll each also be teaching a day of the Microsoft Certified Master - Sharepoint certification too, as SQL experts. As such, we'll be learning a lot about how SQL Server behaves underneath Sharepoint and we're both going to start posting on SQL-Sharepoint issues (such as that it doesn't support multiple filegroups, so partial database availability isn't an option during disaster recovery).

Watch this space!

Both 2005 SP3 CU2 and 2005 SP2 CU12 contain fixes for two bugs that cause corruption in LOB data.

The first bug may happen when a column type is converted to varbinary(max), change the large-value-types-out-of-row option to true, insert a row, and then try to update it - described in KB 961648. The second bug may happen when you update an out-of-row LOB value in a database with snapshot isolation enabled - described in KB 962209. Either of these problems will result in a 7105 error:

Msg 7105, Level 22, State 11, Line 1
The Database ID Database ID, Page (N:N), slot N for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.

You can get the fixes at 2005 SP3 CU2 and 2005 SP2 CU12.