Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,601,709 hits
  • Select GETDATE()

    April 2019
    M T W T F S S
    « Apr    
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  
  • Advertisements

SQL Cluster Setup Error – System.Runtime.InteropServices.COMException (0x80070490): Element not found

Posted by blakhani on May 19, 2015


My job revolves around troubleshooting and fixing the broken thing. Here is one of the situation which I ran into recently and was unable to find solution on internet. It my responsibility to provide self-assist option to the SQL community so that they can find the problem and fix by themselves.

I was trying to install SQL Server 2012 on a 2 nodes Windows cluster. When I tried installing it, it failed with error in subject line. At this first look it sounds like some COM+ error but as always, setup logs are my first place to find the errors. Here is the MSDN link which explains the various files created by setup

https://msdn.microsoft.com/en-us/library/ms143702(v=sql.110).aspx (View and Read SQL Server Setup Log Files)

The information which I saw in setup logs was pretty interesting. In particular, I looked into Detail.txt file which is the parent file of all MSI logs. (I have removed date and time for better reading)

Error: Action "Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction" threw an exception during execution.
Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Element not found. (Exception from HRESULT: 0x80070490) —> System.Runtime.InteropServices.COMException (0x80070490): Element not found. (Exception from HRESULT: 0x80070490)
   at Microsoft.SqlServer.Interop.MSClusterLib.ISClusResource.get_Disk()
   at Microsoft.SqlServer.Configuration.Cluster.ClusterPhysicalDisk.get_Partitions()
   at Microsoft.SqlServer.Configuration.ClusterConfiguration.ClusterDiskPublicConfigObject.IsPathOnSharedDisk(String path)
   at Microsoft.SqlServer.Configuration.SetupExtension.SlpInputSettings.ValidateNotOnSharedDisk(ValidationState vs, String directoryName, String bindingKey, String errorMessage)
   at Microsoft.SqlServer.Configuration.SetupExtension.SlpInputSettings.Validate_InstallSharedDir(ValidationState vs)
   at Microsoft.SqlServer.Configuration.SetupExtension.SlpInputSettings.ValidateSettings()
   at Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction.ExecuteAction(String actionId)
   at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun)

 

The stack goes from bottom to Top. If we read the function calls made, anyone can conclude that there is something happening with cluster disks. That’s a good hint. So, I went back to failover cluster manager and looked into Disks under “Storage”. There was a disk which was in failed state. I was not able to bring this online and that’s THE problem! SQL Setup would enumerate the disks to find eligible disks which can be used and it’s not able to find details about that disk. Here was the error when I attempted to bring it online.

The resource ‘Cluster Disk 1’ did not come online.
The desired state change for ‘Cluster Disk 1’ did not occur before the timeout expired.

I realized that I have played with iSCSI and messed up the disk which was presented.

Solution: Delete the disks which are not able to come online under “Storage > Disks” or “Available Storage” in failover cluster manager interface.

Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in Cluster, Installation, Setup, SQL Server 2012, SQL Server 2014 | Tagged: , , , , | 3 Comments »

    Latest News : SQL Server 2016 announced – New Features and Enhancements

    Posted by blakhani on May 6, 2015


    From past SQL Server releases, there have been pattern of 2 years release. Here are the release date since SQL 2008 (taken from lifecycle page)

    SQL Server Version Lifecycle Start Date
    Microsoft SQL Server 2008 11/6/2008
    Microsoft SQL Server 2008 R2 7/20/2010
    Microsoft SQL Server 2012 5/20/2012
    Microsoft SQL Server 2014 6/5/2014

     

    Looking at this pattern and trend, anybody would have guessed that next release of SQL would be in year 2016. Recent announcement at Microsoft Ignite event confirmed the same. Here is the blog of the announcement made by our CEO Satya. http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx

    Here are my favorite features taken from sources listed at the end of the blog.

    • Enhancement in AlwaysOn Availability Group:
      • Round-robin load balancing – In SQL Server 2016, it would be possible to define routing of secondary on round robin basis. This would help in load-balancing. If you have worked with SQL Server 2012 and SQL Server 2014 the you would recall that when read-only routing is defined and application makes connection to Listener via read-only intent, the connection would go to first replica in routing list. In SQL Server 2016, we would have option to route requests to various secondary replica in round robin fashion.
      • DTC Support : SQL Server 2016 would support distributed transactions and cross database transactions when database is an availability database. As per earlier documentation, this scenario was unsupported. (Books online, blog)
      • Automatic failover based on database health: This was one of the ask from community (refer this connect item). Imagine a situation where databases are part of AG and the drive containing LDF of one database has gone bad. This would cause database to go unavailable BUT availability group would report healthy. This has been taken care in SQL Server 2016.
      • Up to 3 synchronous replicas for auto failover: In earlier versions there was only one “auto” failover partner. Here was can have more than one. So in case, two servers are down, still the automatic failover can happen.
      • Simplified Add Azure Replica Wizard: This would help in having hybrid deployment of AlwaysOn Availability Groups.
    • Stretch Database: This feature allows “hot” part of a table to be in on-premise SQL Server and cold part would be move to Azure SQL Database automatically. Good part is that it would be transparent to the end user.
    • Security Enhancements
      • Row Level Security: This is another security big win. Now we can control the rows which are visible to user based on the rights given to the logged in user. It is available in SQL Azure Database. (documentation)
      • Always Encrypted: Data on the wire would be encrypted.
      • Dynamic Data Masking: Data obfuscation to avoid unauthorized access. It is already available in SQL Azure Database. (Video, Blog)

    Dynamic Data Masking (Image taken from Channel9 Video)

    • Performance Improvements:
      • Query Data Store: Query plans can be stored along with various statistics in permanent tables. This would help in finding various query and what was the plan used. Parameter sniffing issue would be easy to track and kill.
      • Enhancements in In-memory OLTP: There were some limitations in SQL Server 2014 about In-Memory OLTP and they have been removed.

    There are many more features which would help customers in getting breakthrough mission critical performance, deeper insights across any data on many devices and enable the power of hyperscale cloud to unlock new hybrid scenarios. (quote taken from datasheet)

    Sources:

    My Plan: As soon as public preview is out, I would start learning series for SQL Server 2016. This would be similar to what I have done earlier for SQL Server 2014.

    Stay Tuned!

    Posted in SQL 2016 Learning Series | Tagged: , , , | 2 Comments »

    Information : Checkpoint in SQL Server Cluster Resources

    Posted by blakhani on April 17, 2015


    If you have searched for checkpoint in SQL Server and landed on this page, then you might get disappointed. This blog is NOT about the checkpoint background process which executes in SQL Server. This blog is about the checkpoint which is windows cluster piece.

    Checkpoint Manager is the component of Cluster Service which monitors the changes in the Resources on a Cluster. In simple words, checkpoint is a process in cluster which synchronize the registry key values between the nodes. SQL Server startup depends on the registry key values. By default there are three registry keys: –d for path of master.mdf, –l is for the location of mastlog.ldf fie and –e is for the location of SQL Server ERRORLOG file. More startup parameters can be added based on requirement. For example if you are troubleshooting deadlock then you may add –T1222 as startup parameter. In case of cluster, you make change to registry key by using SQL Server Configuration Manager (a.k.a. SSCM). Once value is saved in SSCM, it would be saved in registry. Once failover is performed, the same change would be applied to other node. This is done by cluster checkpoint.

    What would happen if checkpoints are not defined? The changes made on active node would not propagate to other nodes during failover. Another interesting point which you must note is that if any change is made to registry key when resource is offline, the changed would be reverted back. When the resource goes offline, the values are saved into registry.

    Since this is windows piece, there are windows commands and PowerShell cmdlets available to view and edit them. In earlier version of windows, cluster.exe was used to view and edit the checkpoints and in newer version of operating systems where admins loves PowerShell, there are cmdlets exposed. Here is the list of commands to add checkpoint

    Old Version (if you have cluster.exe)

    cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Cluster"
    cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\MSSQLServer"
    cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Replication"
    cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Providers"
    cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerSCP"
    cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\CPE"
    cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerAgent"

    PoweShell

    Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Cluster"
    Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\MSSQLServer"
    Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Replication"
    Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Providers"
    Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerSCP"
    Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\CPE"
    Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerAgent"

    Here are the command to get list of checkpoint (my Virtual Server Name is RajaSQLServer).

    cluster res "SQL Network Name (RajaSQLServer)" /checkpoints

    Here is the PowerShell command

    Get-ClusterCheckpoint

    You would notice the difference in registry key values as compared to the command. This is because of the fact that the command is for SQL 2014 that’s why we are seeing “MSSQL12”. The exact value can be fetched from registry.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Checkpoint, Cluster, SQL Server | Tagged: , , , | 1 Comment »