Log Shipping vs database mirroring

Hi Mvps:

Whats the major difference between Database mirroring and Log shipping?. I did read few differences but was told when it comes to applying the transaction logs on the secondary server , DB Mirroring has a diffferent methodology. Is that right?. Please clarify.

Thanks

Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring supports only one miror for each principal database. Database mirroring also supports automatic failover if the principal database becomes unavailable. The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting, etc.

Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondaries for each primary database.

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

More information about both technologies is available in SQL Server 2005 Books Online in the topics "Understanding Log Shipping" and "Overview of Database Mirroring".

Phillip Garding
Senior Program Manager
Microsoft SQL Server

|||

Log shipping uses the tried and true restore logic.

Database mirroring uses a variation on the restore logic that is completely incorporated into the mirroring process. Plus redo is multi-threaded in Enterprise Edition.

|||

Hi Meher,

Other differences are listed below:

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

Hope this helps

Thank you,
Saleem Hakani
Saleem@.sqlnt.com

|||

thankx Saleem,

i would appreciate if you could help me. i am gloin to have my iterview in the comin weeks. so if you could

help me this problem i will b greatful... my email is is gibtronics2000@.gmail.com. The reply of yours is really helpful.

keep it up...

thankx

gangster

|||One important thing to keep in mind is that snapshots from db mirroring only works with Enterprise edition -- $$$ (especially if you need 2 or 3 copies of it). One last thing to consider is bi-directional transactional replication (this takes some thinking to have your schema support it correctly though).|||Saleem, Phillip

Phillip says "Mirroring is preferable to log shipping in most cases"...
Could I submit one case to you, please?

We have a web site for which we already have failover clustering but we want to setup some disaster recovery plan.
We will have cheaper hardware in a different location with exactly the same software and a "recent" copy of the database. We have connection between the two sites to keep our copy up-to-date.
A manual "switch" would be fine.

Although there is no plan for this, it might be useful to use the secondary database for reporting but I think this should be fine with both technologies (as we have Sql Server Enterprise Edition).

Could you advise between Asynchronous Mirroring and Log Shipping?

Thanks

Eric
|||

I have to disagree with Saleem on a few points.

Client Re-direction: Fully automatic as it uses .NET 2.0

It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes adavatage of mirroring if you tell it the alternate server in the connection string.

Failover: Automatic

Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds

Role Change: Role change is fully automatic

Failover is only automatic if you are running mirroring in High-Safety mode with a Witness server that is up and functioning and able to contact the mirroring partner.

I should also add that failing back to the original primary server is not supported. If you want mirroring to return to the original primary server, as soon as it becomes available, you have to set up a job to do it. I set up a job that runs every minute on the mirroring server that checks for databases that are the primary mirror and that are in the synchronized state (Where sys.database_mirroring.mirroring_state = 4 And sys.database_mirroring.mirroring_role = 1) and fail it over.

Another point about mirroring is that the official recommendation by MSFT is to only mirror a maximum of 10 databases per server because each mirroring session consumes 2 threads. Logshipping does not have this limitation.

Yet another good point about mirroring is that you can mirror the publisher of merge replication and configure replication to automatically use the alternate database if it fails over. This is designed to replace Alternate Synchronizing partners in replication as Alternate Synchronizing partners is being deprecated.

You should also take note that you can do both mirroring and log shipping.

|||Robert,

Thanks for the info but it does not really help me in choosing...

Can I phrase my question slightly differently...

I don't see how log shipping could have a serious negative effect on my primary server as it's pretty simple in principle...
Is there anyway that Asynchronous Mirroring could have a negative impact on my Primary Server?

Eric

|||

Hi Robert,

I will be setting up Mirroring in the next few days. I cam across this thread and saw your comments:

"It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes advantage of mirroring if you tell it the alternate server in the connection string."

Can you post sample code on connection string to do this? I believe our developers do not have this string comnnection option.

Another thing: "I should also add that failing back to the original primary server is not supported". Do you mind if you give me a bit of technique on how you do the job for failover to revert back to the primary node? This feature is new to me. I am not really into things like this. And one question, if the failover returns back to the primary server, do all transactions made in the mirrored database be automatically reflected on the primary?

Thanks!

sql