active/active clustered design

Question

active/active clustered design

Judy Why

Hall of Fame

Points: 3656
More actions
August 18, 2008 at 1:39 pm

#191318

Hello everyone,
before I post my question, let me simply descript my situation. out computer bought a series of new hardware for upgrading our server infrastrucature. this is including:
- Two Content switch. for load balancing
-Two SQL Box
-Two Fibre Switch
- Two MSA SAN array.
Load Balancing request will be coming from Content switch. Currently, we have around 100 databases in one sql instance. our web application query will cross multiple databases in this instance.
can I implement HA and load balancing as below way.
1. create a active/avtive clustering node in two SQL box.
named, SQL1 and SQL2; instance in both serverzs named SQL_in1,SQL_in2.
SQL_in1 is active in SQL1. SQL_in2 is active in SQL2.
2. SQL1 uses user Databases in G drive on SAN. all the user databases will sit here;
3.SQL2 uses user Databases in H drive on SAN. all the user databases will sit here;
4. if nothing happen, Content Switch will forward user requests to one of the servers
5. if SQL1 itself failed, for example, SQL1 will fail to SQL2. and SQL_in1 in SQL2 use the Databases in G drive
if G drive fail, then only SQL_in2 in SQL2 working on H Drive
6. because SQL1 and SQl2 use seperate databases, use replication to sync each other.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

An interesting solution, but I think you have a major replication task ahead of you. How much latency will be accceptable between the two servers? Is your database using GUID's on every table for uniqueness? Are you prepared for managing a constant merge replication?

I would be very uneasy about implementing something that would require keeping two identical databases in sync all the time with updates happening to both of them and the possibility of requests for data coming through either. I think you are going to have lots of replication conflicts that you have to deal with and merge replication out of the box will probably not suffice.

You should look at the suggested approaches for HA and load balancing from Microsoft. I think I would tend to simplify and use standard clustering for load balancing and mirror the database to a standby server for failover - possibly using a solution from my SAN vendor if one is available. Read the MS white papers and talk to your SAN vendor before inventing your own solution.

Judy Why Hall of Fame Points: 3656 More actions · Answer 2

Thanks for your reply.

Web users just query most databases. Internel users maybe occationlly change table structure/add Sps/DML. For my databases, Changes in one part is manually updated. using Merge replication to implement sync for Other databases.

I don't know if it is practicial to do replication between two active/active node.

dbaker-620086 SSCrazy Points: 2336 More actions · Answer 3

you mentioned loadbalancing, webservers and database servers but remember these are normally configured as

[font="Courier New"]

inter/intranet

external FW1

loadbalancing }

web1 .. } DMZ

webn }

corporate FW2

virtual1 }

db1 }

virtual2 }

db2 } internal zone

resourcegroup1 }

IP1 }

G: on SAN }

resourcegroup2 }

IP2 }

H: on SAN }

[/font]

where the MSCS manages failover-cluster on a shared-nothing basis.

If web1 needs the SQL_in1 data it would connect to virtual1

ditto webn needs SQL_in2 data it would connect to virtual2

if db1 breaks, then resourcegroup1 [instance] would be adopted by db2 [and would rollback to last checkpoint].

Thus any inflight web1-db1 would be fatal'ed but next re-connection (pooling) would actually hit db2 and carry on new business OK.

The point is that there should be NO requirement for replication from db1 to/from db2, since only ONE would "own" the data on G: or H: respectively.

There is another possible scenario that you could harness for read-only data, and that is to replicate from the internal zone onto slave db3..dbn boxes within the DMZ subject to security sanctions. Point is that most queries are SELECTs that then can be satisfied within the DMZ without bothering FW2 and db1/db2.

Any write (I/U/D) queries WOULD need to hit the virtual1 (db1) OR virtual2 (db2), and this rare case would then use transactional repl to ripple (push/pull) to the db3..dbn slaves.

Like Michael Earl, I would be very nervous of 2-way repl (transactional with IU, or merge) across any boxes, and this should be totally unnecessary since only ONE db1/db2 would shoulder the X workload ([font="Courier New"]G:[/font]). You still have the other db2/db1 doing the Y workload ([font="Courier New"]H:[/font]) thus true A-A with each doing useful work (not just waiting for the other to die).

HTH

Dick

Judy Why Hall of Fame Points: 3656 More actions · Answer 4

I understand your saying.

but if not implement replication between two clustering active/active nodes or just two seperate nodes (no auto failover) , what 's the best way to do database synchronization?

this is really confused me.

thanks