July 21, 2011 at 1:34 am
Hi,
This issue is related to Database Mirroring + Witness, I have faced a issue taken me few days and unable to find any solution hope someone able to help me about this. Following is the full details and step I have test
Server Info
Server A [Primary] - SQL Server 2005 SP2 - 9.00.5000.00
Server B [Secondary] - SQL Server 2005 SP2 - 9.00.5057.00
Server C [Wtiness] - SQL Server 2005 SP2 - 9.00.3042.00
I have try setup the database mirroring for server above when on Primary try set the witness it fail (Please reference to following the action have done)
Action A:
ALTER DATABASE BCNCIMB
SET WITNESS = N'TCP://SERVERC:5022';
GO
OR
Action B:
Right click the primary database > Tasks > Mirror...
Both Action A & B also giving me error
Error:
Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://SERVERC:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
Error log from \Microsoft SQL Server\MSSQL.1\MSSQL\LOG
Database mirroring is active with database 'BCNCIMB' as the principal copy. This is an informational message only. No user action is required.
Error: 1456, Severity: 16, State: 3.
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://SERVERC:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
The following is the step I have try
1) Ping the server by IP Address
Server A - able to ping Server B & Server C
Server B - able to ping Server A & Server C
Server C - able to ping server A & Server B
2) Telnet the ip + port
Server A - able telnet to Server B & Server C
Server B - able telnet to ping Server A & Server C
Server C - able telnet to ping server A & Server B
3) SQL Server Management Studio GUI [try login]
Server A - able login to Server B & Server C
Server B - able login to ping Server A & Server C
Server C - able login to ping server A & Server B
All the step / action above I have try multiple time, worry the database did not backup and try backup + transaction log and the step for the script or step to link the 3 server (Primary, Secondary and Witness).
The database mirroring script I have try another set of server but with same witness server is working and same server above please reference below
This work (no problem also SQL 2005 SP2)
Server D (Primary)
Server E (Secondary)
Server C (Witness) (The witness server using above)
I have try this too and it work too (this server is exactly same as above)
Server B (Primary)
Server A (Secondary)
Server C (Witness)
If you see this will ask why not using this sever for database mirroring? My answer Server A is the server already Go-Live and primary database. Server B I just get it last 2 week I using it for testing.
The problem have now is very weird, ping and telnet is successful but when perform witness it fail. Please anyone read this topic hope you can give a hints to solve this
Thank you for reading the long long story and another thank you if you have solution for me......
July 21, 2011 at 3:36 am
after posted this question finally found a solution and also from other topic until I have try this way then it work
Go to Windows hosts file and added as following
Example:
IP_ADDRESS_FOR_SERVER SERVER_NAME
Either the primary, secondary and witness use IP but WINDOWS will find the server name and connect through.
Thank You
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply