cluster white paper

Is there a reliable white paper for clustering sql2k on win2k3. Ive already
got the OS clustered.
SQL2K SP3
TIA, ChrisRChrisR wrote:
> Is there a reliable white paper for clustering sql2k on win2k3. Ive
> already got the OS clustered.
Some of these might help:
http://www.microsoft.com/resources/...r />
1261.mspx
http://support.microsoft.com/defaul...blurb101802.asp
http://msdn.microsoft.com/library/d...
ering_2icn.asp
David Gugick
Imceda Software
www.imceda.com|||This is great information. Does anyone have any updated information on
setting up SQL Server 2000 in a Windows 2003 environment?
Just wondering if 2003 brings anything more to the table over Windows Server
2000.
Thanks!
Tony
"David Gugick" wrote:

> ChrisR wrote:
> Some of these might help:
> http://www.microsoft.com/resources/.../>
/c1261.mspx
> http://support.microsoft.com/defaul...blurb101802.asp
> http://msdn.microsoft.com/library/d...tering_2icn.asp
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Cluster under sqlserver 2000

I would like to know if the clustering under sqlserver 2000 works fine.
Could you give some pointers in order to install a cluster under sqlserver ?
Best regardsorion30,
Yes SQL Server has fault tolerance capabilities which can be implemented with
the help of its clustering feature. SQL Server has two types of cluster
configuration ie Active/Active and Active/Passive cluster.
A cluster is a safeguard for fault tolerance for the servers, the operating
system, and the software. You can safeguard the data with the help of shared
disk arrays.
MS has a clustering feature to its operating system in Windows NT 4.0 Enterprise
Edition / WIN2K advanced & datacenter servers, this clustering feature is called
MSCS ie Microsoft Clustering Server. SQL Server's enterprise edition ships with
Clustering feature(Virtual Server).
For more information on cluster visit this url
http://www.sql-server-performance.com/clustering_intro1.asp
Also you can post your queries to more relevent newgroup
microsoft.public.sqlserver.clustering
-- Vishal
"orion30" <orion30@.ifrance.com> wrote in message
news:bri7f1$bvi$1@.news-reader5.wanadoo.fr...
> I would like to know if the clustering under sqlserver 2000 works fine.
> Could you give some pointers in order to install a cluster under sqlserver ?
> Best regards
>

Cluster synchronize

