SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Difference between Log shipping and DB mirroring


Difference between Log shipping and DB mirroring

Author
Message
vkundar
vkundar
SSC Eights!
SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)

Group: General Forum Members
Points: 809 Visits: 869
Hi,

I woulkd like to know what is the diffrenece between log shipping and DB mirroring. I found both to be same except in mirroring automatic fail over is allowed.

Would you please share some of your experience.

Thanks in advance..

Cheers,

Got an idea..share it !!

DBA_Vishal
Paresh Prajapati
Paresh Prajapati
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1915 Visits: 464
In the database mirroring , transaction which are commited directly write to mirror server database files, whereever in transaction log shipping transaction log will be backup and resored on secondary server..

_____________________________________________________________________________________________________________
Paresh Prajapati
Arrow +919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8103 Visits: 6594
The automatic failover is very important different. Beside that there are other differences. Log shipping is always asynchrony. Mirroring can be synchrony or asynchrony. You can do log shipping to multiply sites. You can do mirroring to one site only. Depending on the way that you configured the log shipping, you might be able to work with the target database and issue select statements on it for reports. You can not work with the secondary database in mirroring. Mirroring is supported only from SQL Server 2005. Log shipping was supported on much earlier versions (I think that it was introduced with SQL Server 6.5, but I’m not sure).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7437 Visits: 2250
Officially Logshipping was introduced from SQL Server 2000 only. But surely we can create job to do the same thing in 6.5 & 7.0 with creating customized jobs/SP.

Also one more difference is in Logshipping you can have access of the destination database as READ-only; While in mirroring you do not.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Josep
Josep
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 4678
I was asking myself the same question. Because although they are different they're used for quite the same (High Availability), but I don't know which one to use.

In a SAFETY = FULL, the active server has more latency in transactions, but what happen if the passive server goes down? I suppose it keeps the log and tries to apply it later. Is it right? This lattency is one of the things that makes me thinking more in Log Shipping...

In a active/passive clustered system, you pay only one licence. Is it happen the same in Data Mirroring and Log Shipping? I don't get the answer anywhere...


Regards,
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53517 Visits: 17672
database mirroring provides a warm standy copy of the database.

Log shipping works differently, log files are shipped to a remote database and applied. The database has 1 of 2 states, readonly or norecovery. Norecovery aloows subsequent log file restores and requires manual intervention to bring the database online. Read only provides a readonly copy of the database and clients are disconnected during log restores

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
kkk514
kkk514
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 153
In Logshipping both Commited and Uncommitted data will be shipped whereas in Mirroring only commited data.
ps.
ps.
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7095 Visits: 3668
In Logshipping both Commited and Uncommitted data will be shipped whereas in Mirroring only commited data.


is this so?

In log shipping, I was of the opinion that only commited transactions are backed up during backup log and the same backed up log file is moved to secondary server(containing committed data only). Please clarify.



Pradeep Singh
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8103 Visits: 6594
In log shipping the log is backed up. The log includes both open and close transactions. Each time that the log is being restored the server applies all the transactions (open and close transactions). Since in log shipping the restore is done with norecovery option or with standby option, the server knows that it will have another log with the continuation of the open transaction. As long as you are using one of those options (and with log shipping you should use it all the time), you'll restore the open transaction. If something goes wrong with your source server/database and you want to use the target database from you log shipping, you'll have to bring it online by running the last restore with recovery option. At that point, since there will be no other restore that might close the open transactions, the server will rollback all the open transactions.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
ps.
ps.
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7095 Visits: 3668
Thanks for Clarification Adi.
I get so much learn here Smile



Pradeep Singh
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