MSSQLWIKI

Karthick P.K on SQL Server

Archive for the ‘SQL Server Cluster’ Category

SQL-Server resource fails to come online IS Alive check fails

Posted by Karthick P.K on January 31, 2012

SQL-Server resource fails to come online with below Error:

[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 35; message = [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.

Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

 

Resolution:

Look at the version of (c:\windows\system32\sqsrvres.dll) and install the same version of SQL Server native client.

Cause:

When Higher version of SQL-Server is installed on a cluster in which lower version of SQL Server is already installed, the lower version SQL Server Resource DLL (c:\windows\system32\sqsrvres.dll) is upgraded to higher version and Higher resource DLL will be loaded by the resource monitor process to monitor Lower version as well.

For example: The Denali SQL Server Resource uses SNAC 11.0 to connect to the SQL instance and because SNAC 11.0 can be used to connect to Shiloh, Yukon and Katmai as well this side by side configuration will work. However if Denali is uninstalled, the Denali SQL Server resource DLL is not downgraded to Katmai, Yukon or Shiloh version and hence care should be taken to not uninstall SNAC 11.0 otherwise Yukon or Shiloh instance cannot be brought online.

Similarly When we install Yukon and Shiloh together, Yukon SQL Server Resource uses SNAC to connect to the SQL instance and because SNAC can be used to connect to Shiloh as well this side by side configuration will work. However if Yukon is uninstalled, the Yukon SQL Server resource DLL is not  downgraded to Shiloh version and hence care should be taken to not uninstall SNAC otherwise Shiloh instance cannot be brought online.

 

If you liked this post do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group MSSQLWIKI

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer
The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Configuration, Connectivity, SQL General, SQL Server Cluster | Tagged: , , , , | 8 Comments »

How to get the current state of cluster resource?

Posted by Karthick P.K on December 9, 2010

#include <windows.h> 
#include <ClusApi.h>
#include <winbase.h> 
#include <iostream> 
using namespace std;
#pragma comment(lib,"ClusApi.lib")
#include <stdlib.h>

void main()
{
    HCLUSTER Clusterhandle=NULL; 
    HRESOURCE  ClusterRhandle=NULL; 
    LPCWSTR lpszClusterName =NULL;
    WCHAR *lpszResourceName;
    CLUSTER_RESOURCE_STATE A = ClusterResourceStateUnknown;
    lpszResourceName =new WCHAR;
    wcout<<"enter the name of cluster resource:";
    wcin.getline (lpszResourceName,256);
    Clusterhandle= OpenCluster(lpszClusterName);
    if (Clusterhandle!=NULL)
    {
    
        ClusterRhandle=OpenClusterResource(Clusterhandle,lpszResourceName);
        if (ClusterRhandle!=NULL)
        {
         printf("Got Cluster resource handle\n");
            
         A= GetClusterResourceState(ClusterRhandle,NULL,NULL,NULL,NULL);

         if (A!=ClusterResourceStateUnknown)
             {
             printf("ClusterResourceState is:%d",A);
         
              }
              else
              {
              printf("ClusterResourceState is unknowm",GetLastError());
              
              }
              
        }
        else
        {
        printf("Unable to get ClusterRhandle:%d",GetLastError());
        }
    }
    else 
    {
    printf ("unable to get handle to cluster",GetLastError());
    }
 
}

Posted in Programming, SQL Server Cluster | Tagged: , , | 1 Comment »

SQLServer 2008 Fails to come online on cluster after upgrade

Posted by Karthick P.K on October 29, 2010

SQL-Server 2008 Fails to come online on cluster after upgrade.

Reason:

Server is in script upgrade mode. Only administrator can connect at this time.

Login failed for user ‘ccccc\xxxxx. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

Issue:

SQL-Server 2008 Fails to come online on cluster after upgrade.

Error from SQL-Server Error log

“Reason: Server is in script upgrade mode. Only administrator can connect at this time.”

“Login failed for user ‘ccccc\xxxxx. Reason: Server is in script upgrade mode. Only administrator can connect at this time.”

Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous Error log entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Resolution:

Start the SQLServer from services console (or) Command prompt and wait till the master database is completely upgraded. Once the script upgrade is complete start the SQL-Server normally from Cluster admin (or) Failover cluster manager.

Cause: During the Script upgrade mode only administrator can connect to SQL-Server, So when the SQL-Server resource is brought online ISAlive check fails immediately before upgrade completes and SQL-Server resource goes down. When we start SQLServer from services or command prompt ISAlive check doesn’t happen so upgrade completes. Once the upgrade is completed we can start SQL-Server normally.

Thanks,

Regards

karthick pk

Posted in SQL Cluster Setup, SQL Server Cluster, Startup failures | Tagged: , | 1 Comment »

Unable to start SQLServer agent resource on cluster after upgrading to 9.00.3186 or Higher

Posted by Karthick P.K on January 14, 2009

SQLServer agent resource fails to come online on cluster after upgrading to build 9.00.3186 or Higher

 

Error

2008-03-19 23:06:23 – ? [100] Microsoft SQLServerAgent version 9.00.3186.00 (x86
unicode retail build) : Process ID 2428
2008-03-19 23:06:23 – ? [101] SQL Server SNETNAME version 9.00.3186 (0 connection
limit)
2008-03-19 23:06:23 – ? [102] SQL Server ODBC driver version 9.00.3042
2008-03-19 23:06:23 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local
host server is
2008-03-19 23:06:23 – ? [310] 4 processor(s) and 2560 MB RAM detected
2008-03-19 23:06:23 – ? [339] Local computer is SNETNAME running Windows NT 5.2
(3790) Service Pack 2
2008-03-19 23:06:23 – ? [432] There are 11 subsystems in the subsystems cache
2008-03-19 23:06:38 – ! [364] The Messenger service has not been started – NetSend
notifications will not be sent
2008-03-19 23:06:38 – ? [129] SQLSERVERAGENT starting under Windows NT service
control
2008-03-19 23:06:38 – + [260] Unable to start mail session (reason: No mail profile
defined)
2008-03-19 23:06:38 – + [396] An idle CPU condition has not been defined – OnIdle
job schedules will have no effect
2008-03-19 23:06:38 – + [408] SQL Server MSSQLSERVER is clustered – AutoRestart has
been disabled
2008-03-19 23:06:39 – ! [298] SQLServer Error: 22022, CryptUnprotectData() returned
error -2146893813, ‘Key not valid for use in specified state.’ [SQLSTATE 42000]
2008-03-19 23:06:39 – ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2008-03-19 23:06:40 – ? [098] SQLServerAgent terminated (normally)
Error2

2008-03-18 12:18:30 – ? [100] Microsoft SQLServerAgent version 9.00.3200.00
((Unknown) unicode retail build) : Process ID 6512
2008-03-18 12:18:30 – ? [101] SQL Server PISTONDIST version 9.00.3200 (0 connection
limit)
2008-03-18 12:18:30 – ? [102] SQL Server ODBC driver version 9.00.3042
2008-03-18 12:18:30 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local
host server is np:pistondist
2008-03-18 12:18:30 – ? [310] 16 processor(s) and 32765 MB RAM detected
2008-03-18 12:18:30 – ? [339] Local computer is PISTONDIST running Windows NT 5.2
(3790) Service Pack 2
2008-03-18 12:18:31 – ? [432] There are 11 subsystems in the subsystems cache
2008-03-18 12:18:31 – ! [364] The Messenger service has not been started – NetSend
notifications will not be sent
2008-03-18 12:18:31 – ? [129] SQLSERVERAGENT starting under Windows NT service
control
2008-03-18 12:18:32 – + [396] An idle CPU condition has not been defined – OnIdle
job schedules will have no effect
2008-03-18 12:18:32 – + [408] SQL Server MSSQLSERVER is clustered – AutoRestart has
been disabled
2008-03-18 12:18:32 – + [162] Internal request (from SetJobNextRunDate [reason:
schedule will not run again]) to deactivate schedule 66
2008-03-18 12:18:32 – ! [298] SQLServer Error: 22022, CryptUnprotectData() returned
error -2146892987, ‘The requested operation cannot be completed. The computer must
be trusted for delegation and the current user account must be configured to allow
delegation.’ [SQLSTATE 42000]
2008-03-18 12:18:32 – ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2008-03-18 12:18:33 – ? [098] SQLServerAgent terminated (normally)

 

Resolution

Modify the the following Key.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SQLServerAgent
Modify the value data of the serverhost key to np:Virtualservername

Ie:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.X\SQLServerAgent
ServerHost
Value: np:Virtualservername.

This will force the SQLServer agent to connect with SQLserver using Named Pipes so
delegation is not used.

—————————————————————————————————————–

We have a HOTFIX available for this issue and it is included in the cumulative update pack9 for SQLServer service pack2. http://support.microsoft.com/?id=956378
Note: Before applying the Hotfix. you have to follow the  steps mentioned in Resolution else hotfix would fail. Revert the steps after applying the fix.

 

Regards

Karthick PK

Posted in Configuration, Connectivity, SQL Cluster Setup, SQL Server Cluster | Tagged: , , , , , | 2 Comments »

How to enable Constraint delegation for SQLServer2005

Posted by Karthick P.K on January 14, 2009

Constraint delegation for SQLServer2005

Follow the below steps to set the constraint delegation for SQLServer2005

1. In active directory users and computers for the startup account of SQLServer
a.Select “Trust this user for delegation to specified services
only”
b.Add host service for each node of the cluster
2. In active directory users and computers for each node under the delegation
tab
a. Select “Trust this computer for specified services only”
b. Add CIFS and Protected storage for each node of the cluster and each DC which
SQLserver may use to authenticate.

Take the SQLServer Group offline in clutser administrator and bring it online.

Please refer the attached document for additional information with Image and example   ConstraintDelegationforSQLServer2005

Regards

Karthick PK

Posted in Configuration, SQL Server Cluster | Tagged: , , | 10 Comments »