SharePoint Configuration Database IP Address Blues

A really weird situation happened yesterday at work.

The Symptoms

We wanted to move the Virtual Machine that serves as a SQL Server for our development SharePoint environment, from one host computer to another host computer. No renaming, no changes…it should have been really transparent to SharePoint.

Well, it wasn’t.

image

Once we moved and restarted the server, the SharePoint refused to work. We were starting to dig within the log files and we found out that the front-end was trying to contact a fixed IP address as a SQL Server. This was the old IP address of the database server virtual machine before it was moved. We scanned the Registry for references to this IP address but none was found.

We even made a brand-new virtual machine with SharePoint 2007 and tried to connect it to the SQL Server. It did, but the dreaded error connecting the old IP address persisted. So, we deduced that the culprit IP was stored somewhere in SharePoint databases.

The Workaround

The solution to this problem was simple, once we found out what it was. The network card on the database server was set to multicast IP with the old IP address as a “second identity” for the card. In this way, SharePoint could connect itself to the databases using the bad IP address.

Once we got into Central Administration, we removed the server that showed as bad IP address in Server Topology page. Furthermore, we did a STSADM –o renameserver to change the references from the old IP-address name to fully-qualified domain name of the server. Bravely, we removed the alternate IP from the database server NIC card settings and did an IISRESET on the SharePoint side.

Worked as a charm. Two days lost fixing this, though 🙁

The Learning

In this case, cut the right hand of the SharePoint administrator who first added the reference to the database server by IP address alone. (Just kidding, of course).

Truncate that Huge DB Transaction Log!

How many times you’ve seen the small SharePoint content database and a huge transaction log by its side? And the corresponding “Not enough storage space” warning on the SQL Server?

The cause of this behaviour is that the SharePoint databases are created with FULL RECOVERY transaction log. It essentially creates a full record of every sentence sent to the database, even if it’s only a SELECT. As you can imagine, SharePoint does a lot of querying to the database every second and the transaction log grows very quickly indeed.

To prevent this behaviour, change the RECOVERY mode of the transaction log to SIMPLE. But, if you are coping with a huge transaction log already, you can use this snippet of T-SQL to truncate it back to inoffensive 1 MB in size:

(of course, being WSS_Content_DB your database name).