I have an architecture which has 4 DB servers on the whole; two will
participate in load balancing and other two in fail over clustering.
My question is how will the two DB servers that is used for load
balancing synchronize?
Hi
To synchronize the data between the 2 clusters, you need to use something
like replication. Clustering is not a load-balancing technology.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<mukesh.thiru@.gmail.com> wrote in message
news:1135764099.960391.227140@.g43g2000cwa.googlegr oups.com...
>I have an architecture which has 4 DB servers on the whole; two will
> participate in load balancing and other two in fail over clustering.
> My question is how will the two DB servers that is used for load
> balancing synchronize?
>
|||Clustering is also not compatible with Windows Load Balancing, so you would
need to use a hardware based load balancer.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eQ%23n4Y6CGHA.620@.TK2MSFTNGP10.phx.gbl...
> Hi
> To synchronize the data between the 2 clusters, you need to use something
> like replication. Clustering is not a load-balancing technology.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> <mukesh.thiru@.gmail.com> wrote in message
> news:1135764099.960391.227140@.g43g2000cwa.googlegr oups.com...
>
|||mukesh.thiru@.gmail.com wrote:
> I have an architecture which has 4 DB servers on the whole; two will
> participate in load balancing and other two in fail over clustering.
> My question is how will the two DB servers that is used for load
> balancing synchronize?
If you are using SQL server 2005, you might want to consider using read
only shared databases for your reporting . Using a shared file system,
all the nodes can see the same database files which are read only, and
mount the database as read only. You could run reporting quaries
against up to 16 nodes archiving close to 16X speedup.
If your update cycle and reporting cycle do not overlap, switching
between the read write to read only (and back) takes about 20 seconds.
Note that no replication is needed and no storage space is wasted.
Tomer Meshorer
Database Engineer
Polyserve Inc (http://www.polyserve.com)
Email:tmeshorer@.polyserve.com
|||I'd very strongly disagree with that assessment. It isn't a shared file
system, it requires a SAN.
Additionally, just because you point 2 SQL Server instances at the same
database does not mean you get 2X performance improvement, just like
pointing 16 instances at a single database will not give you 16X
performance. You could in fact see your performance degrade. The only way
that your performance would improve is if you have queries running on each
instance that are not competing with each other for disk throughput. If
there is disk contention, your performance will degrade since a single set
of disks are now servicing requests from multiple instances at the same
time.
Read only, shared databases is a nice new feature, but any performance
benefit you may get is very highly dependent upon your query patterns, the
volume of data in your active working set, and the amount of physical data
you can cache in memory on each instance.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"tmeshorer" <tmeshorer@.polyserve.com> wrote in message
news:1135935720.275938.306680@.g43g2000cwa.googlegr oups.com...
> mukesh.thiru@.gmail.com wrote:
> If you are using SQL server 2005, you might want to consider using read
> only shared databases for your reporting . Using a shared file system,
> all the nodes can see the same database files which are read only, and
> mount the database as read only. You could run reporting quaries
> against up to 16 nodes archiving close to 16X speedup.
> If your update cycle and reporting cycle do not overlap, switching
> between the read write to read only (and back) takes about 20 seconds.
> Note that no replication is needed and no storage space is wasted.
>
> Tomer Meshorer
> Database Engineer
> Polyserve Inc (http://www.polyserve.com)
> Email:tmeshorer@.polyserve.com
>
|||Your scenario is correct on a specific context. Vis you have an I/O
bottleneck
However, this is customer specific.
In cases where you have a CPU bottleneck (usually occurs in DW type of
quaries,which tends to do sequential I/O) the suggested solution will
scale close to linear(at least as observed in internal benchmarks where
I/O was not a bottleneck)
What I am suggesting, and specifically in the context of reporting, is
an alternative way for scaling SQL server.
Currently the only way to scale is up, which become very costly when
you go to the 8,16,32 CPU boxes.
In addition, you cannot scale economically since you move in increments
of 2^n each time you want to decrease your reporting time (and move
exponentially in price)
Note that in both scenarios (scale up to big SMP, or scale out on a
shared file system cluster) if you have an I/O bottleneck you reach the
state as described in your email.
However scaling out is more economically sound (assuming that you do
not have I/O bottle neck) since:
1. It is much cheaper
2. Pay as you go
3. No storage duplication
4. No changes to the database configuration(I.e. no need for
replication).
Best,
Tomer Meshorer
Database Engineer
Polyserve Inc
|||My only point was that you stated that you would get a 16X performance
improvement by using this when you deploy on 16 machines. That statement is
100% false and we both know it. You get ZERO performance improvement. Not
a single, solitary second of improved performance. A query running on the
operational system and taking 10 seconds is going to take 10 seconds to
complete if you are using a scalable shared database model.
Do not mix improved performance with the ability to execute more read only
queries per unit time. It is NOT the same thing and is an incredibly
misleading statement. It is misleading statements like that which lead to
people implementing things and then saying that "SQL Server can't scale" or
"SQL Server can't perform", because they are trying to make a feature do
something that it was not designed to do and never had the possibility of
doing in the first place.
You CAN be increased throughput and you CAN get increased capacity by being
able to run many more of these 10 second queries simultaneously when they
are executed against multiple machines. The ONLY time that you get
increased throughput (more queries per second) or increased capacity (more
queries per second) is if you hae a VERY specific situation where memory or
CPU is the ONLY bottleneck. In those situations, you can get increased
throughput or capacity, BUT, you do NOT and never will get a linear
increase in query capacity. The amount of increased capacity is VERY highly
dependent upon your database design, the volume of data you are working
with, and query patterns.
You cited DW type of queries. There is no such thing. You either have
queries that perform sequential I/O or queries performing random I/O.
Giving it a fancy name doesn't change it, because you run both types of
queries in every operational system. So, let's look at a CPU contrained
query. We have system where we have 4 processors and 4 GB of RAM. Queries
executing against the 400GB of data within the database normally take 15
minutes to execute and pull as much as 3 GB of data. We have isolated the
issue to CPU bottlenecks and moved the system to an 8 processor machine,
thereby doubling the CPU capacity. We observed that the execution time of
this query drops to 11 minutes whereby, we found that memory became the
bottleneck in the system. We doubled the RAM to 8GB and found that the
query execution only dropped to 10 minutes. Both memory and CPU became
bottlenecks at that point, because we were all of a sudden executing more
queries per unit time and 3 simultaneously executing queries would saturate
our memory. This would cause pages to be ejected from the cache (incurring
additional processor overhead). More pages would have to be read from disk
(more processor overhead). We quickly found out that it if we increased the
processor or memory capacity, we would then shift the bottleneck to the I/O
subsystem.
Sure, you can craft testing matrices and benchmarks that will say that
scalable, shared databases will get you a nearly linear scaling. (VERY
important to note that the word is SCALING, NOT performance improvement.)
For every scenario that you come up which exhibits this behavior, I can
change it a hundred different ways to cause the performance to degrade
either by changing the query pattern, introducing a new query, changing the
indexing, increasing the data volume, changing query parameters, etc.
It is a nice technology that has a VERY specific use in an environment
experiencing VERY specific scalability issues. Unless you have done a full
analysis on the system to determine whether it is appropriate, implementing
this technology would be a very big mistake. It most definitely isn't going
to produce a linear scaling and there is a reason that Microsoft is not
giving any specific numbers. (The instant they publish numbers like you
have, there are going to be several thousand people handing evidence that
the findings are not correct.)
This very specific issue is what makes all of the Oracle marketing and sales
surrounding RAC a complete joke. Oracle RAC won't produce a linear scaling
for exactly the same reasons that Scalable Shared Databases won't produce a
linear scaling. If your query pattern, active data volumes, and bottlenecks
meet a VERY specific profile then these types of technologies will get you
increased SCALABILITY. (That does NOT mean you get improved performance.)
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"tmeshorer" <tmeshorer@.polyserve.com> wrote in message
news:1136012125.047011.237960@.g47g2000cwa.googlegr oups.com...
> Your scenario is correct on a specific context. Vis you have an I/O
> bottleneck
> However, this is customer specific.
> In cases where you have a CPU bottleneck (usually occurs in DW type of
> quaries,which tends to do sequential I/O) the suggested solution will
> scale close to linear(at least as observed in internal benchmarks where
> I/O was not a bottleneck)
> What I am suggesting, and specifically in the context of reporting, is
> an alternative way for scaling SQL server.
> Currently the only way to scale is up, which become very costly when
> you go to the 8,16,32 CPU boxes.
> In addition, you cannot scale economically since you move in increments
> of 2^n each time you want to decrease your reporting time (and move
> exponentially in price)
> Note that in both scenarios (scale up to big SMP, or scale out on a
> shared file system cluster) if you have an I/O bottleneck you reach the
> state as described in your email.
> However scaling out is more economically sound (assuming that you do
> not have I/O bottle neck) since:
> 1. It is much cheaper
> 2. Pay as you go
> 3. No storage duplication
> 4. No changes to the database configuration(I.e. no need for
> replication).
> Best,
> Tomer Meshorer
> Database Engineer
> Polyserve Inc
>
|||I should have clarified my point. I am looking at the actual wall clock
time that it takes to run a reporting job. I also assume that you have
more queries than servers, which I think would be the case for a
typical reporting job.
So from a wall clock time perspective, you will be able to get close to
X#server execution time improvement. I.e. if you are CPU constrained, a
6 hour job with 6 queries on one server can be reduced to 1 hour job on
6 servers.
If you have an I/O bottleneck there are two cases. If you are storage
constrained that this is a natural case. Vis, it does not matter if you
have 4*2 way servers or one 8 way server. If you are internal I/O BUS
constrains (e.g. PCI Express) moving to 4*2 way might improve your
situation since now you have 4 internal buses in your disposal.
However, I do not agree with your other point that this is very
specific situation. I think that you will encounter this situation at
the same probability that you will encounter any other kind of
bottleneck (which is about 1/3 of the cases). However, as your example
describe, to move out of the "CPU as a bottleneck" state into "I/O as
bottleneck state" you have to scale up. Based on your example, your
customer had to scale up from 4 way to 8 way.
Now lets say that you paid the money and now you are I/O constrained.
Ok, you buy more disks, change your RAID method, etc. Time passes and
you are again constrained by the CPU... are you going to move to 16
way?, 32 way? 64 way ?
My point is that if you do not have CPU bottleneck now, you will be
after you solve your current bottleneck. I.e. this is not VERY specific
scenario but rather a common one.
Tomer Meshorer
Database Engineer
Polyserve Inc (www.polyserve.com)
|||So that means that I can put 9 women to work and produce a baby in 1 month?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"tmeshorer" <tmeshorer@.polyserve.com> wrote in message
news:1136319106.372823.46070@.o13g2000cwo.googlegro ups.com...
>I should have clarified my point. I am looking at the actual wall clock
> time that it takes to run a reporting job. I also assume that you have
> more queries than servers, which I think would be the case for a
> typical reporting job.
> So from a wall clock time perspective, you will be able to get close to
> X#server execution time improvement. I.e. if you are CPU constrained, a
> 6 hour job with 6 queries on one server can be reduced to 1 hour job on
> 6 servers.
> If you have an I/O bottleneck there are two cases. If you are storage
> constrained that this is a natural case. Vis, it does not matter if you
> have 4*2 way servers or one 8 way server. If you are internal I/O BUS
> constrains (e.g. PCI Express) moving to 4*2 way might improve your
> situation since now you have 4 internal buses in your disposal.
> However, I do not agree with your other point that this is very
> specific situation. I think that you will encounter this situation at
> the same probability that you will encounter any other kind of
> bottleneck (which is about 1/3 of the cases). However, as your example
> describe, to move out of the "CPU as a bottleneck" state into "I/O as
> bottleneck state" you have to scale up. Based on your example, your
> customer had to scale up from 4 way to 8 way.
> Now lets say that you paid the money and now you are I/O constrained.
> Ok, you buy more disks, change your RAID method, etc. Time passes and
> you are again constrained by the CPU... are you going to move to 16
> way?, 32 way? 64 way ?
> My point is that if you do not have CPU bottleneck now, you will be
> after you solve your current bottleneck. I.e. this is not VERY specific
> scenario but rather a common one.
> Tomer Meshorer
> Database Engineer
> Polyserve Inc (www.polyserve.com)
>
|||No. What I suggest is "Concurrent " execution, I.e. wall time speed up.
However, I cannot formulate and answer using babies and women while
staying
politically correct :-)
Tomer Meshorer
Database Engineer
Polyserve Inc (www.polyserve.com)

Cluster Switch Hardware

New to clustering and wanted to know if anyone had any hardware suggestions
with regard to the dedicated switch for the heartbeat network.
Initially this will be a 2 node Active/Active cluster hosting SQL Server
connected to a SAN. I do expect to add at least another set of clustered
hosts or two before year's end.
We typically use all Cisco equipment.
Suggestions?
"john d" <johnd@.discussions.microsoft.com> wrote in message
news:D9FCFA8C-13FB-4A7C-9E88-9573BEF9C047@.microsoft.com...
> New to clustering and wanted to know if anyone had any hardware
> suggestions
> with regard to the dedicated switch for the heartbeat network.
Anything will work for you. I, personally, prefer dumb hubs. That way Bucky
can misconfigure it and hose up my heartbeat network.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Feb 12th
NYC starting Feb 19th
|||I agree with Russ. The bandwith requirements for the heartbeat are really
low. I wish I could get some old co-ax NICS and just run a single cable
instead of an active device.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Russ Kaufmann [MVP]" <russ@.clusterhelp.com> wrote in message
news:erF4iczOHHA.4280@.TK2MSFTNGP02.phx.gbl...
> "john d" <johnd@.discussions.microsoft.com> wrote in message
> news:D9FCFA8C-13FB-4A7C-9E88-9573BEF9C047@.microsoft.com...
> Anything will work for you. I, personally, prefer dumb hubs. That way
> Bucky can misconfigure it and hose up my heartbeat network.
>
> --
> Russ Kaufmann
> MVP - Windows Server - Clustering
> ClusterHelp.com, a Microsoft Certified Gold Partner
> Web http://www.clusterhelp.com
> Blog http://msmvps.com/clusterhelp
> The next ClusterHelp classes are:
> Denver starting Feb 12th
> NYC starting Feb 19th
>
|||Geoff/Russ - Thanks for your input. Glad I didn't already go out and buy a
switch.
"Geoff N. Hiten" wrote:

> I agree with Russ. The bandwith requirements for the heartbeat are really
> low. I wish I could get some old co-ax NICS and just run a single cable
> instead of an active device.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Russ Kaufmann [MVP]" <russ@.clusterhelp.com> wrote in message
> news:erF4iczOHHA.4280@.TK2MSFTNGP02.phx.gbl...
>
|||For a 2-node system, we typically use just a network cross-over cable. If
you expand this cluster for additional nodes, the heartbeat uses multi-cast
to communicate (Win2K3); so, you will definitely want to isolate this
network from the others, either by constructing a physical interconnect
between the servers or through a larger corporate switch interconnect and
the use of VLANs.
Regardless, unless you also duplicate the hardware, this network will also
represent a Single Point of Failure. If you are unfamiliar with this term,
you should learn it. It is the single most important concept with regards
to Highly Available systems.
In this case, you must provide multiple network paths between the servers.
In addition to the dedicated heartbeat network, we typically allow the
public network to also transport cluster heartbeat communications. In
Cluster Administrator, on the cluster object, you can specify the network
priority for cluster communications, but also include the secondary network
as a fail-safe.
Sincerely,
Anthony Thomas

"john d" <johnd@.discussions.microsoft.com> wrote in message
news:DC09A8BC-4126-4002-B526-401B90D2A1DB@.microsoft.com...
> Geoff/Russ - Thanks for your input. Glad I didn't already go out and buy
a[vbcol=seagreen]
> switch.
>
> "Geoff N. Hiten" wrote:
really[vbcol=seagreen]

Cluster Set up Question

Hello,
We are implementing clustering (Active/Passive) in our Production environment.
So we will have two windows server 2003 for SQL SERVER. (ProdDbServer1 and
ProdDbServer2)
I will install SQL Server on both the machine and i will create my database
(FinDB)
on both the machine's SQL SERVER with all the tables,store procs, functions
etc.
So lets say if my Active Server is in action and accepting all the OLTP
transaction
and for some reason if it goes fail...How can passive server can take over,
how
passive server can be at the same point of the fail of active server ?
Bacically how both the Database will be in sink with each other ?
Pls help me
A cluster consists of two machines and a storage system that is physically
connected to both systems. The Cluster software arbitrates ownership of the
disk resource so that only one server can access it at a time. The cluster
software also monitor the sql instance on the active host node for failure.
If the first node fails, ownership of the SQL instance and the corresponding
disk resource is transferred to the other node. From the outside, it looks
like the SQL Server stopped and restarted.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
> Hello,
> We are implementing clustering (Active/Passive) in our Production
> environment.
> So we will have two windows server 2003 for SQL SERVER. (ProdDbServer1 and
> ProdDbServer2)
> I will install SQL Server on both the machine and i will create my
> database
> (FinDB)
> on both the machine's SQL SERVER with all the tables,store procs,
> functions
> etc.
> So lets say if my Active Server is in action and accepting all the OLTP
> transaction
> and for some reason if it goes fail...How can passive server can take
> over,
> how
> passive server can be at the same point of the fail of active server ?
> Bacically how both the Database will be in sink with each other ?
> Pls help me
|||Thanks Geoff.
So From your reply, I understand that i will have to install SQL SERVER 2000
on both the windows 2003 server.
I have following questions.
1) After installing SQL SERVER on both the windows m/c, When i create
database on both the machine, Would i have to mentioned same Data files and
Transaction Log Path ( Because we will have storage will be in SAN), when i
create DB ?
2) For example if i create one table in the FinDB of on ServerA and then if
i go to FinDB of ServerB, I will see that table there too, if i will have
Datafiles and Transaction Log Path are same for both the servers ?
"Geoff N. Hiten" wrote:

