SQL Server

Data Channel : Managing SharePoint Databases

I was recently interviewed for the Data Channel about managing SharePoint databases on SQL Server.

In the interview with Nagaraj Venkatesan (SQL Server MVP from Singapore) I cover many of the common questions DBA’s have about SharePoint including:

  • Things that make SharePoint databases unique
  • Capacity Planning
  • Remote Blob Storage (RBS)
  • Backup / Recovery
  • SSRS Integration
  • Patching

You can download a copy of my presentation from #SQLsat614 which covers the basics for DBA’s and is also useful for SharePoint Admins.

Thanks to The SQL Saturday team for giving me the opportunity to present and Nagaraj for the interview.




SharePoint Orphaned Content Database

Today I resolved an interesting SharePoint 2010 issue. Examining the Windows Application Log on the Application Server I found the following error:

SQL Database ‘WSS_Content_ba49ed16-9131-4e70-ab2a-4378914fd6f0’ on SQL Server instance ‘SharePointSQLServerName’ not found. Additional error information from SQL Server is included below.

In Central Admin SharePoint listed in “Stopped” content database called “WSS_Content_ba49ed16-9131-4e70-ab2a-4378914fd6f0″. This database didn’t exist in SQL Server.

I believe the database appeared when the SharePoint server was restarted but wasn’t able to contact the SQL Server due to an issue with DNS. A reference to the database existed but the database itself was never created.

The Get-SPContentDatabase command lists all databases except the one above. Hummm, this is going to be tricky to removed…

Here’s the process I followed to remove the database orphaned database:

  • In SQL restore a small content database into a new database and gave it the name listed in the event log error.
  • In Central Admin start the orphaned content database.
  • In Central Admin I viewed the content database properties and selected the “remove” option to delete the database.
  • Manually removed the database from SQL server.

The SharePoint environment is now back to it’s usual happy self.

SQL Server 2008 Certified

SQL Server is something that most people in IT roles will touch one way or another in the course of their working lives. It is the engine behind many line of business systems, SharePoint, ERP and CRM systems.

Having the basic skills to correctly install, configure, backup, recover and troubleshoot SQL Server issues is important and something that is often just assumed. Over the past 16 or so years I have seen many examples where this assumption is wrong. To make matters worse, troubleshooting is often focused in the wrong areas leading to time consuming and expense solutions to often simple problems.

A few things everyone should know about SQL Server:

  • How disks should be configured when performance is required
  • Databases and Log files should be on dedicated disks
  • How to setup a maintenance plan and why you should
  • SQL Agent service defaults to manual startup
  • How to backup
  • How to recover to a point in time
  • The difference between Full and Simple Recovery models
  • How to use SQL Profiler to identify poor SQL code
  • Basic SQL queries
  • Licensing options and SQL Server Editions

This is by no means a complete list, but is a good step in the right direction for anyone who manages SQL Server environments.

I first touched SQL Server back in the early 1990’s with version 4.21 and since then I have use ever version right up to the current 2008R2 edition. Recently I decided to formalise this experience and become a Microsoft Certified Technology Specialist: SQL Server 2008 Implementation and Maintenance. This was a rewarding experience and I learnt many new and useful things.

If are you are going to certify, I recommend reading “The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit” and a bit of hands on experience to pass this exam.

The 70-432 exam is oriented towards IT Pro’s, but I think it would look great on the CV of a developer, SharePoint consultant or specialist for any SQL Server based application.
70-432 Certification Home Page