locked
SQL Server 2017 || Distributed Always-On || Geo replication RRS feed

  • Question

  • Hi All,

    I am trying to configure Distributed Always AG from Geo location 1 (site 1) to Geo location 2 (site 2). The distance between DC1 to DC2 is 68 Kilometers. Below is the my environment setup:

    WSFC_Cluster1 (site 1): DB_Node1, DB_Node2
    WSFC_Cluster2 (site 2): DB_Node3, DB_Node4

    The Windows servers are in Workgroup not in Domain. So i have created Windows local users (part of admin group) for SQL services on all 4 nodes.

    I have tried to configure DAG using certificates. Below steps are working for site 1. Please guide me what steps and commands I need to follow to configure DAG from site 1 to site 2 for Database: Office.

    PS: I have tried to follow below link to configure DAG from site 1 to site 2. But it's not working. This may becuase servers are not in domain. The steps mentioned in below link are working where servers are in domain (tested on local temp VM's)

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f40b6227-652b-4fdb-8688-75a94a6b3d3b/sql-2017-dag-configuration?forum=sqldisasterrecovery#f40b6227-652b-4fdb-8688-75a94a6b3d3b

    Steps for site 1 using certficates:

    /*Run below commands on Cluster1-DB1*/
    Use Master
    Go
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1R3@llyStr0ngP@ssw0rd!';
    Go
    CREATE CERTIFICATE [SMC-WG1-SQL01-Cert] WITH SUBJECT = 'SMC-WG1-SQL01-Cert';
    Go
    CREATE LOGIN [SMC-WG1-SQL02-Login]  WITH PASSWORD = 'D1ff3rentStr0ngP@ssw0rd!';
    Go
    CREATE USER [SMC-WG1-SQL02-User] FOR LOGIN [SMC-WG1-SQL02-Login];
    Go
    BACKUP CERTIFICATE [SMC-WG1-SQL01-Cert] To FILE = 'd:\db1\SMC-WG1-SQL01CertificatesSQL-SMC-WG1-SQL01.cer';
    Go
    CREATE CERTIFICATE [SMC-WG1-SQL02-Cert] AUTHORIZATION [SMC-WG1-SQL02-User] FROM FILE = 'D:\db2\SMC-WG1-SQL02CertificatesSQL-SMC-WG1-SQL02.cer';
    Go
    CREATE ENDPOINT WGAG_Endpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
    FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [SMC-WG1-SQL01-Cert] , ROLE = ALL);
    Go
    GRANT CONNECT ON ENDPOINT::WGAG_Endpoint TO [SMC-WG1-SQL02-Login];
    GO
    
    IF (SELECT state FROM sys.endpoints WHERE name = N'WGAG_Endpoint') <> 0
    BEGIN
    ALTER ENDPOINT [WGAG_Endpoint] STATE = STARTED
    END
    GO
    
    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
    BEGIN
    ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
    END
    IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
    BEGIN
    ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
    END
    GO
    
    CREATE AVAILABILITY GROUP [SMC_WG1_AG1]
    WITH (
     AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
     DB_FAILOVER = ON,
     DTC_SUPPORT = NONE
    )
    FOR DATABASE [Office]
    REPLICA ON N'DB1' WITH (
     ENDPOINT_URL = N'TCP://DB1.xxx.yyyy.ua:5022', 
     FAILOVER_MODE = AUTOMATIC, 
     AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
     BACKUP_PRIORITY = 50, 
     SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
    ),
     N'DB2' WITH (
     ENDPOINT_URL = N'TCP://DB2.xxx.yyyy.ua:5022', 
     FAILOVER_MODE = AUTOMATIC, 
     AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
     BACKUP_PRIORITY = 50, 
     SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
    );
    GO
    
    /*Run below commands on Cluster1-DB2*/
    ---
    Use Master
    Go
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1R3@llyStr0ngP@ssw0rd!';
    Go
    CREATE CERTIFICATE [SMC-WG1-SQL02-Cert] WITH SUBJECT = 'SMC-WG1-SQL02-Cert';
    Go
    CREATE LOGIN [SMC-WG1-SQL01-Login]  WITH PASSWORD = 'D1ff3rentStr0ngP@ssw0rd!';
    Go
    CREATE USER [SMC-WG1-SQL01-User] FOR LOGIN [SMC-WG1-SQL01-Login];
    Go
    BACKUP CERTIFICATE [SMC-WG1-SQL02-Cert] To FILE = 'd:\db2\SMC-WG1-SQL02CertificatesSQL-SMC-WG1-SQL02.cer';
    Go
    CREATE CERTIFICATE [SMC-WG1-SQL01-Cert] AUTHORIZATION [SMC-WG1-SQL01-User] FROM FILE = 'd:\db1\SMC-WG1-SQL01CertificatesSQL-SMC-WG1-SQL01.cer';
    Go
    
    CREATE ENDPOINT WGAG_Endpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
    FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [SMC-WG1-SQL02-Cert] , ROLE = ALL);
    Go
    GRANT CONNECT ON ENDPOINT::WGAG_Endpoint TO [SMC-WG1-SQL01-Login];
    go
    IF (SELECT state FROM sys.endpoints WHERE name = N'WGAG_Endpoint') <> 0
    BEGIN
     ALTER ENDPOINT [WGAG_Endpoint] STATE = STARTED
    END
    GO
    
    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
    BEGIN
      ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
    END
    IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
    BEGIN
      ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
    END
    GO
    ALTER AVAILABILITY GROUP [SMC_WG1_AG1] JOIN;
    GO
    ALTER DATABASE [Office] SET HADR AVAILABILITY GROUP = [SMC_WG1_AG1];
    GO

    Sunday, May 3, 2020 12:22 PM