> A cluster consists of two machines and a storage system that is physically
> connected to both systems. The Cluster software arbitrates ownership of the
> disk resource so that only one server can access it at a time. The cluster
> software also monitor the sql instance on the active host node for failure.
> If the first node fails, ownership of the SQL instance and the corresponding
> disk resource is transferred to the other node. From the outside, it looks
> like the SQL Server stopped and restarted.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
>
>
|||Thanks Geoff.
So From your reply, I understand that i will have to install SQL SERVER 2000
on both the windows 2003 server.
I have following questions.
1) After installing SQL SERVER on both the windows m/c, When i create
database on both the machine, Would i have to mentioned same Data files and
Transaction Log Path ( Because we will have storage will be in SAN), when i
create DB ?
2) For example if i create one table in the FinDB of on ServerA and then if
i go to FinDB of ServerB, I will see that table there too, if i will have
Datafiles and Transaction Log Path are same for both the servers ?
"Geoff N. Hiten" wrote:

> A cluster consists of two machines and a storage system that is physically
> connected to both systems. The Cluster software arbitrates ownership of the
> disk resource so that only one server can access it at a time. The cluster
> software also monitor the sql instance on the active host node for failure.
> If the first node fails, ownership of the SQL instance and the corresponding
> disk resource is transferred to the other node. From the outside, it looks
> like the SQL Server stopped and restarted.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
>
>
|||Does anyone want to rethink their perspective on why active/active and
active/passive should be used as terminology?
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
> Hello,
> We are implementing clustering (Active/Passive) in our Production
> environment.
> So we will have two windows server 2003 for SQL SERVER. (ProdDbServer1 and
> ProdDbServer2)
> I will install SQL Server on both the machine and i will create my
> database
> (FinDB)
> on both the machine's SQL SERVER with all the tables,store procs,
> functions
> etc.
> So lets say if my Active Server is in action and accepting all the OLTP
> transaction
> and for some reason if it goes fail...How can passive server can take
> over,
> how
> passive server can be at the same point of the fail of active server ?
> Bacically how both the Database will be in sink with each other ?
> Pls help me
|||If you install SQL Server twice, you will have 2 physically separate
instances of SQL Server running within a single cluster. If you then create
your database in each of these instances, you will have 2 physically
separate copies of the database. In order to synchronize data between the
two databases, you would have to implement replication, log shipping, or
database mirroring (2005 only). If one of the servers failed, you would
have to change your application connections to point at the other SQL Server
instance.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
> Hello,
> We are implementing clustering (Active/Passive) in our Production
> environment.
> So we will have two windows server 2003 for SQL SERVER. (ProdDbServer1 and
> ProdDbServer2)
> I will install SQL Server on both the machine and i will create my
> database
> (FinDB)
> on both the machine's SQL SERVER with all the tables,store procs,
> functions
> etc.
> So lets say if my Active Server is in action and accepting all the OLTP
> transaction
> and for some reason if it goes fail...How can passive server can take
> over,
> how
> passive server can be at the same point of the fail of active server ?
> Bacically how both the Database will be in sink with each other ?
> Pls help me
|||No. You create the cluster. You then install a single copy of SQL Server
into the cluster. You then create your database inside that instance. You
do not install SQL Server directly to a machine. It is installed to the
cluster.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:95A55E02-1BCB-47E1-9458-E01080486B3D@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff.
> So From your reply, I understand that i will have to install SQL SERVER
> 2000
> on both the windows 2003 server.
> I have following questions.
> 1) After installing SQL SERVER on both the windows m/c, When i create
> database on both the machine, Would i have to mentioned same Data files
> and
> Transaction Log Path ( Because we will have storage will be in SAN), when
> i
> create DB ?
> 2) For example if i create one table in the FinDB of on ServerA and then
> if
> i go to FinDB of ServerB, I will see that table there too, if i will have
> Datafiles and Transaction Log Path are same for both the servers ?
> "Geoff N. Hiten" wrote:
|||The SQL Installer is cluster-aware. That is, it handles all the details of
cluster-wide installation. It creates the virtual server and installs the
local binaries on all nodes. It also groups all the cluster resources and
sets the dependencies correctly. Once SQL is running and you are connected,
there is almost no difference in SQL operations between clustered and
non-clustered SQL Servers. You end up connecting to a virtual SQL Server
instance that looks identical regardless of which node is actually hosting
it at the moment.
Here is an excellent overview of SQL failover clustering that you may find
helpful:
http://www.microsoft.com/technet/pro.../failclus.mspx
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:95A55E02-1BCB-47E1-9458-E01080486B3D@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff.
> So From your reply, I understand that i will have to install SQL SERVER
> 2000
> on both the windows 2003 server.
> I have following questions.
> 1) After installing SQL SERVER on both the windows m/c, When i create
> database on both the machine, Would i have to mentioned same Data files
> and
> Transaction Log Path ( Because we will have storage will be in SAN), when
> i
> create DB ?
> 2) For example if i create one table in the FinDB of on ServerA and then
> if
> i go to FinDB of ServerB, I will see that table there too, if i will have
> Datafiles and Transaction Log Path are same for both the servers ?
> "Geoff N. Hiten" wrote:
|||Thanks for the reply.
No i do not want two sql server instance in single cluster, I just want one
instance in my cluster. so what should i do, install SQL SERVER 2000 Software
and create my database in just one machine (ProdSqlServerA)?
If i do like this and if i go to another sever (ProdSqlServerB), would i see
SQL SERVER and my created Db on that maching too ?
"Z" wrote:

