Click here to monitor SSC
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
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1285 Visits: 752
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
Mr or Mrs. 500
Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)

Group: General Forum Members
Points: 570 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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 1352
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
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6360 Visits: 13687
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