Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Difference between Log shipping and DB mirroring Expand / Collapse
Author
Message
Posted Sunday, November 16, 2008 11:18 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:52 AM
Points: 89, Visits: 451
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
Post #603471
Posted Sunday, November 16, 2008 11:25 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 7:41 AM
Points: 323, 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
+919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Post #603474
Posted Sunday, November 16, 2008 11:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
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/
Post #603476
Posted Monday, November 17, 2008 3:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:45 AM
Points: 2,385, Visits: 1,843
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."
Post #603536
Posted Monday, November 17, 2008 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 3:34 AM
Points: 139, Visits: 4,614

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,

Post #603671
Posted Monday, November 17, 2008 10:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 5,961, Visits: 12,847
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"
Post #603801
Posted Tuesday, December 16, 2008 5:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:55 AM
Points: 6, Visits: 129
In Logshipping both Commited and Uncommitted data will be shipped whereas in Mirroring only commited data.
Post #620842
Posted Tuesday, December 16, 2008 9:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, January 26, 2014 11:13 AM
Points: 2,242, Visits: 3,641
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
Post #620908
Posted Tuesday, December 16, 2008 11:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
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/
Post #620963
Posted Tuesday, December 16, 2008 11:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, January 26, 2014 11:13 AM
Points: 2,242, Visits: 3,641
Thanks for Clarification Adi.
I get so much learn here :)






Pradeep Singh
Post #620970
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse