SQL Server - I got error 1418 while configuring mirroring on sql server 2008

Asked By prathap chowdary on 28-Jan-12 02:29 AM
Hi,

while i am configuring Mirroring i got error : i.e The server Network Address "TCP://MUMCHSQL11:5022" can not be reached or does not exist. Check Network adress name and that the ports for the local and remote end points are operational(Microsoft sql server error:1418)------This error occured when i am clicking the start mirroring......

Please find my server details:
. I am using same domain account.
.Server Versions are same 10.0.2531 and standard edition
.server names are MUMCHSQL10 and MUMCHSQL11
.And servers are running in VMWARE i.e virtual servers.

Please help out me.....how to resolve this error.....and please provide me step by step..

Thanks advance
Suchit shah replied to prathap chowdary on 28-Jan-12 02:37 AM

Verify the network address name and reissue the command. removed the EndPoint in both server and started the process from the begging TCP and name pipes must be available in both servers.

Additional Corrective Actions
  • Make sure that the mirror database is ready for mirroring.

  • Make sure that the name and port of the mirror server instance are correct.

  • Make sure that the destination mirror server instance is not behind a firewall.

  • Make sure that the principal server instance is not behind a firewall.

  • Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. If either endpoint is not started, execute an ALTER ENDPOINT statement to start it.

Suchit shah replied to prathap chowdary on 28-Jan-12 02:39 AM
This topic provides information to help you troubleshoot problems in setting up a database mirroring session.

http://msdn.microsoft.com/en-us/library/ms189127.aspx
prathap chowdary replied to Suchit shah on 28-Jan-12 02:41 AM
thanks for quick replying.......

TCP and Named piped are all enable and RPC also enabled in Network configuration of SQL server. And please give me the suggestions how to remove the end points and how to check the network address.......and please give me as a basic level......

Please..........
prathap chowdary replied to Suchit shah on 28-Jan-12 02:51 AM
i had already checked in both this query's

select * from sys.database_mirroring_endpoints


select * from sys.tcp_endpoints


in both servers i.e principle and mirror server...both server showing endpoints are started

but Principle ID are different in both servers
MUMCHSQL10:

name                                                               endpoint_id principal_id protocol protocol_desc                          type type_desc                            state state_desc                           is_admin_endpoint role role_desc                            is_encryption_enabled connection_auth connection_auth_desc                     certificate_id encryption_algorithm encryption_algorithm_desc
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ -------- ------------------------------------------------------------ ---- ------------------------------------------------------------ ----- ------------------------------------------------------------ ----------------- ---- ------------------------------------------------------------ --------------------- --------------- ------------------------------------------------------------ -------------- -------------------- ------------------------------------------------------------
Mirroring                                                              65536     332      2      TCP                              4    DATABASE_MIRRORING                       0   STARTED                            0         1    PARTNER                            1           3         NEGOTIATE                            0        1            RC4

(1 row(s) affected)

name                                                               endpoint_id principal_id protocol protocol_desc                          type type_desc                            state state_desc                           is_admin_endpoint port      is_dynamic_port ip_address
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ -------- ------------------------------------------------------------ ---- ------------------------------------------------------------ ----- ------------------------------------------------------------ ----------------- ----------- --------------- ---------------------------------------------
Dedicated Admin Connection                                                     1       1        2      TCP                              2    TSQL                             0   STARTED                            1         0       1         NULL
TSQL Default TCP                                                         4       1        2      TCP                              2    TSQL                             0   STARTED                            0         0       1         NULL
Mirroring                                                              65536     332      2      TCP                              4    DATABASE_MIRRORING                       0   STARTED                            0         5022      0         NULL



MUMCHSQL11:

name                                                               endpoint_id principal_id protocol protocol_desc                          type type_desc                            state state_desc                           is_admin_endpoint role role_desc                            is_encryption_enabled connection_auth connection_auth_desc                     certificate_id encryption_algorithm encryption_algorithm_desc
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ -------- ------------------------------------------------------------ ---- ------------------------------------------------------------ ----- ------------------------------------------------------------ ----------------- ---- ------------------------------------------------------------ --------------------- --------------- ------------------------------------------------------------ -------------- -------------------- ------------------------------------------------------------
Mirroring                                                              65536     263      2      TCP                              4    DATABASE_MIRRORING                       0   STARTED                            0         1    PARTNER                            1           3         NEGOTIATE                            0        1            RC4

(1 row(s) affected)

