THE VIDEO

THE SYNOPSIS

In this video, we will build out two availability groups for SQL Server on Linux.

THE COMMANDS

Following are the command line operations I used throughout the video.

Initial preparation

This block runs on each server.

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server
sudo apt install -y openssh-server
sudo mkdir /var/opt/mssql/certs/

Create Certificate

This is the version for SQLAG0. For each node in the eventual availability group, you’ll want to build out a variant of this.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPassword!>';
GO

CREATE CERTIFICATE SQLAG0_Cert
    WITH SUBJECT = 'Availability Group certificate for SQLAG0';
GO

BACKUP CERTIFICATE SQLAG0_Cert TO FILE = '/var/opt/mssql/data/SQLAG0_Cert.cer';
GO

CREATE ENDPOINT AvailabilityGroupEndpoint STATE = STARTED AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE SQLAG0_Cert, ROLE = ALL);
GO

Copy Files

These are the commands I ran on SQLAG0.

sudo su -

cd /var/opt/mssql/data/
ls -la

ssh testo@SQLAG1
# Connected to SQLAG1
sudo cp /var/opt/mssql/data/SQLAG1_Cert.cer ~/
sudo chown testo:testo SQLAG1_Cert.cer
exit

# Back in SQLAG0
scp testo@SQLAG1:/home/testo/SQLAG1_Cert.cer SQLAG1_Cert.cer

ssh testo@SQLAG2
# Connected to SQLAG2
sudo cp /var/opt/mssql/data/SQLAG2_Cert.cer ~/
sudo chown testo:testo SQLAG2_Cert.cer
exit

# Back in SQLAG0
scp testo@SQLAG2:/home/testo/SQLAG2_Cert.cer SQLAG2_Cert.cer
chown mssql:mssql SQLAG1_Cert.cer SQLAG2_Cert.cer
ls -la

cp SQLAG0_Cert.cer /home/testo
chown testo:testo /home/testo/SQLAG0_Cert.cer

These are the commands I ran on SQLAG1.

sudo su -

cd /var/opt/mssql/data/

scp testo@SQLAG0:/home/testo/SQLAG0_Cert.cer SQLAG0_Cert.cer

scp testo@SQLAG2:/home/testo/SQLAG2_Cert.cer SQLAG2_Cert.cer

chown mssql:mssql SQLAG0_Cert.cer SQLAG2_Cert.cer

exit

And here it is for SQLAG2.

sudo su -

cd /var/opt/mssql/data/

scp testo@SQLAG0:/home/testo/SQLAG0_Cert.cer SQLAG0_Cert.cer

scp testo@SQLAG1:/home/testo/SQLAG1_Cert.cer SQLAG1_Cert.cer

chown mssql:mssql SQLAG0_Cert.cer SQLAG1_Cert.cer

exit

Restore Certificates

This T-SQL script runs on SQLAG0. You’d also need to run similar scripts for SQLAG1 and SQLAG2.

CREATE LOGIN SQLAG1_Login WITH PASSWORD = '<StrongPassword>';
CREATE USER SQLAG1_User FOR LOGIN SQLAG1_Login;
GO
CREATE LOGIN SQLAG2_Login WITH PASSWORD = '<StrongPassword>';
CREATE USER SQLAG2_User FOR LOGIN SQLAG2_Login;
GO

CREATE CERTIFICATE SQLAG1_Cert AUTHORIZATION SQLAG1_User
FROM FILE = '/var/opt/mssql/data/SQLAG1_Cert.cer';
GO
CREATE CERTIFICATE SQLAG2_Cert AUTHORIZATION SQLAG2_User
FROM FILE = '/var/opt/mssql/data/SQLAG2_Cert.cer';
GO

GRANT CONNECT ON ENDPOINT::AvailabilityGroupEndpoint TO SQLAG1_Login;
GO
GRANT CONNECT ON ENDPOINT::AvailabilityGroupEndpoint TO SQLAG2_Login;
GO

Create the Availability Group

This is the availability group we have for the BusData database. The create statement runs on SQLAG0.

