Always-on AG configurations

Create a test DB and Availabilit Group

  • At this stage we can create a test DB to be included in the AG. Do this on node 1:

    USE MASTER
    GO
    CREATE DATABASE TestDB
    GO
    CREATE TABLE dbo.Employee (EmployeeID int PRIMARY KEY CLUSTERED);
    GO
    INSERT INTO dbo.Employee (EmployeeID) Values (1)
    INSERT INTO dbo.Employee (EmployeeID) Values (2)
    INSERT INTO dbo.Employee (EmployeeID) Values (3)
    GO
    BACKUP DATABASE TestDB TO DISK = '/var/opt/mssql/data/TestDB.bak' WITH FORMAT;
    GO
  • Now we can create an AG and add our test DB to it. Do this on node 1:

    USE MASTER
    CREATE AVAILABILITY GROUP TestAG
    WITH (BASIC, CLUSTER_TYPE = EXTERNAL)
    FOR DATABASE TestDB
    REPLICA ON N'ip-10-0-1-233' WITH (
    ENDPOINT_URL = N'TCP://10.0.1.233:5022',
    FAILOVER_MODE = EXTERNAL,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT),
    N'ip-10-0-3-82' WITH (
    ENDPOINT_URL = N'TCP://10.0.3.82:5022',
    FAILOVER_MODE = EXTERNAL,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
    GO
  • To join the second node to the AG and initiate seeding, run the following on the second node (like before, use AWS SSM Session Manager):

    ALTER AVAILABILITY GROUP TestAG JOIN WITH (CLUSTER_TYPE = EXTERNAL);
    
    GO
    
    ALTER AVAILABILITY GROUP TestAG GRANT CREATE ANY DATABASE;
    
    GO
  • SQL Server Always On Basic AG is configured.

Add SQL Server and AG to Pacemaker

  • Next we have to enable Pacemaker to access SQL Server and AG. Use SSM Session Manager to run following T-SQL statements on node 1:

    CREATE LOGIN PMLogin WITH PASSWORD='<StrongPassword>';
    GO
    GRANT VIEW SERVER STATE TO PMLogin;
    GO
    GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::<AGThatWasCreated> TO PMLogin;
    GO
  • Run these commands on both nodes:

    sudo echo "PMLogin" | sudo tee /var/opt/mssql/secrets/passwd
    sudo echo "<YourPassword>" | sudo tee /var/opt/mssql/secrets/passwd
    sudo chmod 400 /var/opt/mssql/secrets/passwd
  • Finally, we have to create the AG resource in Pacemaker. Run following on node 1:

    sudo pcs resource create TestAG ocf:mssql:ag ag_name=TestAG meta failover-timeout=30s master notify=true