Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DB Mirroring AND Log Shipping on same db!

Posted on 2013-12-10
6
Medium Priority
?
838 Views
Last Modified: 2014-01-30
I'm currently employing Database Mirroring in High Safety Mode without Automatic Failover on a SQL Server 2008R2 machine for eighteen high-volume OLTP databases. The architecture is comprised of a Principal and Mirror without a Witness. All of that works well. As an HA/DR solution we're looking get our log backups onto the mirror via Log Shipping.

I've read that DB Mirroring and Log Shipping can operate on the same DBs. But I can't find a whole lot of info describing what both can achieve while both operate on the same DB. With DB Mirroring in High Safety Mode, transactions are sent to the mirror added to the transaction log, persisted to the mirror's disk and then committed to the principal's log and disk. So this is pretty much real-time.

When running Log Shipping on the same DB that employs DB Mirroring, the DB Mirroring Principal is also the Log Shipping Primary and the DB Mirroring Mirror is also the Log Shipping Primary/Standby.

After the Log Shipping backs-up the Principal/Primary is the log first truncated on the Mirroring Mirror / Log Shipping Standby and then the Principal/Primary log or vice-versa?

How does the restore work when employing DB Mirroring and Log Shipping on the same DB? This confuses me especially because mirroring is up-to-the-second.

I've already read this: http://technet.microsoft.com/en-us/library/ms187016%28v=sql.105%29.aspx.

If you have experience with this please respond!

Thanks!

pae2
0
Comment
Question by:pae2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39711492
I haven't set this up ... but having read your scenario and the article you link, I think I understand the scenario.

Your mirror gives you a near-real-time, completely consistent 2nd copy of the DB to which you can fail over during production, should that be needed.  As such, it will assume the role of being the log shipping primary in the event of a failover.

The log shipping recipients become eventually consistent with the primary, serving both as read-only partners and emergency backup sources.

The emergency that requires you to restore from a log shipping partner is an extreme situation in which both the primary and the mirror failed.  As far as I can tell, you would need to re-establish the mirror and log shipping after that restoration.  I can't see any way that a log-shipping recipient could be restored and automatically assume the role of the master.
0
 
LVL 79

Expert Comment

by:arnold
ID: 39711665
The difficulty I am having in understaning what third system is used for the log shipping destination.
Principal/mirror
Primary log shipper to third system.

Log shipping from the mirror would likely need to be established on failover.

Your question seems to suggest that the mirroring and log shipping are going to the same set of systems.
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 39722936
You can combine Mirroring and LogShipping as illustrated in the article, but your description seems to imply that you can have mirroring and log shipping to the same target.  That would not make sense.

The way the combining works is that you have a Primary Mirror and it mirrors to the secondary, you can use the Primary Mirror in a log shipping scenario to another secondary not the same one that is the Secondary mirror.  So if you have another target for the restore of the log shipping then you are OK.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:pae2
ID: 39809432
arnold/dbaduck, we are using mirroring and log shipping on the same machines i.e. two machines. Machine-1 is the mirroring principal and the log shipping primary. Machine-2 is the mirroring mirror and the log shipping secondary. Our goal is to simply copy over our log backups to our secondary so that we have two copies of our logs backups (one local and one on the mirror/secondary) until our long-term backup storage application grabs those logs from the mirror/secondary. Then we delete those log backups, i.e. both locally and on the mirror/secondary. The architecture works and is very applicable in achieving our goal. Thanks for the effort.
0
 

Accepted Solution

by:
pae2 earned 0 total points
ID: 39809433
I've done some experimenting with this and have validated the following functionality. If you have mirroring and log shipping running on a primary server and a secondary server and then mirroring is paused or stops running, and then a log backup executes while mirroring is paused/not running (but the mirroring session still exists) the log is NOT truncated. In essence, while a mirroring session exists (despite not running at the time), the log will NOT be truncated by a log backup! Once mirroring is dropped completely, the log will then truncate during a log backup, which is normal. So it really doesn't matter which log is truncated first while mirroring is running. And it won't truncate while mirroring isn't running (but the session exists) so the logs will always be synched.

If you need to restore via log shipping because mirroring failed, you can restore a full db w/no recovery, by overwriting the current db or creating a new db. During that time, take a tail-log backup, then you can restore the log shipping logs w/no recovery, and then restore the tail of the log w/recovery to get the db up-to-date and online. Then enable db mirroring.
0
 

Author Closing Comment

by:pae2
ID: 39820341
I spent a lot of time answering my own questions and validating my claims. My statements are proven.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question