Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,158,201 hits

  • Select GETDATE()

    April 2026
    M T W T F S S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

Archive for 2014

« Previous Entries
Next Entries »

Solution : Cannot add new node – Rule "SQL Server Database Services feature state" failed.

Posted by blakhani on September 11, 2014


While deploying SQL Server 2014 cluster in my lab I ran into this problem when I was trying to add second node (Node 2) to SQL cluster.

—————————
Rule Check Result
—————————
Rule "SQL Server Database Services feature state" failed. The SQL Server Database Services feature failed when it was initially installed. The feature must be removed before the current scenario can proceed.
—————————
OK
—————————

Above error means that when I installed SQL Server on first cluster node, it didn’t install properly. There were some errors while creating cluster and due to that AddNode is blocked. In my case also this is true because I had an issue with SQL Server Network Name Resource and it didn’t come online earlier. Below was the original error on Node 1

Cluster network name resource ‘SQL Network Name (Balmukund)’ cannot be brought online. The computer object associated with the resource could not be updated in domain ‘MyDomain.com’ for the following reason:
Unable to update password for computer account.

The text for the associated error code is: Access is denied.

The cluster identity ‘WinCluster$’ may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain.

I fixed above error by giving proper permission to cluster computer object on Balmukund (which was the network name of SQL) After fixing that I created SQL Server and SQL Agent manually. Sometime it may also happen that SQL Agent didn’t come online on Node 1 due to some failure and it was fixed after setup was completed.

So, if we see above rule failure then we should go to the first node and check setup log files to see if the installation succeeded or failed. If the first node installation had some failures which were fixed later then we should do a repair of the installation. Repair option can be found under the "Maintenance" page in SQL Server Installation Center (setup).  This action will clear the failure state and allow the node addition action.

If you are lazy like me to do repair then you can also check registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\ConfigurationState

If you see value as 2 for any component then there were some failures of those components. In most of the cases since issue would be with SQL Server or SQL Agent, we might see 2 for MPT_AGENT_CORE_CNI, SQL_Engine_Core_Inst, SQL_FullText_Adv, SQL_Replication_Core_Inst

Note: that we MUST fix the error on first node before taking shortcut of registry value. Once error is fixed, we can make the value of those component to 1 (means success)

The highlighted value MSSQL12.SQL2014 might vary based on SQL version and Instance name. The first piece for SQL 2008 it would be MSSQL10, for SQL 2008 R2 it would be MSSQL10_50, for SQL 2012 it would be MSSQL11 and for SQL 2014 it would be MSSQL12. Second piece is instance name (for me instance name is SQL2014)

Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Installation, Troubleshooting | Tagged: , , , | 2 Comments »

    Tips and Tricks: One Query – Multiple Servers – One Result Set

    Posted by blakhani on September 9, 2014


    How many times you have been into a situation where you need to run one query across many servers? I have asked his question from one of the DBA and his answer was I will create a linked server and then modify the query to refer linked server. Well this needs modification to server configuration and most of the production server don’t allow changed without change request.

    The better approach would be to use an under-estimated feature of SQL Server Management Studio called as Multiserver query. This can be done via by creating a group and registering one or more registered servers within the groups. Once group is created then we can query the complete group and the result format can be customized. Pinal (b|t) has written a blog here. This is a extension of that blog in little more details. 

    To get to below option we need to open “Registered Servers” Window. If you don’t see it hit Ctrl+Alt+G or go to View > Registered Servers. I have registered two server under a group called “Fake Production”. We can created your own hierarchy to logically group the servers. Display Name can also be changed in this windows. I have given a name as “HiddenName” to one of my instance for display purpose. Effectively I have 4 Servers under “Local Server Groups” and 2 Servers under “Fake Production Group”

    We can right click on any of the node and choose “New Query” as shown below.

    First thing you would notice is that the color of status bar is changed. Also notice that we can see how many servers are connected out of total servers. For demo purpose I have used different account to connect each server that’s why we are seeing “<various logins>”

    Color Customization can be done by going to Tools > Option > Text Editor > Editor Tab and Status Bar as shown below.

     

    From above, we can also change other setting for status bar but they are not specified to multi-server.

    If we ran a query in this query window, it would run on all the servers and give results.

    We are seeing same number of databases on 1st and 4th row in my lab because they are same servers. dot (.) and (local) both means local server.

    This result can be customized using “Tools > Option > Query Results > SQL Server > Multiserver Results” as below

    Note: We can’t join two tables across the servers using multiserver query feature.

    Hope this would help in getting some quick reports from various servers.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Tips and Tricks | Tagged: , , | Leave a Comment »

    Solution: Unable to launch SQL Server Configuration Manager – Invalid class [0x80041010]

    Posted by blakhani on September 4, 2014


    While launching SQL Server Configuration Manager on one of my machine, I got below error

    image

    Here is the text of the error message:

    —————————
    SQL Server Configuration Manager
    —————————
    Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
    Invalid class [0x80041010]
    —————————
    OK  
    —————————

    There might be various reason for this error. In this case our actual problem is “Invalid class” which I have highlighted above. I have looked further and found that below is the solution for me. Same solution also works for Invalid namespace [0x8004100e] error also. 

    image

    C:\WINDOWS\system32>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof"
    Microsoft (R) MOF Compiler Version 6.3.9600.16384
    Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
    Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof
    MOF file has been successfully parsed
    Storing data in the repository…
    Done!

    MofComp is a command line utility to compile MOF (Managed Object Format) files and store the data in WMI repository. The MOF Compiler is available in the %Windir%\System32\wbem directory. So if you are getting “‘mofcomp’ is not recognized as an internal or external command then try changing current directory from command prompt to %Windir%\System32\wbem

    Also note that in mof file on my machine is under 120 folder. Depends on SQL version installed, you may have it in different folder. The value of that number depends on the version of SQL Server.

    Microsoft SQL Server 2014

    120

    Microsoft SQL Server 2012

    110

    Microsoft SQL Server 2008 R2

    100

    Microsoft SQL Server 2008

    100

    Microsoft SQL Server 2005

    90

     

    Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Error, Troubleshooting | Tagged: , , , | 31 Comments »

    « Previous Entries
    Next Entries »