SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBMirroring SQL2017 - SQL2017 not starting !?!


DBMirroring SQL2017 - SQL2017 not starting !?!

Author
Message
ALZDBA
ALZDBA
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115816 Visits: 9307
Testing SQL2017 I've come to DBMirroring
I know AG is the "new" way of handling that desire, but DBMirroring is not yet supposed to be out of service.

Topology:
2 * SQL2017 CU3 DevEdtn named instances on Win2016 X64 StdEdtn ( 1 physical box + 1 HyperV )
( No firewalls inbetween / no firewalls active on the OS )
Endpoint port numbers tested 5022, 50220 and 50221 ( and the mix )
No Witness server involved !

Endpoints created and granted to SQLServer service account ( eventually added in sysadmin role on each instance )

Db active on Inst_1
Db restored ( NoRecovery ) on Inst_2


:Connect DBAServer1\Inst1
SELECT @@SERVERNAME AS ServerName
, service_account
, servicename
, is_clustered
, cluster_nodename
FROM sys.dm_server_services WITH (NOLOCK) OPTION(RECOMPILE);

use master

IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'DbMirroring')
DROP ENDPOINT [DbMirroring]
GO

EXEC AS LOGIN = 'sa';
go

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DBADomain\SvcAcntDEV')
CREATE LOGIN [DBADomain\SvcAcntDEV] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
--Just voor DBMirroring Test
--ALTER SERVER ROLE [sysadmin] ADD MEMBER [DBADomain\SvcAcntDEV]
GO

CREATE ENDPOINT [DbMirroring]
AUTHORIZATION [DBADomain\SvcAcntDEV]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS KERBEROS
, ENCRYPTION = REQUIRED ALGORITHM AES);

GRANT CONNECT on ENDPOINT:BigGrinbMirroring TO [DBADomain\SvcAcntDEV];
GO

REVERT;
GO
SELECT @@Servername AS ServerName, *
FROM sys.tcp_endpoints
order by name;

Select @@Servername AS ServerName, *
from sys.database_mirroring_endpoints
order by name;
GO

/* at the Mirrored server */
:Connect DBAServer2\Inst2

SELECT @@SERVERNAME AS ServerName
, service_account
, servicename
, is_clustered
, cluster_nodename
FROM sys.dm_server_services WITH (NOLOCK) OPTION(RECOMPILE);


use master

IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'DbMirroring')
DROP ENDPOINT [DbMirroring]
GO

EXEC AS LOGIN = 'sa';
go

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DBADomain\SvcAcntDEV')
CREATE LOGIN [DBADomain\SvcAcntDEV] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
--Just voor DBMirroring Test
--ALTER SERVER ROLE [sysadmin] ADD MEMBER [DBADomain\SvcAcntDEV]
GO

CREATE ENDPOINT [DbMirroring]
AUTHORIZATION [DBADomain\SvcAcntDEV]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS KERBEROS
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
GRANT CONNECT on ENDPOINT:BigGrinbMirroring TO [DBADomain\SvcAcntDEV];
GO
REVERT;
GO

SELECT @@Servername AS ServerName, *
FROM sys.tcp_endpoints
order by name;

Select @@Servername AS ServerName, *
from sys.database_mirroring_endpoints
order by name;
GO



Activate DBMirroring


/* Activate DBMirroring */
:Connect DBAServer2\Inst2
ALTER DATABASE [DDBAStatistics] SET PARTNER = 'TCP://DBAServer1.DBADomain.com:5022'
GO

Executes without a problem !


:Connect DBAServer1\Inst1
ALTER DATABASE [DDBAStatistics] SET PARTNER = 'TCP://DBAServer2.DBADomain.com:5022'
GO



Fails

Msg 1418, Level 16, State 1, Line 3
The server network address "TCP://DBAServer2.DBADomain.com: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.




I just don't get it ...

What is wrong ?

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Sue_H
Sue_H
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69577 Visits: 14483


This article has a pretty good checklist to run through for that error -
The server network address “TCP://SQLServer:5023” can not be reached or does not exist.

Sue



ALZDBA
ALZDBA
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115816 Visits: 9307
Sue_H - Monday, February 19, 2018 2:38 PM


This article has a pretty good checklist to run through for that error -
The server network address “TCP://SQLServer:5023” can not be reached or does not exist.

Sue

Already checked those items, but very reluctant to 'GRANT CONNECT ON ENDPOINT::Mirroring TO ALL'

Tested the grant to all ... gives a syntax error ( there is no ALL option with grant connect to endpoint ).


Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ALZDBA
ALZDBA
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115816 Visits: 9307
I finally got this to work lowering AUTHENTICATION settings

 ..;   FOR DATA_MIRRORING (
ROLE = ALL,
AUTHENTICATION = WINDOWS NEGOTIATE ,
ENCRYPTION = REQUIRED ALGORITHM AES)


in stead of "AUTHENTICATION = WINDOWS KERBEROS "

I checked SPNs, they were OK

Now I'll have to hunt down the used servce accounts and figure out why it isn't able to use Kerberos.


Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ALZDBA
ALZDBA
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115816 Visits: 9307
SOLUTION:

For DBMirroring to be able to use KERBEROS,
you need to register an SPN for the service account to the Endpoint port number !

( my SPN registrations were only set for the SQLInstances port number )

$DBMPortNumber = '5022'
New-SPN -FQServerName $('{0}.{1}' -f $TargetComputerName, $ADDomain.DNSRoot ) -PortNumber $DBMPortNumber -ServiceAccount $SQLInfo.ServiceAccount.ToString()


Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Jason Sheets
Jason Sheets
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 2
I've run into this several times, it almost always was the windows firewall (unless you know for sure it is turned off) or a physical firewall that was preventing the traffic from flowing. From the server that the command is failing on use PuTTY and try to connect to the destination server / port in RAW, if the connection succeeds you aren't running into any firewall problems, if the connection is blocked you have network or firewall problems.
Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 264
ALZDBA - Tuesday, February 20, 2018 2:37 AM
SOLUTION:

For DBMirroring to be able to use KERBEROS,
you need to register an SPN for the service account to the Endpoint port number !

( my SPN registrations were only set for the SQLInstances port number )

$DBMPortNumber = '5022'
New-SPN -FQServerName $('{0}.{1}' -f $TargetComputerName, $ADDomain.DNSRoot ) -PortNumber $DBMPortNumber -ServiceAccount $SQLInfo.ServiceAccount.ToString()

Good to know. Thanks for sharing.


GASQL.com - Focus on Database and Cloud
ALZDBA
ALZDBA
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115816 Visits: 9307
Jason Sheets - Tuesday, February 20, 2018 12:01 PM
I've run into this several times, it almost always was the windows firewall (unless you know for sure it is turned off) or a physical firewall that was preventing the traffic from flowing. From the server that the command is failing on use PuTTY and try to connect to the destination server / port in RAW, if the connection succeeds you aren't running into any firewall problems, if the connection is blocked you have network or firewall problems.

In this case you could connect using telnet, but as soon as you pressed enter on the prompt, it said "connection lost".


Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search