Database Mirroring Impact on Backups

Question

Database Mirroring Impact on Backups

Scott Arendt

SSCrazy Eights

Points: 8218
More actions
February 5, 2009 at 7:06 am

#199672

What impact does adding database mirroring have on a backup strategy?
Currently, I have a backup plan as follows:
One full per week, differentials every 6 hours, trans every 15 minutes.
Assuming that this plan is currently sufficient without database mirroring, does it need to be changed at all when adding mirroring? Any gotchas to be concerned with here? Does it make a difference when using High-Performance versus High-Safety?
Any input would be greatly appreciated.
TIA,
Scott

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MVIR Valued Member Points: 63 More actions · Answer 1

High Safety supports synchronous operation means that your principal and mirror databases are always in sync state wilt almost no data loss

High performance supports async operation so both databases are not in sync and there is possibility of data loss during failover

Richard M. SSCertifiable Points: 7401 More actions · Answer 2

We run High performance async mirroring and have no overhead at all that could be measured. The mirror DB's are synced up to the second (ie, basically no delays)

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

MVIR Valued Member Points: 63 More actions · Answer 3

The overhead depends on the amount of load that your server has

As per the articel on microsoft

High-performance mode, runs asynchronously. The mirror server attempts to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database, though, typically, the gap between the databases is small. However, the gap can become substantial if the principal server is under a heavy work load or the system of the mirror server is over loaded

http://msdn.microsoft.com/en-us/library/ms189852(SQL.90).aspx

Richard M. SSCertifiable Points: 7401 More actions · Answer 4

true.... both of our servers are pretty beefy..... even with a lot of transactions I haven't seen it lag behind more than a few seconds.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 5

Maybe I asked the question wrong. When you implemented mirroring, did you change your existing database backup plan?

Richard M. SSCertifiable Points: 7401 More actions · Answer 6

in our case, no... we still do backups as before, both full and transaction logs..... these get also backed up via our backup service.

Even with a mirrored database, you still need to (or should?) rely on your ol' backups.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

MVIR Valued Member Points: 63 More actions · Answer 7

thats true....backups are always the lfe savers!!!!..we still have old backup procedure inplace ..

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 8

Thanks for the input. My searches just didn't lead to any answers.

Scott