Unable to create Maintenance Plans or DBMail Fails after service pack/Hotfix is applied on SQL Server 2005 fail over cluster instance running on Windows 2008 cluster

We have seen a couple of cases, wherein we are unable to create maintenance plan or DBMail Fails after applying SP on SQL Server 2005 failover cluster instance running on Windows 2008 Cluster.

Resource Database and other system databases may not be updated when SP and other hotfixes are applied to SQL Server 2005 clustered instance running on Windows 2008 cluster servers.  

The setup will complete successfully and will not report any errors. This may lead to unforeseen errors since the system objects are not upgraded.

An example of such a problem can be not being able to create maintenance plans. You may notice below error while creating a maintenance plan in such scenario,

Invalid column name 'from_msx'.
Invalid column name 'has_targets'. (Microsoft SQL Server, Error: 207)

OR DMail Fails with error

The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'  

Once the SP setup or any hotfix setup is completed, you should check the version of SQL Server through the following query:

SELECT @@VERSION -- This should return the upgraded build number

To check the version of Resource Database:

SELECT SERVERPROPERTY('ResourceVersion') -- This should return the upgraded build number

If the resource database version is not consistent with SQL Server version after SP or hotfix is applied successfully. Please check the SQL Server service pack or hotfix setup logs:

Error from SQL9_Hotfix_KB955706_sqlrun_sql.msp:

 

MSI (s) (B4!B4) [18:34:26:092]: PROPERTY CHANGE: Adding SqlClusterSec property. Its value is '1'.

.

.

.

.

MSI (s) (B4!1C) [18:35:21:520]: Note: 1: 2262 2: _sqlAction 3: -2147287038 Function=ComponentUpgradeInstall

Skipping Action: ComponentUpgradeInstall (Condition is false)

<EndFunc Name='LaunchFunction' Return='0' GetLastError='203'>

MSI (s) (B4:38) [18:35:21:520]: Doing action: RSComponentUpgrade.3EA9D9BF_D9D2_4023_B2A7_9E2137B2FB1B

Action ended 18:35:21: ComponentUpgradeInstall.3EA9D9BF_D9D2_4023_B2A7_9E2137B2FB1B. Return value 1.

MSI (s) (B4:38) [18:35:21:520]: Transforming table CustomAction.

If you see the above messages in the log, then service pack or hotfix install is incomplete. You can check the above log at the following location: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix and you can search for the string SqlClusterSec and if its value is 1 as shown above then please refer to cause and resolution section.

 

Cause:
This issue occurs if the node names are in lower case as displayed in computer properties. The datastore of the SQL Server setup presents the node names in the same case, however Windows Installer presents them in the upper case. The setup thinks that we are running it from the passive node and it does not upgrade Resource Database and other system databases.

 

Resolution :

This issue has been fixed in Cumulative Update 9 for SQL Server 2005 SP3. Please refer to the KB below for more information:

https://support.microsoft.com/kb/978308

 

 

Workaround:

1. Move all groups To Node 1.

2. Modify the possible owners for the SQL Server Instances so that Only Node 1 is the possible owner.

3. Evict Node 2.

4. On Node 2 Change the Computer Name so it is all Upper Case

5. Join it back into the cluster.

6. Modify the possible owners for the SQL Server Instances so that Node 1 and Node 2 are now the possible owners.

7. Move All Groups To Node 2.

8. Modify the possible owners for the SQL Server Instances so that Only Node 2 is the possible owner.

9. Evict Node 1.

10. On Node 1 Change the Computer Name so it is all Upper Case.

11. Join it back into the cluster.

12. Modify the possible owners for the SQL Server Instances so that Node 1 and Node 2 are now the possible owners.

13. Move SQL Back to the correct nodes etc.

14. Re-install Service Pack/Hotfix so that the system databases are upgraded and to avoid the issue.

 Note:

The above workaround may not work in all the cases. On evicting and changing the node name to UPPER Case, the 'add node' wizard might present the node name in lower case. If you encounter the node name is still coming back in lower or mixed case, add the node from the command prompt.

Below is the command which worked for us:

CLUSTER /Cluster: <ClusterName> /ADDNODE /NODE: <NodeNameInUpperCase>

 

Santosh Kumar Goli
SE, Microsoft SQL Server.

 

Reviewed by

Anurag Sharma
SQL Server Escalation Services