replication / mirroring scenario | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

replication / mirroring scenario

Hi I need to achieve the following, but im not entirely sure on the best topology – could someone please advise? I have a production server which is updated every minute, a development server, an on-site failover and an offsite failover. The On-site failover box is in case my prod server dies for whatever reason, and my off site is part of my business continuity planning strategy in case my building blows up. These 3 boxes need to be syncronous – the Dev box can be a day out of date if necessary, but It would be good if it were also sync’ed. Live data can ONLY go into one prod server at a time ie the current prod server. I have tried to get replication going and whilst its topology supports my requirements ie one publisher and multiple subscribers, it is INSANELY painful to get going, and I dont hate myself that much. I’m currently evaluating mirroring and whilst it looks pretty good and is not giving me another ulcer to match the one that rep gave me, it seems its only a 2-server process ie primary and mirror so it would not seem to fit into my model.. Im thinking Mirror the prod server to the Disaster Recovery site (DR) and have the on-site failover and dev updated nightly from scripted backup / restores but this obviously leaves my on-site with out-of-date data and is also cludgy as hell. so what do people suggest? ultimately I want to mirror to two mirror servers, but I dont think the process supports multiple end-points. what about failover clustering? Any idead would be greatly appreciated. Thanks Alastair Jones.
Hi Alastair, some other possible options… – log shipping (may not be fine grained enough in terms of timing)
– SAN replication (may be too expensive?)
– MSMQ or 2 phase commit across all ‘live’ servers (may be too complex to design into the application?) failover clustering is not really a candidate here since it is not geared towards losing the storage component (unless you were using geographically dispersed clusters, in which case it is like SAN replication really) Synching Dev with Prod should really be an ad hoc activity, since the Dev data structure may be out of sync and/or developers may have created specific test data to check their program. Wouldn’t want to just blanketly overwrite that… I can see why you’d have a prod server plus a backup server off site, but unless the network between the two servers is unstable I wouldn’t see a need for a third server? Cheers
Twan Thanks for the reply. The problem is that the application that references the DBase uses ‘User profiles’ which can be up to 10 / 20 / 30 Mega bytes (not bits) in size. If my prod box fails, I would far rather use this data local rather than pull it back over the WAN from my DR site. If my building is a smoking crater then my DR plan uses VMware XP machines on a server local to the DR site SQL server, so pulling data about the place isnt an issue. not sure how clear that is..