There is a large amount of debate going on in our IT group about whether to
go with Active/Active or Active/Passive. We are running several databases
using SQL 7.0 std edition. The server has 4 cpus and can only address 2gigs
(due to the std. edition). We are getting a pair of new servers with 8gigs
of ram and 4 cpus. We plan to upgrade to SQL 2000 Enterprise Edition.
Currently we are having performance problems relating to not enough memory.

Here is the debate. Some are saying to put all of the databases on one
server and make it active/passive so that we can access the full 8gigs of
ram. Others are saying that we would be better to go active/active. That
with the faster CPUs and 4Gigs of ram for each server and having half the
databases on each would be the better option.

I am leaning toward the Active/Active option, but thought I should get
opinions from others who have already gone through this.

Thanks.

Hi

The biggest issue with be your disk throughput capabilities in both
scenarios. If your IO is the bottleneck, either solution will still be slow.

There are the following implications:
Active/Active: Downside: Both boxes are live at the same time so you need
double the licenses compared to an active/passive
Active/Active: Upside: Only 1/2 of your applications would be affected by a
failover as the other 1/2 are already running on the other node.

If you have the hardware and the budget, Active/Active is the one that will
give you the most bang for the buck as both servers are working at the same
time.

Cheers
--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike (AT) NOSPAMepprecht (DOT) net

Specialist SQL Server Solutions and Consulting

"Mike Johnson" <mj (AT) noemail (DOT) com> wrote

One other aspect of the Active/Active would be that you need 2 separate
physical drives (or arrays). One for each SQL server.

Rand
This posting is provided "as is" with no warranties and confers no rights.

It may be helpful to review "Choosing a Cluster Model" in
the Windows 2000 documentation at
http://www.microsoft.com/windows2000/en/advanced/help/.

Linchi

rboyd (AT) onlinemicrosoft (DOT) com (Rand Boyd [MS]) wrote in message news:<bwH#LcR2DHA.2184 (AT) cpmsftngxa07 (DOT) phx.gbl>...
In general, you can use a clustered file system and have only one
physical disk instead of two.

Another issue is that in a active/active cluster you will be forced to
use a named instance which might imply changing your application
connection strings. Especially if you are migrating from SQL 7.


----
Tomer Meshorer
Database Engineering
Polyserve Inc
http://www.polyserve.com/sql

Please differentiate the marketing statements for your product from what is
available and officially supported from Microsoft.

Multiple SQL instances on a MS cluster require separate physical disk
resources. Unsupported add-on products may change that behavior.

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




"Tomer Meshorer" <tmeshorer (AT) polyserve (DOT) com> wrote

In an active/active set up at failover time both SQL
Servers will compete for resource on the remaining active
node.

The only way to ensure both instances will give reasonable
performance is therefore to limit both SQL instances in
terms of CPU and RAM on their regular host nodes.

The temptation is not to do this (you just mention RAM) in
which case at failover time you might run into problems.
We have several active/passive clustres (no problems) and
one active/active where our sound advice (above) was
ignored. Both active/active nodes are much busier than
ever envisaged and we ahve big problems at failover time.
Technically failover works but the failing over instance
is unusable.

Personally I would go for active/passive and use AWE.
Sorry I haven't left me Email address it's to avoid
potential future spam.

You make some very good points here. I will mention that at this time we
are averaging 30% CPU utilization on 4 550mghz processors and we are going
to 4 2.4ghz processors. I would be surprised if we ran into the same issues
that you experienced. I would be very interested in hearing what your
feelings are given the above information. I really want to do this right
the first time.


"Ken Proctor" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Geoff,

Perhaps, Tomer wasn't thorough with his comment. <G>

Simply, the available MSCS does not support a CFS. However, you can take
advantage of Polyserve's MxS. Polyserve supplies the clustering HA solution
along with the CFS in its product. You can take a look at
http://www.polyserve.com/products_mswindows.html for some info.

"Unsupported add-on..." is such a harsh comment (not to mention it's really
not correct). Polyserve's MxS is a fully supported solution, meaning
Polyserve is responsible in providing support for MxS and MS fully supports
theirs when used in combination with Polyserve's product (just like every
other ISV).

Geoff, I can certainly take this off-line if you have additional
question/comment. Ping me directly or through the MVPs newsgroup.

Cheers,
--
-oj


"Geoff N. Hiten" <SRDBA (AT) Careerbuilder (DOT) com> wrote

Mike

All I can do is give you our experience. In terms of measured perforamance each node doesn't look too bad. CPU usage ranges 30-70% daily, mostly at the low end of that spectrum. We don't have any complicating factors such as AWE memory. Our regular SQL monitoring doesn't look too bad (e.g. Buff Cache Hit Ratio is usually > 95%) but both nodes have many more databases than were ever planned for and we are pretty low on disk space. To give some figures Node1 has 161 databases (total daily backup size 40 Gig) and Node2 (total daily backup size 41 Gig).

When Node1 failed over the symptoms (apart from lots of CPU being used) were that the application couldn't connect to SQL Server, they were in a weird queued state 'TM REQUEST'. I investigated later and the only inconclusive hit I found was this http://dbforums.com/arch/70/2003/3/623750 I didn't bother reporting it to Microsoft because things were fine when we got Node 1 back. Now I guess it is possible that this specific problem wasn't related to both instances running on the one node at all, sometimes the obvious conclusions are wrong. The developers on these boxes always push things to the limit, but if their code or configuration was at fault they certainly didn't admit it. Both boxes were on SP3a

I would think you will probably be OK with Active/Active if you have a defined workload on both nodes but the only way to be sure would be to do some testing with a genuine application workload on both instances running on 1 node. Another option would be to cap each SQL instances memory and restict it's processor use, e.g. Node1 uses 0,1 and Node 2 2,3. If your application works in that restricted way then you are playing safe. You can always allow SQL extra resources if necessary

Hope this helps.