Fog Creek Software
Discussion Board




Experience with SQL Server Clustering?

We are basically an Oracle shop and have an internal COTS application that requires SQL Server.

We currently have a four cpu (non-expandable) server with 8 gig of memory running SQL Server Enterprise.  We would like to upgrade to an eight cpu server, which in our case, would require buying a totally new server.

SQL Server seems to support clustering (Microsoft calls it "Distributed Partitioned Views").  My question is, what kind of performance hit would we take (overhead of clustering) if we added a second four cpu server in a cluster as opposed to replacing our existing four way box with an eight way box? 

Any success or horror stories with clustering SQL Server?

Rich
Saturday, June 07, 2003

This is easy: go with replacing the server.

Generally the cost of an 8 way server will significantly outweigh the administration headaches added by distributed partitioned views. In a typical application successfully implementing DPV is, to say the least, not easy. You should scale-up as much as feasible before considering scaling-out.

Good luck and your mileage may vary.

See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_faq_238z.asp

And if you have a copy of the SQL 2k Resource Kit read the chapter on scaling-out.

Jeff

Jeff
Sunday, June 08, 2003

If, by an off-chance, you're interested in fail-over clustering, SQL Server does support this and it works as expected.

I'm not at all familiar with Oracle clustering. Our non-failover clusters implemented as "shared-nothing" clusters?

Thanks,

J

J
Sunday, June 08, 2003

An article from Oracle comparing SQL Server Partioned Views vs Oracle9i Real Application Clusters:

http://otn.oracle.com/deploy/performance/pdf/FederatedvsClustered.pdf

Oracle9i clusters are shared disk not shared-nothing.

Jeff

Jeff
Sunday, June 08, 2003

Partitioned views require that you've designed them into your system: i.e. you can't just replicate the database and suddenly enjoy the advantage of double the power. Instead you need to ensure that all table access takes place through views, and the views have constraints applicable for the clustering setup (i.e. on server one the Customers view is A-H, while on server two  it's I-Z).

If you can easily do that then switching to a horizontally partitioned federation of server offers a very good return. I believe the scaling is something like 80% (i.e. two servers offer 180% of the performance of one). The same sort of diminishing return happens when you add more CPUs in the same box as well.

Dennis Forbes
Sunday, June 08, 2003

SQL Server clustering can be done in two modes - Active-Active and Active-Passive. If you choose Active-Passive there is no increase in performance because only one of the servers is used at a time. Active-Active will povide some increase in performance by itself, and can definitely help increasing the performance combined with partitioned views (the application has to support the feature).
Administration hurdles while setting up and maintaining a cluster are significant. You will need Active Directory, to start with. When things go wrong it might be very hard to find out why. If you don't have experience with MS SQL clusters, ask a consultant for help. There is a lot of specific knowledge and best practices that are not documented anywhere.
In the application I am developing right now we have chosen to:

- Use Active-Active cluster.
- Perform transactional replication for tables that are not frequently updated (as a matter of fact we'll use our own implementation of transactional replication instead of the wizards, because it's easier to set up with scripts instead of UI).
- Create partition views for the tables that are frequently updated.

Alexander Chalucov (www.alexlechuck.com)
Monday, June 09, 2003

*  Recent Topics

*  Fog Creek Home