active/active clustering

how does an active/active cluster work with sql server? What about both servers trying to write to the same database files? Each has their own locks. How does active/active work with such constraints?also can you scale out with active/active via database filegroups?|||There is no such thing as an active/active SQL Server cluster. You can install multiple instances or just a single instance in a cluster. Each instance runs on one and only one node at any given time and is inaccessible through any other node. So, it is not possible to use clustering for load balancing, scaling out, or anything other than as a hardware protection mechanism only.|||

thank you for the confirmation of trying to scale via active/active.

|||

There is some sort of "load balancing" possible. It is not real load balancing as you would like it to be, but a very usefull option in my opinion.

Install a cluster with 2 instances and mirror the database from the first instance to the second instance. (it does not have to be a cluster, but it can be usefull for up-times)

The database on the second instance can not be accessed now, but you can make a snapshot of that database and use the snapshot for querying.

The snapshot will not be available for updates, but in some cases you can use the snapshot for query users. That way you keep the query users from your live database, so performance of the live database will not be affected by them and the query users do have reasonable up to date data at their fingertips.

|||

If you were going to do that, there really wouldn't be any point in configuring clustering at all. You would simply drop in Database Mirroring with a Database Snapshot created against the mirror database. Actually gives better fault tolerance than clustering, because you have two copies of the data and it gives better availability, because mirroring reacts more quickly than clustering does. And, you wind up in exactly the same state as if you clustered with 2 instances and then built mirroring, because a cluster failover would trigger a mirror failover and your applications would still need to connect to the mirror which most likely will require manual intervention since it will be a while until all applications are using the new connection libraries with the redirect code in it.

I don't like clustering combined with Database Mirroring for one very specific reason. Every cluster failover casues a cascading failover of Database Mirroring. So, instead of having just a single failover event, you have two each time.

A scenario like this would be served much more stably by using either replication or log shipping instead of Database Mirroring, because you won't get a cascading failover in the event the primary node in the cluster fails and once the instance is brought back online, processing resumes on exactly the same instances as before.