is Active/Active preferred over Active/Passive? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

is Active/Active preferred over Active/Passive?

Hi, What I understand from the documentation is that Active/Passive is a true failover solution. But there is like a resource waste as one server is just doing nothing when there is no failover. On the other hand, the active/active seems to be more money efficient as it makes use of the 2nd server. Each node must have two SQL2K instances installed. In case of failover, the remaining node takes the load of both SQL instances. And when both nodes are working, the server resources are shared by the two SQL2K instances. i.e. the active instance cannot use all the RAM of the server, because this RAM must be split and allocated at the time the SQL instance is installed. Q1. Is the summary above correct? Q2. What is the recommended approach for SQL2K Clustering? Thanks in advance.
In your case, my suggestion would be, YES Make it Active / Active. If the databases are big the just Install another instance of SQL Server and switch half of your load in to the new instance on to the other server and make both the server ACTIVE. One of the MS document refers In the MSCS active/active scenario, you would move the group on one node to the other node prior to applying a service pack. The only down time involved is the services going offline on the first node and coming online on the second node; the shutdown and startup time for the application. In the event of a failure, the group goes offline and comes online on the other node automagically; there is no user intervention. There is still a small amount of downtime while the services go offline and come online. This is the nature of a failover cluster with shared storage. Competative pailover clustering products suffer the same fate; it’s part and parcel to the concept of failover clustering. We’ve Active/Passive setup running successfully since a year. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
quote:Originally posted by satya In your case, my suggestion would be, YES Make it Active / Active. If the databases are big the just Install another instance of SQL Server and switch half of your load in to the new instance on to the other server and make both the server ACTIVE.
Does that imply that the current DB should be reorganized into two DBs? This sounds not very flexible because all the applications, DTS packages, maintenance tasks should be updated to take in account 2 DBs. Is there a significant performance gain to split a DB into smaller DBs?
quote:We’ve Active/Passive setup running successfully since a year.

What was the main reason you did NOT select Active/Active configuration?
You do not split a single database. Active/active can be good if you have multiple databases, then you can put some on one instance and some on the other. The instances are independent of eachother and act as their own SQL servers. They have their own services and their own seperate disks in the shared storage. Reasons not to go active/active:
– License cost is doubled with processor licenses. Active/passive requires SQL licensing of the active node only
– If you do not have many disks in your shared storage it can be better in terms of performance to only have one instance and let it use all the storage you have.
– If you only have one database there is no need to use more than one instance of SQL Server. Not that with SQL 2000 you can have 16 instances in a cluster even though 1 or 2 is common. How you divide them is up to you. You could have 16 instances on one server and 0 on the other and it would still be an active/passive cluster. If you have 15 on one server and 1 on the other its an active/active cluster.
Hi Argyle, Thank you very much for clarification.
Bare in mind the licence costs! We run a cluster on windows 2003 with 8 processors/machinese.
This is an active/passive cluster. For an active/passive cluster we pay sql server licenses for only one node. If it is an active active we would need to pay for all 16 processors in the cluster. Since the licenses are more expensive than the machines, it is not worth it. At least not for us.
Hi mandarinboy, Thanks for the info. You said that you used Windows 2003 (enterprise edition I suppose). How do you like it compared to Windows 2000 Enterprise?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |