Setting up Always ON (SQL Server)

There is a great little docker setup that starts Always On (SQL Servers instance replication not to be confused with their article i.e. table replication). The docker setup creates a primary and a secondary. Find it here https://github.com/rafaelrodrigues3092/docker-mssql-alwayson

Just do

    git clone https://github.com/rafaelrodrigues3092/docker-mssql-alwayson
    cd docker-mssql-alwayson 
    docker compose up

and voila you’ll have a primary SQL Server with a Secondary where the “SALES” database is replicated.

To connect I use “sqlcli.sh” ( https://github.com/dbcli/mssql-cli/blob/main/doc/installation ) . It’s much nicer that sqlcmd but sqlcli has the drawback that it doesn’t handle @variables. For that I use Azure Studio

For this docker setup I use a script sqlcli.sh, and then run “sqlcli.sh 2500″ or “sqlcli.sh 2600″

CMD=//usr/local/bin/mssql-cli
PORT=",${1-1433}"
USER=SA
PW=Password123
HOST=localhost$PORT
 echo "$CMD -S $HOST -U $USER -P $PW --auto-vertical-output"
 eval "$CMD -S $HOST -U $USER -P $PW --auto-vertical-output"

I must say the SQL Server world seems bit opaque to me with all of the usage of GUI tools. GUI tool examples are mainly what shows up when I do searches on how to accomplish things, so I really appreciate examples that are SQL based. Here are a couple good references on setting up Always On with SQL

A couple of the things I wanted to modify in this docker configuration were:

Add another database to replicate
Add another secondary instance

Adding another database as surprisingly easy but it did have one trick which was to back up the database

   CREATE DATABASE KYLE;
   BACKUP DATABASE kyle TO DISK= '/tmp/kyle.back'
   ALTER AVAILABILITY GROUP AG1 ADD DATABASE kyle;

The surprising thing is that with the above commands the database was replicated. I didn’t need to recover the database on the secondary.

The next thing I did took me a lot longer. I wanted to add another secondary.

The first step was easy, I just duplicated the secondary called “db2″ in the docker.yml and called the next one db3 on a new port 2700 ( the primary d1 used 2500 and secondary db2 used 2600)

    db3:
        build: ./sql
        depends_on:
            - "db1"
        environment:
            INIT_SCRIPT: "aoag_third.sql"
            INIT_WAIT: 60
        ports:
            - "2700:1433"
        container_name: db3
        hostname: db3
        volumes:
            - mssql-server-shared:/var/opt/mssql/shared
            - mssql-server-backup:/var/opt/mssql/backup
        networks:
            - sqlaoag
        cap_add:
            - NET_ADMIN

That’s all it took to get a secondary up and running with “docker compose down”, then “docker compose up”. Problem is on the primary this new secondary db3 was reported as UNHEALTHY.

There were some changes that needed to be made to the SQL scripts that set up Always on.

One thing that threw me for a loop is that as I was making changes in docker container startup scripts, none of my changes were taking effect. Turns out I still had a image of one of my containers still floating around and it needed to be deleted before docker read my local files on startup again.

With this set of containers I can look for any remaining images with

% docker images | grep alwayson
docker-mssql-alwayson_db3             latest            f1c8834cd110   3 days ago      1.67GB
% docker image rmi docker-mssql-alwayson_db3

First the PRIMARY had to be changed in ./sql/aoag_primary.sql to have db3 registered. The script aoag_primary.sq is run when starting up ‘”db1″

     SET @cmd ='
     CREATE AVAILABILITY GROUP [AG1]
     WITH (
         CLUSTER_TYPE = NONE
     )
     FOR REPLICA ON
     N'''+@@SERVERNAME+''' WITH
     (
         ENDPOINT_URL = N''tcp://'+@@SERVERNAME+':'+@hadr_port+''',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         SEEDING_MODE = AUTOMATIC,
         FAILOVER_MODE = MANUAL,
         SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
     ),
     N''db2'' WITH
     (
         ENDPOINT_URL = N''tcp://db2:'+@hadr_port+''',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         SEEDING_MODE = AUTOMATIC,
         FAILOVER_MODE = MANUAL,
         SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
     ),
     N''db3'' WITH
     (
         ENDPOINT_URL = N''tcp://db3:'+@hadr_port+''',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         SEEDING_MODE = AUTOMATIC,
         FAILOVER_MODE = MANUAL,
         SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
     );
     ';
     exec sp_executesql @cmd

adding the part in red.

I’m not sure if the following is necessary or not. I’ll have to do some more testing but I change the always on user to have a different name on db2 than db3. I changed ./sql/aoag_secondary.sql as well as making a copy called ./sql/aoag_third.sql and change the user from aoag_login to aoag_login_db2 for secondary and aoag_login_db3 for third. For example the diffs from new and old aoag_secondary.sql are:

     < SET @cmd = 'CREATE LOGIN aoag_login_db2 WITH PASSWORD = '''+@hadr_login_password+''', DEFAULT_DATABASE=[master],      CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
      ---      
     > SET @cmd = 'CREATE LOGIN aoag_login WITH PASSWORD = '''+@hadr_login_password+''', DEFAULT_DATABASE=[master],      CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
     25c25
     < CREATE USER aoag_user FOR LOGIN aoag_login_db2;
      --- 
     > CREATE USER aoag_user FOR LOGIN aoag_login;
     62c62
     < GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [aoag_login_db2];
      --- 
     > GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [aoag_login];

I also had to change the yaml to use ./sql/aoag_third.sql from db3, see red in yaml lines above

Code to set up 2 secondary Always on manually without using docker running the sql setup scripts. I added drops at the beginning of the scripts because I was running these multiple times.

Setting up Always ON (SQL Server)

Recent Posts

Comments