We have a sans and a server box that currently has a SQL 2005 Failover
cluster installed, we are attempting to install a new SQL 2005 Failover
cluster but ran into an issue. After setting up the new cluster group and
installing the instance to this group the installer modified the SQL browser
to use a log file located in my second cluster group (chnsql04). This caused
the CHSSQL04 to fail and not allow logins from remote connections even though
the surface area configuration was set to allow remote and local logons. Once
I changed the path back to g:\ drive my chssql04 cluster works but my
chnsql04 cluster does not.
How can resolve this issue?
We have a second group of users wanting to use our server and we don’t want
them to see the data in the chssql04 cluster which is why we created a new
cluster and instance.
Would it be better to setup the server so that the server program files
“program files\SQL 2005\” are located on one drive that is seen by both
clusters and then create a data drive for each of the cluster groups to store
the db on?
If so how can I share a resource between two clusters?
We have the following setup for our SQL Fail over cluster:
CHSSQL04 Cluster Group
Drives: E, G, K, S, Z
Two file shares
SQL IP address (10.0.50.48)
SQL Network Name (vssql04)
SQL Server (instance name)
SQL server agent (instance name)
SQL Server Fulltext (instance name)
The sql server data and program files are installed on G:
New cluster group setup:
CHNSQL04 Cluster Group
Drives: H and F
The setup then created the following:
SQL IP address (10.0.50.68)
SQL Network name (chnvssql04)
SQL Server (instance name)
SQL server agent (instance name)
SQL Server Fulltext (instance name)
The sql server data and program files are installed on H:
Thanks in advanced,
Daniel
First of all, the Browser service is not cluster-aware and only runs on the
local nodes.
Second, the program files must reside on all participating nodes, for each
instance, in dedicated folders for that instance, sequentially numbered, on
local drives only; they can't reside on the shared drives. So, like on each
node's D: drive, \MSSQL.01, \MSSQL.02, etc.
Each virtual instance will require a separate resource group with dedicated
IP, Network Name, Disk(s), and SQL Server Instance names. You have
indicated some, but not all, of this information. They must be unique.
Lastly, either allow dynamic port generation, or follow the best practice
and select unique dedicated ports.
Here are the links to the documents. Both are relevant, although not
entirely.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en
Sincerely,
Anthony Thomas
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:AAD449C6-D287-44A8-BE1E-1B36B0982A04@.microsoft.com...
> We have a sans and a server box that currently has a SQL 2005 Failover
> cluster installed, we are attempting to install a new SQL 2005 Failover
> cluster but ran into an issue. After setting up the new cluster group and
> installing the instance to this group the installer modified the SQL
browser
> to use a log file located in my second cluster group (chnsql04). This
caused
> the CHSSQL04 to fail and not allow logins from remote connections even
though
> the surface area configuration was set to allow remote and local logons.
Once
> I changed the path back to g:\ drive my chssql04 cluster works but my
> chnsql04 cluster does not.
> How can resolve this issue?
> We have a second group of users wanting to use our server and we don't
want
> them to see the data in the chssql04 cluster which is why we created a new
> cluster and instance.
> Would it be better to setup the server so that the server program files
> "program files\SQL 2005\" are located on one drive that is seen by both
> clusters and then create a data drive for each of the cluster groups to
store
> the db on?
> If so how can I share a resource between two clusters?
>
> We have the following setup for our SQL Fail over cluster:
> CHSSQL04 Cluster Group
> Drives: E, G, K, S, Z
> Two file shares
> SQL IP address (10.0.50.48)
> SQL Network Name (vssql04)
> SQL Server (instance name)
> SQL server agent (instance name)
> SQL Server Fulltext (instance name)
> The sql server data and program files are installed on G:
> New cluster group setup:
> CHNSQL04 Cluster Group
> Drives: H and F
> The setup then created the following:
> SQL IP address (10.0.50.68)
> SQL Network name (chnvssql04)
> SQL Server (instance name)
> SQL server agent (instance name)
> SQL Server Fulltext (instance name)
> The sql server data and program files are installed on H:
>
> Thanks in advanced,
> Daniel
|||Anthony,
To answer some of the items you have pointed out we have the following setup.
We have two nodes
Chssq04a – active
Chssq04p – passive
For clarification: there are two groups with the prefixes: CHS and CHN
We have two cluster groups; chnsq04 group is the new cluster and where the
new sql instance is being installed. Both SQL instances are installed on the
active node, CHSSQ04A.
CHSSQ04 Group
Assigned nodes: Chssq04a and Chssq04p
CHNSQ04 Group
Assigned nodes: Chssq04a and Chssq04p
Each virtual instance is unique with its own disks, ip, network name, and
instance name.
CHSSQ04 Group
Drives: E, G, K, S, Z
Two file shares
SQL IP address (10.0.50.48)
SQL Network Name (vssql04)
SQL Server (CHSNextgen)
SQL server agent (CHSNextgen)
SQL Server Fulltext (CHSNextgen)
SQL 2005 is installed for this instance at
G:\Microsoft SQL Server\MSSQL.1
CHNSQL04 Cluster Group
Drives: H and F
The setup then created the following:
SQL IP address (10.0.50.68)
SQL Network name (chnvssql04)
SQL Server (CHNNextgen)
SQL server agent (CHNNextgen)
SQL Server Fulltext (CHNNextgen)
SQL 2005 is installed for this instance at
H:\Microsoft SQL Server\MSSQL.2
We are not sharing drives for each cluster group and instance, they have
their own drives, and this means that CHNSQL04 can not see drives that are
used by CHSSQL04 and visa versa.
What I think your going to tell me is that I can not have two SQL Cluster
groups on the same node because the SQL Browser is not cluster aware and thus
can not see both clusters running at the same time. This is why I can not
start my second cluster group and instance.
|||Either your system is hoplessly messed up or your terminology is hosed.
First, unless you are running SQL 7.0, all cluster nodes are equivalent.
Active and Passive designations are arbitrary and only reflect the current
state, not the system configuration.
A computer can only belong to a single cluster at a time and is typically
called a node when it is a cluster member.
Instance = group = virtual server. The terms mean exactly the same thing.
Instances are installed to the cluster, not to individual nodes. While it
is possible to set up SQL to not use specific cluster nodes, that is almost
never done on a two node cluster. After the installation is correctly
completed, there is no difference between the nodes. You can install from
one node, shift the group to the other node, and apply a service pack. The
SQL installer handles the situation correctly and updates the binaries on
each node and the database instance on the currently active node.
The first group/virtual server is the cluster group. Later groups map to
specific SQL instances. Nodes host zero or more groups.
The SQL Browser service is not cluster aware and should not use any
resources that come from any cluster group. Each node's browser service
must be configured separately.
Could you rewrite your question using the above terminology? I think the
answer will be fairly easy once we can see what you mean.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:C082F024-7E12-4F4D-A822-4E93503D0DD4@.microsoft.com...
> Anthony,
> To answer some of the items you have pointed out we have the following
> setup.
> We have two nodes
> Chssq04a – active
> Chssq04p – passive
> For clarification: there are two groups with the prefixes: CHS and CHN
> We have two cluster groups; chnsq04 group is the new cluster and where the
> new sql instance is being installed. Both SQL instances are installed on
> the
> active node, CHSSQ04A.
> CHSSQ04 Group
> Assigned nodes: Chssq04a and Chssq04p
> CHNSQ04 Group
> Assigned nodes: Chssq04a and Chssq04p
> Each virtual instance is unique with its own disks, ip, network name, and
> instance name.
> CHSSQ04 Group
> Drives: E, G, K, S, Z
> Two file shares
> SQL IP address (10.0.50.48)
> SQL Network Name (vssql04)
> SQL Server (CHSNextgen)
> SQL server agent (CHSNextgen)
> SQL Server Fulltext (CHSNextgen)
> SQL 2005 is installed for this instance at
> G:\Microsoft SQL Server\MSSQL.1
>
> CHNSQL04 Cluster Group
> Drives: H and F
> The setup then created the following:
> SQL IP address (10.0.50.68)
> SQL Network name (chnvssql04)
> SQL Server (CHNNextgen)
> SQL server agent (CHNNextgen)
> SQL Server Fulltext (CHNNextgen)
> SQL 2005 is installed for this instance at
> H:\Microsoft SQL Server\MSSQL.2
> We are not sharing drives for each cluster group and instance, they have
> their own drives, and this means that CHNSQL04 can not see drives that are
> used by CHSSQL04 and visa versa.
> What I think your going to tell me is that I can not have two SQL Cluster
> groups on the same node because the SQL Browser is not cluster aware and
> thus
> can not see both clusters running at the same time. This is why I can not
> start my second cluster group and instance.
>
|||In addition to Geoff's comments, I do have some remarks regarding your
comments.
CHSSQ04 Group
Drives: E, G, K, S, Z
G:\Microsoft SQL Server\MSSQL.1
and
CHNSQL04 Cluster Group
Drives: H and F
H:\Microsoft SQL Server\MSSQL.2
You host the database files, logs, etc. on a shared drive, but not the
binaries, they are installed on the local node drives for each cluster
member. What drives are the binaries installed on?
Later:
We are not sharing drives for each cluster group and instance, they have
their own drives, and this means that CHNSQL04 can not see drives that
are
used by CHSSQL04 and visa versa.
Whenever resources are running on one of the nodes, then all resources for
each network name are available on that node. So, if you have the Cluster
Group, the MS DTC Group, and each of the SQL Server Instance Groups running
on the same node, then you will see all of the resources if you connect to
the Node name, the Cluster name, the MS DTC name, the SQL Instance 1 name,
and the SQL Instance 2 name, including any network shares.
The point about clustering network shares is that those shares come online
and offline along with the resource group. So, if the group is on node 1,
then so are the shares and not visible from node 2. If you move group
resources to node 2, then the resources go offline on node 1 and come online
on node 2.
This configuration does nothing about securing what each instance will
"see."
Lastly, for the browser service, it runs on each node and translates
Instance Names to Port Numbers to connect to. It is not clustered, and runs
as an individual server service on every node of the cluster, just like the
Cluster service, Event Log, Server and Workstation, IPSec, or whatever other
stand alone application being hosted. So, on each cluster node, the browser
service needs to be installed on a local drive and its associated log file
located there as well. It cannot exist on any of the Cluster Resource Group
drives.
Everyone here is more than happy to help you work through this; however,
please read through the documentation provided in the links from my first
reply. It seems to me that you have a basic misunderstanding about what
clustering is, and it is affecting your installation decisions
inappropriately.
The simplest way I can describe it is thusly. If you installed SS to a
stand-alone server, hopefully, you would install the binaries to one drive,
your data files on a dedicated second drive, transaction logs to a third,
and perhaps dedicate the database backups to a 4th and the tempdb to a 5th.
Now, you want have a cold standby. You would install the binaries to a
second server, hopefully in a similar location, but you would be relying on
the SAN guys to be able to move your volumes for your database files, logs,
backups, tempdb, etc., and present them to this second server in the event
of a disaster. If you imported those drives and then lettered them the same
as they were on the first server, then you should be able to turn the
services on without any problem.
Clustering adds more complexity to this solution, but in essence does
exactly this, but automatically, and unlike the scenario above where the two
server names would be different, the cluster presents a virtual server name
and IP address to the public that does not change when resources run on one
server or the other; so, except for the offline and online sequence, remains
transparent to the end users.
However, what is required is to have the binaries on local drives of each
node. The services have to be installed on each node; it is just that only
one server at a time is actually running that service for those items that
have been added as a cluster resource. All other server service must be
online on all cluster nodes.
I hope this helps.
Sincerely,
Anthony Thomas
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:C082F024-7E12-4F4D-A822-4E93503D0DD4@.microsoft.com...
> Anthony,
> To answer some of the items you have pointed out we have the following
setup.
> We have two nodes
> Chssq04a - active
> Chssq04p - passive
> For clarification: there are two groups with the prefixes: CHS and CHN
> We have two cluster groups; chnsq04 group is the new cluster and where the
> new sql instance is being installed. Both SQL instances are installed on
the
> active node, CHSSQ04A.
> CHSSQ04 Group
> Assigned nodes: Chssq04a and Chssq04p
> CHNSQ04 Group
> Assigned nodes: Chssq04a and Chssq04p
> Each virtual instance is unique with its own disks, ip, network name, and
> instance name.
> CHSSQ04 Group
> Drives: E, G, K, S, Z
> Two file shares
> SQL IP address (10.0.50.48)
> SQL Network Name (vssql04)
> SQL Server (CHSNextgen)
> SQL server agent (CHSNextgen)
> SQL Server Fulltext (CHSNextgen)
> SQL 2005 is installed for this instance at
> G:\Microsoft SQL Server\MSSQL.1
>
> CHNSQL04 Cluster Group
> Drives: H and F
> The setup then created the following:
> SQL IP address (10.0.50.68)
> SQL Network name (chnvssql04)
> SQL Server (CHNNextgen)
> SQL server agent (CHNNextgen)
> SQL Server Fulltext (CHNNextgen)
> SQL 2005 is installed for this instance at
> H:\Microsoft SQL Server\MSSQL.2
> We are not sharing drives for each cluster group and instance, they have
> their own drives, and this means that CHNSQL04 can not see drives that are
> used by CHSSQL04 and visa versa.
> What I think your going to tell me is that I can not have two SQL Cluster
> groups on the same node because the SQL Browser is not cluster aware and
thus
> can not see both clusters running at the same time. This is why I can not
> start my second cluster group and instance.
>
|||Thanks for all your input so far and I apologize that I have my terminology
wrong. After reading through the documentation and what you have posted let
me try and repost what our current setup is.
We have two nodes in our active passive cluster, sq04a and sq04p, sq04a is
the active node and sq04p is the passive node.
We created a cluster called CHSSQ04 and inside of this cluster we created
several groups; CHSSQ04, Cluster, and MSDTC.
The group Cluster has our Quorum Disk, Cluster IP, and Cluster Name. The
group called MSDTC has our MSDTC disk, MSDTC IP Address, MSDTC Network name,
and MSDTC Resource.
Note: All of the disks are also part of the sans, and the sans has been set
to allow the two nodes to see the disks, but only the node that is active can
be using the disks.
For each of the groups we assigned the two nodes as the preferred owner.
In our sans we created 5 more drives (LUNS) and give both nodes permission
to use the drives, just like the other ones listed above. So when we open up
disk management on both nodes we see the drives, but only the active server
can see drive name and if its been formatted.
We then went to the active node and formatted the drives and assigned drive
letters. The passive node at this point still only sees the drives as
unformatted and with no drive letters assigned. We then created disk
resources for the 5 disks and assigned them to the CHSQ04 group, since this
is where the SQL instance is going to be at.
Note: yes we do have two things in our setup named CHSSQ04, the actual
cluster is called CHSSQ04 and then there is a group called chssq04 group.
By doing so the SQL installer created an instance and setup the SQL IP
Address, SQL Network Name, SQL Server (with the instance name CHSNextgen),
SQL Server Agent (CHSNextgen), and SQL Server Fulltext (CHSNextgen).
So now we have a SQL server with the name vsSQL04 with a public ip address
and an instance called CHSNextgen. We then setup our database and move are
data into and set the clients to use vsSQL04\chsnextgen to connect to the db.
The SQL installer also created binaries for the instance on the c:\ drive of
both nodes. It looks like this;
C:\Program Files\Microsoft SQL Server>dir
Volume in drive C has no label.
Volume Serial Number is 44E8-2F28
Directory of C:\Program Files\Microsoft SQL Server
02/06/2007 10:18 AM <DIR> .
02/06/2007 10:18 AM <DIR> ..
11/10/2006 10:22 AM <DIR> 80
01/16/2007 10:25 PM <DIR> 90
11/10/2006 10:26 AM <DIR> MSSQL.1
11/20/2006 08:49 AM 4,286,976 SQLUASetup.msi
1 File(s) 4,286,976 bytes
6 Dir(s) 109,489,816,576 bytes free
And on G: we have the following,
G:\Microsoft SQL Server\MSSQL.1\MSSQL>dir
Volume in drive G is SQL Data Primary
Volume Serial Number is FE52-BCF2
Directory of G:\Microsoft SQL Server\MSSQL.1\MSSQL
02/04/2007 09:59 AM <DIR> .
02/04/2007 09:59 AM <DIR> ..
02/05/2007 12:58 AM <DIR> Backup
02/04/2007 09:59 AM <DIR> Data
11/10/2006 10:26 AM <DIR> FTData
11/10/2006 10:26 AM <DIR> JOBS
02/12/2007 02:07 PM <DIR> LOG
11/10/2006 10:26 AM <DIR> repldata
0 File(s) 0 bytes
8 Dir(s) 28,332,949,504 bytes free
This works great and fails over the way it should. In our test we turned off
sq04a and the cluster failed over to sq04p with all the drive letters and
instance. So everything worked the way it should.
What we are trying to do is create another SQL fail over instance on the
same active node so that when someone is in the SQL management console for
the new instance they can not see the drives nor the data in the first SQL
instance named vsSQL04. What I did to try and accomplish this was create a
new group in the cluster called chnsq04, then assign more drives to this
group for the new instance. I started the setup and selected SQL fail over
cluster, create a new instance, and then selected the new chnsq04 group. The
setup created the instance and placed the files and folders where they need
to be, but after the install finished the SQL browser stopped and caused our
first instance in the other group to stop accepting and dropped connections.
I have since removed the new instance and group and corrected a few issues
with the admin port and the SQL browser
Now you guys have both noted that there should be a SQL browser for each
instance, well I only have one. If I look at the SQL server browser
properties from the SQL server configuration manager, I notice that the dump
directory is set to use G:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\. When I
had installed the second instance it changed this value to H:\Microsoft SQL
Server\MSSQL.2\MSSQL\LOG\ which caused my first instance to die. Also my
adminConnection/tcp port was deleted, which I have already resolved.
On a side note, our setup (before we attempted to create another fail over
instance) was done by EMC/Dell according to a MS guide. The only thing I was
trying to do was mimic the same setup for a new group so I could have a new
virtual server name and instance, if this is at all possible.
I hope this clarifies what is going on and what I am trying to accomplish.
