October 8, 2012 at 7:41 am
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
October 8, 2012 at 8:02 am
DBA_Learner (10/8/2012)
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
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2012 at 8:39 am
Thanks Gila. But my question is how to resume the Principal server and make the Mirror server database into Recovering state
October 8, 2012 at 8:45 am
incorrect and misleading info removed, didnt read thorughly, please see below
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 8, 2012 at 8:49 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2012 at 9:05 am
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.
October 8, 2012 at 9:10 am
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;
October 8, 2012 at 9:19 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2012 at 9:29 am
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" 😉
October 8, 2012 at 9:38 am
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?
October 8, 2012 at 9:39 am
Also, I am talking about SQL Server standard edition 2008
October 8, 2012 at 9:41 am
DBA_Learner (10/8/2012)
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?
Those steps are not relevant to you as you have said that Server A the principal failed.
In this case you can only force service. Please see my last post!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 8, 2012 at 9:42 am
Perry Whittle (10/8/2012)
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.
Isn't that just the case for async mirroring? With synchronous mirroring, even with no witness, there can be no data loss.
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
October 8, 2012 at 9:56 am
you would have thought so Gail, but BOL still states that forced service is possible with safety on and no witness but at the risk of possible data loss. In this case i think the data loss would be more when switching back when the original principal comes back online. TBH its something i've never tested but may well do now 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 8, 2012 at 10:04 am
I can understand data loss in the case where the network connection drops first and the principal server fails at some point later while still disconnected (but data loss could occur in that case even with a witness), but not when the principal just fails having been connected and synchronised up until that point, there shouldn't be any data loss.
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
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply