locked
Distributed Always-On without Domain RRS feed

  • Question

  • Hi All,

    We have requirement to configure Distributed Always-On from Data center1 to Data Center2 using SQL Server 2017 Ent edition.

    DC1 (two nodes WSFC setup): SQLDB1, SQLDB2
    DC2 (two nodes WSFC setup): SQLDB3, SQLDB4

    The servers are in workgorup not in domain. Is it possible to configure Distributed SQL always-on from DC1 to DC2 without domain?

    As per my understaning, using certificates it's possible to configure Distributed SQL always-on if all four nodes are in same cluster. Is this my understanding is correct?

    Please guide me.
    Friday, April 24, 2020 10:36 AM

All replies

  • Please update.
    Sunday, April 26, 2020 10:07 AM
  • Hi PraveenKumar_1999,

    Sorry for the delay reply.

    >>Is it possible to configure Distributed SQL always-on from DC1 to DC2 without domain?

    Yes. Please refer to Distributed availability groups.

    >>using certificates it's possible to configure Distributed SQL always-on if all four nodes are in same cluster.

    When both WSFC clusters are joined to the same domain (not trusted domains), you don't need to do anything special when you create the distributed availability group. For availability groups and WSFC clusters that are not joined to the same domain, use certificates to make the distributed availability group work, much in the way that you might create an availability group for a domain-independent availability group. Please refer to Windows Server versions and distributed availability groups for more details.

    Best Regards,

    Amelia


    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, April 27, 2020 6:47 AM
  • Hi Amelia Gu: The below steps and commands working on Cluster1 - DB1 and DB2 (DC1). Please suggest what steps and command I need to run on Cluster2 - DB3 and DB4 (DC2) for DAG.

    /*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

    Monday, April 27, 2020 9:16 AM
  • Hi Amelia Gu,

    Could you please review steps of DC1 and suggest what steps need to run on DC2 for Distributed Always-on AG.

    Thanks in advance.

    Wednesday, April 29, 2020 6:06 AM
  • You've already asked this question and I've already given you things to work on. Please do not post multiple different threads for the same question.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6262277d-623d-4cdf-af27-4eb414fbae01/sql-server-2017-distributed-alwayson-geo-replication?forum=sqldisasterrecovery


    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.

    Wednesday, May 6, 2020 2:18 PM
    Answerer