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 @cmdadding 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.