name                                                               endpoint_id principal_id protocol protocol_desc                          type type_desc                            state state_desc                           is_admin_endpoint port      is_dynamic_port ip_address
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ -------- ------------------------------------------------------------ ---- ------------------------------------------------------------ ----- ------------------------------------------------------------ ----------------- ----------- --------------- ---------------------------------------------
Dedicated Admin Connection                                                     1       1        2      TCP                              2    TSQL                             0   STARTED                            1         0       1         NULL
TSQL Default TCP                                                         4       1        2      TCP                              2    TSQL                             0   STARTED                            0         0       1         NULL
Mirroring                                                              65536     263      2      TCP                              4    DATABASE_MIRRORING                       0   STARTED                            0         5022      0         NULL

Sri K replied to prathap chowdary on 28-Jan-12 03:03 AM

Try "netstat -aon" from command prompt and see if someone else has grabbed this port?

check http://blogs.msdn.com/b/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

 http://blogs.technet.com/b/josebda/archive/2007/06/11/misleading-error-1418-message-in-sql-server-database-mirroring.aspx

kalpana aparnathi replied to prathap chowdary on 28-Jan-12 05:24 AM

hi,


Maybe the port was block, use telnet command to confirm.

 

Or the server does not have permission to connect the port.

Solution:

to fix it i removed the EndPoint in both server and started the process from the begging and everything works fine.  TCP and name pipes must be available in both servers.

For more detail:http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/dd4bc7cf-32da-44ec-9850-b1a70d7a4123


prathap chowdary replied to kalpana aparnathi on 30-Jan-12 02:00 AM
hi,

Thanks for your replying..........

still i am facing problem...............and Please tell me how to remove end points and port number 5022 from sys.tcp_endpoints.....and how to check whether the port number blocking or not......

Please suggest me........
kalpana aparnathi replied to prathap chowdary on 30-Jan-12 02:55 AM

hi,

Essentially you want port 5022 for default instance on Server A and Server B.

For named instance you want 5023 for Server A and Server B.

To test which ports are being used, run the following script on each of the instances:


SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
     t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
     e.connection_auth_desc
FROM   sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON   e.endpoint_id = t.endpoint_id;


Then drop the incorrect end points (where DBMirrorEndPoint is the name of the endpoint):

IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'DBMirroringEndPoint')

DROP ENDPOINT  DBMirroringEndPoint; 


And recreate them using the correct port number (changing the port number as required):


IF NOT EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'DBMirroringEndPoint')
CREATE ENDPOINT DBMirroringEndPoint
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5022 )
    FOR DATABASE_MIRRORING (
     AUTHENTICATION = WINDOWS NEGOTIATE,
     ENCRYPTION = REQUIRED,
     ROLE=ALL);

How to manually open Port 5022/tcp in Windows Firewall?


Windows Firewall may block port 5022/tcp by default. If you want to allow a program to communicate using Port 5022/tcp through the firewall, you can usually do that by selecting the required program on the Exceptions tab in Windows Firewall. However, sometimes the program is not listed in the Exceptions tab. In this case you need to open the port manually.

1.Open Windows Firewall by clicking Start, Control Panel, Security, and then Windows Firewall.

2.Click 'Allow a program through Windows Firewall'.

3.Click 'Add port'.

4.In the 'Name' field, type a friendly name for the port like 'Port-5022/tcp'.

5.In the 'Port number' field, enter the port number '5022/tcp'.

6.Select TCP or UDP, depending on the protocol required for port 5022/tcp.

Free


 

prathap chowdary replied to kalpana aparnathi on 30-Jan-12 11:50 PM
Hi,

Still i am facing same problem.......error 1418.....can not be reached.........

yesterday i had remove all existing end points and freshly configure with full and log backup........after configuration of mirroring when i am click start mirroring........i got error 1418 error.

when i am trying to give the "telnet server ip address 5022" in cmd prompt......it gives the blank screen....i dont know whether that port is working or not....

If i am adding port:5022.....is there any impacts to server or other process? and I have one more doubt..can i use 1433 port for mirroring also?
And in my servers firewall also turn off status..........
I dont know where is the problem? Please suggest me...its very urgent.....my boss put the pressure on me........
prathap chowdary replied to kalpana aparnathi on 01-Feb-12 03:29 AM
Thanks all........its resolve.........the error is 1474.....i have findout in event viewver application logs under windows logs.......there it showing.........forcebily port close by TCP host............

Then i had discuss with my server support people..........and they peolple enabled that port.......
Now its working.........