> If you install SQL Server twice, you will have 2 physically separate
> instances of SQL Server running within a single cluster. If you then create
> your database in each of these instances, you will have 2 physically
> separate copies of the database. In order to synchronize data between the
> two databases, you would have to implement replication, log shipping, or
> database mirroring (2005 only). If one of the servers failed, you would
> have to change your application connections to point at the other SQL Server
> instance.
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
>
>
|||No thanks! I don't want Mike and Geoff mad at me anymore on this subject.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
"Z" <z@.z.com> wrote in message
news:%23MIgYMvFGHA.984@.tk2msftngp13.phx.gbl...
> Does anyone want to rethink their perspective on why active/active and
> active/passive should be used as terminology?
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
>
sqlsql

Cluster services overhead

It is possible, although I would not expect the clustering technology to =
have a noticable impact on performance. You talk about instances. Are =
you running multiple instances of SQL Server on the same hardware, or by =
"instance" do you mean Server A (standalone box) and Server B (clustered =
box)? If you are talking about multiple instances on one server are the =
instances configured to use the same amount of resources? =20
This reply will be posted within .server and it will also be copied to =
the .clustering newsgroup. Hopefully the experts that hang out there =
will have additional comments orideas.
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
>=20
> Consider the scenario ...
>=20
> Instance 1 of Sql Server running on Compaq with MSA1000=20
> disks=20
>=20
> Instance 2 of Sql Server running on Compaq with MSA1000=20
> disks BUT in a clustered environment
>=20
> We are finding that Instance 1 offers better performance=20
> than 2 for the same configuration (memory, sql version,=20
> windows etc). Could the use of cluster services for=20
> clustering be causing the overhead?
>=20
> TIA,
> Jack
>
I have never felt the presence of clustering on any of my SQL clusters. The
cluster service has such a small footprint. Could it be that you are not
comparing like DBs?
Cheers,
Rod
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OYr8Ip9TEHA.716@.TK2MSFTNGP11.phx.gbl...
It is possible, although I would not expect the clustering technology to
have a noticable impact on performance. You talk about instances. Are you
running multiple instances of SQL Server on the same hardware, or by
"instance" do you mean Server A (standalone box) and Server B (clustered
box)? If you are talking about multiple instances on one server are the
instances configured to use the same amount of resources?
This reply will be posted within .server and it will also be copied to the
..clustering newsgroup. Hopefully the experts that hang out there will have
additional comments orideas.
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
> Consider the scenario ...
> Instance 1 of Sql Server running on Compaq with MSA1000
> disks
> Instance 2 of Sql Server running on Compaq with MSA1000
> disks BUT in a clustered environment
> We are finding that Instance 1 offers better performance
> than 2 for the same configuration (memory, sql version,
> windows etc). Could the use of cluster services for
> clustering be causing the overhead?
> TIA,
> Jack
>

