September 13, 2010 at 8:43 am
Log Shipping and Database Mirroring against data corruption issue for example ...
In mirroring if the Principle is corrupt (data) the Mirror will be corrupt and a full backup restore with tlog backups to where you detected the corruption in the tlog back ... so this is pure for SQL Server failure not data corruption
Log shipping if the tlog backup interval is long enough for you to detect data corruption you can do a tail log backup and restore to that point if you can correct? so this is can maybe get you restored quicker from a data corruption issue than a SQL Server failure issue ...
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
September 13, 2010 at 8:51 am
If the principal becomes corrupt it is highly unlikely that the mirror will as well. Corruption is an IO subsystem problem and, since mirroring just copies log records, such corruption cannot be copied to the mirror.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2010 at 12:42 am
Thanks Gail ... so no DB corruption from the principle will reach the mirror DB ...
So both Log shipping and Database mirroring will not require downtime to setup initially on the primary or principle ...
Mirroring is a true High availablity solution (auto failover) compared to Log shipping which is manual and more a disaster recovery solution ...
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
September 14, 2010 at 1:30 am
Just to Add to the info Gail has added for 2005... Failover is only automatic of you have a wintess to act a quorum (2 v's 1 vote). In addition to this you will need to configure the DSN or connection string to contain the "Failover Partner" .
SQL 2008 has the ability automatically repair page level corrupt. Basically if a corrupt page occurs on the principle then it can read the mirror to repair the page...
Paul has written an excellent article on it... http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Automatic-Page-Repair-with-Database-Mirroring.aspx
Summary (read carefully)
SQL Server 2008 introduces an enhancement to database mirroring that can bi-directionally pull pages between the principal and mirror databases to fix page corruptions. One word of caution I'd give is that this feature doesn't mean you can ignore these errors when they occur - you still need to do root-cause analysis on the corruption and take steps to prevent them happening again before a corruption occurs that automatic page repair cannot fix (as I mentioned above).
Nevertheless, this is a tremendously useful feature that's going to save a lot of downtime. Cool!
September 14, 2010 at 2:05 am
THANKS 😎
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
September 14, 2010 at 6:21 am
DB mirroring can be set to pause ...
With log shipping could you just disable the log backup, copy and restore jobs to pause it for a server reboot for example???
Enable the jobs when the server is back online again to get log shipping back online.
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
September 14, 2010 at 6:25 am
sql_lock (9/14/2010)
SQL 2008 has the ability automatically repair page level corrupt. Basically if a corrupt page occurs on the principle then it can read the mirror to repair the page...
Enterprise Edition only, and not all page types
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2010 at 6:26 am
Feeg (9/14/2010)
DB mirroring can be set to pause ...
Why would you need to? If the principal is down, the mirror becomes active, that's the point of mirroring. Mirroring won't break if the mirror disappears for a short while
With log shipping could you just disable the log backup, copy and restore jobs to pause it for a server reboot for example???
Enable the jobs when the server is back online again to get log shipping back online.
But why would you do that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply