Database Mirroring or Log shipping on different server

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Database Mirroring or Log shipping on...

37 posts, Page 2 of 4

««1 2 3 4 »»»

Database Mirroring or Log shipping on different server

Rate Topic

Display Mode

Topic Options

Author

Message

krayknot

Posted Thursday, November 20, 2008 9:47 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, November 05, 2009 11:04 PM
Points: 330, Visits: 465

Paresh Prajapati (11/20/2008)

But i think Mirroring is degrade database performanance than shipping..

As per the locations of your servers, the performance will surely degrade but the purpose for which you are going to adopt mirroring or shipping, mirroring will provide accurate and up-to-date results.

kshitij kumar
Senior Software Engineer
Pune.

krayknot@yahoo.com

Post #606281

Paresh Prajapati

Posted Friday, November 21, 2008 2:13 AM

Old Hand

Group: General Forum Members
Last Login: Monday, August 31, 2009 3:35 AM
Points: 314, Visits: 399

My both server are located in individual location (in in usa and other in Hongkong).so mirroring wirh sync mode will be time consuming to commit in partner server...

_____________________________________________________________________________________________________________
Paresh Prajapati

+919924626601
http://www.linkedin.com/in/pareshprajapati
http://paresh-sqldba.blogspot.com/

Post #606364

BobAtDBS

Posted Friday, November 21, 2008 7:24 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 257, Visits: 259

Paresh Prajapati (11/21/2008)

My both server are located in individual location (in in usa and other in Hongkong).so mirroring wirh sync mode will be time consuming to commit in partner server...

Which is exactly why I advised that for such an arrangement, you would use High Performance (non synched writes) rather that High Avail (synched writes).

I have found that the impact on the primary server is negligible with high performance mirroring or log shipping. The impact on the secondary server is much more noticable with log shipping. With LS, all the changes for the databases come over in big clumps and get applied at once. Disk Queue length skyrockets, and other apps/databases on the secondary server are affected. With mirroring, I see almost no performance degradation on the secondary server. Coupled with the latency inherent in LS, the only reason I see to use LS anymore is for directing copies to more than one server or for delaying the copying process to an after hours time frame to save bandwidth during the production day.

Student of SQL and Golf, Master of Neither

Post #606509

Paresh Prajapati

Posted Friday, November 21, 2008 7:29 AM

Old Hand

Group: General Forum Members
Last Login: Monday, August 31, 2009 3:35 AM
Points: 314, Visits: 399

Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages:

1. it provides backup files as part of the process
2. multiple secondaries are supported
3. it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error

and i found some adv and disadv :

With Log Shipping:

Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required

With Database Mirroring:

Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred
Server Limitation: Can be applied to only one mirror server
Failover: Automatic
Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change: Role change is fully automatic
Client Re-direction: Fully automatic as it uses .NET 2.0

so as per this what we can conclude for shipping or mirroring ?

_____________________________________________________________________________________________________________
Paresh Prajapati

+919924626601
http://www.linkedin.com/in/pareshprajapati
http://paresh-sqldba.blogspot.com/

Post #606514

Perry Whittle

Posted Friday, November 21, 2008 7:37 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, October 30, 2009 11:52 AM
Points: 1,084, Visits: 3,548

if using database mirroring be sure the network connection is stable otherwise you could end up with unwanted fail overs. Log shipping would be more preferrable, but it depends on what availability you require on the replicated database

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

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #606519

BobAtDBS

Posted Friday, November 21, 2008 8:09 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 257, Visits: 259

Paresh: Your cut and paste was in reference to High Avail style mirroring
Perry: You were talking about automatic failover.

Don't do either one with a normal internet type connection!

My posts were concerning actual experience with High Performance (no wait on read commit) style mirroring with manual failover; compared to actual experience with Log Shipping (always manual failover).

There is zero concern about spurious failovers with HP Mirroring with no witness server, because just like log shipping it's done manually when you want it. It works amazingly well over a distance and has great tools for you to monitor status with. There is little to no performance penalty on either server.

Student of SQL and Golf, Master of Neither

Post #606548

Paresh Prajapati

Posted Friday, November 21, 2008 11:03 PM

Old Hand

Group: General Forum Members
Last Login: Monday, August 31, 2009 3:35 AM
Points: 314, Visits: 399

My Producation databasae is huge and if it stop for few minuteds, it can create problem with us and alos users.
so i am confused for if i have to keep performanance then i have to lost failover time and
if i have to reduce failover time then i have to loss perfomanance.

_____________________________________________________________________________________________________________
Paresh Prajapati

+919924626601
http://www.linkedin.com/in/pareshprajapati
http://paresh-sqldba.blogspot.com/

Post #606945

Paresh Prajapati

Posted Friday, November 21, 2008 11:50 PM

Old Hand

Group: General Forum Members
Last Login: Monday, August 31, 2009 3:35 AM
Points: 314, Visits: 399

I confuse to choose high availability or high performamance...?

_____________________________________________________________________________________________________________
Paresh Prajapati

+919924626601
http://www.linkedin.com/in/pareshprajapati
http://paresh-sqldba.blogspot.com/

Post #606954

Paresh Prajapati

Posted Sunday, November 23, 2008 9:44 PM

Old Hand

Group: General Forum Members
Last Login: Monday, August 31, 2009 3:35 AM
Points: 314, Visits: 399

Resolve my confusion please..it's important for me.

_____________________________________________________________________________________________________________
Paresh Prajapati

+919924626601
http://www.linkedin.com/in/pareshprajapati
http://paresh-sqldba.blogspot.com/

Post #607284

Paresh Prajapati

Posted Sunday, November 23, 2008 9:47 PM

Old Hand

Group: General Forum Members
Last Login: Monday, August 31, 2009 3:35 AM
Points: 314, Visits: 399

Can Transactional Replication solve problem?

_____________________________________________________________________________________________________________
Paresh Prajapati

+919924626601
http://www.linkedin.com/in/pareshprajapati
http://paresh-sqldba.blogspot.com/

Post #607286

« Prev Topic | Next Topic »

37 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions