October 8, 2012 at 10:05 am
Perry is correct. If the principal is down and you don't have automatic failover configured, you have to use FORCE_SERVICE_ALLOW_DATA_LOSS to bring the mirror online.
After the principal comes back up, you have to resume mirroring manually for mirroring to continue. If you want to fail back to the original principal, after you resume mirroring, you can issue the failover command.
October 8, 2012 at 10:06 am
But no data loss possible if the mirroring was synchronised at point of failure, and no possibility of data loss when failing back?
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 10:30 am
From Wrox Book " SQL Server Prof admin"
I see the following Info:
Execute the following command on ServerB (which is still the mirror after ServerA becomes unavailable):
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
That will bring the database online on ServerB, which becomes the principal server.
When ServerA comes online, it automatically assumes the mirror role. However, the mirroring session is suspended,
meaning no transaction logs will be sent from ServerB to ServerA. You need to resume the
mirroring session by starting to send the transaction logs from ServerB to ServerA by executing the
following command:
ALTER DATABASE <database_name> SET PARTNER RESUME
October 8, 2012 at 10:49 am
GilaMonster (10/8/2012)
But no data loss possible if the mirroring was synchronised at point of failure, and no possibility of data loss when failing back?
Correct. If it was synchronized, there will be no data loss.
October 8, 2012 at 11:16 am
Books Online just being cautious then:-D
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 8, 2012 at 12:33 pm
🙂
October 8, 2012 at 12:53 pm
DBA_Learner (10/8/2012)
I am thinking of these steps to determine. Please correct if there is any mistake. Thanks1)If Primary Server Fails, then on Mirror Server execute
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS.
Yes, that is correct.
DBA_Learner (10/8/2012)
2)When Primary server comes online, execute the following on Primary Server to make original primary as Primary serverALTER DATABASE <database_name> SET PARTNER RESUME
Yes, this can be executed on either partner (principal or mirror).
DBA_Learner (10/8/2012)
Incase if it is a large database or resolution didn’t took place in maximum of 1 day, then restore the mirrored database copy onto Principal and keep the principal database in “recovering state” and execute the folllwoing cmd:ALTER DATABASE <database_name> SET PARTNER RESUME
No, you don't need to do this. It doesm't matter if it's more than a day as once you resume mirroring the new mirror will drop any recovery forks and synchronise with the new principal. Size has effect here either
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 8, 2012 at 1:07 pm
Thanks Perry and All, I think I have learnt something new..Will implement on my VM machine and will get back to you if any more questions.
October 8, 2012 at 3:24 pm
Sure, no problem
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 10, 2012 at 10:05 pm
Hello,
I received the following error when I ran the following command on Mirror server:
alter database dbname set partner
force_service_allow_Data_loss
Msg 1455, Level 16, State 106, Line 1
The database mirroring service cannot be forced for database "db name"because the database is not in the correct state to become the principal database.
I have disconnected my Prinicpal server and tried to execute the command on Mirror. Currently the mirror is in
(Mirror,Synchronized/Restoring ) state.
But, If I apply this command, it is completely breaking the Mirroring, where I again need to setup from scratch.
ALTER DATABASE <database_name> SET PARTNER OFF
RESTORE DATABASE <database_name> WITH RECOVERY
Can any one tell me why I am receiving this error. Thanks
October 10, 2012 at 10:20 pm
Describe "disconnected principal server". The mirror thinks it is still connected.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply