Database Mirror - Host can not be reached or does not exist

Question

I tried mirroring database with GUI and T-SQL, but I had problems in both cases.

Here is some information about infrastructure and SSMS:

INFRASTRUCTURE

• Principal server with SQL Server 2017 Enterprise Edition
• Mirror server with SQL Server 2017 Enterprise Edition
• Witness server with SQL Server 2017 Express Edition
• All servers S.O.: Windows Server 2016 Standard
• All servers with firewall disabled
• All servers in common domain
• All servers has same users and privileges
• Telnet works perfectly with port 5022 in all servers
• I created user to manage service in all servers with GRANT CONNECT ON ENDPOINT::Mirroring

SQL Server Management Studio Info - SSMS:

Microsoft SQL Server Management Studio                      14.0.17289.0
Microsoft Analysis Services Client Tools                    14.0.1016.283
Microsoft Data Access Components (MDAC)                     10.0.14393.0
Microsoft MSXML                                             3.0 6.0 
Microsoft Internet Explorer                                 9.11.14393.0
Microsoft .NET Framework                                    4.0.30319.42000
Operating System                                            6.3.14393

I'll explain both error cases, with GUI and T-SQL.

With GUI:

Success configuration endpoints in all servers

But I had this error when I tried start mirroring

It's so confusing because the database must be in recovery status in mirror server. But I read some posts about this and is recommended configure with T-SQL, so

WITH T-SQL:

With T-SQL I had this problem:

Msg 1418, Level 16, State 1, Line 54 The server network address "TCP://HML-SQLS-01-TST.domain.br: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.

Let me explain step-by-step I had followed:

PRINCIPAL SERVER

CREATE DATABASE mirror
GO

ALTER DATABASE mirror
SET RECOVERY FULL
GO

USE mirror
GO

CREATE TABLE TESTE(
    id INT IDENTITY(1,1),
    descricao VARCHAR(255)
);

BACKUP DATABASE mirror
TO DISK = 'F:\Backup\mirror-full.bak'
GO

BACKUP LOG mirror
TO DISK = 'F:\Backup\mirror-log.bak'
GO


CREATE ENDPOINT Mirroring
    STATE = started
    AS TCP (listener_port=5022)
    FOR database_mirroring (role=partner)
GO

USE MASTER
GO
CREATE LOGIN [DOMAIN\svc.witness] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\svc.witness];
GO

MIRROR SERVER

CREATE ENDPOINT Mirroring
    STATE = started
    AS TCP (listener_port=5022)
    FOR database_mirroring (role=partner)
GO

RESTORE DATABASE mirror
FROM DISK = 'F:\Backup\mirror-full.bak'
WITH NORECOVERY
GO

RESTORE DATABASE mirror
FROM DISK = 'F:\Backup\mirror-log.bak'
WITH NORECOVERY
GO

USE MASTER
GO
CREATE LOGIN [DOMAIN\svc.witness] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\svc.witness];
GO

WITNESS SERVER

create endpoint Mirroring
state = started
    as tcp (listener_port=5022)
    for database_mirroring (role=WITNESS)
go

USE MASTER
GO
CREATE LOGIN [DOMAIN\svc.witness] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\svc.witness];
GO

MIRROR SERVER

alter database mirror
set partner = 'TCP://HML-SQLS-01.domain.br:5022'
GO

PRINCIPAL SERVER (The error pops in this execution)

--The error pops in this execution:
alter database mirror
set partner = 'TCP://HML-SQLS-01-TST.domain.br:5022'
GO

Msg 1418, Level 16, State 1, Line 54 The server network address "TCP://HML-SQLS-01-TST.domain.br: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.

But if I try this command in CMD, its work perfectly:

telnet hml-sqls-01-tst.domain.br 5022

ENDPOINTS IN SERVERS:

Principal server endpoint

Mirror server endpoint

Witness server endpoint

Has anyone been through this or could suggest a way forward?

Answer 1

Related to question but not the answer: While you got SQL Server 2017 with Enterprise Edition why to consider a feature which is under maintenance and might removed in future.

However, in your case, i would suggest to verify following areas (if not done already), hope it must fix the issue:

• Make sure all SQL instance services running under same domain account
  • That service got permissions (as mentioned in OP) on all the SQL instances
• Verify end-point ports are different, if one server got multiple SQL instances
  • Allow end-point ports at firewall (incoming and outgoing), even firewall disabled
• Restart all SQL instance services if it's not done so since the last password change
  • Check SQL Error log to verify same port numbers are listening

Answer 2

I checked the LOG throug Management>SQL Server Logs>Current and its showed the service tried connect in endpoint with SQL Server Instance default user (In my case [DOMAIN\svc.ssms])

Database Mirroring login attempt failed with error: Connection handshake failed. The login 'DOMAIN\svc.ssms' does not have CONNECT permission on the endpoint. State 84.

So, I grant connect endpoint permission to this user and works fine.

Follow the commands used in all servers:

USE master
GO
CREATE LOGIN [DOMAIN\svc.ssms] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::Mirroring to [DOMAIN\svc.ssms];
GO 

I'm bothered not to use user [DOMAIN\witness] in connection attempt even granting the connect permission to the endpoint. Not a more elegant solution but solves my case. However, there may be best practices or methods. If you know, I ask you to comment below.

Grateful to all!

Answer 3

Not sure why, but try running the Alter database command on the Secondary, first, using 5022 as the port number. Then, run the Alter database command on the Primary, next, using 5023 as the port number. Let me know if you continue to get the same error.