Monday, August 30, 2010

Clustering Gotchas

A couple of weeks ago my company clustered a SQL2005 install and I posted about the 6 core CPU issues that were experienced. This weekend we clustered a SQL2000 on SQL2003 environment. SQL2000 is end of life but we still use it until the financial guys approve our upgrades. So with one of our most critical servers running standalone we attempted the cluster.

This server had been clustered in the past under a design the previous DBA had created and sold using mountpoints. Mountpoints are not supported on SQL2000/Windows 2003 configurations. Timeout issues on the mountpoints caused the cluster to fail regularly.

The following issues and resolutions from this weekend are below, it was successful and within the outage window.
Temp and Tmp environment variables have spaces in the paths:
Microsoft sets the default location for the Temp and Tmp variables are in the folder of the current user. When installing SQL on a cluster this will cause the install to fail with a folder not found error. The folder exists but if the path has spaces the install fails. Change the environment variables to a path without spaces, I usually use c:\temp for both.

Timeout during install caused by multiple drives in the SQL cluster group;
Not sure what causes the timeout but to get around this issue move all physical drives, excluding those needed for the install to the Cluster group. Once the install is complete move them back to the SQL group. We have 20 drive resources in the SQL group, this was done to assist with EMC snapshots but that technology is not currently being used.

Clustering in SQL2000 is a far easier task than was SQL7.0. SQL2005 continues the improvement and hopefully so will SQL2008. As tasks become more and more straight forward DBAs will have to distinguish themselves by the other talents needed to excel. Performance tuning, database design etc. The administrative tasks will always be needed, but at least at my company, it takes more than just normal admin skills to succeed.

Friday, August 27, 2010

SQL2005 on 6 Core CPU

A couple of weeks ago we upgraded one of our enterprise systems to a clustered environment using Dell Hardware with 2 physical CPUS, 6 Core Hyperthreaded, showing 24 available CPUs in task manager, add in 32gb of memory and still stay under 10k. These were great boxes for the application a document storage retrieval system.

After windows was installed and we started on the SQL2005 initial install the setup would just fail and not report any usable error. What had started as a fairly simple upgrade turned into a nightmare.

After several attempts and several hours of scouring the web our systems guy found an article about SQL2005 not installing on a server where the cores were not divisible by 4. Who would have thought that MS would have been so short sighted, or that the error message would not have given some indication.

Once the system team modified the server to only show a single CPU the install went smooth and after applying SP2 all 24 processors put back into the pool.

Since this upgrade the server is running at under 10 percent CPU usage and very seldom has any type of wait condition.

Hope this helps someone else avoid a headache.