What is Always On in SQL Server – Always On is High Availability(HA) and Disaster Recovery(DR) solution that maximizes database availability for Business to run as usual with minimum impact. Please go though this link to get more details on Always On Availability Group.

Following are the step by step process to configure Always On Availability group (Basic Availability Group(BAG) Setup) in SQL Server 2019 Standard Edition on Linux environment. Please visit this link to get more details on BAG.

Step#1: Host File Update

First step is to update the host file(available under /etc/hosts) with primary/Secondary host name along with its corresponding IP Address. In our case, below are primary/secondary host/IP details.

so, after entries are made to /etc/hosts file, it will look like this. Update the same on all the participating nodes.

Step#2: Enable AG on all the participating nodes

To enable Always on Availability group on SQL on Linux, run the following query using mssql-conf file followed by SQL Server restart.

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

Repeat the same steps for Secondary replica as well.

Step#3: Create Master key and Certificate on Primary Replica

SQL Server uses certificates to authenticate communication between mirroring endpoints. so, we will create master key followed by certificates and other steps as mentioned below.

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='xxxxxx'
go
CREATE CERTIFICATE ALWYSON_certificate WITH SUBJECT = 'ALWYSON_subject'
go
BACKUP CERTIFICATE ALWYSON_certificate
TO FILE = '/var/opt/mssql/SQL/ALWYSON_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/SQL/ALWYSON_certificate.pvk',
ENCRYPTION BY PASSWORD = 'xxxxxx' — keep same password as above
)
go

once certificates are created, we can run below command to verify it.

SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date FROM sys.certificates;

Now, copy both the files, certificate file (ALWYSON_certificate.cer) and private key file (ALWYSON_certificate.pvk) at same location on Secondary Replica.

Step#4: Create Master key and Certificate on Secondary Replica

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='xxxxxx'
go
CREATE CERTIFICATE ALWYSON_certificate
FROM FILE = '/var/opt/mssql/SQL/ALWYSON_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/SQL/ALWYSON_certificate.pvk',
DECRYPTION BY PASSWORD = 'xxxxxx' — use primary replica password
)
go

Step#5: Create Mirroring Endpoint for Primary/Secondary Replica:

To participate in Always On availability groups or database mirroring, a server instance requires its own, dedicated database mirroring endpoint. Database mirroring endpoints uses TCP to send and receive messages between the server instances participating in database mirroring sessions or hosting availability replicas. The database mirroring endpoint listens on a unique TCP port number.

Run below command on Primary as well as on Secondary Replica.

CREATE ENDPOINT [EP_SQLAG]
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (
ROLE = ALL,
AUTHENTICATION = CERTIFICATE ALWYSON_certificate,
ENCRYPTION = REQUIRED ALGORITHM AES
)
go
ALTER ENDPOINT [EP_SQLAG] STATE = STARTED;

Step#6: Create Availability Group on Primary Replica:

CREATE AVAILABILITY GROUP [test_AG1]
WITH (BASIC , CLUSTER_TYPE = NONE)
FOR REPLICA ON
N'Node1' — Put Primary replica hostname
WITH (
ENDPOINT_URL = N'tcp://Node1:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
N'Node2' — Put Secondary replica hostname
WITH (
ENDPOINT_URL = N'tcp://Node2:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
ALTER AVAILABILITY GROUP [test_AG1] GRANT CREATE ANY DATABASE;

Step#7: Join Secondary Replica to Always On Availability Group:

ALTER AVAILABILITY GROUP [test_AG1] JOIN WITH(CLUSTER_TYPE=NONE)
go
ALTER AVAILABILITY GROUP [test_AG1] GRANT CREATE ANY DATABASE
go

Step#8: Adding Database to Always On Availability Group:

ALTER DATABASE [test] SET RECOVERY FULL WITH NO_WAIT
go
BACKUP DATABASE [test] to disk='D:/SQL/test_AGBACKUP.bak'
go
ALTER AVAILABILITY GROUP [test_AG1] ADD DATABASE [test]
go

Note: Once above steps are completed, you would see secondary replica in RESTORING state for bigger databases, so its seeding status can be monitored using below query.

Step#9: Monitoring automatic seeding status(can be used for large databases) :

Below query can be used to monitor currently running automatic seeding process on DR server and this query is referred from Microsoft site. You can keep monitoring value of transferred_size_bytes and database_size_bytes to get an idea as in how long will it take for DR database to get in sync.

SELECT local_database_name,
role_desc,
internal_state_desc,
transfer_rate_bytes_per_second,
transferred_size_bytes,
database_size_bytes,
start_time_utc,
end_time_utc, estimate_time_complete_utc,
total_disk_io_wait_time_ms,
total_network_wait_time_ms,
is_compression_enabled
FROM sys.dm_hadr_physical_seeding_stats

Once everything goes well, we should see both databases in Synchronized state.

Primary SQL Server:

Secondary SQL Server:

Note: Since we are configuring AG using automatic seeding, no need to create database on DR side as automatic seeding process will take care of database creation during this process. If database already exists on DR side and we are setting up AG using automatic seeding , then AG setup might get completed and doesn't throw any error, but you may not see DR database in Synchronize mode, so make a note of the same.

Also note that this is BAG setup under standard edition and you cannot enable the secondary replica as a readable secondary. If you want to enable the secondary replica for reporting workloads, then you need Enterprise Edition.

I hope this post would have helped you in understanding basics of AG and setting up the same on Linux environment. If you have any suggestions please put your comments below. Thank you.