asked on

Trying to mirror database sql server 2005 - port blocked

I'm tring to setup a SQL 2005 database mirror with 3 separate servers acting as principal, mirror and witness. The setup goes fine until I click on the button to 'Start Mirroring' and then I get the below error messsage. Does anyone know how to go about resolving this? Thanks

=====================================================================
An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'WebData'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://sql1.TBMC.local:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

======================================================================

Crag

It does not appear to be able to contact the server with the addres provided sql.tbmc.local or the endpoint on it with port number 5022.
Have you checked that the server is available at that address and that it can be contacted from this system?
Use the ping command:

ping sql.tbmc.local

ASKER

yes have pinged the principal from the mirror and it replies fine

Crag

I've not seen the error before and as it's through the wizard it's not clear where the issue lies.
Lets try one piece at time. Can you run the following statement from the prinicpal server:

-- Specify the partner from the principal server
ALTER DATABASE [WebData] SET PARTNER = N'TCP://<server>:5022';

You need to replace <server> with the FQDN of the mirror server.
Please post the log

ASKER

Thanks

Tried the above and got the following error

Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://SQL2:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Crag

Can I just check that SQL2 as it relates in the command is the FQDN of the server?
Your original question had sql1.tbmc.local - should this command not use sql2.tbmc.local

Crag

Can you also run the following command on the SQL2 system and post the results

select *
from sys.tcp_endpoints
WHERE
(type= 4)

ASKER

results from sql2 after running the above code are

Mirroring 65536 264 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5022 0 NULL

Crag

OK so this establishes that the endpoint exists on the partner system. can you just check you get the same answer on the prinicpal system sql1?

Then can you run the following command on the principal system:

-- Specify the partner from the principal server
ALTER DATABASE [WebData] SET PARTNER = N'TCP://sql2.tbmc.local:5022';

I presume that sql2.tmbc.local is the full name of the prinicpal server - change if not.

Crag

Something else to try to verify the endpoint.
Open a command window and type

telnet sql2.tbmc.local 5022

This should either fail as the server/port cannot be access or if the port is available you'll get a blank window instead.
Keep pressing enter and it will return to the prompt.

ASKER

Yes i get the same on sql1 and sql when I execute the endpoints code above.

SQL1 is the principal, sorry to confuse.

When I run the following code I get the error messge below

ALTER DATABASE [WebData] SET PARTNER = N'TCP://sql1.tbmc.local:5022';

Msg 1452, Level 16, State 6, Line 1
The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.

Crag

if you run that version on sql1 the error is correct - you can't point it at itself.
On SQL1 use this one instead:

ALTER DATABASE [WebData] SET PARTNER = N'TCP://sql2.tbmc.local:5022';

ASKER

doh! sorry

Did the above and got

Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://sql2.tbmc.local:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

ASKER CERTIFIED SOLUTION

Crag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

sorry, not sure what you mean by the telnet command?

ASKER

Sorry I somhow missed your above commects regarding telnet.

I used telnet from sql1 to run the command telnet sql2.tbmc.local 5022

and got a blank screen which indecates as you mentioned that it is accessable

Crag

Mm that would indicate that the endpoint is working ok and can be connected to from sql1.
Not sure why the alter database does not work.

Do you have the TCP protocol enabled on both servers or are you using a different protocol?

ASKER

yes, enabled on both.

ASKER

Fixed the problem by making the the SQL service (in services) run under the domain administrator account on the principal, mirror and witness servers.

Thanks for everyones help