SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirroring, Replication or Log Shipping


Mirroring, Replication or Log Shipping

Author
Message
Vinit Fichadia
Vinit Fichadia
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 56
thank you all for your kindest responses.


but our requirement has been slightly changed i.e.

when link between pune and mumbai goes down, pune users will updating localdb and mumbai users will be waiting for link to get online, all the transaction will always be done at pune office and mumbai users will be performing read only operation from mumbai server and write operation to pune server

2 servers i.e, pune(primary), mumbai(secondary)
all read write operations at pune server will be done by pune user
all read operation by mumbai users will be done from mumbai server
all write operation by mumbai user will be done at pune server
when the link between pune and mumbai goes down, pune user will be performing read write operation at pune server only, and now mumbai user can do nothing except reading from mumbai server

now with this situation which architecture would be most feasible.

thanks

Vinit
magasvs
magasvs
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2299 Visits: 756
Database mirroring will not work for you definitely. Mirror database will not available for reading.
You can use transactional replication or log shipping. Log shipping is easier to setup and troubleshoot. It would be my preference if latency is not an issue. Also, it doesn't require additional database (distributor in replication).
tedo
tedo
SSC Eights!
SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)

Group: General Forum Members
Points: 912 Visits: 523
Yep I would use logshipping probaly every 1hr depends on how fast your line is and the over head on these systems.

Regards,

Terry
Wilfred van Dijk
Wilfred van Dijk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2388 Visits: 1363
Mirror database will not available for reading.

If you put a snapshot on a mirrored database, you can use the database for read operations

About logshipping: If you apply logs, no one can access the database during the log apply

Wilfred
The best things in life are the simple things
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23378 Visits: 13698
you have described a typical simple transactional replication scenario, so this sounds like your best bet.

log shipping will make the mumbai db unusable whilst the log so being loaded, so unless you don't mind mumbai users being kicked off when logs are restored this is out.

snapshot off of a mirrored databases could be done, it all depends on latency you require, If you want minimal latency then transactional replication is way to go.

If you are happy with mumbai data being say a day old then you have many options:

snaphot replication daily
snaphot of a mirrored database
log shipping but restore all logs once a day
restore a full database backup nightly

of those restore a full backup nightly is the simplest and the way I would go, setting the db to read_only in mumbai.

If using replication remember there are limitations such as having a primary key on all replicated tables.

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search