Cluster SDK

Can someone point me out SDK for clustering on Win2000? I am planning to
invoke Cluster API's using VBS...
Thanks in adv.
http://msdn.microsoft.com/library/de...clustering.asp
Good luck.
Anthony Thomas

"Vai2000" <nospam@.microsoft.com> wrote in message
news:u8Tad2dIGHA.1836@.TK2MSFTNGP11.phx.gbl...
> Can someone point me out SDK for clustering on Win2000? I am planning to
> invoke Cluster API's using VBS...
> Thanks in adv.
>
|||To be more specific, with VBScript you probably want to script MSCS using
Cluster Automation Server:
http://msdn.microsoft.com/library/de...er_scripts.asp
Linchi
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uhY8YbiIGHA.668@.TK2MSFTNGP11.phx.gbl...
> http://msdn.microsoft.com/library/de...clustering.asp
> Good luck.
>
> Anthony Thomas
>
>
> --
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:u8Tad2dIGHA.1836@.TK2MSFTNGP11.phx.gbl...
>

Cluster Parameters in BIDS Wizard?

I am wondering how to change the default parameters for the Clustering algorithm in BIDS. I set up an initial MiningStructure with the wizard but i want to tweak the

Clustering_Method and Cluster_Count parameters prior to training the dataset but I do not see where to do this..

thanks!

aem

Open the mining structure in BIDS, navigate to the Mining Models tab (2nd tab), right click on the mining model and select "Set Algorithm Parameters" in the context menu. You can also select "Properties" for the mining model and modify the algorithm parameters from the property grid.

Please let me know if you have any additional questions

Cluster on Same Box (for educational/training purposes)

Is it possible to setup a second instance of SQL Server Enterprise on a box
and setup clustering? I have someone asking me that has only one Server to
do this with and they want to learn about Clustering.
Win2003 <-> Win2003 (SameBox)
OR
Can you run SQL Server on an XP machine and still setup clustering on a
Win2K03 Server and cluster the 2?
XP <--> Win2003 (Two Boxes)
Thanks
Erik
Cluster on XP <--> Win2003 (Two Boxes) -- No, the hardware needs to be
identical.
A better solution would be to run PC emulation software like VMWARE and
create a 2-node virtual cluster on one Windows 2003 computer.
Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.
This posting is provided "as is" with no warranties and confers no rights.

Cluster Names

I have a basic segmentation model that uses the clustering algorithm. I've renamed the clusters in the cluster diagram, but they still show up as Cluster 1-n in the dimension browser and in the cube browser.

After renaming the clusters, I did a full reprosses on the data mining dimension and on the linked cube. Can anyone tell me what else I need to do to use the new cluster names in the report?

Thanks

A full reprocess of the DM dimension and the linked cube should do the trick - in fact, you don't really need to reprocess the cube with the linked DM dimension. Are you sure the reprocess completed successfully and you refreshed the dimension/cube browsers?|||

Yes. I tried it again with the same results. I noticed that when I opened the project that the cluster names that I entered were gone. The model had Cluster 1-10 again. I renamed them again, saved the .dmm file, reprocessed the dimension (full, successful), and reconnected in the browser. It still shows Cluster 1-10. I tried re-processing the model. That resets the cluster names in the model viewer.

Thanks for your help Raman.

|||

Renaming clusters in the viewer directly updated the model on the server - there is no need to reprocess the model. What you're seeing still looks like a client refresh issue.

What version of the Analysis Services 2005 and client tools (BI Dev Studio) are you running? RTM or SP1? Would you be able to provide complete repro steps for this problem?

|||

SP1, using BIDS to browse

In trying to re-create the problem in AdventureWorks, I found that changing the cluster names worked. I noticed the AllowDrillthrough setting in the AW cluster model was set to True. After changing that property in my project, the cluster names showed in the dimension. This seems to fix the problem.

Thanks for your help Raman.

sqlsql

Cluster Model Viewer Timeout

Hi,

I am trying to browse a clustering model and encounter the following timeout error:

XML for Analysis parser: The XML for Analysis request timed out before it was completed.
Execution of the managed stored procedure GetNodeGraph failed with the following error: Exception has been thrown by the target of an invocation.Microsoft::AnalysisServices::AdomdServer::AdomdException.

Is there anything I can do to change settings to enable viewing of this model? Or, perhaps we have too many attributes in the model?

Thanks.

Please try the following:

In BI Dev Studio, go to the Tools menu and select Options\Business Intelligence Designers. Change the value of the Query Timeout to some larger value (600 would be a good start)

|||

Excellent, thanks Bogdan! That worked perfectly. For clarification, it's:

Tools --> Options

--> Browse left-side tree in the pop-up window to Designers --> Analysis Services Designers --> General

cluster manager and sql authentication

