Monday, October 20, 2014

1 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

image

Change IP address

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

image

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

image

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

image

Verify the port ip change in the error log.

image

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

image

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

image

image

Verify the port number change in the error log.

image

Add Disk to SQL Server Cluster failover instance

Add the disk resource to the SQL Serve resource group.

image

image

image

Add dependence in the SQL Server instance .

image

image

Confirm in the sys,dm_io_cluster_shared_drives

image

Configure failover policy

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

image

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

image

Reference

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

1 comments:

  1. Different type of platform required to opinion from their visitor but I am excited to read about a new feature of essay writing. It is good for all users who have an account on this platform, they easily write on child support topics and multiple topics using their mind but this custom essay writing service is the best source for complete work with the help of our experienced qualified writers.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates