Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID ‘ ‘) online (Error code 5018).

Problem description

You might get below error when configuring Always on availability group If the cluster services startup account is not part of SQL Server logins.

Error:

TITLE: Microsoft SQL Server Management Studio

——————————

Creating availability group resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

——————————

ADDITIONAL INFORMATION:

Create failed for Availability Group ‘KK_AG’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3373.0+((SQL11_SP1_QFE-CU).130629-2102+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroup&LinkId=20476

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID ’74eaaa74-4e7d-470a-96b4-29459cb4516e’) online (Error code 5018). The WSFC service may not be running or may not be accessible in its current state, or the WSFC resource may not be in a state that could accept the request. For information about this error code, see “System Error Codes” in the Windows Development documentation.

Failed to designate the local availability replica of availability group ‘KK_AG’ as the primary replica. The operation encountered SQL Server error 41066 and has been terminated. Check the preceding error and the SQL Server error log for more details about the error and corrective actions.

Failed to create availability group ‘KK_AG’. The operation encountered SQL Server error 41160 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41066)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3373&EvtSrc=MSSQLServer&EvtID=41066&LinkId=20476

You would also see errors similar to one below when you configure always on availability group using scripts.

Msg 41131, Level 16, State 0, Line 1

Failed to bring availability group ‘TDE_AG’ online. The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

Msg 41152, Level 16, State 2, Line 1

Failed to create availability group ‘TDE_AG’. The operation encountered SQL Server error 41131 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

Msg 41131, Level 16, State 0, Line 1

Msg 41152, Level 16, State 2, Line 1

Resolution:

Add start up account of cluster service to SQL Server login and grant sysadmin role (Start up account of cluster service will be nt authority\system by default).

SQL Server cluster installation checklist

List of checks you may need to perform before you install SQL Server Cluster.

1. Verify your system meets the minimum requirements
910228 SQL Server 2005 Readme and installation requirements
http://support.microsoft.com/default.aspx?scid=kb;EN-US;910228
907284 Changes to the readme file for SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;907284
327518 The Microsoft support policy for a SQL Server Failover cluster – Please note that ONLY Microsoft Failover Clusters are supported.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;327518
SQL Server 2008 Before Installing Failover Clustering
http://download.microsoft.com/download/4/5/3/453739ed-cb74-46b1-b7af-f157ee71db20/SQL2008Readme.htm.
http://msdn.microsoft.com/en-us/library/ms189910%28v=SQL.100%29.aspx

2. Log on as an Administrator.
SQL Server 2005/2008: Log on to the cluster node as a domain administrator that is also local administrator on all nodes, but not necessarily an account that is to be used as a service startup account for the SQL Server services.

