Sunday, April 17, 2011

SQL Server Database Instance on MS Cluster

I was looking for a way to make use of our idle server which is passive to one of the SQL DB instance.the attempt was to install two SQL Server 2005 database instance in a cluster with three node. this is not a full installation steps but a clarification of instances that can be installed in the cluster.

The structure goes like this.

Node1 - active (application1 db instance) (private ip:a.a.a.a, public ip:b.b.b.b)
Node2 - passive for both Node1 & Node 3 (private ip:c.c.c.c, public ip:d.d.d.d)
Node3 - active (application2 db instance) (private ip:e.e.e.e, public ip:f.f.f.f)

Node1 & 2 has a storage (SAN1)
Node 3 & 2 has a storage (SAN2)
A Quorum Drive common to all nodes (DiskQ)

Cluster Name (cluster1)
Cluster IP Address (x.x.x.x)

SQL Network Name1 (sqlcluster1)
SQL IP Address1 (y.y.y.y)
SQL Network Name2 (sqlcluster2)
SQL IP Address2 (z.z.z.z)

all the three nodes are in a cluster named cluster1 with one quorum drive DiskQ
by default a group is created Cluster Group with Quorum drive (DiskQ), Cluster name (cluster1) and Cluster IP Address (x.x.x.x)

a second group is created as Group1 which holds SAN1, SQL Network Name1, SQL IP Address1, SQL Server1 which is the default instance. they are added to the group during the installation of SQL Server except SAN1.

a third group is created as Group2 which holds SAN2, MSDTC, SQL Network Name2, SQL IP Address2, SQL Server2 which is the named instance. they are added to the group during the installation of SQL Server except SAN2 & MSDTC.( for some reason MSDTC needs to be added as there has been error during installation)

the resources are assigned auto during the installation of SQL server instance as specified when you choose failover cluster during SQL server installation.

the thing i have noticed is no two default instance of SQL Server 2005 db is possible in a cluster. whether they have different virtual host name (sqlcluster1 or sqlcluster2) or ip. MS SQL cluster can have only one default instance and all other instances are named. i couldn't get any documentation which explains this before the installation. the reason i found is installation is using the relative path in registry to point to default instance location . as per microsft documentation the default instance get installed in MSSQL.1 folder and all named instances in MSSQL.2, MSSQL.3 etc. since passive node is involved in both the installation it already has a path registered for first instance that is default. so a named instance is required for the second installation. this is my conclusion. if anyone has a different opinion please let me know.

from the application side i am accessing the database instances as sqlcluster1 for applcation1 and sqlcluster2 for application2.

No comments:

Post a Comment