CREATE AVAILABILITY GROUP [BusDataAG] WITH (CLUSTER_TYPE = NONE)
FOR DATABASE BusData REPLICA ON
    N'SQLAG0' WITH (
        ENDPOINT_URL = N'TCP://SQLAG0:5022',
        FAILOVER_MODE = MANUAL,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = (
            (
                'SQLAG1',
                'SQLAG2'
                )
            )),
        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = N'TCP://SQLAG0:1433')
    ),
    N'SQLAG1' WITH (
        ENDPOINT_URL = N'TCP://SQLAG1:5022',
        FAILOVER_MODE = MANUAL,
        SEEDING_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = (
            (
                'SQLAG0',
                'SQLAG2'
                )
            )),
        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = N'TCP://SQLAG1:1433')
    ),
    N'SQLAG2' WITH (
        ENDPOINT_URL = N'TCP://SQLAG2:5022',
        FAILOVER_MODE = MANUAL,
        SEEDING_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = (
            (
                'SQLAG0',
                'SQLAG1'
                )
            )),
        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = N'TCP://SQLAG2:1433')
    )
    LISTENER 'AGListener' (
        WITH IP (('172.19.189.185', '0.0.0.0')), Port = 1433
    );
GO

Then we need to run these two commands on SQLAG1 and SQLAG2.

ALTER AVAILABILITY GROUP [BusDataAG] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER DATABASE BusData SET HADR AVAILABILITY GROUP = BusDataAG;
GO

Create the (Other) Availability Group

This is the command to create the availability group on AnalyzingBizData. This command runs on SQLAG0.

CREATE AVAILABILITY GROUP [AnalyzingBizDataAG] WITH (CLUSTER_TYPE = EXTERNAL)
FOR DATABASE AnalyzingBizData REPLICA ON
    N'SQLAG0' WITH (
        ENDPOINT_URL = N'TCP://SQLAG0:5022',
        FAILOVER_MODE = EXTERNAL,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = (
            (
                'SQLAG1'
                )
            )),
        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = N'TCP://SQLAG0:1433')
    ),
    N'SQLAG1' WITH (
        ENDPOINT_URL = N'TCP://SQLAG1:5022',
        FAILOVER_MODE = EXTERNAL,
        SEEDING_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = (
            (
                'SQLAG0'
                )
            )),
        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = N'TCP://SQLAG1:1433')
    ),
    N'SQLAG2' WITH (
        ENDPOINT_URL = N'TCP://SQLAG2:5022',
        FAILOVER_MODE = EXTERNAL,
        SEEDING_MODE = AUTOMATIC,
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
    )
    LISTENER 'AGListener' (
        WITH IP (('172.19.189.185', '0.0.0.0')), Port = 1433
    );
GO

And we have commands to run on SQLAG1 and SQLAG2:

ALTER AVAILABILITY GROUP [AnalyzingBizDataAG] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
ALTER DATABASE AnalyzingBizData SET HADR AVAILABILITY GROUP = AnalyzingBizDataAG;

LINKS AND ADDITIONAL INFORMATION

All of these videos are on behalf of Catallaxy Services, LLC, my consulting company where I help customers on problems all across the data platform space.

4 thoughts on “Video: Create an Availability Group in SQL Server on Linux”

  1. Pingback: Creating an Availability Group in SQL Server on Linux – Curated SQL

  2. thanks Kevin – that was very useful. I followed along in my own lab and all went ok and the AG appear working and healthy. However, I was not able to connect using the Listener – how can we add it to DNS or anything similar to provide name resolution – similar to what is being automatically done on WSFC-based AG.

    Thanks!

    Reply

    1. Yeah, that’s the downside to the “no cluster type” approach I used in the video: the listener isn’t really effective.

      The best approach is to use Pacemaker, something I did not show in the video. Pacemaker allows you to control assets similar to how WSFC has resources like DNS entries. This gives you a listener experience and, depending on how you set things up and if you’re using Red Hat Enterprise Linux, could allow for nearly automated failover. If you’re using Ubuntu with Pacemaker, you can still perform manual failover and listener resource updates.

      Otherwise, you can connect directly to the primary or secondary nodes. I understand that the whole point of the listener is to make it so you don’t need to care about which node is primary and which is secondary, but we’re only able to pull this off on Linux with Pacemaker, as far as I’m aware.

      Reply

      1. Thanks for getting back to me. For now, I ended up creating a DNS alias for the AG listener name and pointing to the Primary replica IP address. It works good on both replicas also supporting the readonly routing. So, for the failover, I guess I will just have to do the same for the “then” primary replica IP. Since it is manual failover used mainly for DR and Read-Scaleout - I guess this should be an ok setup for now.

        Thanks & Regards

        Reply

Leave a comment