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:
Has anyone been through this or could suggest a way forward?