locked
reverse database mirroring - and is this the right approach? RRS feed

  • Question

  • Here is my scenario. I have 2 database servers, P and M, to serve our production website. P = Principal; M = Mirror.

    After some time my Principal crashes. No problem, I've got M, it's been synchronizing, and it has all latest data. So I repoint my production web server to hit the Mirror. Still, all is good.

    Now comes the part where I'm fuzzy, and I need to know if my understanding is correct...

    The Principal comes back online, but my website is still pointing at the Mirror. I want to repoint the site back to Principal. But P now has old data. Maybe it's been down 3 days and M has 100,000 new records that don't exist in P. When P came back online, did transaction logs begin shipping from M to P to get P back in sync? Would it do that if a Witness were in place? How does P get caught up with all latest data? Do I have to do a full backup and restore from M to P? Is there a way this can be set up to just gracefully (or even easy manual) get P updated and synced? Can the Principal/Mirror roles be easily swapped between the 2 servers? Does that happen automatically with a Witness in place?

    Thanks in advance for any help.


    • Edited by SweatCoder1 Tuesday, January 21, 2014 6:53 PM
    Tuesday, January 21, 2014 6:52 PM

Answers

All replies

  • First, mirroring is being removed for "AlwaysOn" technology.  I would not recommend using it in SQL 2012.  However, if you are talking about prior versions, then...

    When the failover occurs, the old mirror, now primary, attempts to send updates back to the old principal, now mirror.  This is completely automatic and requires nothing manual to happen.

    However, if the now mirror is down for a long period of time, the transaction log on the principal will continue to grow and grow until it is able to deliver the transactions to the mirror.

    You can setup your website with a "Failover Partner=" in the connection string and the failover/failback will be completely invisible to your application.

    I would suggest reading:

    http://technet.microsoft.com/en-us/library/ms189852.aspx

    http://technet.microsoft.com/en-us/library/cc917713.aspx

    Tuesday, January 21, 2014 7:22 PM
  • Tom,

    Thank you. I am using SQL2012, so what do you recommend instead of mirror? I want something simple to set up, as little config as possible.

    Thanks!

    Tuesday, January 21, 2014 7:58 PM
  • I would suggest using "AlwaysOn Availability Groups":

    http://technet.microsoft.com/en-us/library/ms190202.aspx

    Also, this discussion would be better served in the "SQL Server High Availabity" forum.

    • Marked as answer by SweatCoder1 Tuesday, January 21, 2014 9:47 PM
    Tuesday, January 21, 2014 8:15 PM
  • Thanks very much for the info. I have just spent some time studying AlwaysOn Availability Groups. It's kind of unfortunate when Microsoft deprecates a technology/technique that works and is easy to set up, and tells me I have to start using something that probably won't work in my server environment, and is far more difficult to set up. For example, I can only run AlwaysOn if both my servers are part of AlwaysOn Failover Cluster Instances. I have been reading but can hardly understand what it even means, and I'm pretty sure my servers don't currently support this. So I think I'm stuck using depracated technology.

    I'm not sure why MS thinks that highly complex setup must always be better, but anyway Tom, thanks for the excellent info.

    Tuesday, January 21, 2014 9:47 PM