November 16, 2008 at 11:18 pm
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
November 16, 2008 at 11:25 pm
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..
November 16, 2008 at 11:31 pm
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/
November 17, 2008 at 3:38 am
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."
November 17, 2008 at 7:45 am
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,
November 17, 2008 at 10:10 am
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" š
December 16, 2008 at 5:58 pm
In Logshipping both Commited and Uncommitted data will be shipped whereas in Mirroring only commited data.
December 16, 2008 at 9:06 pm
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.
December 16, 2008 at 11:28 pm
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/
December 16, 2008 at 11:47 pm
Thanks for Clarification Adi.
I get so much learn here š
December 17, 2008 at 9:47 am
Josep (11/17/2008)
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...
When using log shipping or db mirroring, the receiving server doesn't need a seperate license as long as it remains passive. If queries are run against the server, it needs to be licensed. See http://www.microsoft.com/Sqlserver/2005/en/us/special-considerations.aspx
Greg
December 17, 2008 at 11:54 pm
Please go through the below post,
http://www.sqlservercentral.com/Forums/Topic604457-146-1.aspx
I think u will be very clear after that.
December 18, 2008 at 8:19 am
Greg Charles (12/17/2008)
When using log shipping or db mirroring, the receiving server doesn't need a seperate license as long as it remains passive. If queries are run against the server, it needs to be licensed. See http://www.microsoft.com/Sqlserver/2005/en/us/special-considerations.aspx
Thanks Greg for the link.
Josep.
December 19, 2008 at 3:46 am
Adi Cohn (12/16/2008)
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.
I have not implemented log shipping, so I have to accept the greater knowledge and experience of anyone who has. But this sounds wrong - all I know about backing up a transaction log says that only committed transactions are backed up, and unless I am missing something, open == uncommitted. Can someone provide a reference to verify that "open" transactions are shipped, or clarify this?
December 19, 2008 at 5:06 am
I'm all the more confused. I believed what Adi said in his post. But again searched through internet when Ewan raised the issue.
Here are some of the exceprts :-
Each time the transaction log is backed up, SQL Server removes all of the committed transactions in the log and writes them to the backup media from
http://databases.about.com/od/sqlserver/a/disaster_3.htm
here this URL says only committed entries are backed up.
The log is backed up from the last successfully executed log backup to the current end of the log. from http://msdn.microsoft.com/en-us/library/ms186865.aspx
msdn doesnt explicitly mentions active/inactive transactions in the log file, so i assume it backs up all entries.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply