Server configurations

Prepare SQL Server and Pacemaker cluster

  • Use AWS Systems Manager Session Manager to change SQL Server SA password, using this command:

    #Do these on both instances
    sudo /opt/mssql/bin/mssql-conf set-sa-password
    sudo systemctl start mssql-server
    sudo /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P YOUR_SA_PASSWORD -i /opt/mssql/move_tempdb.sql
    sudo passwd hacluster
    
    sudo /opt/mssql/configure_ha.sh
    
    #Do this on instance 1 only:
    sudo /opt/mssql/create_pcm_cluster.sh

Create certificates and endpoints

  • Use AWS Systems Manager Session Manager and SQL Server Linux client tool to create AG endpoints and certificates. You have to create a certificate on each node and copy it across to the other node

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPassword>';
    
    GO
    
    CREATE CERTIFICATE AGNLE1_Cert
    WITH SUBJECT = 'AGNLE1 AG Certificate';
    
    GO
    
    BACKUP CERTIFICATE AGNLE1_Cert
    TO FILE = '/var/opt/mssql/data/AGNLE1_Cert.cer';
    
    GO
    
    CREATE ENDPOINT AGEP
    STATE = STARTED
    AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE AGNLE1_Cert,
    ROLE = ALL);
    
    GO
  • Repeat above for node 2:

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPassword>';
    
    GO
    
    CREATE CERTIFICATE AGNLE2_Cert
    WITH SUBJECT = 'AGNLE2 AG Certificate';
    
    GO
    
    BACKUP CERTIFICATE AGNLE2_Cert
    TO FILE = '/var/opt/mssql/data/AGNLE2_Cert.cer';
    
    GO
    
    CREATE ENDPOINT AGEP
    STATE = STARTED
    AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE AGNLE2_Cert,
    ROLE = ALL);
    
    GO
  • Now copy certificates from each node to the other. First use your SSM Session Manager session on node 1:

    #Use S3 to Copy the certificate between nodes
    #Instance 1: 
    sudo aws s3 cp /var/opt/mssql/data/AGNLE1_Cert.cer s3://ss-experiments/AGNLE1_Cert.cer
  • Now use your SSM Session Manager session on node 2:

    #Use S3 to Copy the certificate between nodes
    #Instance 2: 
    sudo aws s3 cp /var/opt/mssql/data/AGNLE2_Cert.cer s3://ss-experiments/AGNLE2_Cert.cer
    sudo aws s3 cp s3://ss-experiments/AGNLE1_Cert.cer /var/opt/mssql/data/AGNLE1_Cert.cer
    sudo chown mssql:mssql /var/opt/mssql/data/AGNLE1_Cert.cer
    
    sudo /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P YOUR_SA_PASSWORD
    CREATE LOGIN AGNLE1_Login WITH PASSWORD = '<StrongPassword>';
    CREATE USER AGNLE1_User FOR LOGIN AGNLE1_Login;
    GO
    CREATE CERTIFICATE AGNLE1_Cert
    AUTHORIZATION AGNLE1_User
    FROM FILE = '/var/opt/mssql/data/AGNLE1_Cert.cer';
    GO
    GRANT CONNECT ON ENDPOINT::AGEP TO AGNLE1_Login;
    GO
  • Go back to SSM Session Manager session on node 1 and finish this step:

    #Use S3 to Copy the certificate between nodes
    #Instance 1: 
    sudo aws s3 cp s3://ss-experiments/AGNLE2_Cert.cer /var/opt/mssql/data/AGNLE2_Cert.cer
    sudo chown mssql:mssql /var/opt/mssql/data/AGNLE2_Cert.cer
    
    sudo /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P YOUR_SA_PASSWORD
    CREATE LOGIN AGNLE2_Login WITH PASSWORD = '<StrongPassword>';
    CREATE USER AGNLE2_User FOR LOGIN AGNLE2_Login;
    GO
    CREATE CERTIFICATE AGNLE2_Cert
    AUTHORIZATION AGNLE2_User
    FROM FILE = '/var/opt/mssql/data/AGNLE2_Cert.cer';
    GO
    GRANT CONNECT ON ENDPOINT::AGEP TO AGNLE2_Login;
    GO