SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Manually failover database mirroring


Manually failover database mirroring

Author
Message
david.alcock
david.alcock
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 1195
We have mirroring set up over a WAN and as such opted for manual failover to avoid network issues causing automatic failover and rendering connected clients unusable.

My question is, if the primary site was destroyed and connection to the principle is lost...is it still possible to failover or do you have to have that connection?

Many thanks
David

'Only he who wanders finds new paths'
david.alcock
david.alcock
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 1195
Can I do this on the mirror?

ALTER DATABASE<Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

'Only he who wanders finds new paths'
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12368 Visits: 8930
Did you have a look at BOL ?

Forced Service (with Possible Data Loss) http://technet.microsoft.com/en-us/library/ms189977(v=sql.100).aspx

it has a ref How to: Force Service in a Database Mirroring Session (Transact-SQL). which states the command you mentioned for the purpose you intended.

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
david.alcock
david.alcock
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 1195
Yes of course I looked on BOL, I was just after some confirmation really after posting the command. However, I didnt put any of that in my 2nd post and left it rather brief relying on everyones psychic knowledge which I apologise for! Thanks for the reply and yes, its exactly what I was looking for.

Thanks again. :-)

'Only he who wanders finds new paths'
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12368 Visits: 8930
Of course, testing it is fairly easy to do and the preferable way before implementing it into production.

Walk through all scenarios and test the actions before you need to apply them in production.

I strongly advise to monitor your mirrored databases as mentioned in BOL:
Monitoring Database Mirroring

and prepare your DRP inventory scripts Cool

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20252 Visits: 17244
david.alcock (8/12/2013)
Can I do this on the mirror?

ALTER DATABASE<Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Losing the principal in either async mode or sync mode with no witness requires you to use the above query to bring the mirror online.

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
SQL Galaxy
SQL Galaxy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2546 Visits: 3465
Hi

ALTER DATABASE<Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Instead of above query, can we run as below query for make it online in MIRROR Database..? Mirror database always sychronizing/Restoring mode. wheather High performance and High safty mode configured



USE master
GO
ALTER DATABASE <MIRROR DATABASE> SET PARTNER OFF
GO
RESTORE DATABASE <MIRROR DATABASE> WITH RECOVERY
GO



ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12368 Visits: 8930
ananda.murugesan (8/14/2013)
Hi

ALTER DATABASE<Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Instead of above query, can we run as below query for make it online in MIRROR Database..? Mirror database always sychronizing/Restoring mode. wheather High performance and High safty mode configured



USE master
GO
ALTER DATABASE <MIRROR DATABASE> SET PARTNER OFF
GO
RESTORE DATABASE <MIRROR DATABASE> WITH RECOVERY
GO






That would turn it back into a non-mirrored database !

If you would want to re-enable mirroring for that database, you would have to perform the full setup for it.

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
SQL Galaxy
SQL Galaxy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2546 Visits: 3465
I have doubt.. I have Mirror setup manual failover without witness server

If Principal server is down due to N/W or H/W issues, In this cause mirror database condition is Restoring only...so I want to make online mirror database which command need to be executed?

Is it correct as below command? only one command

ALTER DATABASE <MIRROR DATABASE> WITH RECOVERY

rgds
ananda
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20252 Visits: 17244
ananda.murugesan (8/14/2013)
I have doubt.. I have Mirror setup manual failover without witness server

If Principal server is down due to N/W or H/W issues, In this cause mirror database condition is Restoring only...so I want to make online mirror database which command need to be executed?

Is it correct as below command? only one command

ALTER DATABASE <MIRROR DATABASE> WITH RECOVERY

rgds
ananda

As i said, whether you use synch or asynch mirroring without a witness, if you lose the principal the only way to bring the mirror online is by using

ALTER DATABASE <Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS



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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search