5 SQL2014: Explore SQL Server Failover Cluster

• Explore DMV for Cluster
• Change IP address
• Configure static port number
• Add Disk to SQL Server Cluster failover instance
• Configure failover policy

Explore DMV for Cluster

SELECT @@VERSION

GO

SELECT

    SERVERPROPERTY('MachineName') AS [ServerName], 

    SERVERPROPERTY('ServerName') AS [ServerInstanceName], 

    SERVERPROPERTY('InstanceName') AS [Instance], 

    SERVERPROPERTY('Edition') AS [Edition],

    SERVERPROPERTY('ProductVersion') AS [ProductVersion], 

    SERVERPROPERTY('ProductLevel') AS [ProductLevel], 

    Left(@@Version, Charindex('-', @@version) - 2) As VersionName,

    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') As CurrentNode

GO

Select * from sys.dm_os_cluster_nodes

GO

select * from sys.dm_io_cluster_shared_drives

GO

select * from sys.dm_os_cluster_properties

GO

Change IP address

We can change the IP address in the failover cluster manger.

After click apply and confirm. It will bounce the SQL server instance.

The ip changes may effective in the DNS, but we need to flush the dns cache in the client with ipconfig /flushdns

Verify the port ip change in the error log.

Configure static port number

The port number can be changed dynamically but require down time to make it effective.

1. Find the node number from the SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
2. RDP to the host
3. Change the port number in the SQL Server Configuration manager

Below example is to add the static port 8001 and bounce SQL server instance from SQL Server configuration manager.

Verify the port number change in the error log.

Add Disk to SQL Server Cluster failover instance

Add the disk resource to the SQL Serve resource group.

Add dependence in the SQL Server instance .

Confirm in the sys,dm_io_cluster_shared_drives

Configure failover policy

Below are the list of failover conditions. The condition value could be 0 ~ 5. Refer here for more detail

sp_server_diagnostics can be used to check SQL Server cluster health status.

Reference

http://msdn.microsoft.com/en-us/library/ff878233.aspx