Prepare SQL Server 2005 in a clustered environment for SP3

Standard

Introduction

Recently I was asked to create a new high availability solution for MS SQL Server 2005. When I finished the installation of SQL Server 2005, I found myself troubled as to why the installation of SP3 failed with the update of the database services. After a lot of searching I found the solution to my problem.

Because I spend a lot of time figuring out why my installation failed I decided to write this article so other people won’t have to spend so much time and frustration installing the service pack. This article also covers the problem for Service Pack 2. I haven’t tested Service Pack 1 with this method.

What exactly goes wrong?

When I looked closely at the installation I saw that at a certain moment the setup program starts some actions for services and databases. After watching the databases being created and deleted, I noticed that something went wrong at the “mssqlresource” database. The setup program tries to overwrite the “mssqlresource” database but for some reason fails, which results in a rollback of that part of the installation. It looks like the “mssqlresource” database is still in use by SQL Server 2005 what creates teh error.

Below are the steps that you can take to install SP3 on your SQL 2005 cluster nodes.

1: Create Backups

As for any DBA you should always backup your databases before you start the installation of updates or complete service packs. In my case I didn’t have many databases on my server because it was a fresh installation. I created backups of all the present databases and I copied the “master” and the “mssqlresource” backup files to a different location.

2: Take the SQL Services offline

Before you stop the services for SQL Server you should take that part of the cluster down. If you don’t do this, the cluster will try to start the service as soon as it goes down. Go to the “Cluster Administrator” and select the resourcegroup where the services for that node reside. Right click the “SQL Server (instancename)” and click on “Take offline”. Do this for every SQL Service.

3: Copy and rename the mssqlresource database

Now that the services are down, we can copy and rename the “mssqlresource” database.

The “mssqlresource” database isn’t visible in SSMS (SQL Server Management Studio) so I had to copy the files from this database.

Go to the shared array where the database is installed and copy and paste them in that directory. In my case I rename it to “mssqlresource_old.mdf” and “mssqlresource_old.ldf” but you can use any name you want.

4: Start SQL Server in single-user mode

Before we can make any changes to the system databases we put the SQL Server in the single-user mode so nobody except the admins can connect to the server. Open a command prompt and type the following command:

NET START MSSQLSERVER /f /T3608

For servers with multiple instances you can use the following command:

NET START MSSQL$instancename /f /T3608

5: Alter the mssqlresource database

Through the SQLCMD program we can send queries to the SQL Server. In the command prompt type the following command:

SQLCMD S servername\instance -U adminuser -P password

If you’re authenticated you’ll see a prompt like “1>”. Now we can send the ALTER queries. Type the following commands for the MDF and the LDF files and press the Enter-key after each command. For the MDF File:

ALTER DATABASE mssqlsystemresource
MODIFY FILE (
NAME = data,
FILENAME = 'mssqlsystemresource_old.mdf'
)
GO

For the LDF File:

ALTER DATABASE mssqlsystemresource
MODIFY FILE (
NAME = log,
FILENAME = 'mssqlsystemresource_old.ldf'
)
GO

After the completion of both commands you’ll get a message that the changes will take effect after the restart of the SQL Server.

6: Restart the SQL Server

Stop the service with the following command:

NET STOP MSSQLSERVER

For servers with multiple instances you can use the following command:

NET STOP MSSQL$instancename

Start the service with the following command:

NET START MSSQLSERVER

For servers with multiple instances you can use the following command:

NET START MSSQL$instancename

If you complete all these steps, you’ve done the necessary preparations for installing SP2 and SP3 in a clustered environment. The database files that are renamed will be replaced by new files during the installation with the original filenames.

This article is based on the Technet article on this location: http://technet.microsoft.com/en-us/library/ms345408.aspx. I wrote this so that you don’t have to search for that solution in the first place when you install SP3.

Failing SQL 2005 cluster installation because of amount processors

Standard

Introduction
You come to work and set your mind to start installing your new SQL 2005 64-bit cluster. You’ve prepared your servers with the installation of Windows Server 2003 Enterprise Edition 64-bit, created all the right users and groups, all the IP addresses are available and all the needed disks are already created. There is a little more to it like names for virtual servers but that’s outside of the scope of this article.

The problem

All the settings for the installation are made and the installation starts. The entire installation goes right but at the very end it gives the following error:

SQL 2005 error

Not a real clear error but still an error. Clicking the retry-button doesn’t help so “Cancel” is your only option. The installation rolls back and you’ve lost about a 45 minutes of your time monitoring a failed installation.

Let’s go back to the configuration of the cluster node. The node has the following hardware (not every item of hardware is listed only those that could affect the installation):

  • DL 580 G5
  • 34 GB memory
  • 4 x Intel Xeon E7450 (6 core) CPU
  • Several logical disks

Both nodes for the cluster have the same hardware specifications.

I started puzzling over what could be the problem. Did I use the wrong settings for the installation? The log files didn’t give me any direction as to where the problem could be; they only showed that the installation stopped at starting the services.

In a desperate attempt to fix things, I tried to do the installation again but the same error occurred at the same point. What to do now?

The solution

Google wasn’t my friend in this situation because I couldn’t find anything related to this problem. Than I remembered an article about the fact that SQL Server 2005 has trouble with processors that have an uneven number of cores in the CPU. I have an even number of cores, but I could at least try it because I wasn’t going to solve my problem by looking at my screen.

I started searching the Microsoft articles and found the following page: http://support.microsoft.com/kb/954835

I started up the “Systems Configuration Utility” through Start->Run->msconfig. Set the “/NUMPROC” to 2 in the “Advanced Options” in the “BOOT.INI” tab and restarted the server.

I started the installation of SQL 2005 and guess what, the installation went perfect, just like it should. A little slower than normal, but I got the SQL cluster installed.

You’re probably thinking “I’m not going to use 2 cores if I have a lot more of them”. This problem is resolved in service pack 2 for SQL Server 2005 (at the time of this writing Service Pack 3 has been released).

After SQL Server 2005 was installed and the server restarted, I installed Service Pack 3 and undid the settings I had made in the “Systems Configuration Utility”. I restarted the server and everything worked like a charm.

If you have trouble installing your service pack 3 in your clustered environment you can probably fix this by reading my other article “Prepare SQL Server 2005 in a clustered environment for SP3“.

The entire process took me a lot of time because the installation errors weren’t unveiling what the exact problem was. I hope that others won’t have this problem with this information.