February 1, 2007 at 11:49 pm
Hi ,
i am going to implement clustering for my one application so databases are same , what to confirm which clustering is best is it active-active or active-passive as far as performance is concerned.Boss is asking for Active Active so want confirm how i can keep same databases on different instances on each node for Active-Active Scenario and what happens if any update happens in any of the table for one instance
Thanks Much
February 2, 2007 at 3:21 am
If you have one application and one database then you can't have active-active clustering. Active-active involves having two instances or more set up, with those instances running on different nodes of the cluster. Remember that clustering is primarily a resilience solution and will not give you any higher performance than an a single-node environment.
John
February 2, 2007 at 4:24 am
Thanks John for reply so it means for same databases i can not go Active-Active Cluster ,what about if i keep one default instance on one node and named instance with other node with same databases , Is it possible? ,if yes then how data integrity is maintained when update happens on any table since databases are same.
Thanks
February 2, 2007 at 5:09 am
Yes, you can do that using replication, but I can't think why you would want to. Perhaps you could explain a bit more about what you (or your boss) are looking for?
John
February 2, 2007 at 5:30 am
I think a read through ms docs on clustering would be useful. In general terms clustering only protects against server hardware failure ( excluding disks ). Clusters bring their own increased maintenence and administration and can often cause ploblems you wouldn't have had before.
As John says you need to understand what it is you're trying to achieve and the best way to go about it. Clustering is HA for server hardware. It won't protect your application or database.
active / passive means you have a server sitting waiting to take over if your primary fails.
active / active means you have two servers each doing their own thing but each being able to do the work of both if one fails.
( that's two nodes - you can expand clustering beyond this )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 2, 2007 at 7:33 am
If you are still considering a cluster implementation, also consider to implement SQL2005 !!! because it has enhancements like mirrored databases which is also a high availability solution with failover possibillities, with redundant hardware and controlable latency ! Which means you don't need the $$$-san and $$$clusterHW.
btw you can have the mirrored database in a read-only version available at the seconde node of the concept.
You would need SQL2005 std or EE with SP1.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 2, 2007 at 9:08 am
Sqlquery, I echo what all of these guys have said, but I do want to add one thing. Active-Active SQL Server clustering is not the same as active-active Windows Server clustering. Clustering Windows servers in this manner allows for load balancing requests which may be why your boss want to do the same for SQL Server. SQL Server active-active clustering is not true active-active; rather, you can think of it as a multi-instance cluster. There are no load balancing related performance gains in an active-active SQL Server cluster.
February 3, 2007 at 9:11 pm
Thank you guys ,you are always awesome ,All suggetions has been taken in consideration.
Thanks Again.
February 4, 2007 at 6:12 am
A word of caution regarding clusters as they can create more problems than they solve.
Some cases in point:
a) I am on call this week and between Saturday at 9 AM and Sunday at 4 AM, there have been 4 cases where SQL Server for production applications failed over from one cluster node to another cluster node with the typical fail-over time of 20 seconds. In 3 cases, the reason for the fail-over was loss of connectivity to the SAN. In the fourth case, the power supply went out and they did not bother to have a redundant power supply.
This is for 23 production Servers running as a cluster - so 18% of the clustered servers had an outage in a 19 hour period. None of the non-clustered servers had an outage.
B) This client has about 275 SQL Servers in total, all use SAN and about 100 are production. Loss of connectivity to the SAN is a daily occurrence. Sometimes the problem lasts only for a few seconds and is resolved with a bus reset (which happens automatically) but there are other cases where the impact is enormous. A few months ago, there was a bad disk drive in one of the SANs and when the operator went to hot-swap the drive, he pulled the wrong drive and the entire SAN went down. This affected 8 production systems and another 6 test/development systems. As the database and transaction logs were on the same SAN as the backups, recovery had to be made from tape backups and there was permanent data loss. 4 DBAs worked continously for 17 hours to perform the recovery for production. Then there was another 60 hours of work to restore the test and development SQL Server databases.
C)There have also been cases where production and test servers were connected to the same SAN and when a thruput test was performed in test, the SAN to be overwhelmed and production response time was severly affected.
D)About 3 months ago, I did a extra weekend job with a different client where their weekly CheckDB were always reporting errors. The root cause was the SAN configuration.
Lastly are the costs as there are now twice the servers when clustering:
1. More rack spaces is needed
2. More electrical power is needed.
3. More cooling is needed.
4. More than double the time to do initial installation
5.More than double the time to do patch installation.
6.Higher skill level requirements for Windows and Database Administrators resulting in higher salaries and higher personnel costs.
7.Using SAN affects response time. Benchmarks performed by MS and the SAN vendors show a 20% reduction is disk I/O thruput.
SQL = Scarcely Qualifies as a Language
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply