Log Shipping vs database mirroring

Question

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

 

 

Answer 1

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

Answer 2

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.

 

Answer 3

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
[e-mail]

 

Answer 4

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 [e-mail]. The reply of yours is really helpful.

keep it up...

                  thankx

                               gangster

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

 

 

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!

Answer 10

Hi
Is it possible to use "log shipping" or "database mirroring", incase the secondary server is a remote server and geograpically located and is not in same domain. for replication methods i have the possiblity to have a ftp server as a communication platform in between primary and secondary servers, but unable to find any such kind for log shipping or database mirroring. How to apply a mirror database to a remote server via internet without using any third party tools?

Thanks in advance

Answer 11

 

Sorry for not replying sooner. Today was the first notice I received that anyone had replied to this thread.

 

Eric, the biggest negative impact that I have seen from log shipping is that you want to do the log backups as frequently as possible to minimize the chance of lost data in the case of an outage. This generally means that you are doing the log backups more frequently than you would be otherwise. This puts a considerably higher load on your disk subsystem, and if your disks are not setup optimally, you can experience system wide slowdown because of waits caused by log operations.

 

 

The negative impact of asynchronous mirroring are as follows

 

1. On the server itself is that it still consumes 2 worker threads. That means less worker threads for processing queries. This is true whether you are mirroring synchonously (high safety) or asynchonously (high performance).

 

2. If the mirrored database is experiencing high transactions, your databases will not be in sync, and if there is an outage, it could result in significant data loss.

 

3. If transactions are being queued, the part of the tran log that has not been committed on the mirror can not be truncated or over-written. This can result in larger tran logs. If the tran logs get too big, and the servers are rebooted, database mirroring may not be able to resume mirroring because the scan of the transaction log will time out before the mirroring connection can be re-established. When this happens, SQL Server will keep both sides of the database offline leaving neither principal nor mirror available. The only way to recover from this scenario is to drop mirroring, shrink the log file, and then set up mirroring starting with a new full backup.

 

 

Matilda, there are examples in Books Online. Look up "database mirroring [SQL Server], connecting clients to" and then click on "Making the initial connection to a database mirroring session." Or use this link from Books Online: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fd000cb1-9426-4ed9-8108-1907ddfd1c5f.htm

 

 

Ashok, database mirroring is designed for databases that can maintain connections to each other at nearly all times. If the databases are in different domains or geographical locations, you should use log shipping. Database mirroring will have difficulty maintaining the connection well enough to be effective.

 

Answer 12

Thanks for clarifying robert,
is there any link for which i can make use of ftp server to ship out the backuped log  files to the remote server by means of ftp? or should i use SSIS for this purpose? if so what needs to be the configurations to be done on remote server.

Answer 13

 

You don't need to use an FTP server or SSIS package. You would need IIS installed on the server to set up an FTP site. Windows has a perfectly fine ftp client built in to the OS which is easily scriptable. Obviously, if you take this approach, you can't use the SQL log shipping wizard, but I never use it any way.

Answer 14

Thanks once again, can i have a link to make this configuration for using log shipping via ftp? I have an IIS ftp service running on the production server for which the log shipping needs to be configured. any details available  for setting these configurations of log shipping via ftp?

Answer 15

I am looking for Disaster recovery Purpose, Distance Between Primary and secondary Sites are about 2000 miles and we have the 200mb band width,Can I chose Logshipping or Mirroring what will be the best option.Primary site we use Cluster and Number of Databses are about 20

Answer 16

Hi Ram,

 

You don't mention if you want to fail over to your DR site, so I'll assume you don't, and it's purely used as a backup.  As your sites at 2000 miles apart, synchronous mirroring is going to be slow (4000 mile round trip to commit every transaction!), and it's recommended that only about 10 databases are mirrored on any one principle server, due to the extra threads that mirroring requires.  You could try asynchronous mirroring, which wouldn't slow down your transactions, but again, only about 10 databases can be handled.  The benefit of asynchronous mirroring over logshipping is that your data will be more up to date than a log shipping solution - say less than a minute compared with 5, 10 or 15 minutes.

 

Log shipping would be the option I'd recommend.  It can handle any number of databases, and the fact that the sites are 2000 miles apart does not affect the operation.

 

The other thing to consider with mirroring, is that if your mirror goes down, then your transactions will stay in the transaction log until they can be transferred to the mirror.  This could cause your log to grow.  Log shipping will be taking backups every 5, 10, or 15 minutes, and doesn't require the DR server to be operational.  

 

Answer 17

Adding to Jim's comment,

 

1. In log shipping the secondary database will be in Read-only mode so that it can be used for reporting purposes.

2. In database mirroring the mirror database will be in Restoring state and hence cannot be used for accessing. If you want it for reporting purposes you need to make use of database snapshot.

3. Log Shipping supports only manual failover whereas in mirroring you have both automatic and manual depending on the configuration mode.

 

I would go for log shipping rather than mirroring as the distance and number of databases are into consideration. You can still mirror more than 10dbs but it 'depends' on the system requirements, disk space, bandwidth etc and hence most will tend to avoid it..Refer this link for more info, http://sql-articles.com/index.php?page=articles/diffdbmrr.htm

 

- Deepak

Answer 18

Please read the below table to find out the difference between mirroring and log shipping.

Database Mirroring	Log-shipping
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.	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 secondary’s for each primary database.
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.	Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule
Database mirroring supports only one mirror for each principal database. That means DB mirroring is at database level	Log-shipping can work on database and server level. You can configure multiple databases in logshipping
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/.Net 3.0	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
Support only full recovery model	Supports full and bulk-logged recovery model
Mirror database is always in recovery mode. To read it you have use database snapshot.	You can use the stand-by option to read the database on standby server
Auto Page Recovery introduced with SQL SERVER 2008 so it will recover the damaged pages.	Not supported

---

LAKSHMI NARAYANA REDDY.L

Answer 19

Database Mirroring

1)Database mirroring is at database level

2)Database mirroring can operate synchronously or asynchronously.

3) Multiple Mirrors are not possible .Database mirroring supports only one mirror for each principal database.

4)Individual Transactional Log records are transferred using TCP endpoints.

5)Only committed transactions are transferred
6)Automatic  Failover is there in mirroring

7)Support only full recovery model

 Log-shipping

 1)Log-shipping can work on database and server level

2) Log shipping is always asynchronously.

3) Multiple standBy servers are possible.we can configure multiple databases in logshipping.

4) Transaction Logs are backed up and transferred to secondary server in the logshipping.

5) All committed and un-committed are transferred.

6) Manual Failover in logshipping.

7) Supports full and bulk-logged recovery model.