Cluster Group
SQL Group :
Physical Disk
SQL IP Address1(MIISSQL01)
SQL Network Name(MIISSQL01)
Sql Server
Sql Server Agent
Disk J:K:L:S:X
1XX.77.196.164
Network Name MIISSQL01
Resource Depend (Physical Disk,Network Name)
Resources: (Above mention list is show in this section)
Cluster Configuration
Resource Type: Resource DLL information like DHCP services, file Share, IP Address
Networks
Node
MIIS-SQL1
MIIS-SQL2
Address
1XX.77.196.161
1XX.77.196.162
Network Interfaces
Node1 (MIS-SQL1)
Node2 (MIS-SQL2)
How many IP is required in 2 nodes Cluster?
5 IP’s ........2 for private network ,2 for public network 1 for virtual server which used to connect application.
List out some of the requirements to setup a SQL Server failover cluster.
Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Hearbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk and MSDTC Disk.
Quorum: A quorum is essentially a log file; similarly in concept to database logs. Its purpose is to record any changes made on the active node .when both nodes of a cluster are up and running participating in their respective roles active and passive they communicate with each other the network.
What is MSDTC used for?
SQL Server 2005 requires MS DTC in the cluster for distributed queries and two-phase commit transactions, as well as for some replication functionality.
What is Quorum used for?
In the old style cluster quorum model the quorum disk did represent a single point of failure. If the quorum drive failed then your whole cluster failed .
Although you can still configure it that way (it is called No Majority: Disk Only), the preferred quorum model in a 2-node cluster is "Node and Disk Majority".
In this model, the shared disk only represents one vote in the cluster. For the cluster to maintain a quorum, it only needs a majority of votes,
so if the disk fails the remaining two nodes continue to function as they represent 2 out of 3 votes
LooksAlive Check:
This check performs a basic verification that the SQL Server service is running on the hosted node which should be online on a given interval of time. The default time that has been set is 5 seconds.
IsAlive check:
This process checks and verifies the cached result of the internal IsAlive process in the SQL Server resource DLL. The internal IsAlive process runs every 60 seconds and verifies if SQL server is online or not. The check uses SELECT @@SERVERNAME to verify the state of the SQL Server.
Open a command prompt and type cluster group
will list all the group available in the windows cluster
To move it other node we have run below command.
cluster group "Cluster Group" /move
In you have more nodes in your cluster and to move the 'cluster group' to a specific group use the below command.
cluster group "Cluster Group" /moveto:node5
What is the maximum number of nodes in an MNS cluster?
Windows Server 2003 supports 8-node clusters for both Enterprise Edition and Datacenter Edition
What is the maximum number of nodes in an MNS cluster in Windows Server 2008, Enterprise x64 Edition?
Maximum 16.
What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?
On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster and so on…
What is meant by Active – Passive and Active – Active clustering setup?
An Active – Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in am idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.
An Active – Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.
On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?
Using Cluster Administrator, connect to the cluster and select the SQL Server cluster. Once you have selected the SQL Server group, in the right hand side of the console, the column “Owner” gives us the information of the node on which the SQL Server group is currently active.
How do you open a Cluster Administrator?
From Start -> Run and type sql server 2008 R2 Cluadmin.msc used
From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you can also go to Start -> All programs -> Administrative Tools -> Cluster Administrator.
Due to some maintenance being done, the SQL Server on a failover cluster needs to be brought down. How do you bring the SQL Server down?
In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu item choose Take Offline.
How to get Error log file in Cluster
1 Go to a command prompt
2. Type "Cluster /Cluster: yourclustername log /gen /copy "C:\temp". You should get output as
Which are the cluster aware services for SQL Server Failover 2005 and 2008 clusters??
Database engine and SSAS is cluster aware and can be installed as a part of cluster services and applications directly.
SSIS - Clustering Integration Services is not recommended because the Integration Services service is not a clustered or cluster-aware service, and does not support failover from one cluster node to another. Therefore, in a clustered environment, Integration Services should be installed and started as a stand-alone service on each node in the cluster. Although the Integration Services service is not a clustered service, you can manually configure the service to operate as a cluster resource after you install Integration Services separately on each node of the cluster
Note: SSRS is not cluster aware.
With the release SQL Server 2008 R2 SP1, I have compiled the list of steps to create a slipstream drop. Once you have created this slipstream drop, you can install the original release of SQL Server 2008 R1 and SP1 at the same time. The slipstream functionality performs a single installs that is quicker when compared to installing the original release and then applying the service pack. Additionally this slipstream drop fixes any Setup issues that prevent a successful installation or upgrade that has been addressed in SP1
How to Stop the Sql Server Services on cluster mode server
1. Take the SQL resource offline and set the start up type from Manual to Disabled on all nodes
2. Stop the windows cluster service on all nodes and set the startup type from automatic to disabled.
3. Shut down Primary node (active)
4. Shut down Passive node
5. Shut down the SAN
6. Do the network maintainance
7. Turn on SAN 8. Boot primary node server
9. Boot passive node server
10. Start the windows cluster service on the primary node and set the startup type from disabled to automatic
11. Start the windows cluster service on the passive node and set the startup type from disabled to automatic
12. Test the failover
13. Start the SQL resource on the primary server and set the start up type from Disabled to Manual.
14. Set the startup type on the passive server from Disabled to Manual.
15. Test the SQL failover
Query Cluster environment Check
In SQL SERVER 200Oserver get information Cluster name in client machine
SELECT SERVERPROPERTY('IsClustered')
In SQL SERVER 2005 server get information Cluster name in client machine
SELECT * FROM sys.dm_os_cluster_nodes
---Server running node
select SERVERPROPERTY ('computernamephysicalnetbios')
---Cluster Node Information
*********************************************************************************************************************************************************************8
How to Cluster Windows 2008 document attached.
http://www.youtube.com/watch?v=B1iXZEa2_w8&list=PLA4BFFBFC78D79662
http://www.youtube.com/watch?v=dONG4NWcgpE
http://www.youtube.com/watch?v=nA-EADu2Q7Y
http://www.youtube.com/watch?v=WdAod0cSlwM
Attached Related Documents Below :
Different types of Quorum in Windows server 2008
Node Majority
– Used when Odd number of nodes are in cluster. 2.Node and Disk Majority
– Even number of nodes(but not a multi-site cluster) 3.Node and File Share Majority
– Even number of nodes, multi-site cluster 4.Node and File Share Majority
– Even number of nodes, no shared storage