Answers

  • Hello Sean Gallardy and Yuxi666,

    I have configured DAG on my local VM's with the help of below link.

    https://blog.sqlauthority.com/2018/04/18/sql-server-steps-to-deploy-distributed-availability-group-windows-clusters-in-different-domains/

    I have added TESTDB (without any data - Empty DB) in Primary (DC1-DB1) for testing purpose. on DC1-DB1 and DC2-DB1, the status is showing as Synchronized. But in DC1-DB2 and DC2-DB2, the status is showing as Synchronizing (from past 1 hour the status is same). What could be the possible reason?

    I have tried to open Distributed AG - Dashboard in Primary (DC1-DB1), getting below error. Please suggest. I am using SSMS 18.5

    Cluster1: DC1-DB1, DC1-DB2

    Cluster2: DC2-DB1, DC2-DB2

    VM Hardware and Software configuration for all DB servers:
    RAM: 2 GB
    Processors: 2
    SQL: SQL 2017 Ent edition
    OS: Windows Server 2016 Std edition

    SSMS error:

    ===================================

    Object reference not set to an instance of an object. (Microsoft.SqlServer.Management.HadrTasks)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Hadr.OperationsDashboardDataContext.<EnumReplicas>d__5.MoveNext()
       at Microsoft.SqlServer.Management.Hadr.CollectionHelpers.AddRange[T](ICollection`1 target, IEnumerable`1 items)
       at Microsoft.SqlServer.Management.Hadr.OperationsDashboardDataContext.Load(ServerConnection connection, String groupName, CancellationToken cancel, AlwaysOnPolicyStore policyStore, XEStore xeStore)
       at Microsoft.SqlServer.Management.Hadr.OperationsDashboardModel.LoadData(ServerConnection connection, AlwaysOnPolicyStore policyStore, XEStore xeStore)
       at Microsoft.SqlServer.Management.Hadr.OperationsDashboardModel.PerformUpdate()
       at Microsoft.SqlServer.Management.Hadr.OperationsDashboardModel.Connect()

    Tried to check Sync status using below query, percent_complete value is 0 same from past 1 hour.

    SELECT
     r.session_id, r.status, r.command, r.wait_type
     , r.percent_complete, r.estimated_completion_time
    FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s
     ON r.session_id = s.session_id
    WHERE r.session_id <> @@SPID
    AND s.is_user_process = 0
    AND r.command like 'VDI%'


    • Edited by Sridhar_G Monday, May 11, 2020 1:45 PM --
    • Marked as answer by Sridhar_G Friday, May 15, 2020 5:10 AM
    Monday, May 11, 2020 1:44 PM

