re-establishing mirror

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.

How do I remove old information from witness server? Thanks.

If the witness server contacts the former principal, it should discover that mirroring was turned off and remove the metadata from master.

What version of SQL are you using? Build number?

Thanks,

Mark

|||

Witness came up but it did not remove the metadata. I am using SQL server 2005 sp1. Build number is 9.0.2047. Thanks

Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks.

|||

Thanks for the info.

"Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks."

1. There is no way to manually remove the witness information.

2. The principal server should be able to over write the witness information.

My guess is that there is a communication problem between the two servers. Did you change the port # for mirroring? Look in the errorlog. It should have more information about what went wrong. My guess is that you are setting up mirroring again and somehow changed the network configuration.

Thanks,

Mark

|||

Witness is showing the following error:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://primary:5022'.

Primary server has the following error in the error log:

Database mirroring connection error 4 'An error occurred while receiving data: '1236(The network connection was aborted by the local system.)'.' for 'TCP://witness:5024'.

Server combination have been changed after the initial breakdown of database mirroring. So initial primary server is no longer the new primary server. So you are right about network settings being changed. What can I do so that I can reestablish the mirroring? Thanks.

|||

So, the only way that the witness can drop its mirroring information is to connect to one of the original servers and be told that the database is not there or the mirroring session is different. Now you can't change any of the connection parameters on the witness.

Setup a server just like on of the partners in the original scenario, which means have a sql server instance running on a server with the same name as one of the earlier partners AND have a mirroring endpoint up and running on the original port with the original settings.

The witness is using the orginal connection properties to log into the partner servers once a second. Once it gets to a sql server instance on the original ports, the witness will be told that database for which it wants to be a witness for doesn't exist or it isn't mirrored. At that point the witness will stop trying to be a witness.

As far as setting up the new witness, are you sure that the servers can connect to each other?

Thanks,

Mark

|||

I can't set up the original server. I don't have another computer to name as the original computer name and I can't change the name of the existing servers.

Current primary and witness can see each other. I created linked servers and verified queries.

When I am going through the wizard setting up mirroring on the primary server, security configuration is working fine. When I press start mirroring button, I am getting the error. Please let me know if you want to verify connection any other way or have any other ideas to set up mirroring. Thanks.

|||

So, if you can telnet into the endpoints from each server, then the endpoints on setup properly.

Is it still failing with the same error number? I believe that the witness server should be able to be a new witness server for this new database because the mirroring GUID will be different from the previous one.

But, to get rid of the original metadata for the first witness config, you need to have that server talk to sql on the original port and name.

Thanks,

Mark

|||If I can't get rid of original metadata, I can live with that. However I can't use the witness server because it is failing with the same error. Database name is same from the original one. I can try with a new database name to see if database name is the problem. I need to make this work with the original database name. The only other thing I can think of is reinstall mirror server. Thanks.|||I used a different database. Witness server did not work. Finally I completely uninstalled witness server and reinstalled wintness server. Re-established mirroring using the new mirror server. Now mirroring is working fine. So when things go wrong witness server will have certain stuff which will be only cleaned up if it can see the original servers. If it can't see the original servers then we can not only clean up witness server, that server will not be able to participate as witness server again. This could be an isolated situation, in Microsoft labs as Mark indicated, witness server works on unique guids, so one mirror combination should not effect others. In my case some thing went wrong. In rare cases like this it would be nicer if I can clean up the witness server rather than reinstall. Thanks very much Mark for all your help. On a different thread I asked about changing synchronous to asynchronous when witness and mirror are down. I would like to read your comments on that kind of thinking. Thanks again.|||

OK, so I'm sorry you had so much trouble with mirroring. The witness should have been able to be used as is with the metadata intact.

Since the information for the witness is stored in master. If you had a copy of master from before, you could have restored it and see what happens.

Since the database isn't on the witness server, there really isn't a good way to turn off the witness on the witness. We'll think about that.

Thanks,

Mark

|||I did not think about that. That's a good suggestion. Thanks Mark.|||I am having this very issue using certificate-based authentication. Am I correct in understanding that the current resolution, in absence of a backup of Master, is to uninstall and reinstall SQL Server 2005 SP1 on the witness server?|||

FYI, as it turned out in my case, I had been trying to use the IP address to configure the Witness (This worked for Principal and Mirror). E.g.:

ALTER DATABASE [AdventureWorks]
SET WITNESS = N'TCP://10.0.0.5:10001';
GO

That T-SQL statement, run on Principal, produced this error:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://10.0.0.5:10001'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

The Windows Event Log said:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://10.0.0.5:10001'.

The solution to my problems was when I put entries for Principal, Mirror, and Witness in each machine's C:\WINDOWS\system32\drivers\etc\hosts file.

Now it works.

|||

Hi,

I think i am having the same mirroring issues as you had.....in my case, server1 is the principal, server2 is the mirror and server2\witness is the witness..

i am getting the Msg 1456 error when i go through the mirroring wizard

in event log, i am getting the same error as you had...

can you please explain a little bit more on how to add entries in the hosts file?

thanks