DB Mirroring High Safety Mode wihtout witness

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » DB Mirroring High Safety Mode wihtout witness...

26 posts, Page 1 of 3

1 2 3 »»»

DB Mirroring High Safety Mode wihtout witness

Rate Topic

Display Mode

Topic Options

Author

Message

DBA_Learner

Posted Monday, October 08, 2012 7:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:37 PM
Points: 170, Visits: 388

I have a general question regarding DB Mirroring, which is currently running on High safety Mode without witness.

If server_A fails, then On Server_B we will execute the following command right,

ALTER DATABASE <database name> SET PARTNER OFF
RESTORE DATABASE <database name> WITH RECOVERY

Now, my Question is - If Server_A becomes available, how do we need to re-establish the mirroring session in "Recovering state" and make Server_A as Principal.
What Query do I need to run on Mirror / Principal. Please let me know. Thanks

Post #1369814

GilaMonster

Posted Monday, October 08, 2012 8:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

DBA_Learner (10/8/2012)

No, not unless you want to break the mirroring session entirely. To failover you run

ALTER DATABASE <database name> SET PARTNER FAILOVER;

Edit: missed the server failed portion. Ignore.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1369831

DBA_Learner

Posted Monday, October 08, 2012 8:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:37 PM
Points: 170, Visits: 388

Thanks Gila. But my question is how to resume the Principal server and make the Mirror server database into Recovering state

Post #1369859

Perry Whittle

Posted Monday, October 08, 2012 8:45 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 5,242, Visits: 11,259

incorrect and misleading info removed, didnt read thorughly, please see below

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1369862

GilaMonster

Posted Monday, October 08, 2012 8:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

DBA_Learner (10/8/2012)

Thanks Gila. But my question is how to resume the Principal server and make the Mirror server database into Recovering state

By running the exact same command a second time. That command fails over the mirroring. Run it a second time and the mirroring fails over again, back to how it originally was.

Now, the original commands you were considering running would have resulted in you having to reconfigure the mirroring again (new backups, etc)

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1369864

DBA_Learner

Posted Monday, October 08, 2012 9:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:37 PM
Points: 170, Visits: 388

Thanks All,

So, here is what I understood.

If Server A fails we need to execute the following command on Server B to make it available.
ALTER DATABASE <database name> SET PARTNER FAILOVER;

Once, the Server A is available, in order to make ti available and make Server B into restoring mode, we need to re-run the following queyr(same)
ALTER DATABASE <database name> SET PARTNER FAILOVER;

Is this correct? Am I going in right order.

Post #1369875

DBA_Learner

Posted Monday, October 08, 2012 9:10 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:37 PM
Points: 170, Visits: 388

These are steps I am thinking..Correct me if anything wrong.

1. Viewing Mirror Information

SELECT d.name, d.database_id, m.mirroring_role_desc,
m.mirroring_state_desc, m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL

2. Execute the following on serverB to make the database service available:

ALTER DATABASE <database name> SET PARTNER OFF
RESTORE DATABASE <database name> WITH RECOVERY

3. Once Server A becomes available, you need to re-establish the mirroring session.

ALTER DATABASE <database name> SET PARTNER FAILOVER;

Post #1369877

GilaMonster

Posted Monday, October 08, 2012 9:19 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

2. Execute the following on serverB to make the database service available:

ALTER DATABASE <database name> SET PARTNER OFF
RESTORE DATABASE <database name> WITH RECOVERY

3. Once Server A becomes available, you need to re-establish the mirroring session.

ALTER DATABASE <database name> SET PARTNER FAILOVER;

2. Execute the following on serverB to make the database service available:

ALTER DATABASE <database name> SET PARTNER FAILOVER;

3. Once Server A becomes available, to make it the principal again.

ALTER DATABASE <database name> SET PARTNER FAILOVER;

If you set partner off, you are breaking the mirroring completely and you will have to reconfigure it (new backups, etc)

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1369884

Perry Whittle

Posted Monday, October 08, 2012 9:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 5,242, Visits: 11,259

Ah right I see now i have re read thoroughly and i made an error in my post. If Server A fails you cannot failover you must force service using

ALTER DATABASE mydb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

As the principal is down no failover will be possible. When Server A comes backup resuming the session will lose some unsent data and also updates on the new principal.

If you cannot lose data then break the mirror and bring the mirror online as principal. If you can accept some data loss then force service and when the original principal comes back up resume mirroring.

Check Books online for more info

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1369896

DBA_Learner

Posted Monday, October 08, 2012 9:38 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:37 PM
Points: 170, Visits: 388

Thanks Gila, That's good steps what you have given. So last question is When the principal is ready, do we need to do any backup on mirror server and restore that onto Principal?

Post #1369900

« Prev Topic | Next Topic »

26 posts, Page 1 of 3

1 2 3 »»»

Permissions