Pre create user groups in active directory for different SQL Server 2005/2008 services(SQL,Agent,FT).SQL Server setup will add the service accounts of SQL Services to this groups during setup. You have to pre create unique group name for each Service in AD and enter it when prompted during SQL Server2005/2008 Cluster setup.
SQL Server Setup grants the following Windows NT rights and privileges to the Group you enter during setup (If you can not run the setup as domain administrator then add the service logon accounts to the security groups created in AD before the setup).
For SQL Server group
Log on as a service (SeServiceLogonRight)
Act as part of the operating system (SeTcbPrivilege) (only on Windows 2000)
Log on as a batch job (SeBatchLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Permission to start SQL Server Active Directory Helper
Permission to start SQL Writer
and many more
For SQL Server Agent group
Log on as a service (SeServiceLogonRight)
Act as part of the operating system (SeTcbPrivilege) (only on Windows 2000)
Log on as a batch job (SeBatchLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

3. There should not be a pending Reboot requirement

Check pending Reboot requirement in following registry:
HKLM\SYSTEM\CurrentControlSet\Control\Session Manager
In the right-hand pane, is there a value of PendingFileRenameOperations? If so, is it empty?
If empty close the registry editor. If not empty, reboot the machine prior to running setup. After logging in, re-check this registry value to make sure it is empty.

4. Verify Cluster “Heartbeat”
For a Failover Clustered Instance installation on Windows 2003 or earlier, verify cluster networking was setup per:
258750 Recommended private “Heartbeat” configuration on a cluster server
http://support.microsoft.com/?id=258750
NOTE: This needs to be followed explicitly in regards to the binding orders and your heartbeat adapter’s configuration.

5. Verify the TCP\IP Advanced DNS settings are identical on the “Public” network adapters between all Failover cluster nodes

6. Stop All Non- Microsoft Services Excluding services required by SAN or network adapters online

7. Make sure below services are running on all node’s
Cryptographic Services <====This must be running on all nodes.
DNS Client
Event Log
Remote Registry Service <====This must be running on all nodes.
Task Scheduler
Windows Management Instrumentation Driver Extensions
Windows Management Instrumentation

8. Flush Cached DNS Entries
Flush the DSN cache.

Ipconfig /flushdns
then… Referesh te netbios name registered
Nbtstat –RR

9. Verify Logon Server
Open a command prompt on each node and enter the command:

set logonserver

Verify if logon server is same on all nodes.

10. Verify Admin Shares
IPC$
C$
Admin$
DriveLetter$
DriveLetter = Disk in which you have setup files.

Use the “Shared Folders” MMC snap-in to verify the existing Administrative shares.

11. Only One RDP Session

Make sure no one else is logged in to any of the nodes. There should be only one logon session in active node which we use for setup.
12 . Make sure we are able to failover SQL Server groups to all nodes.

13. Cluster MSDTC
Create MSDTC resource.

14. If windows2008+ Grant “Create Computer Objects” permission for the computer object created for the cluster (Computer Name object(CNO)).

http://mssqlwiki.com/2009/03/26/installation-of-sqlserver20052008-fails-on-windows2008-cluster/

15. For Windows 2008, see How to configure DTC for SQL Server in a Windows 2008 cluster http://blogs.msdn.com/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx
Use the following KB article to Cluster MSDTC if not already clustered on Windows 2003:
301600 How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster
http://support.microsoft.com/default.aspx?scid=kb;EN-US;301600

16. Grant Read SPN and write SPN permission for startup account of SQL Server (or) Security group provided during installation for SQL Service startup account.

17) Verify Dependencies http://support.microsoft.com/default.aspx?scid=kb;EN-US;835185

HOW TO INSTALL SQL Server CLUSTER IN HYPER-V

To install SQL Server failover cluster we need to have windows cluster configured with a shared disk.

Step 1

If you do not have a windows cluster configured you can create using Hyper-v

Follow the steps in “HOW TO CREATE CLUSTER USING HYPER-V” to create Domain controller and two member servers which will act as nodes of the cluster.

Once the domain controller and two member servers are created proceed to step 2

Step 2

Once the domain controller and nodes are created install failover cluster feature and create cluster

To install the failover cluster feature and create a cluster follow the steps in “How to install cluster in windows 2008 and windows 2012“

Step 3

Once the cluster is created we have to create shared Disk for installing failover aware clustered application like SQL Server.

Follow the steps in ”How to create shared disk using iSCSI Software Target” to create a shared disk using iSCSI target software.

Once shared disk is configured invoke Setup from SQL server installation media .Make sure the node in which you perform the installation has shared disk online.

Run the setup.

Start the installation by selecting New SQL Server Failover Cluster Installation option.

Setup support rules will analyze and identify the problems that might occur during setup installation.

After setup validation ,you will get details about operations completed,Click Next and continue

On the Product key page, specify the Product ID key of the product. Click Next.

Then read and select the check box to accept terms and agreements.Click Next.

Select the developer features which you want and click Next.

Perform instance configuration.Give SQL server network name.Either you could have default instance or named instance.But you could have only one default instance.

Click Next.

Allocate required disk space for your installation.Click Next.

Select the SQL Server cluster resource group name.Click Next and continue.

Cluster disk selection will help us to view the disk we have selected for SQL Server failover cluster.

Configure the network resource for your SQL Server failover cluster.Select DHCP check box if you are going to use DHCP network.Otherwise give the IP address for your network(IP address which you provide not be in use currently).

Select the database engine domain group and SQL Server agent domain group. These groups have to be pre created in AD before the installation and startup account of SQL Server services will be added to these groups. You can also use service SIDs option.

Specify the account name and password for SQL server agent and SQL server database engine.Click Next.

Specify the authentication mode for your SQL Server failover cluster.Click Next and continue your installation.Verify the cluster installation rules.

Enable error reporting service if you want or else,click Next and continue.

Cluster installation rules will run the setup to determine whether any operations will block the installation.After operation completes.Click Next and continue.

Ready to install page will display the summary of options which we have selected during the setup process.If you want to make any changes,go back and make the required changes or else Click Install and start the installation.

Click Close and Complete the installation process.

Successfully we have installed the SQL server failover cluster in first node.

We can install the SQL server failover cluster in remaining nodes by selecting Add node to a SQL Server failover cluster in installation centre window. To add node to existing SQL Server failover cluster follow the steps in “How to add node to SQL Server cluster”

June 17, 2013 by Karthick P.K

windows cluster freezes at “waiting for notification that node ‘‘ is a fully functional member of the cluster”

Adding node in windows cluster freezes at “waiting for notification that node ‘‘ is a fully functional member of the cluster”

When you try to add a node to cluster in windows2008 or windows2008 R2 or windows 2012 it may get stuck in the below screen With message “waiting for notification that node is a fully functional member of the cluster”

After some time you will notice “operation is taking longer time than expected” and eventually after the time out add node wizard will fail with error “Unable to successfully cleanup”.

You may notice “the operation is taking longer than expected in some time”

If you look at cluster diagnostic log you may find

[RHS] RhsCall::Perform_NativeEH: ERROR_NOT_READY(21)’ because of ‘Startup routine for ResType MSMQTriggers returned 21.’

Failed to write SPNs to node’s computer object – status 5

Resolution

1. To resolve the exclude UDP port 3343 from fire wall.

2. If the issue persists after opening the UDP port 3343. Check failover clustering diagnostic event for error “Failed to write SPNs to node’s computer object – status 5”. If you find above error make sure http://support.microsoft.com/kb/976424 is applied in Domain controller.

3. If you find event ID 1289 “The Cluster Service was unable to access network adapter ‘Microsoft Failover Cluster Virtual Miniport’. Verify that other network adapters are functioning properly and check the device manager for errors associated with adapter ‘Microsoft Failover Cluster Virtual Miniport’. If the configuration for adapter ‘Microsoft Failover Cluster Virtual Miniport’ has been changed, it may become necessary to reinstall the failover clustering feature on this computer” follow the steps in http://support.microsoft.com/kb/973838

4. Make sure IPv6 is enabled on all NIC’s

5. Enabled Cluster communication and client access on the Public network http://technet.microsoft.com/en-us/library/cc728293%28v=ws.10%29.aspx

6. Make sure Symantec Endpoint Protection is uninstalled/disabled when you add the node.

7. Make sure there is no duplicate name computer objects for nodes in AD .follow http://blogs.technet.com/b/askcore/archive/2011/05/31/cluster-installation-time-out-issues.aspx

June 17, 2013 by Pradeepa

How to install cluster in windows 2008 and windows 2012

Failover cluster feature can be installed enterprise and datacenter edition of windows2008,windows2008R2 and windows2012.

There are changes to the quorum configuration in windows2008,windows2008R2 and windows2012 cluster compared with earlier versions in which it is mandatory to have a shared disk to install the cluster (Exceptions Geo clusters). In this blog I will explain how to install failover cluster feature and create cluster. Shared disk is not mandatory for installing windows cluster but it is required for server applications like SQL Server when you are installing them on cluster.

If you would like to learn how to create shared disk for using in cluster follow the steps in How to create shared disk using iSCSI Software Target

To install failover cluster feature Open Sever Manager (Start—>Run—>Servermanager.msc),Expand Features.Select Add Features.

Then install Failover Clustering.

Confirm the installation and Click install.

Click close and complete the installation.

After installation ,open failover cluster manger.

Right click failover cluster manager->Create a cluster wizard.

Enter Server name of all the nodes which will be part of cluster you are creating.If you do not know the server name you can click on browse and select the servers.

Below window appears when you click browse–>Advanced->Find now. Select the systems which you want to add in the cluster.

In the below example I have select Node1

Configure the validation warning and click Next.

Enter the IP address for cluster and Click Next.

Click Finish and complete the cluster creation.

Once you have created the cluster follow the steps in http://blogs.msdn.com/b/clustering/archive/2010/05/14/10012930.aspx to configure the Quorum setting for your cluster

If you liked this post, do like us on Face book at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Pradeepa

June 16, 2013 by Pradeepa

How to create shared disk using iSCSI Software Target

One of the mandatory requirement to install SQL Server and similar application in cluster is to have shared disk.

I will explain how to create shared disk using iSCSI software target in this blog. You can download iSCSI Software Target from This link.

Installing Microsoft iSCSI software target.

When you invoke the self extracting package from downloaded above link it extracts X64 and x86 version of installer.

Select iSCSI_target for installation on the system which will act as iSCSI target. We will create iSCSI target disks in this machine and these disks will be shared by nodes using iSCSI initiator.
Select iSCSItargetClient_public and install in nodes in which you would like to have shared disk probably all nodes which is going to be part of cluster you are installing.

INSTALLATION IN CLUSTER NODES:(Follow same steps in all the nodes which will be part of cluster which you are installing)

Install iSCSItargetClient _public in all nodes which are going to be part of cluster which you are installing .

Run the file to start the installation.

Click Next and continue

Select the type of installation as Typical.

Specify users account that is member of administrators group in system which is acting iSCSI target and iSCSI initiator (current system).

Configure other settings.

Start the installation by clicking Install button.

Click finish to complete the installation

Install the iSCSI_target on the system which will act as iSCSI target probably file server (or) Domain controller if you are installing failover clustering for testing.

CONFIGURE iSCSI INITIATOR IN CLUSTER NODE’S:

After installation,Open ISCSI initiator.

Select yes and continue.

Type the target server name and click on Quick Connect

(Or)

Click Discover panel and select the target sever.

Give the server IP address.Click ok.

Similarly Follow the above steps and select the target in all nodes of cluster.

Configuring iSCSI target

Now after selecting the target,we have to add the all the iSCSI clients in iSCSI target.

Open MS iSCSI Target in system which is acting as iSCSI target.

Right click iSCSI target->create ISCSI target.

Create target wizard will open.

Specify the target node name,click Next.

Browse and select the iSCSI initiator,click ok.

Selected initiator will appear.Click Next.

Click Finish and complete the target creation.

Target Node1 is added.

Follow the above steps in all nodes.

CREATING VIRTUAL DISK FOR ALL NODES:

To create virtual disk in iSCSI target right click Node1->Create virtual disk.

Click Next.

Specify the file name which will be used as virtual disk.Specify it with .vhd extension.Click Next.

Specify the size for your virtual disk.Click Next.

Click Finish.

For node2,Right click Node2->Add Existing virtual disk to iSCSI target.

Click OK.

CLUSTER NODES:

Now after adding the nodes as target in DC,we have to initiate the server in cluster nodes.

Select iSCSI initiator.Click Refresh,if you didn’t get any discovered target.

Select the target and connect it.Click OK.

Open Disk management in cluster nodes:

Right click Disk1->Online

Again ,Right click Disk1->New Simple Volume.

New Simple Volume Wizard->Format this volume with the following setting.Click Next.

Then Finish the setting.

Open Failover cluster manager.Expand SQL Cluster.mssql.wiki.com.

Storage->Add a disk.

Click OK and add disk to a cluster.

Disk is added. (If disk is created for one node,automatically it will be shared by other node)

Now the shared disk is ready for use and can be used for installation of SQL Server cluster (or) any other cluster aware application which needs shared disk.

April 26, 2013 by Karthick P.K

How to stimulate a SQL Server resource failure in cluster

How to stimulate a SQL Server resource failure in cluster?

If you like to perform a check in SQL Server cluster to identify if the SQL Server resources are failing over properly follow the below steps. you can use Step 2 to create a SQL Server hang and deadlocked scheduler dump in standalone server as well.

1. Create a instance failures scenario by issuing “shutdown” command to SQL Server which will terminate/Shutdown SQL Server. SQL Server resource DLL in cluster should detect that the SQL Server have failed and start the SQL Server resource automatically.

Steps

a. Connect to SQL Server using an account which has Sysadmin privilege and execute Shutdown command.

2. Create a SQL Server hang scenario by creating excessive blocking, once SQL Server exhaust all the available threads SQL Server will stop accepting connections and will go unresponsive. SQL Server resource in the cluster should detect that the SQL Server resource have failed and restart/failover SQL Server.

Steps

a. Connect to SQL Server and below SQL Statements. (Do not close the session)


Create database test;
use test;
create table block  (a char(10));
insert into block values ('Test');
go 10
begin transaction;
update block set a ='Test1'

b. Download the Ostress.exe

c. Execute the following command in ostress.exe

ostress.exe -Sservername\Instancename -E -d test -Q "select * from test..block" -n 2000

Note: Replace servername\instancename with your server name and instance name. Above Ostress command creates 2000 connections and execute “select * from tempdb..block” which will in turn cause excessive blocking and make SQLServer exhaust all the available worker threads.

Is Alive check will fail once the threads are exhausted causing SQL Server to reach hung state. SQL Server resource DLL’s should detect that SQL Server is hung and restart/failover SQL Server.

If you do not know what is SQL Server LooksAlive and IsAlive Check follow This blog

March 18, 2013 by Karthick P.K

SQLServer LooksAlive and IsAlive Check

The resource DLL for the SQL Server service uses two functions that are used by MS Cluster service to check for availability of the SQL Server resource.

A simple check LooksAlive and more rigorous check called IsAlive

LooksAlive check: Cluster service calls looksAlive function every 5 seconds and LookAlive function Queries the service status by using the Windows NT Service Control Manager. When the LooksAlive test fails ISAlive test is called immediately.

ISalive Check: A more rigorous IsAlive function is called every 60 second and monitors the health of the SQL Server by opening up a connection to SQL Server and issuing “select @@servername” query over the connection. If the checks fail the online Thread reports this failure to the Cluster Service.

For example Assume SQL Server resource is initially in the offline state. Cluster service calls the routine in SQL Server resource DLL to bring the resource to an Online state. First the resource state is set as OnlinePending and then initiates the process of starting up the SQL Server resource. It starts the SQL Server, opens a connection to SQL Server and issues the “select @@servername” query, if this succeeds the resource is put in the Online state. If for some reason SQL Server is not able to start or the connection/query fails and SQL Server is not able to come Online within PendingTimeout, the cluster service tries to restart (or failover) the resource. If after repeated attempt SQL Server cannot be brought online, it is put in the failed state. If the SQLServer Resource DLL encounters an unrecoverable failure like failing to open cluster registry key etc. then resource DLL will put the SQL Server resource in Failed state. Once the resource is put in Failed state Cluster service will not attempt to restart/failover the resource and user intervention is required to diagnose and correct the issue.

By default, LooksAlive is fired every 5 seconds and IsAlive is fired every 60 seconds. The LooksAlive and IsAlive polling intervals can be changed in Cluster Administrator or failover cluster manager from the Advanced tab for the SQL Server resource or using the cluster.exe command prompt utility.

SQLServerScribbles.COM

SQL Server, SQL, Replication, SQL Server memory, SQL Server performance, SQL Server architecture, SQL Server cluster, SQL Server 2008, SQL Server 2012

SQLServer Cluster

Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID ‘ ‘) online (Error code 5018).

SQL Server cluster installation checklist

HOW TO INSTALL SQL Server CLUSTER IN HYPER-V

windows cluster freezes at “waiting for notification that node ‘‘ is a fully functional member of the cluster”

How to install cluster in windows 2008 and windows 2012

How to create shared disk using iSCSI Software Target

How to stimulate a SQL Server resource failure in cluster

SQLServer LooksAlive and IsAlive Check