September 8, 2010 at 3:24 am
Hi,
I have 2 SQL Server 2008 Enterprise Edition instances mirrored successfully (now) using FULL recovery and a witness server running SQL Server Express 2008 R2.
These instances are virtualised, and I recently had an issue whereby both the principal and mirror databases had been reverted to a snapshot prior to setting up the mirroring configuration (I'm currently in test), whilst the witness VM had not been reverted.
I was then encountering an issue when attempting to re-establish the witness via the principal server (using TSQL), and was receiving the error:
Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://ServerName:port'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
After running SELECT * FROM sys.database_mirroring_witnesses on the witness it was apparent that the witness server was still configured for use as a witness (obviously!), and as such this was stopping me from issuing the SET WITNESS command on the principal.
After all of the above...I was wondering whether there is a way that I could have removed the witness settings on the witness? as I ended up having to revert the witness to a previous snapshot and configure it is a witness again from fresh. As far as it was concerned it was still a witness with the partner_sync_state_desc in a disconnected state.
Thanks for reading through my waffley explanation - any comments would be appreciated 🙂
Matt
September 8, 2010 at 1:16 pm
I'm assuming you mean you want to remote mirroring from the witness, when it's not connected to the other servers?
I haven't found a process for this. Still looking. Everything says that you need to have a principal to disable the witness.
September 8, 2010 at 1:30 pm
I had someone reply that if you removed the mirroring ports from the witness (endpoints), it would be available to be set back up again.
September 8, 2010 at 2:11 pm
Thanks Steve, that makes sense actually, I hadn't tried removing the endpoint. I appreciate you looking in to it. I'll give it a go next time I revert my VM and post back.
Cheers
Matt
September 14, 2010 at 2:25 am
Hi, I had the same issue again and removed the endpoint on the witness. The witness still retains that it is part of a mirroring session, but thinks that it is out of snych. I still haven't found a way to remove the session when the witness is disconnected. I'll post if I find a way!
September 14, 2010 at 3:08 am
I think the best way may be to trace the witness at mirroring setup time and during a normal witness removal operation.
This way you may be able to find out what "they" do and use that to remove your witness data after revert time of the other VMs.
Johan
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 14, 2010 at 5:53 am
As traced, not all stuff is traceble ....
Select *
from sys.database_mirroring_witnesses
order by database_name, principal_server_name;
This dmv contains the witness info.
IMO it is fed behind the scenes.
You'll only see the dbmirroring witness service account connect from time to time.
I've also noticed, the dbmirroring endpoint isn't removed after "dismantling" the dbmirroring witness role.
So, I guess, you only need to setup the new mirroring servers to be able to use it.
(a witness can be used by more than one mirroring server pair)
Off course, it's may also be just me, not knowing how to trace dmv feeds.
edited I only found this info regarding witness cleanup ... and that is , you need to perform an alter database on the active database.
ref: http://msdn.microsoft.com/en-us/library/ms191519.aspx
Your situation may be worth a MSconnect entry 😎
Johan
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 19, 2010 at 7:36 am
Did you ever find a solution for this issue ?
Johan
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 28, 2011 at 2:09 pm
Did you get a resolution to this - if so how did you achieve it?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply