WSS 3.0, MSSearch Service and SQL Server

In my previous post I talked about the SQL upgrade that we performed recently. I left out the part that had to do with our SharePoint server. While it is fundamentally related I thought I would share some of the dead-ends I ran in to.

About a year ago we replaced our existing SharePoint server with one that provided a little more flexibility. We created a simple farm, with a web front-end and SQL Server backend. This ran quite well for us up until we upgraded hardware on the SQL Server. Again a little better preparation beforehand then a lot of these problems would not have occurred.

Search on the SharePoint site had been more or less broken for a while, and we were waiting for this recent upgrade to attempt to get it working properly again. I can’t really tell you what caused it to get balled up, other than we may have reset permissions on an early SAN migration. At any rate, search would return what you wanted sometimes, and sometimes it didn’t.

Below is a list of the error messages that I ran into as I worked through the problem. After understanding the fix in my case, and seeing them all laid out it’s obvious that SharePoint just couldn’t get to the SQL server. Adding the sqlsrvr.exe application to the list of allowed programs through the firewall is what resolved the issue for us. So the proper course of action would have been to set the firewall rule first, and then stop and restart the search service.

It sounds rather scary, when you first click the stop link in the SharePoint Central Admin website it says it’s going to remove everything. If your search is broken, that is what you want. If you’re very nervous you can simply create a new database for search, you will want to look at this article from Microsoft. Once you start the service back up it will create all the needed tables and stored procedures.

Error messages encountered

“Your search cannot be completed because of a service error”

“Could not access the Search service configuration database”

“The search request was unable to connect to the Search Service.”

“retry of query machine has failed with error the system cannot find the file specified”

SQL Server Upgrade

In an earlier post I mentioned that we were in the process of upgrading hardware and moving something’s around on the back-end. The end-result of this process would make us feature complete in our stated goals. Well, we had a few hiccups along the way, hopefully in the future I’ll do better at testing all this out. One of the things that needed to happen was our existing SQL server needed to be replaced with something that had significantly more power.

The new SQL server is a Dell PowerEdge R310 with 16GB of Ram and dual Quad core Xeon’s, this is a vast improvement over the hardware replaced. In my own personal goal to keep us as current as possible it was loaded with Windows 2008 R2. We would have liked to upgrade to SQL 2008, but support for that is not quite there yet with System Center Configuration Manager. So we loaded SQL Server 2005, and before you could do anything the most recent service pack needed to be applied.

The old server ran Windows Server 2003 R2 and we had never configured the firewall. Since 2008 comes with the firewall turned on, we opted to make it go with the firewall. If we only had a single instance it would have been much simpler, but we have 4 named instances that needed to work properly. After bringing our first instance online and restoring the data, we found that the best thing to do was to add the program binary to the list of allowed programs.

We performed the following steps for each of the named instances:

  1. Start > Control Panel > Windows Firewall
  2. Allow a program or feature through Windows Firewall
  3. Change Settings
  4. Add another program
  5. browse to the instance bin folder for the sqlsrvr.exe

Once that was done we were able to successfully connect to SQL and get services started.