SQL Server 2022 : Always On Availability Group2023/11/30 |
|
Configure Always On Availability Group for SQL Server on Linux.
This example is based on the environment like follows.
Secondaries are read-only replica Hosts on this settings.
-----------+-----------------------------+-----------------------------+------------
|10.0.0.51 |10.0.0.52 |10.0.0.53
+----------+-----------+ +----------+-----------+ +----------+-----------+
| [ node01.srv.world ] | | [ node02.srv.world ] | | [ node03.srv.world ] |
| (Primary) | | (Secondary) | | (Secondary) |
| SQL Server | | SQL Server | | SQL Server |
+----------------------+ +----------------------+ +----------------------+
|
| [1] | |
| [2] | On all Nodes, Enable Always On Availability Group feature and also Create a user for Database replication. Replace [password] section to any password you like. |
|
[root@node01 ~]# /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. [root@node01 ~]# systemctl restart mssql-server [root@node01 ~]# sqlcmd -S localhost -U SA Password: 1> alter event session AlwaysOn_health on server with (startup_state=on); 2> go 1> create login dbm_login with password = 'P@ssw0rd01'; 2> create user dbm_user for login dbm_login; 3> go 1> exit |
| [3] | On all Nodes, Create certificate and copy it to other all Nodes and also Create an Endpoint. Replace [password] section to any password you like. |
|
[root@node01 ~]# NODENAME=$(hostname -s)
[root@node01 ~]# cat > create-cert.sql <<EOF
create master key encryption by password = 'P@ssw0rd01';
create certificate ${NODENAME}_cert with subject = 'AG ${NODENAME} Certificate';
backup certificate ${NODENAME}_cert
to file = '/var/opt/mssql/data/${NODENAME}_cert.cer'
create endpoint AGEndpoint state = started
as tcp (listener_ip = all, listener_port = 5022)
for data_mirroring ( role = all, authentication = certificate ${NODENAME}_cert);
EOF
[root@node01 ~]#
[root@node01 ~]# sqlcmd -S localhost -U SA -i create-cert.sql
cd /var/opt/mssql/data [root@node01 data]# scp ${NODENAME}_cert.cer node02.srv.world:/var/opt/mssql/data/ dbm_certificate.cer 100% 667 614.7KB/s 00:00 dbm_certificate.pvk 100% 1212 1.0MB/s 00:00[root@node01 data]# scp ${NODENAME}_cert.cer node03.srv.world:/var/opt/mssql/data/ dbm_certificate.cer 100% 667 581.6KB/s 00:00 dbm_certificate.pvk 100% 1212 935.1KB/s 00:00
[root@node01 data]#
ssh node02.srv.world "chown mssql:mssql /var/opt/mssql/data/${NODENAME}_cert.cer" [root@node01 data]# ssh node03.srv.world "chown mssql:mssql /var/opt/mssql/data/${NODENAME}_cert.cer"
# if Firewalld is running, allow endpoint port [root@node01 data]# firewall-cmd --add-port=5022/tcp [root@node01 data]# firewall-cmd --runtime-to-permanent
|
| [4] | On all Nodes, Restore certificates that are copied from other Nodes. |
[root@node01 ~]# cat > restore-cert.sql <<EOF
create certificate node02_cert authorization dbm_user
from file = '/var/opt/mssql/data/node02_cert.cer'
create certificate node03_cert authorization dbm_user
from file = '/var/opt/mssql/data/node03_cert.cer'
grant connect on endpoint::AGEndpoint to dbm_login;
EOF
[root@node01 ~]# sqlcmd -S localhost -U SA -i restore-cert.sql |
| [5] | On primary Node, Configure Availability Group. |
[root@node01 ~]# cat > create-ag.sql <<'EOF'
create availability group [AG01]
with (cluster_type = none)
for replica on
N'node01' with (
endpoint_url = N'tcp://10.0.0.51:5022',
availability_mode = asynchronous_commit,
failover_mode = manual,
seeding_mode = automatic,
primary_role (read_only_routing_list=(('node02','node03'), 'node01')),
secondary_role (
allow_connections = read_only,
read_only_routing_url = N'tcp://10.0.0.51:1433')),
N'node02' with (
endpoint_url = N'tcp://10.0.0.52:5022',
availability_mode = asynchronous_commit,
failover_mode = manual,
seeding_mode = automatic,
primary_role (read_only_routing_list=(('node01','node03'), 'node02')),
secondary_role (
allow_connections = read_only,
read_only_routing_url = N'tcp://10.0.0.52:1433')),
N'node03' with (
endpoint_url = N'tcp://10.0.0.53:5022',
availability_mode = asynchronous_commit,
failover_mode = manual,
seeding_mode = automatic,
primary_role (read_only_routing_list=(('node01','node02'), 'node03')),
secondary_role (
allow_connections = read_only,
read_only_routing_url = N'tcp://10.0.0.53:1433'));
alter availability group [AG01] grant create any database;
EOF
[root@node01 ~]# sqlcmd -S localhost -U SA -i create-ag.sql
|
| [6] | On all secondary Nodes, Join in Availability Group. |
|
[root@node02 ~]# sqlcmd -S localhost -U SA Password: 1> alter availability group [AG01] join with (cluster_type = none); 2> alter availability group [AG01] grant create any database; 3> go 1> exit |
| [7] | On primary Node, Add listener and database to Availability Group. |
[root@node01 ~]# cat > create-db.sql <<'EOF'
alter availability group [AG01] add listener 'AG01_listener'
( with ip (
('10.0.0.51', '255.255.255.0') ), port = 1433);
create database [AG01_DB];
alter database [AG01_DB] set recovery full;
backup database [AG01_DB] to disk = N'/var/opt/mssql/data/AG01_DB.bak';
alter availability group [AG01] add database [AG01_DB];
EOF
[root@node01 ~]#
sqlcmd -S localhost -U SA -i create-db.sql Password: Processed 344 pages for database 'AG01_DB', file 'AG01_DB' on file 1. Processed 1 pages for database 'AG01_DB', file 'AG01_DB_log' on file 1. BACKUP DATABASE successfully processed 345 pages in 0.124 seconds (21.685 MB/sec). # confirm accessible to database on secondary node [root@node01 ~]# sqlcmd -S node02 -U SA -Q 'select name, create_date from sys.databases where name = "AG01_DB"' Password: name create_date ------------------------- ----------------------- AG01_DB 2023-11-30 09:17:11.743 (1 rows affected) |
| [8] | That's OK. Verify it works normally to access to the listener and database. |
|
[root@node01 ~]# sqlcmd -S localhost -U SA Password: # current state 1> select L.replica_id,L.replica_server_name,R.role_desc from sys.availability_replicas as L 2> left join sys.dm_hadr_availability_replica_states as R on L.replica_id = R.replica_id 3> go replica_id replica_server_name role_desc ------------------------------------ ----------------------------- ------------------------ BBACCFF7-CE2F-450C-8491-35E317F3774F node01 PRIMARY 94C43733-1700-46AD-B83B-CD239AC3DE2A node02 SECONDARY C9AF419A-BC17-463D-80DD-102D6E7BB2B7 node03 SECONDARY (3 rows affected) 1> exit # read-only access is responded by secondary replica [root@node01 ~]# sqlcmd -S 10.0.0.51 -U SA -K readonly -d AG01_DB -Q 'select @@servername' Password: ------------- node03 (1 rows affected) # read-only access is responded by secondary replica (round robin routing) [root@node01 ~]# sqlcmd -S 10.0.0.51 -U SA -K readonly -d AG01_DB -Q 'select @@servername' Password: ------------- node02 (1 rows affected) # primary replica responds to non-read-only access [root@node01 ~]# sqlcmd -S 10.0.0.51 -U SA -d AG01_DB -Q 'select @@servername' Password: -------------- node01 (1 rows affected) |
| [9] | It's possible to verify or change state of [Always On AG] with SSMS on Windows client. |
|
Matched Content