September 28, 2006 at 6:23 am
Hi
I have two DB's in an async mirror - I need to keep it async mainly because the developer says so, and im not arguing. Take it for a fact that the state has to remain async. Failover under this model is manual - I dont have a witness configured, and can not in this scenario.
Unfortunately it seems you can only push the failover button on the primary server. This strikes me as insanely stupid - if my primary server is a pool of semi-expensice metals, then I dont have a failover button to push!
I have stopped all the sql services on the primary just to see if - magically - the mirror would let me in, and it doesnt.
Am i missing something here? hows it supposed to work in async / manual failover mode?
Alastair Jones
September 28, 2006 at 7:34 am
Hello Jones,
I too tried by mirroring the databases without a witness server. It is not allowing me to do the manual failover either from the Principal or Mirror sites when the operating mode is in "async".
It is suggesting me to change the operating mode to "sync" and then only manual failover happens when clicked.
Let me know if you have any other work around.
Thanks
Lucky
September 28, 2006 at 7:41 am
thats the problem in not using a witness.
Force service (with possible data loss) on the mirror server.
Forcing service is possible only if the principal server is down, the session is asynchronous (transaction safety is set to OFF), and the session does not have any witness (WITNESS is set to OFF). Forcing service causes the mirror server to assume the role of principal and serve its copy of the database for clients. When service is forced, whatever transaction logs the principal has not yet sent to the mirror server are lost. Therefore, you should limit forced service to situations where possible data loss is acceptable and immediate database availability is critical. For information on how forced service works and on best practices for using it, see Forced Service (with Possible Data Loss).
to force service on the mirror
Connect to the mirror server.
Issue the following statement:
ALTER DATABASE xxx SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
where xxx is the mirrored database.
The mirror server immediately transitions to principal server, and mirroring is suspended.
MVDBA
September 28, 2006 at 7:46 am
Mike
I found that command on MSDN after some googling, so I started a shrink on my primary DB, limited the bandwidth on my mirror (to match the speeds ill get from it when its over the WAN) and promptly stopped the sql services on the primary. then i forced the mirror, and yes, it comes up.
now however it wants to take an earth age to re-sync back / delete logs etc..cest la vie I suppose.
Another question. I cant now dump the trans log on my primary. this log gets to 130GB after a reindexing - what can I do to manage the lgo size now on my primary?
CHeers
Alastair Jones.
September 29, 2006 at 3:54 am
when you say - can't dump the log - why not ?
MVDBA
September 29, 2006 at 3:58 am
the command DUMP TRANSACTION [database_name] WITH NO_LOG gives me an error that i cant as the DB is now used for mirroring.
Ive restricted the max size of the log file, but im not sure how this will work with my re-indexing etc etc
September 29, 2006 at 4:02 am
then new primary or the old primary ?
MVDBA
September 29, 2006 at 4:06 am
sorry? - dont quite understand
As separate from my other query about manually forcing role reversal, i am currently wondering how to manage the size of the log file to stop it from growing exponentially..
September 29, 2006 at 4:28 am
dump transaction has been deprecated in 2005 use backup log (apparently backup log with truncate_only has also been deprecated)-
but if the primary in the mirror has not synchronised with the mirror then you can't - you'll have to break the mirror
MVDBA
September 29, 2006 at 8:08 am
I got it - you have to back up the log file first, then shrink it. Seems to do the job.
Cheers
Alastair Jones
October 2, 2006 at 3:33 am
I lie - removes currently unused space, but does not remove committed transactions.
Will open a new thread
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply