February 19, 2018 at 7:02 am
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::DbMirroring 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::DbMirroring 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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 19, 2018 at 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
February 20, 2018 at 12:36 am
Sue_H - Monday, February 19, 2018 2:38 PMThis 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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2018 at 1:26 am
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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2018 at 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()
Johan
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2018 at 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.
February 20, 2018 at 4:07 pm
ALZDBA - Tuesday, February 20, 2018 2:37 AMSOLUTION: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
February 21, 2018 at 12:48 am
Jason Sheets - Tuesday, February 20, 2018 12:01 PMI'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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply