October 29, 2013 at 8:23 am
Hi,
I have a really strange problem with a Witness Server.
I am not able to change the name of the witness-server on a running db-mirroring, in synchron modus.
The first database mirror connects the witness-server via this hostname: TCP://witness01.my.domain:5022
The second and third database on the same sql server connect the witness-server via an other hostname: TCP://witness02.another.domain:5022
If i try to change the witness-server from the first database-mirroring, my sql-server told me, he is not able to connect to the witness-server.
But the other 2 database-mirrors are able to connect the witness-server with the right name.
thanks
andreas
October 29, 2013 at 9:11 am
so you have three datbases on the primary that you are trying to mirror over the the secondary and only one of the three are getting mirrored?
MCSE SQL Server 2012\2014\2016
October 29, 2013 at 9:22 am
hi,
i have got 3 databases, wich are mirrored:
Database | Prinzipal | Mirror | Witness
db_01 | sql_01 | sql_02 | wit_01
db_02 | sql_01 | sql_02 | wit_01
db_03 | sql_01 | sql_02 | wit_02
and i am not able to change the witness-server from db_03 to wit_01, to the same witness-server like db_01 or db_02.
The 3 databases are on the same server, SQL 2008 R2, and the witness-server is a SQL 2008 Express. And the mirrors are working fine.
October 29, 2013 at 9:31 am
andreas.kreuzberg (10/29/2013)
hi,i have got 3 databases, wich are mirrored:
Database | Prinzipal | Mirror | Witness
db_01 | sql_01 | sql_02 | wit_01
db_02 | sql_01 | sql_02 | wit_01
db_03 | sql_01 | sql_02 | wit_02
and i am not able to change the witness-server from db_03 to wit_01, to the same witness-server like db_01 or db_02.
The 3 databases are on the same server, SQL 2008 R2, and the witness-server is a SQL 2008 Express. And the mirrors are working fine.
Well the first problem is you not suppose to mirror 08R2 (enterprise or Standard) to Express, there is issue 1. It should be ENT to ENT or STD to STD. not either to EXP.
What is the error message? Are you setting this up through GUI? Are all the users able to connect? Have you tried connecting with said users into SQL SSMS?
MCSE SQL Server 2012\2014\2016
October 29, 2013 at 9:39 am
I read, it is possible to use the Express or Webedition, but it musst be the same version of sql, we use 2008 R2 on the servers, and it works on about 5 database mirrors with automatic failovers. => http://technet.microsoft.com/en-us/library/ms175191.aspx#SwHwRecommendations
this is the error-log:
2013-10-29 12:09:24.54 spid161 Error: 1456, Severity: 16, State: 3.
2013-10-29 12:09:24.54 spid161 The ALTER DATABASE command could not be sent to the remote server instance 'TCP://wit_02.my.domain:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
I try to change the witnes-server via GUI and via t-sql script. Both didn't work.
October 29, 2013 at 9:41 am
you are using the same login for all mirrors? Have you tried logging into SQL using that user via SSMS?
MCSE SQL Server 2012\2014\2016
October 29, 2013 at 9:45 am
Hi,
i am using the same login for all 3 databases. I am able to login from server db_01 to the witnes-server via ssms. I used the same port for the mirroring endpoint like the 2 other db-mirroring.
so i don't understand why i am not able to change the witnes?
;-(
thansk for your help
October 29, 2013 at 9:50 am
Go down the checklist on this MS blog and see if everything is in order.
MCSE SQL Server 2012\2014\2016
October 30, 2013 at 2:33 am
Hi,
here are the results:
1. Make sure the witness is listening on the assigned port. Remember that if you telnet into the port, that only shows that something is listening on the port. It may not be your endpoint. Use netstat to verify.
OK, because 2 other database mirrors are running fine on this witness-server
2. Make sure that the endpoint is started and in the role of either witness or all.
OK, because 2 other database mirrors are running fine on this witness-server
3. Make sure that there are no other endpoints on any instances on the server trying to use the same port.
there is only 1 instance installed.
4. Make sure that remote conenctions are enabled and that the tcp protocol is enabled.
OK, because 2 other database mirrors are running fine on this witness-server
5. Make sure that the servers can ping each other and correctly resolve the FQDN's.
OK, ping with name, FQDN, etc, everything is fine
6. Check the hosts file on all servers for any entries with the other servers' names or FQDN.
OK, they are all empty
7. Make sure the port is not being blocked by the firewall.
OK, other database mirrors use the witness-server with success
8. Make sure all servers are using the same domain account for the SQL Server service account or explicit permissions have been granted to connect to the endpoint or certificate authentication has been set up.
OK, same account is used
9. If using certificate authentication make sure that the certificates are not expired.
no certificates used
10. Make sure all endpoints are using the same encryption protocol.
OK, because 2 other database mirrors are running fine
11. Check network latency between the instances.
OK, because 2 other database mirrors are running fine
Is there a difference between change a witness and/or add/remove a witness. Should i try first to remove the witness-server, and than to add the new witness-server?
October 30, 2013 at 7:02 am
I would try removing the database from the witness and re-implement that database again. Is there any errors in the log on either sql instance?
MCSE SQL Server 2012\2014\2016
October 30, 2013 at 7:09 am
Hi,
on the witness-server are no entries in the errorlog. on the principal of the database are these entries:
2013-10-29 12:09:24.54 spid161 Error: 1456, Severity: 16, State: 3.
2013-10-29 12:09:24.54 spid161 The ALTER DATABASE command could not be sent to the remote server instance 'TCP://wit_02.my.domain:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
What do you mean, remove the database from the witness-server. Remove the witness from the existing mirroring?
October 30, 2013 at 7:56 am
the problem is solved
i removed the witness from the mirror, and after a few seconds i add the new witness with the correct FQDN to the mirror.
i don't know if there are differences between changing the witness ore remove and add a new witness
thanks for your help.
:w00t:
October 31, 2013 at 7:59 am
Compare all the database settings and logins for working database and the one not working.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply