Archive

Archive for the ‘SQL Failover Cluster’ Category

Missing registery settings in cluster nodes for SQL Server

October 27, 2014 Leave a comment

I run into this occasionally, I think in last 3-4 years being SQL Server PFE, I have seen this issue total of 4 times.  So its not common, issue.  So for this post, I’ll use example architecture.  Two-Node Cluster, Node A and NodeB running SQLFCI1 on it.  SQLFCI1 runs fine on NodeA but fails on NodeB.  Looking at the Application Log we see strange messages like “Could not open error log file ”“.  Other messages might around missing various configuration settings that SQL Server needs to start up.  So how can that happen?

When SQL Server is running as a Failover Cluster Instance (FCI); its configuration settings (a.k.a registry keys under HKLM\Software\Microsoft\Microsoft SQL Server) are saved in a cluster hive in registry.  So when the node fails over from active to passive these settings get carried over and applied to passive node.  That is why we have best practice to make all configuration settings on active node only, if you make it on passive node, or if instance is offline.  The Cluster Service will over write them with what it know of the settings.  This is called CheckPoint process.

We can check if all the required SQL Server keys being copied to cluster hive or not.  We can do that from Command Prompt using following command:

cluster.exe . res “SQL Network Name (SQLFCI1)” /CheckPoints

You will get a output similar to below:

Listing registry checkpoints for resource ‘SQL Network Name (SQLFCI1)’…

Resource                   Registry Checkpoint
————————– —————————————————————————-
SQL Network Name (SQLFCI1) ‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\Cluster’
SQL Network Name (SQLFCI1) ‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQLServer’
SQL Network Name (SQLFCI1) ‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\Replication’
SQL Network Name (SQLFCI1) ‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\Providers’
SQL Network Name (SQLFCI1) ‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\SQLServerSCP’
SQL Network Name (SQLFCI1) ‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\CPE’
SQL Network Name (SQLFCI1) ‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\SQLServerAgent’

However if you get only some or no record back we have an issue.

PLEASE NOTE DO THIS ON NODE THAT IS WORKING.  IF YOU DO IT ON NODE THAT IS NOT, YOU WILL LOSE ALL YOUR REGISTRY SETTINGS.

  1. Backup the HKLM\Software\Microsoft\Microsoft SQL Server\ hive on both NodeA and NodeB (just in case, you ignore my warning above/ or murphy’s law kicks in).
  2. Confirm instance is on NodeA, if not failback to NodeA from NodeB (NodeA was the good guy in my scenario above).
  3. Execute following commands to add each of the key registry settings to cluster checkpoint.

cluster.exe . res “SQL Network Name (SQLFCI1)” /Addcheckpoint:”SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\Cluster”
cluster.exe . res “SQL Network Name (SQLFCI1)” /Addcheckpoint:”SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQLServer”
cluster.exe . res “SQL Network Name (SQLFCI1)” /Addcheckpoint:”SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\Replication”
cluster.exe . res “SQL Network Name (SQLFCI1)” /Addcheckpoint:”SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\Providers”
cluster.exe . res “SQL Network Name (SQLFCI1)” /Addcheckpoint:”SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\SQLServerSCP”
cluster.exe . res “SQL Network Name (SQLFCI1)” /Addcheckpoint:”SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\CPE”
cluster.exe . res “SQL Network Name (SQLFCI1)” /Addcheckpoint:”SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\SQLServerAgent”

After this re-run the /CheckPoints command above to verify they were added successfully.

SQL Server Integration Services Issue (Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum))

January 27, 2010 Leave a comment

TITLE: Microsoft SQL Server Management Studio
——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic “Configuring the Integration Services Service” in Server 2005 Books Online.

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2].  (MsDtsSrvr)

——————————

I have used SSIS for ETL process few times; but on stand alone instances only.  Never tried to do SSIS in a Cluster environment.  This particular issue can happen in a standalone environment also because SSIS services by default cannot handle named instances.

SSIS also is not cluster-aware and is single instance application (i.e. only one copy of SSIS can run on server at a time).  I read the reference articles [1] and [2] to investigate my options.  I decided to do following to make SSIS Packages management and controlled per SQL instance in cluster and named instance environment.  Current configuration is Active-Active 2-Node Cluster with both instances using Named Instances.

1. Go to C:\Program Files\Micrsoft SQL Server\90\DTS\Binn\ directory.
2. Open MsDtsSrvr.ini.xml file.

You will something similar to following:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
   <TopLevelFolders>
      <Folder xsi:type="SqlServerFolder">
         <Name>MSDB</Name>
         <ServerName>.</ServerName>
      </Folder>
      <Folder xsi:type="FileSystemFolder">
         <Name>File System</Name>
         <StorePath>..\Packages</StorePath>
      </Folder>
   </TopLevelFolders>
</DtsServiceConfiguration>

We need to do couple things in the file for it to work with our cluster:

  • Add the information for each SQL Instance in your Cluster (e.g. for me SQLCluster01p and SQLCluster02p)
  • Update it to make sure it referring to Named Instance instead of one default instance (e.g. for me Instance01 and Instance02)

So I modify the file to following:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
   <TopLevelFolders>
      <Folder xsi:type="SqlServerFolder">
         <Name>MSDB_SQLCluster01p_Instance01</Name>
         <ServerName>SQLCluster01p\Instance01</ServerName>
      </Folder>
      <Folder xsi:type="SqlServerFolder">
         <Name>MSDB_SQLCluster02p_Instance02</Name>
         <ServerName>SQLCluster02p\Instance02</ServerName>
      </Folder>
      <Folder xsi:type="FileSystemFolder">
         <Name>File System</Name>
         <StorePath>..\Packages</StorePath>
      </Folder>
   </TopLevelFolders>
</DtsServiceConfiguration>

If you notice I changed the first instance and added a 2nd instance.  Now when you launch SSIS on either cluster you will see something like this in your SSMS:

Please note even though we did change the name of the SQLServer folder; the information is still being stored in MSDB database on the instance entered in the ServerName property.

Now I did it this way to allow me to manage the SSIS packages remotely and monitor their execution.  But if you don’t need to worry about stopping SSIS packages; then you can simply store it on a network location or in SSIS Package Storage.

References:
[1] Configuring the Integration Services in Cluster Environment, Link.
[2] Configuring the Integration Services Service, Link.
[3] Different ways to execute a SQL Server SSIS package, Link.
[4] Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service, Link.

%d bloggers like this: