SQL Server 2019 : Always On Availability Group2020/01/21

Configure Always On Availability Group for SQL Server on Linux.

This example is based on the environment like follows.

-----------+-----------------------------+-----------------------------+------------
           |10.0.0.51                    |10.0.0.52                    |10.0.0.53
+----------+-----------+      +----------+-----------+      +----------+-----------+
| [ node01.srv.world ] |      | [ node02.srv.world ] |      | [ node03.srv.world ] |
|      (Primary)       |      |(Secondary (ReadOnly))|      |(Secondary (ReadOnly))|
|      SQL Server      |      |       SQL Server     |      |       SQL Server     |
+----------------------+      +----------------------+      +----------------------+

[1]	Install and Start SQL Server on All Nodes, refer to here.
[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 = 'password';
2> create user dbm_user for login dbm_login;
3> go
1> exit

[3]	On all Nodes, Create ceritificate abd copy it to other all Nodes and also Create an Endpoint. Replace [password] section to any password you like. Furthermore, Replace [node01_cert], [node01_cert.cer] words to uniq words on other Nodes.

[root@node01 ~]#

sqlcmd -S localhost -U SA

Password:

1> create master key encryption by password = 'password';
2> create certificate node01_cert with subject = 'AG Node01 Certificate';
3> backup certificate node01_cert
4> to file = '/var/opt/mssql/data/node01_cert.cer'
5> go
1> create endpoint AGEndpoint state = started
2> as tcp (listener_ip = all, listener_port = 5022)
3> for data_mirroring (
4> role = all,
5> authentication = certificate node01_cert);
6> go
1> exit

[root@node01 ~]#

cd /var/opt/mssql/data

[root@node01 data]#

scp node01_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 node01_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. /var/opt/mssql/data/node01_cert.cer'

[root@node01 data]#

ssh node03.srv.world 'chown mssql. /var/opt/mssql/data/node01_cert.cer'

# if Firewalld is running, allow endpoint port

[root@node01 data]#

firewall-cmd --add-port=5022/tcp --permanent

[root@node01 data]#

firewall-cmd --reload

[4]	On all Nodes, Restore certificates that are copied from other Nodes.

[root@node01 ~]#

sqlcmd -S localhost -U SA

Password:

1> create certificate node02_cert authorization dbm_user
2> from file = '/var/opt/mssql/data/node02_cert.cer'
3> go
1> create certificate node03_cert authorization dbm_user
2> from file = '/var/opt/mssql/data/node03_cert.cer'
3> go
1> grant connect on endpoint::AGEndpoint to dbm_login;
2> go
1> exit

[5]	On promary Node, Configure Availability Group.

[root@node01 ~]#

sqlcmd -S localhost -U SA

Password:

1> create availability group [AG01]
2> with (cluster_type = none)
3> for replica on
4> N'node01' with (
5> endpoint_url = N'tcp://10.0.0.51:5022',
6> availability_mode = asynchronous_commit,
7> failover_mode = manual,
8> seeding_mode = automatic,
9> primary_role (read_only_routing_list=(('node02','node03'), 'node01')),
10> secondary_role (
11> allow_connections = read_only,
12> read_only_routing_url = N'tcp://10.0.0.51:1433')),
13> N'node02' with (
14> endpoint_url = N'tcp://10.0.0.52:5022',
15> availability_mode = asynchronous_commit,
16> failover_mode = manual,
17> seeding_mode = automatic,
18> primary_role (read_only_routing_list=(('node01','node03'), 'node02')),
19> secondary_role (
20> allow_connections = read_only,
21> read_only_routing_url = N'tcp://10.0.0.52:1433')),
22> N'node03' with (
23> endpoint_url = N'tcp://10.0.0.53:5022',
24> availability_mode = asynchronous_commit,
25> failover_mode = manual,
26> seeding_mode = automatic,
27> primary_role (read_only_routing_list=(('node01','node02'), 'node03')),
28> secondary_role (
29> allow_connections = read_only,
30> read_only_routing_url = N'tcp://10.0.0.53:1433'));
31> alter availability group [AG01] grant create any database;
32> go
1> exit

[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 promary Node, Add listener and database to Availability Group.

[root@node01 ~]#

sqlcmd -S localhost -U SA

Password:

1> alter availability group [AG01] add listener 'AG01_listener'
2> ( with ip (
5> ('10.0.0.51', '255.255.255.0') ),
6> port = 1433);
7> go
1> create database [AG01_DB];
2> alter database [AG01_DB] set recovery full;
3> backup database [AG01_DB] to disk = N'/var/opt/mssql/data/AG01_DB.bak';
4> alter availability group [AG01] add database [AG01_DB];
5> go
Processed 328 pages for database 'AG01_DB', file 'AG01_DB' on file 1.
Processed 2 pages for database 'AG01_DB', file 'AG01_DB_log' on file 1.
BACKUP DATABASE successfully processed 330 pages in 0.441 seconds (5.837 MB/sec).
1> exit

# 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                  2020-01-20 20:53:53.870

(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
------------------------------------ --------------------- -------------
94D463BE-D124-48FD-AC27-21EC919A0D01 node01                PRIMARY
850D06C6-6F76-4409-9055-53DFF5B2359E node02                SECONDARY
734F7B6D-0370-4124-8B01-350EC14A98B9 node03                SECONDARY

(3 rows affected)

1> exit

# queries routed to secondary node with read only access

[root@node01 ~]#

sqlcmd -S node01 -U SA -K readonly -d AG01_DB -Q 'select @@servername'

Password:

-------------
node03

(1 rows affected)

# queries routed to secondary node with read only access (round robin routing)

[root@node01 ~]#

sqlcmd -S node01 -U SA -K readonly -d AG01_DB -Q 'select @@servername'

Password:

-------------
node02

(1 rows affected)

# queries routed to primary node with no read only access

[root@node01 ~]#

sqlcmd -S node01 -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