Sql Server Faq

 

We have observed a number of cases where the installation of the sql server 2005 instance on the cluster fails with the following unexpected Failure Error Message while performing SCC checks.

ERROR MESSAGE:

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

There is unexpected failure.

MSGID 50000

 

In order to troubleshoot the issue we followed the following steps

1)       We checked the Setup Bootstraps Logs which are located in c:\Program files\Microsoft sql server\90\Setup BootStrap\LOGS\Files.

2)       We checked the Core(Local).log and found the following error was reported in the error log

 

<Func Name=’PerformDetections’>

1

Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.3042.0

Failed to enumerate disks.

HRESULT: 0×80070015

The device is not ready.

Error: Action “InvokeSqlSetupDllAction” threw an exception during execution.  Error information reported during run:

  Source File Name: datastore\clusterinfocollector.cpp

Compiler Timestamp: Sat Oct  7 09:43:48 2006

     Function Name: ClusterInfoCollector::collectClusterGroups

Source Line Number: 391

———————————————————-

Failed to determine disk information

              Source File Name: datastore\clusterinfocollector.cpp

            Compiler Timestamp: Sat Oct  7 09:43:48 2006

                 Function Name: ClusterInfoCollector::collectClusterGroups

            Source Line Number: 390

            ———————————————————-

            Enum*Disks() returned : 2147942421

WinException : -2147024875

        Error Code: -2147024875

Windows Error Text: The device is not ready.

 

  Source File Name: datastore\clusterinfocollector.cpp

Compiler Timestamp: Sat Oct  7 09:43:48 2006

     Function Name: ClusterInfoCollector::collectClusterGroups

Source Line Number: 390

 

Error: Failed to add file :”C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0020_WPSQL1_.NET Framework 2.0.log” to cab file : “C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0020.cab” Error Code : 2

Error: Failed to add file :”C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0020_WPSQL1_SCC.log” to cab file : “C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0020.cab” Error Code : 32

Running: UploadDrWatsonLogAction at: 2008/8/30 10:16:17

Message pump returning: 2147942421

 

 

3)       From the above error log it was clear that setup.exe was not able to query the information about the Physical Disks.

 

Failed to enumerate disks.

HRESULT: 0×80070015

The device is not ready.

Error: Action “InvokeSqlSetupDllAction” threw an exception during execution.  Error information reported during run:

  Source File Name: datastore\clusterinfocollector.cpp

Compiler Timestamp: Sat Oct  7 09:43:48 2006

     Function Name: ClusterInfoCollector::collectClusterGroups

Source Line Number: 391

———————————————————-

Failed to determine disk information

 

Cause

======

The above error is reported when either some of the physical Disk resource is Offline or is not Online from the Active Node where setup was run.

 

Resolution

========

So in order to resolve the issue we need to ensure the Disk Resource is Online from the Node from which we are installing the sql server instance in the cluster.

Once the Disk resource is brought online, the sql server 2005 installation runs fine on the cluster

 

 

 

 

 

 

In sql server the default instance has a listener which listens on the fixed port which is TCP port 1433. However for the named instance the port on which the sql server listens is random and is dynamically selected when the named instance of the sql server starts.
So when we connect to the default instance of the sql server the connections goes to the TCP port 1433 while when we connect to the named instance of the sql server the connections goes to the sql browser service which listens on the UDP port 1434. The sql browser service replies to the client with port no. on which the named instance of the sql server listens at that point of time and then the client is redirected and connects to the port no. provided by sql server service.
You may require to change the dynamic port of the named instance to the static port no. so to implement Firewall in your organization so that clients connect to the sql server through the firewall.
For Standalone instance of the sql server we can change the dynamic port of the named instance to the static port by using sql server configuration manager in sql 2005. In order to change the port we need to the sql server configuration manager.
Start->Program Files-> Microsoft sql server 2005->Configuration Tools->sql server configuration manager.

In configuration manager console, on the left hand panel, expand the sql server 2005 Network Configuration.

Click on the”Protocols for <Named-Instance-Name>”. On the right hand panel we need to right click and go to properties on the TCP/IP.

Click on the Ip Addresses tab on the top and keep the “Dynamic ports” row BLANK and write the desired port no. on which you want the named instance to listen on “TCP ports” row in the “IP ALL “section.

After performing these activities we need to restart the sql server services for the changes to take affects.

