Slinger's Thoughts

May 1, 2008

You always learn from your mistakes; SharePoint database woes.

Filed under: SharePoint — slingeronline @ 3:37 pm

Ran into a very interesting issue about two weeks ago.  The hard drive that our MOSS portal is on got really small, really fast.  So I needed to move the databases to a new and larger drive.  Sounds easy enough, right?  Well, I got the main database that I needed to move relocated.  Crisis averted.  But something occurred to me and now I’m trying to figure out how to fix something I screwed up when I first installed MOSS here. 

When I first installed MOSS, I chose to do a stand alone install. (I was having serious issues with the Farm install, and eventually got frustrated with the whole mess.)  Well, a stand alone install also installs the Windows Internal SQL database.  That’s not a big deal.  Using the MSSQL Server Management Studio is a trick, but you can get it connected too. (Please note that this is only useful for compacting logs and the like.) Here’s my problem.  We are quickly getting to a point where we will have a dedicated SQL server. That’s fine.  We want to move all of our SharePoint databases on to this server.  That’s ok also, for most of the databases.  We want this server computer to also be the engine that serves the database.  Now I’ve hit a snag.  It seems like it would be an easy thing to do.  Apparently not.  The first thing I tried was the solution listed in knowledge base here. Worked like a charm!  Where is my content? Where are my sites? How come it doesn’t work?  Who knows?  But it’s not a solution.  (At this point, I should note, that I did not perform this on our production server, but rather on a development server so that I wouldn’t screw any of our business up.  If you do this on your production farm, you may not list me as a reference on your resume.)  So how do I go about doing this? 

Well, first let’s go over how I moved the content database.  First thing I did was warn the users two weeks in advance that the server would be unavailable for a weekend.  That kept my inbox empty of any complaints.  Then I went into my server’s Central Administration pages and marked all of the databases offline.  I quiesced the farm also.  Once those steps were done, I went into the Services console on the server and stopped any service related to Office, SharePoint, WSS, and IIS.  I probably didn’t need to do anything so drastic, but I figured it would be better to just get it out of the way instead of constantly running into errors here and there.  This also prompted me to write a little batch file to start and stop all the services with one click instead of 30.  I also had a notepad nearby, since all of my MOSS databases have the unique identifier after them. (Yeah, that’s annoying. I can remember WSS_Search.  I cannot for the life of me remember WSS_Search_ghre90375jdksrur94osjf902kdhrue or whatever the hell it was.) It would be nice if Microsoft would let us name these databases our selves.  I think they do in a farm install, but I’m not sure.  Then I followed all of the instructions listed here.  It worked great, and I got my content database moved to the new drive. (It still has that lousy name though.)  I also took the opportunity to get the server up to date with all of the patches and hotfixes. 

Now we get to the meat of the matter. I’m going to try and use the sqlcmd utility to change which MSSQL instance my databse is attached to, and in the process, also try to change the name of each database to get rid of that troublesome GUID. (all the letters at the end of the database name and database filename.) Wish me luck, I’ll be back in two paragraphs.

While I’m working on that, I also wanted to let you know, that we didn’t have a development server for about six months.  I was just granted permission to set one up, and everyone in our company is new to SharePoint.  Some have used SharePoint at other companies, and hated it.  At these other companies however, there was no development done at all on the SharePoint deployment.  We have adopted a policy to gradually introduce SharePoint to the users here, and as they get used to it, give them more abilities in little increments.  Basically instead of inundating them with a thousand new ways to do things all at once, let them take a small bite off at a time.  So far it is working wonders, and our users are itching to see what else SharePoint can do.   The next thing I will introduce them to is workflows, which we can use for approval routing.  In talking with one of my associates about this practice the other day, he commented that it was basically “progressively implemented.”  Wow, what a great acronym.  I’m sure that Microsoft will love the acronym for Progressively Implemented SharePoint Services.  Kind of sounds like what I do with most of my day anyway, so PISS it is.  (See me trying to present that to our executive board and keep a straight face.)

 Well, I gave it a shot, using the SQLCMD utility.  I disconnected the database from the original instance, and then exited out so that I could re-run the SQLCMD utility and connect it to another instance of MSSQL. I then continued using the rest of the steps to re-attach the database, then restarted all of my services.  Tried to hit the main page, and nothing.  I went into the Application Management page in Central Administration, and I clicked on the database.  It still says that it is attached to the old SQL instance.  Ok, since I copied the database and didn’t delete the original, I figured I would just add a content database and everything should be good.  So I added the content database.  Keep in mind that this is a copy of the original, I didn’t change anything.  I am looking at the “Manage Content Databases” page and I see both databases.  Everything is set correctly.  There is one tiny little difference.  The original database shows 2 sites, the new one shows 0.  I wonder why that is?  So, using the SQLCMD utility, you cannot change the database engine that SharePoint connects to.  While I was at it, I also tried renaming the database to see if SharePoint would complain about that or not.  Yep, it complains.  This leaves me to wonder though, if it is possible to change the database engine, or the name of the database that SharePoint uses.  I’m certain that I could think of names for databases that would be infinitely more useful to me than the GUID versions that Microsoft throws.

In any case, I’m left with a conundrum; how do I change the database server that present my SharePoint files, and how do I rename those databases to something that is humanly useful?  Back-up and restore?  Well it does give me options to create a new instance, but unfortunately, I also get the same end results.  Zero sites listed in the content database. This is a stumper.  If someone has an idea of how I can change either of these, I would greatly appreciate it.

Next up; restructuring the entire site collection without losing any data. (somehow I don’t think that will go as planned either.)


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at

%d bloggers like this: