Mirroring to multiple databases possible?

Question

Post reply

Mirroring to multiple databases possible?

Kiran.Baderdinni

SSC Rookie

Points: 26
More actions
July 18, 2012 at 11:30 am

#259094

Can a source db be mirrored to two different databases residing on separate instances in different locations?

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Simple answer, no. You do get this capability in SQL Server 2012 with AlwaysOn.

Kiran.Baderdinni SSC Rookie Points: 26 More actions · Answer 2

Does it make sense to setup Transactional Replication and have the 2 sites subscribe?

I want to be in a position to recover at multiple locations almost instantly.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

For HA, in my opinion Log Shipping to multiple secondary instances is a much better option than Transactional Replication.

Log Shipping Overview (2008 R2)

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Kiran.Baderdinni SSC Rookie Points: 26 More actions · Answer 4

The over-arching concern I have with LS is that 'page corruptions' will propogate to the secondaries. Whereas with Mirror/Replication, it does not.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Kiran.Baderdinni (7/18/2012)
The over-arching concern I have with LS is that 'page corruptions' will propogate to the secondaries.

No they won't. Not unless the corruption is in the initial full backup used to create the secondary. If it is, then mirroring will have the same problem (and replication if you init from backup)

Besides, if you have frequent enough corruptions for that to be the primary concern, you need to fix or replace your IO subsystem.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kiran.Baderdinni SSC Rookie Points: 26 More actions · Answer 6

Won't a bad page in the tran log backup (any of the hourly tan backup) have the same outcome? bcos we are copying and then restoring it on to the secondary.

Can you point me to some KB articles that will help clarify?

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Kiran.Baderdinni (7/18/2012)
Won't a bad page in the tran log backup (any of the hourly tan backup) have the same outcome?

Corruption is from something in the IO subsystem stamping on a page outside of SQL. Said misbehaving I/O subsystem is not polite enough to write garbage all over a page in the data file and then log that change correctly in the database transaction log.

The only way a corrupt page can get into the log backup is if you're in bulk-logged recovery, you do a bulk operation, the pages are flushed from the buffer pool and the pages on disk corrupted before the next log backup runs.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

GilaMonster (7/18/2012)
Kiran.Baderdinni (7/18/2012)
Won't a bad page in the tran log backup (any of the hourly tan backup) have the same outcome?
Corruption is from something in the IO subsystem stamping on a page outside of SQL. Said misbehaving I/O subsystem is not polite enough to write garbage all over a page in the data file and then log that change correctly in the database transaction log.
The only way a corrupt page can get into the log backup is if you're in bulk-logged recovery, you do a bulk operation, the pages are flushed from the buffer pool and the pages on disk corrupted before the next log backup runs.

There is no way to know since it will depend on your workload and environment conditions but I will venture into general terms here and say that the likelihood of something like the bolded scenario Gail gave above occurring will be much lower than the likelihood of a Transactional Replication subscription either getting out of sync on the subscriber or failing to maintain transactional consistency when it's time to failover.

There are usually exceptions (i.e. as usual it depends) but by default I would start with Log Shipping for HA over Transactional Replication.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Another option: Mirror for automatic failover (synchronous with witness) and log ship to a 3rd site for extra redundancy.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 10

A great feature from SQL Server 2012,AlwaysOn Availability.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

Vikrant S Patil SSCrazy Points: 2294 More actions · Answer 11

wow, learnt something new just by reading through all the excellent suggestions from knowledgeable folks. Thanks.

I think most DBAs would go with Log Shipping in this scenario, may be you can Log ship to multiple locations and keep:

1) one of the secondary server in real time sync.

2) and the other a large latency that take care of your worries pertaining to things that may go wrong on primary server (it could be accidental data deletion as well) getting carried forward to the secondary server.