All replies

  • Hello,

    You are having multiple issues here if you're trying to create a distributed AG:

    1. There are no listeners. Distributed AGs talk to each other via the listeners.
    2. DNS most likely isn't working with your FQDN usage due to being in a workgroup.

    If you're just trying to make this one availability group and not going for a distributed AG:

    1. DNS is still an issue, same as the above.

    You'll either want to edit the hosts file or make sure your DNS lookup for the workgroup suffix is correct and working.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Sunday, May 3, 2020 2:57 PM
    Answerer
  • Hi Sridhar_G,

    Do these nodes in the same WG?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 4, 2020 9:05 AM
  • Hello Sean Gallardy and Yuxi666,

    I have configured DAG on my local VM's with the help of below link.

    https://blog.sqlauthority.com/2018/04/18/sql-server-steps-to-deploy-distributed-availability-group-windows-clusters-in-different-domains/

    I have added TESTDB (without any data - Empty DB) in Primary (DC1-DB1) for testing purpose. on DC1-DB1 and DC2-DB1, the status is showing as Synchronized. But in DC1-DB2 and DC2-DB2, the status is showing as Synchronizing (from past 1 hour the status is same). What could be the possible reason?

    I have tried to open Distributed AG - Dashboard in Primary (DC1-DB1), getting below error. Please suggest. I am using SSMS 18.5

    Cluster1: DC1-DB1, DC1-DB2

    Cluster2: DC2-DB1, DC2-DB2

    VM Hardware and Software configuration for all DB servers:
    RAM: 2 GB
    Processors: 2
    SQL: SQL 2017 Ent edition
    OS: Windows Server 2016 Std edition

    SSMS error:

    ===================================

    Object reference not set to an instance of an object. (Microsoft.SqlServer.Management.HadrTasks)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Hadr.OperationsDashboardDataContext.<EnumReplicas>d__5.MoveNext()
       at Microsoft.SqlServer.Management.Hadr.CollectionHelpers.AddRange[T](ICollection`1 target, IEnumerable`1 items)
       at Microsoft.SqlServer.Management.Hadr.OperationsDashboardDataContext.Load(ServerConnection connection, String groupName, CancellationToken cancel, AlwaysOnPolicyStore policyStore, XEStore xeStore)
       at Microsoft.SqlServer.Management.Hadr.OperationsDashboardModel.LoadData(ServerConnection connection, AlwaysOnPolicyStore policyStore, XEStore xeStore)
       at Microsoft.SqlServer.Management.Hadr.OperationsDashboardModel.PerformUpdate()
       at Microsoft.SqlServer.Management.Hadr.OperationsDashboardModel.Connect()

    Tried to check Sync status using below query, percent_complete value is 0 same from past 1 hour.

    SELECT
     r.session_id, r.status, r.command, r.wait_type
     , r.percent_complete, r.estimated_completion_time
    FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s
     ON r.session_id = s.session_id
    WHERE r.session_id <> @@SPID
    AND s.is_user_process = 0
    AND r.command like 'VDI%'


    • Edited by Sridhar_G Monday, May 11, 2020 1:45 PM --
    • Marked as answer by Sridhar_G Friday, May 15, 2020 5:10 AM
    Monday, May 11, 2020 1:44 PM
  • I have resolved issue myself with the help of MS docs.
    Friday, May 15, 2020 5:10 AM