Scribd Logo
Upload

Welcome to Scribd!

  • 53 views

    Configuring DAG in SQL Server

    Uploaded by

    Naga Raj
    The document describes setting up availability groups (AG) and a distributed availability group (DAG) across multiple SQL Server nodes. It creates endpoints, joins replicas, grants permissions, adds listeners, and configures the DAG with two initial AGs between different node pairs, with one node acting as the global primary replica for the DAG.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Configuring DAG in SQL Server

    Uploaded by

    Naga Raj
    53 views3 pages
    The document describes setting up availability groups (AG) and a distributed availability group (DAG) across multiple SQL Server nodes. It creates endpoints, joins replicas, grants permissions, adds listeners, and configures the DAG with two initial AGs between different node pairs, with one node acting as the global primary replica for the DAG.

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes setting up availability groups (AG) and a distributed availability group (DAG) across multiple SQL Server nodes. It creates endpoints, joins replicas, grants permissions, adds listeners, and configures the DAG with two initial AGs between different node pairs, with one node acting as the global primary replica for the DAG.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    53 views3 pages

    Configuring DAG in SQL Server

    Uploaded by

    Naga Raj
    The document describes setting up availability groups (AG) and a distributed availability group (DAG) across multiple SQL Server nodes. It creates endpoints, joins replicas, grants permissions, adds listeners, and configures the DAG with two initial AGs between different node pairs, with one node acting as the global primary replica for the DAG.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    You are on page 1of 3

    --Create Endpoints in all the Replicas and in all the Nodes With LISTENER_IP = ALL

    CREATE ENDPOINT [Hadr_endpoint]


    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
    , ENCRYPTION = REQUIRED ALGORITHM AES)
    GO

    --Grant Connect permission to SQL Server Service Account on the Endpoint.


    CREATE LOGIN [ABC\Administrator] FROM WINDOWS
    GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [ABC\Administrator]

    --configure AG Between Node1 & Node2,


    CREATE AVAILABILITY GROUP [AG1]
    FOR DATABASE Anbhu
    REPLICA ON N'primary1\prod001' WITH (ENDPOINT_URL =
    N'TCP://primary1.anbhu.com:5022',
    FAILOVER_MODE = AUTOMATIC,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    SEEDING_MODE = AUTOMATIC),
    N'secondary1\prod002' WITH (ENDPOINT_URL = N'TCP://secondary1.anbhu.com:5022',
    FAILOVER_MODE = AUTOMATIC,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    SEEDING_MODE = AUTOMATIC);
    GO

    --Join Node2 replica and also join databases after joining replica.
    ALTER AVAILABILITY GROUP [AG1] JOIN
    ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE

    --Create a Listener
    USE [master]
    GO
    ALTER AVAILABILITY GROUP [AG1]
    ADD LISTENER N'AGL1' (
    WITH IP
    ((N'10.0.0.6', N'255.255.255.128')
    )
    , PORT=1433);
    GO

    --Create another AG (AG2) Between Node3 and Node4, .


    CREATE AVAILABILITY GROUP [AG2]
    FOR
    REPLICA ON N'primary2\dr001' WITH (ENDPOINT_URL = N'TCP://primary2.anbhu.com:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    SEEDING_MODE = AUTOMATIC),
    N'secondary2\dr002' WITH (ENDPOINT_URL = N'TCP://secondary2.anbhu.com:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    SEEDING_MODE = AUTOMATIC);
    GO

    --Join Node4 replica.


    ALTER AVAILABILITY GROUP [AG2] JOIN
    ALTER AVAILABILITY GROUP [AG2] GRANT CREATE ANY DATABASE

    --Create listener

    USE [master]
    GO
    ALTER AVAILABILITY GROUP [AG2]
    ADD LISTENER N'AGL2' (
    WITH IP
    ((N'10.1.0.6', N'255.255.255.128')
    )
    , PORT=1433);
    GO

    --Go to Global Primary Replica and configure DAG.


    CREATE AVAILABILITY GROUP [distributedag]
    WITH (DISTRIBUTED)
    AVAILABILITY GROUP ON
    'AG1' WITH
    (
    LISTENER_URL = 'TCP://AGL1.anbhu.com:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
    ),
    'AG2' WITH
    (
    LISTENER_URL = 'TCP://AGL2.anbhu.com:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
    );
    GO

    --Go to Forwarder (Node3) and join DAG to the Global Primary Replica.
    ALTER AVAILABILITY GROUP [distributedag]
    JOIN
    AVAILABILITY GROUP ON
    'AG1' WITH
    (
    LISTENER_URL = 'TCP://AGL1.anbhu.com:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
    ),
    'AG2' WITH
    (
    LISTENER_URL = 'TCP://AGL2.anbhu.com:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
    );
    GO

    You might also like