I am a newbie to clustering so I hope this makes sense. Does the Cluster
Manager have to be in the sysadmin role? If not, what are the roles that it
does require?
Thanks for any guidance.
Neil
The Cluster Service must be a local admin on each server. The Cluster
Manager application is an MMC snap-in and can run wherever. Specific
cluster functions may need specific security role membership. I generally
give the person who administers a Cluster local administrator rights on each
node.
Geoff N. Hiten
Microsoft SQL Server MVP
"Neil W." <neilw@.netlib.com> wrote in message
news:%23VNuQeCeFHA.2604@.TK2MSFTNGP15.phx.gbl...
>I am a newbie to clustering so I hope this makes sense. Does the Cluster
> Manager have to be in the sysadmin role? If not, what are the roles that
> it
> does require?
> Thanks for any guidance.
> Neil
>
>
|||Thanks for the reply. The cluster manager appears to login with Windows
login: domain\Cluster. However, I cannot locate a domain user or a local
user named "Cluster". Am I missing something?
______________________
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:usU0lhCeFHA.1136@.TK2MSFTNGP12.phx.gbl...
The Cluster Service must be a local admin on each server. The Cluster
Manager application is an MMC snap-in and can run wherever. Specific
cluster functions may need specific security role membership. I generally
give the person who administers a Cluster local administrator rights on each
node.
Geoff N. Hiten
Microsoft SQL Server MVP
"Neil W." <neilw@.netlib.com> wrote in message
news:%23VNuQeCeFHA.2604@.TK2MSFTNGP15.phx.gbl...
>I am a newbie to clustering so I hope this makes sense. Does the Cluster
> Manager have to be in the sysadmin role? If not, what are the roles that
> it
> does require?
> Thanks for any guidance.
> Neil
>
>
|||Are you sure? The cluster service account should be a domain-level account
with local admin rights on each node. There should be a domain account that
matches the service account.
Geoff N. Hiten
Microsoft SQL Server MVP
"Neil W." <neilw@.REMOVEnetlib.com> wrote in message
news:eK6Ut7CeFHA.1448@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply. The cluster manager appears to login with Windows
> login: domain\Cluster. However, I cannot locate a domain user or a local
> user named "Cluster". Am I missing something?
> ______________________
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:usU0lhCeFHA.1136@.TK2MSFTNGP12.phx.gbl...
> The Cluster Service must be a local admin on each server. The Cluster
> Manager application is an MMC snap-in and can run wherever. Specific
> cluster functions may need specific security role membership. I generally
> give the person who administers a Cluster local administrator rights on
> each
> node.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Neil W." <neilw@.netlib.com> wrote in message
> news:%23VNuQeCeFHA.2604@.TK2MSFTNGP15.phx.gbl...
>
>

Cluster issue

I'm having an issue with a two node cluster. We're using failover clustering
in SQL 2000 but everytime the first node fails it doesn't automatically
failover to the second node. Are there any KB articles dealing with this?
I've searched but can't seem to find what I'm looking for. Thanks in advance.http://www.microsoft.com/technet/archive/winntas/support/mscstswp.mspx
--
MG
"Nicholas" wrote:
> I'm having an issue with a two node cluster. We're using failover clustering
> in SQL 2000 but everytime the first node fails it doesn't automatically
> failover to the second node. Are there any KB articles dealing with this?
> I've searched but can't seem to find what I'm looking for. Thanks in advance.|||You need to look at the SQL Server and System Event Application logs to see
what resource is not coming online and why. There are many reasons why a
cluster won't fail over, so finding the exact cause is the first step. If
you have troubleshooting time, you can manually (using the Cluster
Administrator tool) take the SQL group offline, move it to the other node,
and bring each resource online one at a time to see exactly where it fails.
Post your results and we will go from there.
Geoff N. Hiten
Microsoft SQL Server MVP
"Nicholas" <Nicholas@.discussions.microsoft.com> wrote in message
news:509577DA-5060-4B80-A61D-28981BED4233@.microsoft.com...
> I'm having an issue with a two node cluster. We're using failover
> clustering
> in SQL 2000 but everytime the first node fails it doesn't automatically
> failover to the second node. Are there any KB articles dealing with this?
> I've searched but can't seem to find what I'm looking for. Thanks in
> advance.|||Thanks. Didn't think to check TechNet for some reason.
"MGeles" wrote:
> http://www.microsoft.com/technet/archive/winntas/support/mscstswp.mspx
> --
> MG
>
> "Nicholas" wrote:
> > I'm having an issue with a two node cluster. We're using failover clustering
> > in SQL 2000 but everytime the first node fails it doesn't automatically
> > failover to the second node. Are there any KB articles dealing with this?
> > I've searched but can't seem to find what I'm looking for. Thanks in advance.

Cluster Installation Failure

Thanks in advance for any help.
A SQL 2005 Failover Clustering installation (from the RTM disks) failed on
Database Services. Please see messages below. Windows cluster server name
is COMDEVDB01 (Windows 2003), primary node = COMDEVCLU01, secondary node =
COMDEVCLU02. SQL cluster virtual name = COMDEVSQLCLU, named instance =
COMDEVPROD. I installed from COMDEVCLU01. Should I install from COMDEVDB01
while COMDEVCLU01 is the active node (i.e., I will be on COMDEVCLU01)?
Earlier install failed on SQLBrowser errors and rolled back. Before the
cluster install two non-clustered SQL 2005 named instances had been installed
on COMDEVCLU01 and SP2 had been applied (both current versions = 3152).
After the SQLBrowser failure I installed a non-clustered SQL 2005 named
instance on COMDEVCLU02 with Database Services and Integration Services (also
version 3152). After that, the cluster install on COMDEVCLU01 failed with
these errors but did not rollback.
Should I try to install SP2 for the SQL cluster from COMDEVCLU01?
Can I "complete" this installation (save it) or will I have to uninstall and
re-install?
Should I uninstall all SQL Server instances on COMDEVCLU01 and install the
clustered instance first?
summary.txt message:
Machine : COMDEVCLU01
Product : Microsoft SQL Server 2005
Product Version : 9.00.1399.06
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0006_COMDEVCLU01_SQL.l og
Last Action : ComponentUpgrade
Error String : The setup has encountered an error while Upgrading
Components. The error: -2147019873
Error message: The group or resource is not in the correct state to perform
the requested operation.
Error Number : 29550
SQLSetup0006_COMDEVCLU01_SQL.log message:
MSI (s) (AC:14) [15:52:23:016]: Product: Microsoft SQL Server 2005 - Update
'Hotfix 3024 for SQL Server Database Services 2005 ENU (KB000000)' could not
be installed. Error code 1603.
MichaelA_CCGA
Michael:
I will be posting a similar question to yours entitled "Failed SQL Cluster
Advice." That hopefully will get answered as well.
In the meantime you mentioned RTM Disks. Are these CDs and did your install
fail when you put in Disk 2 "Tools" This is my issue and I have googled to
find that this is a known issue and the resolution is copy both disks to a
common path.
Was this your issue as well?
Thanks, emagidson
"MichaelA_CCGA" wrote:

