[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2023
  • Last Modified:

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

======================================================================
0
Ed
Asked:
Ed
  • 9
  • 9
1 Solution
 
CragCommented:
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
0
 
EdAuthor Commented:
yes have pinged the principal from the mirror and it replies fine
0
 
CragCommented:
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
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
EdAuthor Commented:

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.
0
 
CragCommented:
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
0
 
CragCommented:
Can you also run the following command on the SQL2 system and post the results

select *
from sys.tcp_endpoints
WHERE
      (type= 4)
0
 
EdAuthor Commented:
results from sql2 after running the above code are

Mirroring      65536      264      2      TCP      4      DATABASE_MIRRORING      0      STARTED      0      5022      0      NULL
0
 
CragCommented:
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.
0
 
CragCommented:
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.
0
 
EdAuthor Commented:
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.
0
 
CragCommented:
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';
0
 
EdAuthor Commented:
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.
0
 
CragCommented:
very odd. When you tried the telnet command above did you get an error or a blank screen?

checklist:
1. You can ping sql1 from sql2 using the FQDN so the network connection must be ok.
2. The endpoint definition on sql2 looks ok from the query results.
3. You can telnet to the endpoint and not get an error - the endpoint is working ok


0
 
EdAuthor Commented:

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

0
 
EdAuthor Commented:
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
0
 
CragCommented:
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?
0
 
EdAuthor Commented:
yes, enabled on both.
0
 
EdAuthor Commented:

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
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now