Welcome to SqlServerFaq.net

cmeb76jvk4

Many times we need to reinstall a sql server instance due to number of reason.

Whenever you reinstall the sql server instance the success of the installation of the new sql server instance primarily depends on the whether the previous uninstall of the sql server was clean so that all the registry keys and the related folders are deleted.

If the un-installation is done primarily from GUI and if it is successful we can say that the uninstall was clean, however if you go for manual un-installation you need to ensure that all the related registry keys and folders are deleted. We can refer to the following KB article to manually cleanup the sql server instance

http://support.microsoft.com/kb/290991

However if the previous un-installation was not clean, when trying to reinstall the sql server instance the installation may fail with error 1603.

As we have mentioned in our previous post whenever an setup installation fails we need to first check the Summary.txt to check for the Error Message which caused the failure.

——————————————————————————–
Machine : NODE01
Product : Microsoft SQL Server 2005
Product Version : 9.00.1399.06
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup001_NODE01_SQL.log
Error Number : 1603
——————————————————————————–
Machine : NODE02
Product : Microsoft SQL Server 2005
Product Version : 9.00.1399.06
Install : Failed
Log File : \\NODE01\C$\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup001_NODE02_SQL.log
Error Number : 1602
——————————————————————————–

We checked the SQLSetup001_NODE01_SQL.log which is mentioned above and looked for “Return Value 3” and reached the following part of the log which leads us to the root cause of the error

SQLSetup001_NODE01_SQL.log

=======================

MSI (s) (34:E4) [02:35:53:642]: Doing action: CAResolveInstanceName.68C6D15C_77E0_11D5_8528_00C04F68155C
Action ended 02:35:53: SQLLicensing.D20239D7_E87C_40C9_9837_E70B8D4882C2. Return value 1.
MSI (s) (34:A0) [02:35:53:642]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI32F.tmp, Entrypoint: ResolveInstanceName
Action start 02:35:53: CAResolveInstanceName.68C6D15C_77E0_11D5_8528_00C04F68155C.
FTECa.DLL: INFO: FTE: ResolveInstanceName(), Entering…
FTECa.DLL: INFO: FTE: This is NOT an Exchange Server install…
FTECa.DLL: INFO: ResolveInstanceName: FTEInstName obtained: TMSDERIV_PROD
FTECa.DLL: INFO: ResolveInstanceName: FTERudeInstName obtained: MSSQL.8
FTECa.DLL: INFO: LookupInstanceId: Iterating the key: 1
FTECa.DLL: INFO: LookupInstanceId: Iterating rude name: MSSQL.1
FTECa.DLL: INFO: LookupInstanceId: Iterating the key: 2
FTECa.DLL: INFO: LookupInstanceId: Iterating rude name: MSSQL.2
FTECa.DLL: INFO: LookupInstanceId: Iterating the key: 3
FTECa.DLL: INFO: LookupInstanceId: Iterating rude name: MSSQL.4
FTECa.DLL: INFO: LookupInstanceId: Iterating the key: 4
FTECa.DLL: INFO: LookupInstanceId: Iterating rude name: MSSQL.6
FTECa.DLL: INFO: LookupInstanceId: Iterating the key: 5
FTECa.DLL: ERROR: LookupInstanceId: RegQueryValueEx(RudeName) failed. Err = 2
Action ended 02:35:53: CAResolveInstanceName.68C6D15C_77E0_11D5_8528_00C04F68155C. Return value 3.
Action ended 02:35:53: INSTALL. Return value 3.

From the above error we see that setup is unable to resolve the GUID 68C6D15C_77E0_11D5_8528_00C04F68155C into the sql server instance name and hence the setup was failing with error 1603.

Setup was looking at the registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSFTESQLInstMap to resolve the GUID to the Instance Name.

We checked the registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSFTESQLInstMap and found there was wrong mapping for GUID 68C6D15C_77E0_11D5_8528_00C04F68155C.

IMPORTANT : This resolution contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

Resolution
========

So to resolve the issue we renamed the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSFTESQLInstMap  to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSFTESQLInstMap.old  and started the installation and the install ran fine without any errors.

Note:

This post should not be treated as the Microsoft’s Recommendation or Resolution to the problem, it is only a workaround which worked in our environment and so we would like to share it.

 cmeb76jvk4

While running your application on SQL CE v3.1 databases you may observe slowness in the performance of some of the queries especially those which involve querying the primary key.

In Sql server databases when you create a Primary Key on a column of a table, it will automatically create a unique index on the column which prevent duplicate entries to be inserted on the primary key column. This Unique index which is generated by Sql server engine automatically are called as the system generated indexes.

Ideally the statistics should be created automatically on the system generated indexes but we have observed that on SQL CE v3.1 databases statistics are not automatically created on the system generated indexes.

Due to the absence of statistics optimizer may select wrong execution which can ultimately lead to poor performance of the sql queries.

To improve the performance we need to manually create the statistics on system generated indexes using following query

CREATE STATISTICS ON <table-name> WITH FULLSCAN,NORECOMPUTE

FULLSCAN ensure that entire table is scanned to generate the statistics and we have not used any sampling.

NORECOMPUTE is used to indicate that Statistics will be updated manually and should not be triggered automatically by optimizer.

Once the statistics are created we need to manually update the statistics periodically using the following command

UPDATE STATISTICS ON <table-name> WITH FULLSCAN,NORECOMPUTE.

The above issue does not occur in SSCE v3.5 in which the statistics are automatically created on system generated indexes and hence the same query might appear to run fast on SSCE v3.5 while it appears to run slow on SSCE v3.1

If after creating and updating the statistics on the system generated indexes manually you still observe slowness in performance you might considering using other query tuning option

