SQL Cluster Related Question and Answer

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… 

New in Windows Server 2008 clustering:

* Cluster setup and management has a new MMC snap-in (Failover Clustering Management) in Windows Server 2008.

* Automatic disk/partition alignment.

* Setup need less steps to configure it.

* IPV6 and DHCP support

* Cluster setup can be automated using scripts.

* New validation wizard to test hardware, storage, and networking compatibility.

* Communication with storage has been improved.

* Support for GTP disks.

* Service SIDs are generated automatically for use with SQL Server 2008 services

New in SQL Server clustering:

* Up to 8 nodes in SQL Server 2005, 16 nodes in SQL Server 2008.

* No remote execution on the cluster nodes. You can now upgrade or patch a cluster with minimal downtime. Updates and service packs must be apply on each node separately; setup must be executed on each node. The same to add a new node, setup must be executed on each node.

* Service packs can now be uninstalled.

* SQL Server 2008 are not supported on WOW mode.

* MSDTC was required as a cluster resource to install SQL Server 2000 and SQL Server 2005 clusters. SQL Server 2008 does not have that requirement anymore to install a cluster

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.

How to slipstream SQL Server 2008 R2 and a SQL Server 2008 R2 Service Pack 1 (SP1)

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

select * from sys.dm_os_cluster_nodes

*********************************************************************************************************************************************************************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 :