Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,552,617 hits
  • Select GETDATE()

    January 2019
    M T W T F S S
    « Apr    
     123456
    78910111213
    14151617181920
    21222324252627
    28293031  
  • Advertisements

Archive for the ‘Denali’ Category

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 AlwaysOnPaperback, Kindle
  • Advertisements

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

    Now Available @ Amazon : My Book on SQL Server 2012 AlwaysOn

    Posted by blakhani on August 5, 2013


    It might come as surprise to many of you. Finally, book authored by me is available for public on Amazon. (paperback and kindle). Those who follow Vinod’s (b|t) blog have seen this announcement already.

    It has been close to an year of writing and multiple reviews, my book on “SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” is finally available on Amazon.

    Reason of this blog is to answer two common questions which would be asked by my friends – “How?” and “Why?”

    “How” –

    A little spark was ignited when me, Vinod (b|t). and Pinal (b|t) were sitting together after a User Group meeting and thinking – what can be done to share the knowledge. Pinal and Vinod were already a well-known blogger and author. I said in feeble voice – I may have some additional knowledge on AlwaysOn, how about a book on this topic? … all of us agreed and journey started.
    Thanks to Vinod and Pinal for the spark of inspiration to set me off into looking things from different perspective. I wouldn’t have thought to become an “author” without your support. 

    After take off of the project, table of content of 5 chapters were prepared and we have taken task to write everything about the feature. I got my home PC upgraded to better RAM so that I can use my home PC for Hyper-V images which were needed for the book. We targeted 100 page and during weekly checkpoint meeting we always thought “something is missing” … after reaching 500+ pages, we felt that we have covered almost everything about the feature which can bring a starter to intermediate level.  Decision was taken to increase the page size and trim down the number of pages without compromising quantity and quality of knowledge. Lots of hours spent on emails, phone calls, chat, internet research, books-online reading, blogs reading to make fine prints. Finally, after an year of hard efforts, book was ready to be published. In paperback as well as kindle version.

    “Why” –

    It feels good when someone meets and says – “You blog helped me in fixing a problem”. Being a part of SQL Server technical support team, I felt that there is not much “organized” content on AlwaysOn feature. I spoke to many folks and sensed a “fear” of this new feature. There are various blogs on the same topic and books online also has lots of content. By writing this book and sharing my knowledge , I feel that I am doing my part for the community which is driven by passionate people and MVPs.  I love technology, SQL Server and its features. There were many questions asked and being of a very tidy disposition, I liked to have them answered. That’s why we added a new chapter in the book for FAQs along and there were around 70+ questions.

    What we have in the book?

    Here is the crisp summary of the book.

    clip_image001

    18 Chapters at a Glance

    Chapter 1. High Availability and Disaster Recovery Concepts
    Chapter 2. Existing High Availability and Disaster Recovery Options
    Chapter 3. What is AlwaysOn?
    Chapter 4. Understanding Quorum Models
    Chapter 5. AlwaysOn Availability Groups
    Chapter 6. Availability Group (AG) Actions on the Secondary
    Chapter 7. Deploying AlwaysOn Availability Groups
    Chapter 8. Features of AlwaysOn Availability Groups
    Chapter 9. AlwaysOn Monitoring and Troubleshooting
    Chapter 10. AlwaysOn Diagnostics
    Chapter 11. AlwaysOn Advanced Monitoring
    Chapter 12. Deployment Variations of AlwaysOn
    Chapter 13. AlwaysOn Common Issues
    Chapter 14. Availability Group Failover
    Chapter 15. Migrating from Previous High Availability Scenarios
    Chapter 16. AlwaysOn Availability Group Maintenance Activities
    Chapter 17. AlwaysOn – FAQs
    Chapter 18. SQL Server 2014 – AlwaysOn Enhancements Teaser

     

    How can you get it?

    At this moment we don’t have any other channel to publish this book in India. Our publisher is in constant pursuit to look for alternatives, till then Amazon will the only channel.
    Print Book: Amazon
    e-Book: Kindle

    You can read first two chapters for free on amazon via LOOK INSIDE feature on kindle edition.

    image

     

    I truly hope that it would help many DBAs in the world to get started with AlwaysOn Availability Group feature and deploy as high availability + disaster recovery in the environment.

    Before I conclude this blog post, I want to specifically call out name of few people from my management team who have backed me in this adventure of learning and sharing via this book – Sri Krishna Jagannath (Team Manager – SQL Support), Rahul Jacob (Team Manager – SQL Support), Xavier S Raj (Escalation Manager – SQL Support), Ranjan Bhattacharjee (Group Manager – Developer Support). It would have been impossible to deliver this book without their unconditional support.

    Last, but not the least, Special Thanks to my wife and daughter for being supportive through out this journey!

     

    Cheers,
    Balmukund Lakhani
    Twitter @blakhani

    Posted in AlwaysOn, Denali, Introduction, SQL Server, SQL Server 2012, Step by Step, TechEd | 11 Comments »

    Help: SQL Server Management Studio is allowing me to connect only to Database Engine! What’s wrong?

    Posted by blakhani on May 10, 2012


    This blog is an outcome of one of internal email thread where SQL Server Management Studio was allowing to connect only “Database Services” and “SQL Azure”. A picture is worth a thousand words, so here it is:

    01_Disabled

    Analysis Services, Integration Services, Reporting Services are grayed out/disabled. I thought that this might be due to SSMS Express but looking at “Help” > “about” in Management Studio showed that it was full version.

    05_HelpAbout

    “Copy Info” gives you this information.

    Microsoft SQL Server Management Studio                        11.0.2100.60
    Microsoft Analysis Services Client Tools                        11.0.2100.60
    Microsoft Data Access Components (MDAC)                        6.1.7601.17514
    Microsoft MSXML                        3.0 6.0
    Microsoft Internet Explorer                        8.0.7601.17514
    Microsoft .NET Framework                        4.0.30319.237
    Operating System                        6.1.7601

     

    While doing some more research, I remember setup screen had option for Management Tools – Basic and Complete. here is the screenshot of setup screen with both features highlighted.

    02_Basic

    03_Complete

    I guess the feature description explains the behavior. If you want to find what was the option you selected, then SQL setup logs are your friend! [One more trick at the end of the blog]

    C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\DateTimeStamp\ConfigurationFile.ini

    To repro the issue, I selected only Basic and this is what you would see in ConfigurationFile

    FEATURES=SQLENGINE,REPLICATION,SSMS

    Once setup completes, you would see same behavior which for which I am writing the blog. Now, to fix the issue we need to add “Management Tools – Complete”. Launch “SQL Server Installation Center” > Installation > “New SQL Server stand-alone installation or add features to an existing installation” . Go through the Wizard and choose as below:

    06_AddFeature

    and then

    07_ChooseComplete

    Now you should see FEATURES=ADV_SSMS in ConfigurationFile.ini and once you finish the setup, you should be able to select any option.

    04_Enabled

    If you are not comfortable looking at Setup Logs, you can also use “Installed SQL Server features discovery Report” from “SQL Server Installation Center” > “Tools” Menu

    Once you click on hyperlink and launch the report, it would show something like below.

    08_Discovery Report

    So after Adding Management Tools Complete, I could see that in report.

    Hope this would help someone in the world!

    Cheers,
    Balmukund Lakhani
    Twitter @blakhani

    Posted in Denali, disabled, grayed out, Images, Management Tools Basic, Management Tools Complete, Screenshot, SQL Server 2012, SQL Server Management Studio, SSMS | 9 Comments »