We have observed a number of issue now where in clustered instance of sql server 2005 fails to come online after performing changing on shared disk on a cluster.

As mentioned in my previous posts if the sql server fails to come online we need to start troubleshooting by looking at the sql server error log. So we checked the sql server error log and  observed the following error

2009-01-11 21:10:06.96 Server      Error: 26054, Severity: 16, State: 1.
2009-01-11 21:10:06.96 Server      Could not find any IP address that this SQL Server instance depends upon.  Make sure that the cluster service is running, that the dependency relationship between SQL Server and Network Name resources is correct, and that the IP addresses on which this SQL Server instance depends are available.  Error code: 0×103.
2009-01-11 21:10:06.96 Server      Error: 17182, Severity: 16, State: 1.
2009-01-11 21:10:06.96 Server      TDSSNIClient initialization failed with error 0×103, status code 0xa.
2009-01-11 21:10:06.96 Server      Error: 17182, Severity: 16, State: 1.
2009-01-11 21:10:06.96 Server      TDSSNIClient initialization failed with error 0×103, status code 0×1.
2009-01-11 21:10:06.96 Server      Error: 17826, Severity: 18, State: 3.
2009-01-11 21:10:06.96 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2009-01-11 21:10:06.96 Server      Error: 17120, Severity: 16, State: 1.
2009-01-11 21:10:06.96 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

As seen from the above Error 26054 message which says that cluster service is unable to find the dependency between the sql server instance and the sql network resource name.

We checked the Cluster Administrator for the sql server resource and found that the dependency exists between the sql server resource and sql network name but still we receive the error.

Cause
=====

We checked the Registry Location HKLM\Cluster\Resources\<SQL Server GUID>\Parameters

The values InstanceName and VirtualServerName were missing from the keys below:

HKLM\Cluster\<SQL Server GUID>\Parameters
HKLM\Cluster\<SQL Agent GUID>\Parameters

The above registry keys are used by the cluster administrator to connect and start the sql server service. So the Cluster Administrator was not able to connect and start the sql server  resource and hence we receive the above error

Resolution
========

IMPORTANT : This resolution contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

We added the following registry values in the Parameters registry key:

For a named instance of SQL Server:

• InstanceName
Value Name: InstanceName
Value Type: REG_SZ
Value Data: <instance name of the named instance>

• VirtualServerName
Value Name: VirtualServerName
Value Type: REG_SZ
Value Data: <Virtual server name of the instance>

Once we added the registry keys we were able to bring the sql server online on Node1. 

We failed the instance over to Node2 we checked the above registry keys and found they were missing, so we added missing keys and rebooted both the servers of the clusters.

After rebooting the server the sql server resources came online fine without any errors

Note:

This post should not be treated as the Microsoft’s Recommendation or Resolution to the problem, it is only a workaround which worked in our environment and so we would like to share it.

Recently I encountered a case wherein clustered instance of sql server 2000 fails to come online on other node of a 2 Node Cluster. 

Usually for such cases we need to start troubleshooting by checking the Error log of the sql server from the server where it fails to come online. The Sql Server Error log can give you the cause if the sql server service itself is not able to start.

In our case we observed that the sql server error log was clean and the sql server was able to start without any errors.

So it appears that cluster service was not able to communicate with the sql server and made an assumption that sql server service could not be started.  Cluster service tries to connect to the sql server until it gets timed out after which cluster service marks the sql resources as failed.

The next obvious step in this cases would be to check the Application event log. We checked the Application Event log and found the following error reported.

Event Type:    Error
Event Source:    MSSQL$TEST
Event Category:    (3)
Event ID:    17052
Date:        1/11/2009
Time:        1:26:01 AM
User:        N/A
Computer:    Node02
Description:
[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 11; message = [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 9c 42 00 40 01 00 00 00   ?B.@….
0008: 0a 00 00 00 4b 00 44 00   ….K.D.
0010: 43 00 44 00 53 00 51 00   C.D.S.Q.
0018: 4c 00 31 00 34 00 00 00   L.1.4…
0020: 00 00 00 00               ….   

Event Type:    Error
Event Source:    MSSQL$TEST
Event Category:    (3)
Event ID:    17052
Date:        1/11/2009
Time:        1:26:01 AM
User:        N/A
Computer:    Node02
Description:
[sqsrvres] checkODBCConnectError: sqlstate = 01000; native error = 274d; message = [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).

From the above Error we observe that Either Sql server is listening on wrong port or cluster service is trying to connect to sql on wrong port.

We checked the following location of the registry and found that sql server was listening on port 2068.

HKLM/SOFTWARE/MICROSOFT/MICROSOFT SQL SERVER/TEST/MSSQLSERVER/SuperSocketNetLib/TCP    

We also confirmed by checking the sql server error log which states as following

2009-01-11 01:28:44.07 server    SQL server listening on 10.10.0.194: 2068

We checked the port on the working Node1 and it was the same i.e 2068

So it appears that cluster was trying to connect to sql server to wrong port.

We checked the Client Network Config Utility and found that there was TCP/IP Alias created  for the sql server instance SQLCLUSTER\TEST on port 1433 while sql was listening on port 2068.

Cause
=====

Whenever we have alias created to connect to the sql server instance using tcp/ip or np protocol on clustered instance of sql server. Cluster service will use only the alias to connect to the sql instance and so if the alias has wrong definition, the cluster service will fail to connect to sql server instance and thereby will fail to bring the sql resource online.

Resolution
=======

So in our case we checked on the box "Dynamically determine port" in the client network utility and removed the static port address of 1433 in the TCP/IP alias and the sql resource came online fine on Node 2 without any errors.