Unable to failover a Named instance of SQL server 2005 in cluster or unable to bring a Named Instance of SQL server 2005 Online

While trying to bring the SQL Services online ,which is a named instance of SQL Server 2005, we get these error messages in the Application Event Log.

Application Event Log:

Event Type:       Error

Event Source:    MSSQL$SQL2k5

Event Category: (3)

Event ID:          19019

Date:                7/17/2009

Time:                7:12:00 AM

User:                N/A

Computer:         BLRS2R17-3

Description:

[sqsrvres] ODBC sqldriverconnect failed

Event Type:       Error

Event Source:    MSSQL$SQL2k5

Event Category: (3)

Event ID:          19019

Date:                7/17/2009

Time:                7:12:00 AM

User:                N/A

Computer:         BLRS2R17-3

Description:

[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Event Type:       Error

Event Source:    MSSQL$SQL2k5

Event Category: (3)

Event ID:          19019

Date:                7/17/2009

Time:                7:12:00 AM

User:                N/A

Computer:         BLRS2R17-3

Description:

[sqsrvres] ODBC sqldriverconnect failed

Event Type:       Error

Event Source:    MSSQL$SQL2k5

Event Category: (3)

Event ID:          19019

Date:                7/17/2009

Time:                7:12:00 AM

User:                N/A

Computer:         BLRS2R17-3

Description:

[sqsrvres] checkODBCConnectError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Native Client]Login timeout expired

Cause:

The cluster service is not able to Connect with Clustered Named instance of SQL Server. SQL Browser even though running is not actually listening for requests.

Note: The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.

Resolution:
We started the SQL Server Browser Service with the -c parameter from command prompt it gave us the below response.

F:\Program Files (x86)\Microsoft SQL Server\90\Shared>sqlbrowser -c

SQLBrowser: starting up in console mode
We checked the same in a working cluster and found that when a browser is started it should have been like below,

F:\Program Files (x86)\Microsoft SQL Server\90\Shared>sqlbrowser -c

SQLBrowser: starting up in console mode

SQLBrowser: starting up SSRP redirection service

SQLBrowser is successfully listening on 0.0.0.0[1434]

[3728]: Waiting for next request…

[3728]: Received request…

[3768]: Waiting for next request…

[0532]: Waiting for next request…

[4612]: Waiting for next request…

[3728]: Waiting for next request…

[3768]: Received request…

[3768]: Waiting for next request…
We found that the SsrpListener key was 0
Path Of The Registry SsrpListener Key :

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\SQL Browser (x64 Machines)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\SQL Browser (x86 Machines)
Note: The SsrpListener registry value allows or disallows the SQL Browser to expose Engine instance discovery information, So if the value is 0 it disallows the SQL Browser to expose Engine instance discovery.
After we changed value to 1 and restarted the browser service we were getting the response as expected.

We stopped the browser service from the command prompt and then started it from services console and then we were able to bring the SQL Server Services online.

Another Workaround for this issue is to manually create a TCP alias with port number in all the nodes. In that case we need to make sure that SQL is not set to work with dynamic ports.

 

 

More Information:
In x64 bit machines you may get into another issue. Once after you change the SsrpListener value to 1 and then when you try to start the browser service through command prompt you may get this response.

C:\Program Files (x86)\Microsoft SQL Server\90\Shared>sqlbrowser -c

SQLBrowser: starting up in console mode

SQLBrowser: starting up SSRP redirection service

SQLBrowser is successfully listening on 0.0.0.0[1434]

SQLBrowser: failed starting SSRP redirection services — shutting down.
This is caused by the entry in the Wow6432Node registry pertaining to this installation eventhough this was a 64 bit installation. This happens only in x64 machines.

You can remove the checkpoint and then delete this key from both the nodes and then add the checkpoint back.

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer

If registry checkpoint is not removed properly before deleting this key you may get this error message continously in the Event Logs.

Cluster service could not write to a file (C:\DOCUME~1\SVC~1.WES\LOCALS~1\Temp\CLS2AF.tmp). The disk may be low on disk space, or some other serious condition exists.

Note: This post was first published  @ http://blogs.msdn.com/b/sqlserverfaq/archive/2009/08/20/unable-to-failover-a-named-instance-of-sql-server-2005-in-cluster-or-unable-to-bring-a-named-instance-of-sql-server-2005-online.aspx

2 thoughts on “Unable to failover a Named instance of SQL server 2005 in cluster or unable to bring a Named Instance of SQL server 2005 Online

    • Hi Aneil,

      As far as I know this is not related with any service packs. It can happen in any service pack as well. Personally I have seen it occurring in SP2 and SP3.

      Regards
      Ashwin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s