0

So I am trying to configure a two server setup, both containing SQL with independent storage. We're using windows server 2012, and I have a question about mirroring. If the server's data bases are in sync then the primary server fails and the mirrored server takes over. How do I re-synchronize the servers after I put the primary(now the one that will act as the mirror) back up? Is that handled automatically?

Edit: I guess I mean after a fail over, how does the old primary get back in sync with the mirror that has been receiving transactions. I think the endpoints are crucial as Kin said.

4
  • Mario, are you using automatic failover?
    – QWE
    Jun 30, 2016 at 15:28
  • @QWE nope, we're using manual failover (maybe forced manual failover) Jun 30, 2016 at 15:30
  • 1
    If you run this on your mirror server (which became primary after failover) then your servers will be back to their original roles. 'ALTER DATABASE [yourDatabase] SET PARTNER FAILOVER GO' Please have a look: link
    – QWE
    Jun 30, 2016 at 15:36
  • Also this one: sqlservercentral.com/blogs/robert_davis/2009/02/22/…
    – QWE
    Jun 30, 2016 at 15:46

1 Answer 1

1

When you do a failover, a role reversal happens i.e. your primary becomes secondary and your secondary becomes primary.

ALTER DATABASE [yourDatabase]
SET PARTNER FAILOVER
GO

Doing so, only the role reversal happens (this is what happens when you initiate a failover using above command) and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN. Refer to my answer : If I fail over one database, do the others that share the same mirror endpoint fail over as well? for more details.

You can have your application use Failover Partner=your_mirror_serve_name, so that your application is redirected to replica database when a failover happens.

Refer to : connectionstring in Database Mirror setup

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.