Clustering for Availability and Performance

[sfonative]

Hi,
I'm a businessperson, not a software engineer, so please bear with me if my questions seem simple.

We are about to migrate from MS SQL Server 2000 to 2005. In our 2000 deployment, we have a primary server, with real-time replication to a backup. If we have a hardware failure, we do a manual failover to the backup server. This has served us well for years, with decent performance and no data loss. The SQL server is accessed by a pair of IIS servers, which use network load balancing to split the load between them.

As part of the forthcoming migration to SQL 2005, we are going to a hardware platform with 2 SQL servers sharing a single storage array. We would like to get the performance benefit of utilizing both of the SQL servers to handle requests simultaneously, and not just have the second server as a backup. Ideally, the two IIS servers would dish requests to both SQL servers, either randomly or in some sort of load-balanced manner.

So here is my question: how can we have both SQL servers active and handling requests simultaneously, achieving both high perfromance and high uptime?

If it is not supported natively in SQL Server 2005, is there a third-party software solution that can achieve this?

Finally, where else should I go to do further research on this subject?

Thanks in advance for any help you can offer.

[rmiao]

Clustering is for availability only. You need two instances for what you need, just like what you did in sql2k.

[scoplan]

Why change the configuration at all if your performance and availability is OK on MSSQL-2000. So you are moving to 2005. Are you upgrading the hardware from a single core CPU to dual or quad core? Does the 2005 machine have faster SATA drives? Are you using RAID 1 for high data availability/redundancy? Is the web server connected to the SQL server on a 100Mbs router/switch? Are you upgrading to a 1Gbs switch/router?

My point is that you are upgrading the server hardware and may gain all the performance and availability you need without changing the entire system design.

[sfonative]

Hi scoplan, thanks for the reply. Here are my answers to your questions. I'd appreciate any further insights you or others might have.

>>Why change the configuration at all if your performance and availability is OK on MSSQL-2000.
Performance is NOT OK currently. Availability has been OK only because the current hardware/software platform has been reliable. If it does crash, manual intervention is required, which poses a big problem since our employees and customers are using the db 24x7, while our IT department is present about 60 hours/week.

So you are moving to 2005. Are you upgrading the hardware from a single core CPU to dual or quad core? Does the 2005 machine have faster SATA drives? Are you using RAID 1 for high data availability/redundancy? Is the web server connected to the SQL server on a 100Mbs router/switch? Are you upgrading to a 1Gbs switch/router?
>>Pretty much yes to all of the above, except we are already on a gigabit connection both between the webservers and the SQL server, and from the webservers to most desktops. We are going from a single-core quad-Opteron server to dual quad-core servers, and would like to have both the processing power and availability gained from using both servers at once.

My point is that you are upgrading the server hardware and may gain all the performance and availability you need without changing the entire system design.

[scoplan]

You originally indicated that "This has served us well for years, with decent performance..."

There are many options to improve performance and availability. To improve performance I would need to know more about the application, is it a read mostly app, or write mostly, or mostly real-time transactions?

Do you have a DB admin who is tuning your system? If not you should at least have an experienced consultant review it for possible improvements.

Review the SQL execution plans for the queries and look for places to optimize performance.

You should try application optimization and load testing on both the web servers and DB servers and locate the performance bottlenecks. Application and DB optimization can provide large increases in performance.

In read-mostly applications adding indexes might provide a large performance increase at the expense of available storage. Caching could help too.

In write-mostly apps, reducing the number of indexes might help by reducing the DB processing overhead time for updating indexes.

Sometimes you might want to dedicate one SQL server to reading and the other to writing.

If you are truly using a single data store array then that will be your most likely single point-of-failure, either because of connection failure, drive failure or power failure unless everything is redundant (i.e. hot swap drives and power).

Here's some good advice for your system architects for using multiple SQL servers with a shared data store.
http://blogs.msdn.com/sqlcat/archive...11/479887.aspx

Regards,
Stan