Mirroring, Replication or Log Shipping

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Mirroring, Replication or Log Shipping

Mirroring, Replication or Log Shipping

Rate Topic

Display Mode

Topic Options

Author

Message

Vinit Fichadia

Posted Sunday, June 22, 2008 10:27 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, November 12, 2008 4:12 AM
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

Post #521509

magasvs

Posted Tuesday, June 24, 2008 4:47 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:43 PM
Points: 1,259, Visits: 704

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).

Post #522943

tedo

Posted Tuesday, June 24, 2008 10:35 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, March 22, 2013 1:27 AM
Points: 568, Visits: 494

Yep I would use logshipping probaly every 1hr depends on how fast your line is and the over head on these systems.

Regards,

Terry

Post #523014

Wilfred van Dijk

Posted Wednesday, June 25, 2008 7:07 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:21 AM
Points: 898, Visits: 1,049

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

Post #523263

george sibbald

Posted Thursday, June 26, 2008 7:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 5,314, Visits: 11,305

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.

---------------------------------------------------------------------

Post #524117

« Prev Topic | Next Topic »

Permissions