Hi,
I have a 4 Gb RAM , 2 Xeon processors machine running SQL Server 2000. I
have a large number of records and it seems to be collapsed. I would like to
upgrade this configuration , and someone told me about clustering the
database. What I would like to know is :

- Is a active/active sql cluster a load balancing solution?

- If not, do I have to buy a hardware specific load-balancing?

Thanks in advance,
Albert

Clustering for SQL Server is strictly for Hardware Failover and has nothing
to do with Load Balancing. You should consider scaling up by getting a
machine with more processors if you need more power.

--

Andrew J. Kelly
SQL Server MVP


<azarazaga (AT) goldcomm (DOT) com> wrote

So, NLB and MSCS cannot work? I was looking at this url :
http://www.microsoft.com/windows2000...ss/cluster.asp
and I found this :
"A server cluster is a set of independent servers (referred to as nodes) and
connected storage devices that are managed together. They present a single
view of the server to users, applications, and the network. Windows 2000
Advanced Server supports two-node clusters, and Datacenter Server supports
four-node clusters."

Dont this mean that I can have 2 or more servers acting as one to my users?

Thanks again,
Albert

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

NLB is for stateless applications such as a web farm.

MSCS is for failover clustering of stateful applications such as SQL. A
clustered SQL instance is a virtual server that can live on any of the
clustered host nodes at any given moment. However, it can only live on a
single node at any moment in time. Thus, you only get one node's computing
power at a time.

You can create multiple instances and use multiple host nodes, but each
instance is independant of the others and cannot access the same databases.

As Andrew recommended, scaling UP in SQL is the way to go. You can purchase
some VERY powerful multi-processor boxes these days.


--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

<azarazaga (AT) goldcomm (DOT) com> wrote

NLB is not a very database friendly architecture unless your data is pretty
much read only. Clustering is one of those terms that is overloaded and can
be very confusing at times. When it comes to SQL Server, Windows clustering
is a hardware failover option only. You can use NLB to spread the user load
across multiple machines but when you have multiple active (Inserts, Updates
& Deletes) databases that need to be kept in sync this falls short. The
currently recommended way to scale your database is to scale up. Meaning
move from a 2 processors to a 4 or 8 etc. processor server. If you need it
to be highly available then hardware clustering is one option. Load
balancing with SQL Server is extremely difficult to accomplish.

--

Andrew J. Kelly
SQL Server MVP


<azarazaga (AT) goldcomm (DOT) com> wrote

Hello,

I have same issue, all DB is in the memory with PAE switch 6GB RAM, 2xCPU PIII
Considering the Performance monitor CPU stays 70-80% so we have to scale up
but to my knowledge Scale Out is not possible.
What is the meaning of the Virtual Server, when one node is always sleeping lets say
once in year it does a fialover and the apps are fail-aware. From the long term practice
I can say that, we have to replace now with a single 4 CPU machine and throw away the cluster,because
even in a case of failover there is no ping for some 10 seconds there we lose 100 of transactions throughput ability.
If a single server fails it will be automatically restarted which takes 10 seconds. In a case of Active/Active
we have 2 separate databases with 2 separate IPs and not one single.
As you say "Load balancing with SQL Server is extremely difficult to accomplish." Why? What needs to be done in order to accomlish this. Do you figure it out now the whole meaning of Clustering or the term Scale Out?
Is there a way to do 2 way Transactional replication between Virtual Server 1 and Virtual Server 2 having the same database of course on different storage without goin out to the distributor?
All apps are using one single ADO provider string.
Do we have to make them using another provider string as well,
so that we ensure all data between the virtual servers is the same?
Any suggestions are appreciated.

Best Regards,
C.Dimitrov
MCP

Once again a when you Cluster SQL Server the two (or more) nodes are
strictly for hardware failover. You can have an Active/Active setup but the
2 nodes are completely different servers and know nothing of each other when
it comes to normal SQL Server related activities. The "Virtual" server is
just a way to abstract the clients from the currently active Server in that
cluster group. The "Virtual" server knows about the Active instance on
node1 and it's associated Passive instance on node2, not the two Active
instances that each reside on separate nodes. Sure you could use
transactional replication between two SQL Servers but not as you are
seeking. If you went this route there would be no need for a Cluster since
you would have 2 separate machines each running all the time. But you now
have to manually deal with switching the clients over to the backup server
when the main one goes down. Yes this can be done but it is a more manual
and involved process. The reason why it is difficult to do load balancing
is that SQL Server can not share the database with two instances of SQL
Server. You can only have 1 physical instance of sql server per database.
As for how long it takes to fail over in a cluster that is governed mainly
by how long it takes to recover your database after the fail over. If 10
seconds is too long then you might consider changing your recovery interval
some.

--

Andrew J. Kelly
SQL Server MVP


"C.Dimitrov" <anonymous (AT) discussions (DOT) microsoft.com> wrote

figure it out now the whole meaning of Clustering or the term Scale Out?
different storage without goin out to the distributor?

Thanks, I agree but I have some more questions,trying to find the best solution
Is it possible to move that DB device into Federated SQL Servers such tha
1 Physical device is partitioned accross the servers and then we can use partitione
views with UNION to select a data between 2 or more servers, which is good for presentatio
but does it also work for INSERT,UPDATE,DELETE, such that all statements are consitentl
enter on all that servers in the federation, having one DB device? Are those federation member
acting the same way as Active/Active scenario

Best Regards
C.Dimitro
MCP

No, not at all. Active/Active simply means that there are 2 nodes in a
cluster and each node has an Actively running Sql Server instance on it.
Both of these instances are totally independent of each other and do not
share any data or processing what so ever. If one fails it switches over
the the other node and now you have 2 Instances running on the same node
instead of one on each node. A federated database essentially uses views to
gather data or process requests on one or more of a series of servers set in
what is called a federation of servers. A federation is not a cluster.
While you can have the servers in the federation clustered for hardware
redundancy that is irrelevant to how a federation works. If you have say 5
servers in a federated setup and any one of them goes down you loose at
minimum the ability to read and write data to that server but more than
likely it will affect all processing. This process is fully documented
under Federated Databases. So you can use federated servers and what is
called "Distributed Partitioned Views" to somewhat spread work load across
more than one server but it is not a true load balancing solution. While
this technology does indeed work it is costly and very difficult to set up
and maintain. It is recommended that you scale up with the hardware vs
scaling out. You can scale up to a 64 processor 64 bit machine now so
scaling up is certainly a viable option.

--

Andrew J. Kelly
SQL Server MVP


"C.Dimitrov" <anonymous (AT) discussions (DOT) microsoft.com> wrote