After restarting the service you can confirm that whether changes has taken affect by checking the following registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name> \MSSQLServer\SuperSocketNetLib\Tcp
This key should contain the port no. defined by you.

However in case of the cluster, many a times (In particular when sql server 2005 32 bit is installed on the 64 bit system) it is observed that when we change the port no. of the named instance to the static port using the method described above the port no. again changes back to the dynamic port after you restart the services.

This is behavior can be explained as follows

When we changed the dynamic port of the sql server to the static port and stopped the sql server service the local copy of the registry which contains the dynamic port no. gets checkpointed to the quorum.

Now when the service is started the Checkpointed value from quorum are copied back to the registry and hence in spite of changing the value to static port. The value was replaced by the dynamic port from quorum.

So in order to change the dynamic port of the server to the static port in the clustered named instance of the sql server 2005 we need to follow the following steps

1) Take the sqlserver service offline from cluster administrator

2)  Disable the checkpointing to the quorum using the following command

Cluster res “SQL Server (TEST2005)” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER”

3) Change the Dynamic port of the sql server to static port  on all the nodes using the method described above.

4) Enable the checkpointing to the quorum using the following command

cluster res ” SQL Server (TEST2005)” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER”

5) Bring the sql server service online

In the above example “TEST2005” is the name of my sql server instance
                                          MSSQL.2  is since it is second sql server 2005 instance installed on the system

You will observe that the dynamic port is changed to the static port.

 

 

While installing SP2 on the sql server 2005, the installation may fail. Whenever the sql server installation fails it creates a log in the following location.
C:\Program Files\Microsoft Sql Server\90\Setup Bootstrap\LOG\

The above location generally contains following folders and file.
1) Files                (Contains Logs for the installation of the RTM (Release to Market) version of the sql server)
2) HotFix             (Contains Logs for the installation of the Hotfixes and service packs)
3) Summary.txt   (Contains summary of all the sql server products which were installed and                               whether they  Successfully installed or Failed) 

                                  
These logs are created for any sql server 2005 setup installation which applies to the installation of the RTM version or any of the service packs and even all the editions of the sql server 2005 viz Standard Edition, Express Edition, Enterprise Edition.
Whenever the sql server 2005 setup fails we need to check the summary.txt located in the above location to get the information for cause of the failure and to know which products were unsable to install.
So during the installing of the sql server 2005 sp2 if the installation fails and when you look at the summary.txt, you may find that one of the cause of the failure is ERROR 29506.
The summary.txt may have the following information
———————————————————————————-
Product                   : Database Services (MSSQLSERVER)
Product Version (Previous): 1399
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number              : 29506
Error Description         : MSP Error: 29506  SQL Server Setup failed to modify security permissions on file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that  exists on the destination drive.

 

When you look into the file C:\Program Files\Microsoft SQL Server\90\Setup  Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log you will see the following error

 

Configuring ACL:
 Object: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data   ACL: (A;OICI;FA;;;[SQLServer2005MSSQLUser$CORPDBPRO$MSSQLSERVER])
Action: 0×103
Failed ACL:
  SetSecurityFileDescriptor is failed at the error code 5; Converted SDDL: ‘(A;OICI;FA;;;S-1-5-21-3714694515-4057322299-1779769068-1013)’
        Error Code: 0×80077342 (29506)
Windows Error Text:   Source File Name: sqlca\sqlsddlca.cpp
Compiler Timestamp: Wed Jun 14 16:27:11 2006
     Function Name: ExceptionInSDDL
Source Line Number: 65

 

The main cause of the above error is that there is improper permissions of the DATA and LOG folder of the sql server 2005. In other words the DATA and LOG folders didn’t have Full Control permissions for the user Administrator and as a result the error message says
MSP Error: 29506  SQL Server Setup failed to modify security permissions on file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ for user Administrator

In order to avoid the error we need
1) To start the SP2 installation using an administrator or a user with administrative privileges.

2) All the files and the folder contained in the DATA and LOG must have Administrative privileges with FULL CONTROL permissions.
In order to achieve this right click on the DATA and LOG folder go to sharing and security. Click on the security tab and make sure Adminstrator is present and it has FULL CONTROL. Also go to the advanced tab and click on inherit the permissions to the child objects.
Finally confirm that all the folders and files located inside DATA and LOG folder have Full Administrator priviledge.
Once the permission issue is resolved we can restart with the installation and it will work fine

Reference
http://support.microsoft.com/kb/916766/en-us