SharePoint Database Performance – Moving the SharePoint SQL Database to New Location on the Same Server

I run an instance of WSS 3.0 on a machine at home for testing. I don't use it much because it is so slow unless you are on the LAN. I always knew it was because of SQL, but I didn't really have time to look into fixing it. See, I don't have the option of having two separate drives in the machine. NOTE: You should never run SQL databases on the same HD spindle where the OS is located due to exactly what I was experiencing: slow response.

Then I woke-up to the year 2007 and apparently there are external HDDs you can buy…okay, I knew about them; I just didn't put too much thought into it. This evening, I moved my SharePoint databases to the new external HDD and the responsiveness of SharePoint increased tremendously.

I did a search on how to move the dbs without causing problems, and honestly, I was unable to find any websites/postings that matched my scenario exactly. The postings talked about: 1) moving from the Microsoft SQL Embedded Edition (MSEE) version to full SQL or 2) Moving to a different server

So, here are my pretty simple steps to move my SQL database to a new HDD:
1) Made sure I had backups of the database and of the SharePoint sites!
2) Make note of the dbo for all SharePoint databases
3) Stopped all SharePoint services
4) Detached the existing databases
5) Copied all SharePoint database files and db logs to the new location
6) Individually attached all the SharePoint databases with the new location
NOTE: Pay special attention to the db owner selection when attaching the databases. You should have made note of the correct dbo in step 2.
7) Started all services
8) Load the SharePoint website.

Simple enough and it worked, which is even better.

Exclude Managed Path

In the SharePoint version 2.0 products, to exclude a directory from SharePoint “control”, you add an Explicit Exclusion to the Managed Paths for the site using the Central Administration Website. You also add Explicit Inclusions and Wildcard Inclusions at the same location.

However, in SharePoint version 3.0 products, it is a bit different. You still create Explicit Inclusions and Wildcard Inclusions on the administration page for the site. However, to create an Explicit Exclusion, you just have to create a new Virtual Directory in IIS underneath the appropriate IIS Virtual Server. This is because ASP.NET knows that when you create a new virtual directory, it should not touch it. Pretty nifty, eh?

When I set my excluded directories up, I did run into a minor issue. It was so minor that all I had to do was give the Network Service account read permissions on the physical directory.