In cluster Administrator, we have a storage resource name
SQL02, which is not assigned to any node and kept as Available Storage and its
Owner Node is XX1DBTSJR17F02.
The requirement is to install SQL Server 2016 on storage
SQL02.
I executed SQL server installation setup from XX1DBTSJR17F01.
The setup got failed stated there is no disk for SQL Server
installation.
It looks to me GUI does not give any option to failover
SQL02 from XX1DBTSJR17FO2 to XX1DBTSJR17FO1
Q:- Now the question is how to move SQL02 available
storage from XX1DBTSJR17FO2 to XX1DBTSJR17FO1 and start installation.
It gives an error Root of the path
G:\MSSQL14.XX1DBTSJR17SQL17\MSSQL\DATA does not exist.
Hence below PowerShell script will move Available storage
from one node to another node.
Function Failver-SQLServerServices()
{
<#
SYNOPSIS
Compile
this function in powershell window using run as Administrator.
.DESCRIPTION
Failover SQL Server services from Active to Passive Node & Vice Versa
DBA team will check back up and move the roles to other node -
Need the script / commands which you use to do this activity .
windows team will include this in the main script.
.NOTES
Author: Rakesh Kumar
.LINK
#>
[cmdletbinding()]
param(
[Parameter(
Mandatory=$true
, HelpMessage='The Name of the SQL Server resource group'
)
]
[string]$GroupName
,[Parameter(
Mandatory=$true
, HelpMessage='The TargetNode'
)
]
[string]$TargetNode
,
[Parameter(
Mandatory=$true
, HelpMessage='The Windows Cluster Name'
)
]
[string]$Workingcluster
)
Import-Module FailoverClusters
Write-Host "Resource group $GroupName moving to node $TargetNode
...";
Move-ClusterGroup –Name
$GroupName -Node
$TargetNode -Cluster
$Workingcluster;
Write-Host "Resource group $GroupName was moved to node $TargetNode succesfully...";
}
|
1.PowerShell
Command to retrieve cluster FQDN
Get-Cluster | fl * -- command to retrieve complete cluster details
2.PowerShell
Command to retrieve cluster resources
Get-ClusterResource – command to retrieve cluster resources
3.PowerShell
Command to retrieve cluster nodes
Get-ClusterNode -- command to retrieve Cluster nodes
4.PowerShell Command to retrieve cluster group -- This command will give all roles and its owner Node Get-Clustergroup |
# Calling
functions- Now we have to call Failver-SQLServerServices function based on
changing parameters..
1. In order to move SQL Server resource 'Available Storage' group from 'XX1DBTSJR17F01' to XX1DBTSJR17F02 we need toexecute below function.
Failver-SQLServerServices -GroupName 'Available Storage'
-TargetNode 'XX1DBTSJR17F01'
-Workingcluster 'XX1DBTSJR17FA.JXXAB.LOCAL'
|
Additional
if we have to failover SQL Server role from one node to another node you can use same user defined function "Failver-SQLServerServices", hence you will save lot of time.
Before executing below function we need to retrieve 3 values
1. Cluster Group Name
2. Target Node
3.Working Cluster Name
1.How to retrieve Cluster group Name: - IN order to retrieve Cluster Group Name we have to execute command Get-Cluster | fl *
it will give output like
Domain : JXXAB.LOCAL
Name : XX1DBTSJR17FA
whole string will be :XX1DBTSJR17FA.JXXAB.LOCAL
2. How to retrieve Target Node:- in order to retrieve Target Node, execute Get-ClusterNode commmand. output of this command will give to Node name, you have to decide which node you have to select to failover of role.
3. Cluster Group Name: - IN order to retrieve Cluster group Name you have to execute
Get-ClusterGroup
output of Get-ClusterGroup will be like this:-
PS C:\Users\LabGuestAdmin> Get-Clustergroup
Name OwnerNode State
---- --------- -----
Available Storage XX1DBTSJR17F02 Online
Cluster Group XX1DBTSJR17F02 Online
SQL Server (MSSQLSERVER) XX1DBTSJR17F01 Failed
XX1DBTSJR17SQL17 XX1DBTSJR17F01 Online
hence
1. In order to move SQL Server Role from XX1DBTSJR17F01 to XX1DBTSJR17F02
use below command
Failver-SQLServerServices -GroupName 'SQL Server (MSSQLSERVER)' -TargetNode 'XX1DBTSJR17F02' -Workingcluster 'XX1DBTSJR17FA.JXXAB.LOCAL'
