SQL Cluster one instance fails when failed over

Question

SQL Cluster one instance fails when failed over

trgrassijr

Right there with Babe

Points: 729
More actions
May 18, 2018 at 12:24 pm

#348341

I built two windows 2012 R2 VM (VMWARE) Cluster servers with SQL 2014 Enterprise
Installed instance One have databases loaded and when I fail over to Node 2 all is good users do not even feel a bleep.
Installed instance two no databases yet and it fails to start when I go from Node 1 to Node 2
Installed instance three no databases yet and it works as instance one no problem.
All the Disk drives move over but the instance does not start
I manually start the SQL services but the Cluster shows failed status still.
What can I look for on this?
Thank you in advance
Tom

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

trgrassijr Right there with Babe Points: 729 More actions · Answer 1

Update to this issue
I found one of my SQL Disks are not working when I fail over to Node 2 and that's why SQL instance does not work.
Any ideas ?

No Errors in the SQL Log the Agent log has 3 warnings

But the cluster had these messages.

408 SQL Server XXXXXXX is clustered Auto Restart has been disabled
396 An idle cpu condition has not been defined onidle job schedules will have no effect
475 database mail is not enabled for agent notifications

On the event log
Log Name: System
Source: Microsoft-Windows-FailoverClustering
Date: 5/18/2018 5:48:27 PM
Event ID: 1069
Task Category: Resource Control Manager
Level: Error
Keywords:
User: SYSTEM
Computer: TGCS014-N2.our.network.tgcsnet.com
Description:
Cluster resource 'Cluster Disk 10' of type 'Physical Disk' in clustered role 'SQL Server (WRESTLING)' failed. The error code was '0x80070490' ('Element not found.').

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it. Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Microsoft-Windows-FailoverClustering" Guid="{BAF908EA-3421-4CA9-9B84-6689B8C6F85F}" />
<EventID>1069</EventID>
<Version>2</Version>
<Level>2</Level>
<Task>3</Task>
<Opcode>0</Opcode>
<Keywords>0x8000000000000000</Keywords>
<TimeCreated SystemTime="2018-05-18T21:48:27.926124700Z" />
<EventRecordID>198542</EventRecordID>
<Correlation />
<Execution ProcessID="1776" ThreadID="3640" />
<Channel>System</Channel>
<Computer>TGCS014-N2.our.network.tgcsnet.com</Computer>
<Security UserID="S-1-5-18" />
</System>
<EventData>
<Data Name="ResourceName">Cluster Disk 10</Data>
<Data Name="ResourceGroup">SQL Server (WRESTLING)</Data>
<Data Name="ResTypeDll">Physical Disk</Data>
<Data Name="ApplicationErrorCode">Element not found.</Data>
<Data Name="ErrorCause">0x80070490</Data>
</EventData>
</Event>

Log Name: System
Source: Microsoft-Windows-FailoverClustering
Date: 5/18/2018 5:48:27 PM
Event ID: 1205
Task Category: Resource Control Manager
Level: Error
Keywords:
User: SYSTEM
Computer: TGCS014-N2.our.network.tgcsnet.com
Description:
The Cluster service failed to bring clustered role 'SQL Server (WRESTLING)' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Microsoft-Windows-FailoverClustering" Guid="{BAF908EA-3421-4CA9-9B84-6689B8C6F85F}" />
<EventID>1205</EventID>
<Version>0</Version>
<Level>2</Level>
<Task>3</Task>
<Opcode>0</Opcode>
<Keywords>0x8000000000000000</Keywords>
<TimeCreated SystemTime="2018-05-18T21:48:27.926124700Z" />
<EventRecordID>198543</EventRecordID>
<Correlation />
<Execution ProcessID="1776" ThreadID="3640" />
<Channel>System</Channel>
<Computer>TGCS014-N2.our.network.tgcsnet.com</Computer>
<Security UserID="S-1-5-18" />
</System>
<EventData>
<Data Name="ResourceGroup">SQL Server (WRESTLING)</Data>
</EventData>
</Event>

Log Name: System
Source: Microsoft-Windows-FailoverClustering
Date: 5/18/2018 5:48:27 PM
Event ID: 1254
Task Category: Resource Control Manager
Level: Error
Keywords:
User: SYSTEM
Computer: TGCS014-N2.our.network.tgcsnet.com
Description:
Clustered role 'SQL Server (WRESTLING)' has exceeded its failover threshold. It has exhausted the configured number of failover attempts within the failover period of time allotted to it and will be left in a failed state. No additional attempts will be made to bring the role online or fail it over to another node in the cluster. Please check the events associated with the failure. After the issues causing the failure are resolved the role can be brought online manually or the cluster may attempt to bring it online again after the restart delay period.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Microsoft-Windows-FailoverClustering" Guid="{BAF908EA-3421-4CA9-9B84-6689B8C6F85F}" />
<EventID>1254</EventID>
<Version>0</Version>
<Level>2</Level>
<Task>3</Task>
<Opcode>0</Opcode>
<Keywords>0x8000000000000000</Keywords>
<TimeCreated SystemTime="2018-05-18T21:48:27.926124700Z" />
<EventRecordID>198544</EventRecordID>
<Correlation />
<Execution ProcessID="1776" ThreadID="3640" />
<Channel>System</Channel>
<Computer>TGCS014-N2.our.network.tgcsnet.com</Computer>
<Security UserID="S-1-5-18" />
</System>
<EventData>
<Data Name="ResourceGroup">SQL Server (WRESTLING)</Data>
</EventData>
</Event>

It is looking like a disk issue not sure

trgrassijr Right there with Babe Points: 729 More actions · Answer 2

Getting further along.

I created a new Lun and added to the SQL Cluster

Now I need help moving the TEMPDB to the new LUN

Question now is how to move the TempDB files to the new drive.

The new drive moves when I move the Role over to Node 2

Update:

Select * from tempdb.sys.database_files

tempdev Y:\SQL-Temp\tempdb.mdf
templog Y:\SQL-Temp\templog.ldf

Alter database tempdb
modify file (name = tempdev, filename = 'O:\SQL-Temp\tempdb.mdf')

Alter database tempdb
modify file (name = templog, filename = 'O:\SQL-Temp\templog.mdf')

Msg 5184, Level 16, State 1, Line 1
Cannot use file 'O:\SQL-Temp\tempdb.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Msg 5184, Level 16, State 1, Line 4
Cannot use file 'O:\SQL-Temp\templog.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

any ideas?

Thanks Tom

trgrassijr Right there with Babe Points: 729 More actions · Answer 3

This can be marked as resolved.

Figured out how to add the new LUN to the cluster

Need to go to the Other Resources section after you select the Role then pick the SQL server instance right click and go to dependencies tab that is where all the dependencies are located for that role.