Hi,
I must install M$ SQL2005 standard on two node in cluster enviroment.
I wanna know some best practices to resolve the installation in the
better way.
The server is based on two blade dual processor dual core. I've a SAN
configured with 4 LUN, each with 1 partition (eventually a can
reconfigure the SAN).
I've some question:
a) SQL2005 is defined by M$ only active/passive on a cluster. This
means that can I work with two nodes, with two cluster groups each one
working with one instance? In other words, in which way can I
configure cluster's groups (or group)? Can I use only a group with two
instance? Can I use two groups? I can obtain a failover enviroment
relative to one instance with two active nodes? (SQL2005 is active/
active on nodes, but each instance is active/passive on nodes?) I
prefer obtain the failover option vs. load balance across two nodes,
but I must know the possibilities for future decision.
b) An istance SQL2005 is based on an installation path for the engine
of the instance and several paths for data files. On a cluster based
on SAN where I can/must install the instance? I've a local disk
partitioned with two logical unit (c: and d
. Also I've have one or
more units on SAN. C: contains Windows2003 and D: is empty. I must
install the instance on the local disk of the node or I use the unit
(one or more) on SAN?. I know that on a normal enviroment it's better
use some different fisical disks/units (The best it's split/organize
datafile in order of cpu capabilities - e.g.: dual processor/dual core
tempdb on 4 files..) but with a SAN? It's also a best practice define
several LUN on a SAN in order of obtain an optimized architecture?. Or
can I use only a large disk with several files? I need install 4
instances and I want obtain the best architectural solution.
c) With the first installation I will make one instance on two nodes.
In which way I will install the other 3 instances (on a cluster
enviroment)?
If you can, tell me more infos about these tasks.
oecre.at.hotmail.dot.com
<marco.crespi@.gmail.com> wrote in message
news:1170322227.120566.94730@.m58g2000cwm.googlegro ups.com...
> Hi,
> I must install M$ SQL2005 standard on two node in cluster enviroment.
> I wanna know some best practices to resolve the installation in the
> better way.
> The server is based on two blade dual processor dual core. I've a SAN
> configured with 4 LUN, each with 1 partition (eventually a can
> reconfigure the SAN).
> I've some question:
> a) SQL2005 is defined by M$ only active/passive on a cluster. This
> means that can I work with two nodes, with two cluster groups each one
> working with one instance?
Yes, one and only one instance per group, you can have multiple groups
>In other words, in which way can I
> configure cluster's groups (or group)? Can I use only a group with two
> instance?
One instance per group
> Can I use two groups?
yes, you will have two instances
> I can obtain a failover enviroment
> relative to one instance with two active nodes?
An instance (group) is only online / "running" on one node. (Always on one
node only)
A Group (instance) is NEVER "running" on two nodes.
> (SQL2005 is active/
> active on nodes, but each instance is active/passive on nodes?)
One instance/group is only online on one node. when you have two groups,
then you can have one group online on one node and the second group online
on the second node. If a node fails, the surviving node is now running all
groups.
> I
> prefer obtain the failover option vs. load balance across two nodes,
> but I must know the possibilities for future decision.
Clustering is fail-over, not load balancing.
> b) An istance SQL2005 is based on an installation path for the engine
> of the instance and several paths for data files. On a cluster based
> on SAN where I can/must install the instance?
follow the instructions, the executables are in the default places, and data
goes on your clustered disks (on a SAN)
> I've a local disk
> partitioned with two logical unit (c: and d
. Also I've have one or
> more units on SAN. C: contains Windows2003 and D: is empty. I must
> install the instance on the local disk of the node or I use the unit
> (one or more) on SAN?.
follow instructions, books on line is a good place to start
or here : http://msdn2.microsoft.com/en-us/library/ms179530.aspx
> I know that on a normal enviroment it's better
> use some different fisical disks/units (The best it's split/organize
> datafile in order of cpu capabilities - e.g.: dual processor/dual core
> tempdb on 4 files..) but with a SAN?
yes, depending on your application / IO patterns, but general rule is data
files and log files on different spindles
> It's also a best practice define
> several LUN on a SAN in order of obtain an optimized architecture?.
Absolutely, every SAN need to be configured properly, every LUN will have to
be created with certain parameters. However this is so dependent on the SAN
you have, you should contact your SAN vendor to get the best practices
(mention it is for SQL 2005).
> Or
> can I use only a large disk with several files? I need install 4
> instances and I want obtain the best architectural solution.
As above, more spindles = more IO = better performance (this is the general
rule)
> c) With the first installation I will make one instance on two nodes.
> In which way I will install the other 3 instances (on a cluster
> enviroment)?
as per documentation
> If you can, tell me more infos about these tasks.
If you have little experience with clusters, I would suggest to read up
about this as much as possible.
Install a cluster, A cluster with Virtual Machine Nodes will do, and
practice the setup. Make sure you are familiar with the tools, interfaces,
configurations, failover/back.
Practice and test first how all this works.
HTH,
_Edwin.
> oecre.at.hotmail.dot.com
>
|||Edwin's responses are all accurate, but for general knowledge, the SQL
Server 200 Failover Clustering guide is a good introduction.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
I realize you are speaking to SS2K5, but the document is "mostly" applicable
to both. Moreover, the document also contains a link to the updated white
paper dedicated for SS2K5. You would be wise to read them both.
In your situation, with only a 2-node cluster, running Standard Edition,
your choices are kind of limited. Basically, when you install each
instance, you will be required to provide a pre-existing cluster resource
group with an existing dedicated shared disk resource. During the
installation process, you will specify not only a unique instance name, but
also a unique virtual server name (Network Name resource) and associated IP
address.
This is where clustering is different that multi-instancing on a stand-alone
system, because each SQL Server instance must be segregated to an
independent dedicated resource group, it will also require a dedicated disk,
network name, and IP address.
The binaries will be installed to a local drive on each cluster node (either
C: or D: in your case). You only have to install once; the setup program
will distribute copies of the binaries to each cluster node you specify as a
potential owner of the clustered resources. The binaries may be collocated
on the same drive, but different installation paths will be chosen for each
dedicated instance.
The data files will be installed on one shared volume. After installation,
you can redistribute database files to other clustered volumes within the
same cluster resource group.
Due to the limitation of drive letters in a Windows system, you will be
limited on how many volumes you can maintain, and thus, how many
simultaneous instances you can support in a single clustered system.
With SS2K5 (as long as SS2K5 is the only version installed, no side-by-side
with any SS2K instances), you can have a single letter-assigned drive per
cluster group, but then use multiple mount-points on this drive to
distribute the data files appropriately to dedicated volumes.
Now, how many "volumes" should you have per cluster group/SQL Server
instance? At a minimum, you should have 3: one for the data files (user and
system), one for the transaction logs (user and system), and one for the
on-disk database backup files. In addition, we usually create another
volume to separate the tempdb data files (the tempdb log files are kept on
the shared log file volume). Not only should these be isolated on the host,
but they need to be partitioned in the SAN as well (usually called "data
fencing"). Basically, on the storage array(s), this is a way of grouping
RAID Group "sets" into similar, dedicated IO workload characteristics, based
on function. You wouldn't want to run your database files from the same SAN
RAID group that hosted another systems page files, especially if that other
system started memory-thrashing.
This is a baseline installation. Upon further testing, you may want to
plaid the database across multiple dedicated volumes, either through
multi-file filegroups, dedicated filegroups, a combination thereof, or
merely to isolate a single database from other database files. You can have
multiple databases, files, and/or filegroups sharing a single volume, or you
can distribute them to dedicated volume devices. The choice is dependent on
the workload characteristics, disk IOPS, and bandwidth requirements.
I specifically used the term "volumes;" this was on purpose because volume
and LUN construction is another complex discussion, and also like Edwin, I
would recommend having that discussion with your SAN administrator/engineer
or SAN vendor. The same workload and IO requirements will also dictate how
many dedicated IO buses, HBAs, switch connectors, and SAN channel adapters
your installation will require.
To give you an idea of what is going on, I'll list out a general
description, but you need to do some homework before you design and
construct. I cannot emphasize enough how important it will be for you to
get your SAN team involved in this design.
Nevertheless, there is disk plaiding and database plaiding, one residing and
dependent on the other. I mentioned a few of the components for database
plaiding above. Below, I will give list some of the volume construction
considerations.
For disk plaiding, LUNS are created in the SAN storage array(s) (yes,
arrays; host presented LUNs can be supported from more than one array
simultaneously; this is the "N" in SAN, which stands for network, both on
the host side as well as the storage side). Sometimes these are created
from hypervolumes (slices from a single disk) or RAID groups (slices of
standard RAID 0, 1, 3, 5, 6, 1+0, etc., etc., etc.). These LUNs can be
presented directly to the host or from these, meta-volumes or meta-LUNs
(second layer striping on the array) can be created.
Either array LUNs or these meta-LUN units are then presented to the host.
These show up as the DISK devices visible from the host side volume manager.
On Windows systems, using the native LDM volume manager, each of these
DISKs/array LUNs will be converted to basic disks with primary
partitions-dynamic disks are not supported for clustered installations
utilizing the physical disk resource type. You will need to consider
partition offsets, drive letters and/or mount points, and File System
Allocation Unit size. However, with third-party volume managers, you can
consider another layer of disk plaiding.
With these volume manager utilities, you can convert these array LUN backed
disk devices into dynamic disks and now consider various host-level striping
scenarios as well. We typically use RAID 0, but RAID 1 or JBOD could also
be considered. Multiple dynamic disk devices can be assembled to construct
a single volume, from which a drive or mounted device can be assigned to a
SQL Server clustered resource group.
The reason you would want to consider host-level striping in addition to the
storage array construction is in order to keep multiple HBAs using
multipathing software as evenly active as possible. Allowing only the
storage array to manage all RAID characteristics, you can limit the
bandwidth capabilities between the host to SAN interconnects. By striping
on the host, you can maximize this bandwidth.
One last thing before I let you go, you are considering running 4 instances
on a two-node cluster. I would recommend you spend some time on the VLDB
web site.
http://www.microsoft.com/sql/prodinfo/previousversions/scalability.mspx
Microsoft has provided studies that have shown that hosting multiple
databases on a single instance is more efficient than running multiple
instances in 64-bit mode. For 32-bit installations, because of limitations
of the Virtual Memory Address Space, multiple instances may provide
additional performance enhancements. So, if performance is your goal, stick
with 2 instances, one running on each node, and perhaps running the entire
installation in a single-instanced configuration on 64-bit equipment.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx#EDD
If money is a factor, and your are trying to maximize hardware resource
utilization, take a look into N + I clustered configurations, these can help
reduce the number of passive clustered nodes per active node, thus reducing
your cost without sacrificing performance.
Take a look at the Number of Nodes and Failover Plan section:
http://technet2.microsoft.com/WindowsServer/en/library/f41adff5-87df-483e-9edd-8484501d79921033.mspx?mfr=true
Best of luck.
Sincerely,
Anthony Thomas
<marco.crespi@.gmail.com> wrote in message
news:1170322227.120566.94730@.m58g2000cwm.googlegro ups.com...
> Hi,
> I must install M$ SQL2005 standard on two node in cluster enviroment.
> I wanna know some best practices to resolve the installation in the
> better way.
> The server is based on two blade dual processor dual core. I've a SAN
> configured with 4 LUN, each with 1 partition (eventually a can
> reconfigure the SAN).
> I've some question:
> a) SQL2005 is defined by M$ only active/passive on a cluster. This
> means that can I work with two nodes, with two cluster groups each one
> working with one instance? In other words, in which way can I
> configure cluster's groups (or group)? Can I use only a group with two
> instance? Can I use two groups? I can obtain a failover enviroment
> relative to one instance with two active nodes? (SQL2005 is active/
> active on nodes, but each instance is active/passive on nodes?) I
> prefer obtain the failover option vs. load balance across two nodes,
> but I must know the possibilities for future decision.
> b) An istance SQL2005 is based on an installation path for the engine
> of the instance and several paths for data files. On a cluster based
> on SAN where I can/must install the instance? I've a local disk
> partitioned with two logical unit (c: and d
. Also I've have one or
> more units on SAN. C: contains Windows2003 and D: is empty. I must
> install the instance on the local disk of the node or I use the unit
> (one or more) on SAN?. I know that on a normal enviroment it's better
> use some different fisical disks/units (The best it's split/organize
> datafile in order of cpu capabilities - e.g.: dual processor/dual core
> tempdb on 4 files..) but with a SAN? It's also a best practice define
> several LUN on a SAN in order of obtain an optimized architecture?. Or
> can I use only a large disk with several files? I need install 4
> instances and I want obtain the best architectural solution.
> c) With the first installation I will make one instance on two nodes.
> In which way I will install the other 3 instances (on a cluster
> enviroment)?
> If you can, tell me more infos about these tasks.
> oecre.at.hotmail.dot.com
>
|||Perfect!!
I've read your notes and solve the task!!
Thanks!!
But..
But..
I've encountered a terribly problem..
When SQL Installation must remotely uses the second node to exec
parallel installation the setup hang...
No way to solve the problem...
I've found that if I give explicitly full rights on CMD.EXE to the
installation user I can make two installation (RPC are allowed:
Dameware does function).....
ONLY TWO INSTANCES!! The others no.
Pay attention: the installation user is administrator of the machine
and of the cluster !!?!?!?!?
To solve the task (4 instances requested..) I use one node for two
instances and the other node for others (two)
Only a question: if I will need another instance tomorrow?...
