Clusters Server in SQL SERVER
Cluster Service:
The Cluster Service controls cluster activities and performs such tasks as
1. Coordinating event notification,
2. Facilitating communication between cluster components,
3. Handling failover operations
4. Managing the configuration.
Heartbeat:
Every 1.20 sec heartbeat send UDP packet from one node to another node..and checks the node availability.
[Active, passive]
If heart beat fails for 5 times then failover automatically happen to application from node 1 to node 2.
MSDTC:
To distributed the transactions between the nodes.
In Windows server 2003 MSDTC is a mandatory disk need to configure but where as in Windows server 2008 onwards MSDTC is optional why because windows cluster uses the local DTC services.
Windows 2003 cluster: 8 nodes max.
Windows 2008 cluster: 16 nodes max.
Cluster quorum disk
A cluster quorum disk is the storage medium on which the configuration database is stored for a cluster computing network. The cluster configuration database, also called the quorum, tells the cluster which physical server(s) should be active at any given time.
Resource:
A resource is a physical or logical entity, which has below properties:
Can be bought online and taken offline
Can be managed in the failover cluster
Can be owned by only one node at a time
Resource State:
All resources can have following states
Offline
Offline_Pending
Online
Online_Pending
Failed
Windows Cluster configuration prerequisites:
Domain controller
Nodes
Public ip adders
Private ip address
disks
Quorum
msdtc
Service account
Windows cluster virtual ip
In windows 2003 quorum drive letter should be Q but where as in Windows 2008 we can give any letter for drives.
HOW MANY IP ADDRESS 2 NODE CLUSTERS?
Active -Passive:
Only one sql server application is installed and services are running, other node is waiting for failover to happen.
6 ip addresses are mandatory for 2 node cluster
2 public ip's
2 private
1 cluster ip
1 network SQL Server ip address
Active -Active:
Each node should have at least 1 sql server installation.
7 ip addresses are mandatory for 2 node cluster
2 public ip's
2 private
1 cluster ip
2 network SQL Server ip address
Windows Cluster node checks before SQL Server installation:
1. Whether all ipaddress are pinging or not [Node1 to node 2 and node2-node1]..
2. Check windows cluster service is running or not.
3.Open CLUSTER ADMINISTRATOR to monitor clustering
Go to run>type "cluadmin.msc' [windows server 2008 o\s]
Go to run>type "cluadmin' [windows server 2003 o\s]
4.How many disks are created and what are the naming conventions are provided.Also check all disks are up & running or not.
5.After open cluster admin then verify,
Services and applications
Nodes
Storage
Network
6. CLUSTER EVENTS: Which helps to track cluster related events [2008 O\S].
WINDOWS SERVER 2003 O\S -
GO TO C:\WINDOWS\CLUSTER\CLUSTERLOG.LOG
How to apply patching?
2005 SQL Server:
1. Failover all resources from active node to passive node.
2.Start applying the patch on passive node which is current active node.[Apply for both servers and tools in passive node which is current active node]
3. Once completed perform DBA health check step...
4. Ask your application team to check connectivity to databases.
5.Once application team confirms then failback to active node and apply patch for tools..
6.Check final DBA health check and confirm to all with patch status.
7. Reboot of your noes.
Cluster Service:
The Cluster Service controls cluster activities and performs such tasks as
1. Coordinating event notification,
2. Facilitating communication between cluster components,
3. Handling failover operations
4. Managing the configuration.