> Thanks in advance for any help.
> A SQL 2005 Failover Clustering installation (from the RTM disks) failed on
> Database Services. Please see messages below. Windows cluster server name
> is COMDEVDB01 (Windows 2003), primary node = COMDEVCLU01, secondary node =
> COMDEVCLU02. SQL cluster virtual name = COMDEVSQLCLU, named instance =
> COMDEVPROD. I installed from COMDEVCLU01. Should I install from COMDEVDB01
> while COMDEVCLU01 is the active node (i.e., I will be on COMDEVCLU01)?
> Earlier install failed on SQLBrowser errors and rolled back. Before the
> cluster install two non-clustered SQL 2005 named instances had been installed
> on COMDEVCLU01 and SP2 had been applied (both current versions = 3152).
> After the SQLBrowser failure I installed a non-clustered SQL 2005 named
> instance on COMDEVCLU02 with Database Services and Integration Services (also
> version 3152). After that, the cluster install on COMDEVCLU01 failed with
> these errors but did not rollback.
> Should I try to install SP2 for the SQL cluster from COMDEVCLU01?
> Can I "complete" this installation (save it) or will I have to uninstall and
> re-install?
> Should I uninstall all SQL Server instances on COMDEVCLU01 and install the
> clustered instance first?
> summary.txt message:
> ----
> Machine : COMDEVCLU01
> Product : Microsoft SQL Server 2005
> Product Version : 9.00.1399.06
> Install : Failed
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0006_COMDEVCLU01_SQL.l og
> Last Action : ComponentUpgrade
> Error String : The setup has encountered an error while Upgrading
> Components. The error: -2147019873
> Error message: The group or resource is not in the correct state to perform
> the requested operation.
> Error Number : 29550
> ----
> SQLSetup0006_COMDEVCLU01_SQL.log message:
> ----
> MSI (s) (AC:14) [15:52:23:016]: Product: Microsoft SQL Server 2005 - Update
> 'Hotfix 3024 for SQL Server Database Services 2005 ENU (KB000000)' could not
> be installed. Error code 1603.
> --
> MichaelA_CCGA
|||Sorry I dropped out of the discussion but I wanted to post the question
before I took a few days off and I'm just now catching up. I have printed
your exchange with Geoff and I will review that in more detail.
I did not have the "2nd CD" problem but the information about uninstalling
may prove very useful.
Thanks again, and I'll check back in tomorrow or Thursday.
MichaelA_CCGA
"EMagidson" wrote:
[vbcol=seagreen]
> Michael:
> I will be posting a similar question to yours entitled "Failed SQL Cluster
> Advice." That hopefully will get answered as well.
> In the meantime you mentioned RTM Disks. Are these CDs and did your install
> fail when you put in Disk 2 "Tools" This is my issue and I have googled to
> find that this is a known issue and the resolution is copy both disks to a
> common path.
> Was this your issue as well?
> Thanks, emagidson
> "MichaelA_CCGA" wrote:

cluster hardware - CPU, memory

1. In MSCS and SQL Server 2-node clustering on windows 2000 advance server,
are there any restrictions on the hardware. That is, do the 2 nodes
necessarily should have the same number of CPU and memory?
2. when we have 2 node with sql server in cluster, can we install another
non-clustered instance on the same server?
3. is there a link which describes the difference between 1 sql instance
cluster and 2 sql instance cluster?
1). Certified cluster configurations must be symmetrical. That is all
nodes indentical. It is possible to run asymmetrical clusters, but they are
often more trouble and less reliable than a stand-alone SQL instance.
2). Yes.
3). The Microsoft SQL Server 2000 Resource Kit has some good information on
clustering, including using more than 2 nodes and multi-instance support.
BOL also has some basic information.
Geoff N. Hiten
Microsoft SQL Server MVP
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:2E64C5E8-A0BB-41AB-8174-FF26137A1F17@.microsoft.com...
> 1. In MSCS and SQL Server 2-node clustering on windows 2000 advance
> server,
> are there any restrictions on the hardware. That is, do the 2 nodes
> necessarily should have the same number of CPU and memory?
> 2. when we have 2 node with sql server in cluster, can we install another
> non-clustered instance on the same server?
> 3. is there a link which describes the difference between 1 sql instance
> cluster and 2 sql instance cluster?
sqlsql

Cluster Addresses

Hi,
I am a newbie to Clustering so please bear with me.
We have a db environment set up with Win 2003 and SQL 2000 connecting to a
single disk array.
The question I have is, what is the difference between the Cluster IP
Address and the SQL IP address.
What I really want to know is what is the purpose for the two different
addresses?
Thanks for any help.
JD
When you create a cluster, you start working with Virtual Servers. The
cluster itself becomes a Virtual server, as does each SQL instance you
install. Each virtual server consists of a minimum of a physical disk
resource, an IP address, and a network name. Of course, each virtual server
has other resources to make it actually do something useful. So, the
Cluster IP address is used to connect to Cluster control resources and the
SQL IP address is used to connect to SQL service resources. These addresses
are in addition to the IP addresses of the host nodes.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"jimmy" <jimmy@.discussions.microsoft.com> wrote in message
news:DC5A7EF8-C516-411B-823D-BA36A6E24A85@.microsoft.com...
> Hi,
> I am a newbie to Clustering so please bear with me.
> We have a db environment set up with Win 2003 and SQL 2000 connecting to a
> single disk array.
> The question I have is, what is the difference between the Cluster IP
> Address and the SQL IP address.
> What I really want to know is what is the purpose for the two different
> addresses?
> Thanks for any help.
> JD
|||Hi Geoff,
Thanks for the explanation. We have a single application that points at the
database, so should we use the SQL IP Address to point the application at the
db server?
So from your explanaition if a file share was one of the Cluster resources
and you wanted to acces it. This is when you would use the Cluster address
to access the file share. Like so:
\\ClusterIP\FileShare
Thanks Again
JD
"Geoff N. Hiten" wrote:

> When you create a cluster, you start working with Virtual Servers. The
> cluster itself becomes a Virtual server, as does each SQL instance you
> install. Each virtual server consists of a minimum of a physical disk
> resource, an IP address, and a network name. Of course, each virtual server
> has other resources to make it actually do something useful. So, the
> Cluster IP address is used to connect to Cluster control resources and the
> SQL IP address is used to connect to SQL service resources. These addresses
> are in addition to the IP addresses of the host nodes.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "jimmy" <jimmy@.discussions.microsoft.com> wrote in message
> news:DC5A7EF8-C516-411B-823D-BA36A6E24A85@.microsoft.com...
>
>
|||Yes, you use the SQL vurtial server IP address as the connection target.
Personally, I prefer to use the network name. That way, I can play with
Aliases and DNS records during disasters.
You shouldn't use the cluster resource group and quorum disk for anything
extra. You should create another virtual server and add the file service to
that resource group. \\FileShareVirtualServerName\ShareName
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"jimmy" <jimmy@.discussions.microsoft.com> wrote in message
news:641B055A-4878-4E12-AE77-DE4BFDEF3718@.microsoft.com...
> Hi Geoff,
> Thanks for the explanation. We have a single application that points at
the
> database, so should we use the SQL IP Address to point the application at
the
> db server?
> So from your explanaition if a file share was one of the Cluster resources
> and you wanted to acces it. This is when you would use the Cluster
address[vbcol=seagreen]
> to access the file share. Like so:
> \\ClusterIP\FileShare
> Thanks Again
> JD
> "Geoff N. Hiten" wrote:
server[vbcol=seagreen]
the[vbcol=seagreen]
addresses[vbcol=seagreen]
to a[vbcol=seagreen]
different[vbcol=seagreen]
|||Hi Geoff,
Thats great.
Thanks
Jamie.
"Geoff N. Hiten" wrote:

> Yes, you use the SQL vurtial server IP address as the connection target.
> Personally, I prefer to use the network name. That way, I can play with
> Aliases and DNS records during disasters.
> You shouldn't use the cluster resource group and quorum disk for anything
> extra. You should create another virtual server and add the file service to
> that resource group. \\FileShareVirtualServerName\ShareName
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "jimmy" <jimmy@.discussions.microsoft.com> wrote in message
> news:641B055A-4878-4E12-AE77-DE4BFDEF3718@.microsoft.com...
> the
> the
> address
> server
> the
> addresses
> to a
> different
>
>
|||So...
Are there any restrictions on the Cluster IP and SQL IP?
If I have an internal and external NIC, should one go on each? Can they have neighboring IPs?
The reason I ask is that I set up a new cluster with the Cluster IP on the external NIC and the SQL IP on the internal NIC. At this point everything worked fine. However the requirements changed and I changed the SQL IP from the internal NIC to the external one - with an IP contigious to the Cluster IP. Ever since, I can not get my application to connect over TCP. Sometimes the sql services will not come online either until I change the IPs back to the original settings.
I have changed the IPs both in Cluster Admin and using the SQL installer as indicated in this KB article: http://support.microsoft.com/kb/244980.
Any thoughts?

cluster

Hello ,
I have couple of questions on clustering as well as on hardware.
1) Is the license for SQL Server 2005 Enterprise edition ( Production) on
active/active and active/passive is same?
2) Is the license for sql server 2005 is socket based or processor based, is
there any differnce between them.
3) Do we have any figures for SQL Server 2005 performance on single socket
as well as dual socket servers ?
4) If we have a active/active cluster on single socket on quad core
processor , what will be the license as well as performance when compared to
the active/active on dual socket on quad processor?
Any input is appreciated?
Comments Inline
"IT" <IT@.discussions.microsoft.com> wrote in message
news:87B5B6CB-413D-4A12-A4D3-59CDAC0C569A@.microsoft.com...
> Hello ,
> I have couple of questions on clustering as well as on hardware.
> 1) Is the license for SQL Server 2005 Enterprise edition ( Production) on
> active/active and active/passive is same?
Sort of. There is a single-instance or N+1 exception to normal licensing
requirements. If you have a node that is always a standby system, Then it
doesn't have to be licensed for SQL. Two nodes, one instance, one license.
Four nodes, three instances, three licenses.
Also, on two-node clusters, you might look into Standard Edition for
clustering. It's lots cheaper and it does support two-node clusters.

> 2) Is the license for sql server 2005 is socket based or processor based,
> is
> there any differnce between them.
Socket-based. This makes dual and quad core systems very price competitive
from a licensing standpoint.

> 3) Do we have any figures for SQL Server 2005 performance on single socket
> as well as dual socket servers ?
I generally don't trust benchmarks all that much. With SQL, the nature of
the load can vary so much between systems that finding a matching benchmark
is difficult if not impossible. Dual and quad socket systems are commodity
priced so I wouldn't go with a single-socket system except for the very
lowest application. Certainly not for a cluster.

> 4) If we have a active/active cluster on single socket on quad core
> processor , what will be the license as well as performance when compared
> to
> the active/active on dual socket on quad processor?
>
Assuming per-socket licensing, you would need one socket license for the
first and four licenses for the second. CAL licensing (assuming the usage
meets the requirements) would be one server license and N CALS for the first
or two server licenses and N CALs for the second. N being the number of
client users or devices.

> Any input is appreciated?
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
|||Thanks that was helpful....
Is there any KB or white paper describing the same for some documentation...
"Geoff N. Hiten" wrote:

> Comments Inline
> "IT" <IT@.discussions.microsoft.com> wrote in message
> news:87B5B6CB-413D-4A12-A4D3-59CDAC0C569A@.microsoft.com...
> Sort of. There is a single-instance or N+1 exception to normal licensing
> requirements. If you have a node that is always a standby system, Then it
> doesn't have to be licensed for SQL. Two nodes, one instance, one license.
> Four nodes, three instances, three licenses.
> Also, on two-node clusters, you might look into Standard Edition for
> clustering. It's lots cheaper and it does support two-node clusters.
> Socket-based. This makes dual and quad core systems very price competitive
> from a licensing standpoint.
> I generally don't trust benchmarks all that much. With SQL, the nature of
> the load can vary so much between systems that finding a matching benchmark
> is difficult if not impossible. Dual and quad socket systems are commodity
> priced so I wouldn't go with a single-socket system except for the very
> lowest application. Certainly not for a cluster.
> Assuming per-socket licensing, you would need one socket license for the
> first and four licenses for the second. CAL licensing (assuming the usage
> meets the requirements) would be one server license and N CALS for the first
> or two server licenses and N CALs for the second. N being the number of
> client users or devices.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
|||Microsoft SQL Server 2005 Licensing
http://www.microsoft.com/sql/howtobuy/default.mspx#EXE
The links to the white paper and FAQ are very useful
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"IT" <IT@.discussions.microsoft.com> wrote in message
news:8848E76E-646D-4AA9-B7FD-7FCCA6D5DAFE@.microsoft.com...[vbcol=seagreen]
> Thanks that was helpful....
> Is there any KB or white paper describing the same for some
> documentation...
> "Geoff N. Hiten" wrote:

Clus. Index keys

Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> > Hi,
> >
> > I read in one sql document tht--If the table have a clustered index, the
> > bookmarks of all non-clustered indexes will have clustering keys for each
> > row, and physically moving a row on disk would of course not have any
> > effect
> > on these.
> >
> > My question is wht is getting stored in clustered index keys which is
> > independent of data row location(as it can be inferred from above tht it
> > doesnt affect non clus. index bookmarks).
> >
> > Thanks in advance.
> > Manu Jaidka
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>> Manu,
>> What is stored in the non-clustered key is the value of the associated
>> clustered key.
>> Therefore, searching a non-clustered index results in the clustered index
>> key, after which the clustered index is searched to find the row.
>> One side effect of this is that the size of the non-clustered index is
>> affected by the size of the clustered index, so keeping the cluster as
>> small
>> as is reasonable is a good idea.
>> RLF
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>> > Hi,
>> >
>> > I read in one sql document tht--If the table have a clustered index,
>> > the
>> > bookmarks of all non-clustered indexes will have clustering keys for
>> > each
>> > row, and physically moving a row on disk would of course not have any
>> > effect
>> > on these.
>> >
>> > My question is wht is getting stored in clustered index keys which is
>> > independent of data row location(as it can be inferred from above tht
>> > it
>> > doesnt affect non clus. index bookmarks).
>> >
>> > Thanks in advance.
>> > Manu Jaidka
>>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David

Clus. Index keys

Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:

> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as sma
ll
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David

Clus. Index keys

Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu Jaidka
Manu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:

> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>
|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David