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.

  • Mario, are you using automatic failover? – QWE Jun 30 '16 at 15:28
  • @QWE nope, we're using manual failover (maybe forced manual failover) – Mario Merendino Jun 30 '16 at 15:30
  • 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 '16 at 15:36
  • Also this one: sqlservercentral.com/blogs/robert_davis/2009/02/22/… – QWE Jun 30 '16 at 15:46
0

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, privacy policy and cookie policy

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