[10] SQL Server – Always On Availability Group

1 lutego 2022 Wyłączono przez Adam [zicherka] Nogły

Skonfiguruj Always On Availability Group (czyli klaster) dla SQL Server w systemie Linux. Ten przykład jest oparty na środowisku, jak poniżej.

[1] Zainstaluj i uruchom SQL Server na wszystkich węzłach w sieci.

[2] We wszystkich węzłach włącz funkcję Always On Availability Group, a także utwórz użytkownika dla replikacji bazy danych. Zamień sekcję [hasło] na dowolne hasło.

[root@vlsr01 ~]# /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
Locale pl_PL not supported. Using en_US.
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
[root@vlsr01 ~]# systemctl restart mssql-server
[root@vlsr01 ~]# 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 = 'Tajne Hasło';
2> create user dbm_user for login dbm_login;
3> go
1> exit

[3] Na wszystkich węzłach Utwórz certyfikat i skopiuj go do innych wszystkich węzłów, a także Utwórz punkt końcowy [Endpoint]. Zamień sekcję [hasło] na dowolne hasło. Ponadto zastąp słowa [vlsr01_cert], [vlsr01_cert.cer] słowami/nazwami unikalnymi na innych węzłach.

[root@vlsr01 ~]# NODENAME=$(hostname -s)
[root@vlsr01 ~]# cat > create-cert.sql <<EOF
create master key encryption by password = 'Z1ch3rka';
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@vlsr01 ~]# sqlcmd -S localhost -U SA -i create-cert.sql
Password:
#przekopiuj pliki *.cer z każdego węzła na każdy
#na każdym węźle powinny być trzy pliki
[root@vlsr01 data]# scp vlsr01_cert.cer vlsr02.zicher.lab:/var/opt/mssql/data/
vlsr01_cert.cer                                    100%  703   898.9KB/s   00:00
[root@vlsr01 data]# scp vlsr01_cert.cer   vlsr03.zicher.lab:/var/opt/mssql/data/
vlsr01_cert.cer                                    100%  703   615.8KB/s   00:00
[root@vlsr01 data]# ssh vlsr02.zicher.lab 'chown mssql. /var/opt/mssql/data/vlsr01_cert.cer'
[root@vlsr01 data]# ssh vlsr03.zicher.lab 'chown mssql. /var/opt/mssql/data/vlsr01_cert.cer'

#jeśli Firewalld działa zezwól na ruch na porcie 5022/tcp
[root@vlsr01 data]# firewall-cmd --add-port=5022/tcp --permanent
[root@vlsr01 data]# firewall-cmd –reload

[4] We wszystkich węzłach przywróć certyfikaty skopiowane z innych węzłów.

[root@vlsr01 ~]# cat > restore-cert.sql <<EOF
create certificate vlsr02_cert authorization dbm_user
from file = '/var/opt/mssql/data/vlsr02_cert.cer'
create certificate vlsr03_cert authorization dbm_user
from file = '/var/opt/mssql/data/vlsr03_cert.cer'
grant connect on endpoint::AGEndpoint to dbm_login;
EOF

[root@vlsr01 ~]# sqlcmd -S localhost -U SA -i restore-cert.sql
Password:

[5] Na węźle Primary skonfiguruj Aviability Group.

[root@vlsr01 ~]# cat > create-ag.sql <<'EOF'
create availability group [AG01]
with (cluster_type = none)
for replica on
N'vlsr01' with (
endpoint_url = N'tcp://192.168.100.101:5022',
availability_mode = asynchronous_commit,
failover_mode = manual,
seeding_mode = automatic,
primary_role (read_only_routing_list=(('vlsr02','vlsr03'), 'vlsr01')),
secondary_role (
allow_connections = read_only,
read_only_routing_url = N'tcp://192.168.100.101:1433')),
N'vlsr02' with (
endpoint_url = N'tcp://192.168.100.102:5022',
availability_mode = asynchronous_commit,
failover_mode = manual,
seeding_mode = automatic,
primary_role (read_only_routing_list=(('vlsr01','vlsr03'), 'vlsr02')),
secondary_role (
allow_connections = read_only,
read_only_routing_url = N'tcp://192.168.100.102:1433')),
N'vlsr03' with (
endpoint_url = N'tcp://192.168.100.103:5022',
availability_mode = asynchronous_commit,
failover_mode = manual,
seeding_mode = automatic,
primary_role (read_only_routing_list=(('vlsr01','vlsr02'), 'vlsr03')),
secondary_role (
allow_connections = read_only,
read_only_routing_url = N'tcp://192.168.100.103:1433'));
alter availability group [AG01] grant create any database;
EOF

[root@vlsr01 ~]# sqlcmd -S localhost -U SA -i create-ag.sql

[6] Na wszystkich węzłach Secondary: przyłącz je do Aviability Group.

[root@vlsr02 ~]# 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] Na węźle Primary dodaj odbiornik oraz bazę danych do Aviability Group.

[root@vlsr01 ~]# cat > create-db.sql <<'EOF'
alter availability group [AG01] add listener 'AG01_listener'
( with ip (
('192.168.100.101', '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@vlsr01 ~]# sqlcmd -S localhost -U SA -i create-db.sql
Password:
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.455 seconds (5.657 MB/sec).

#potwierdź dostęp do bazy danych na węźle Secondary
[root@vlsr01 ~]# sqlcmd -S vlsr02 -U SA -Q 'select name, create_date from sys.databases where name = "AG01_DB"'
Password:
name                                                                                                                             create_date
-------------------------------------------------------------------------------------------------------------------------------- -----------------------
AG01_DB                                                                                                                          2022-02-01 20:03:17.207
(1 rows affected)

[8] Wszystko OK. Sprawdź czy wszystko działa normalnie oraz czy jest dostęp do odbiornika i bazy danych.

#aktualny stan
[root@vlsr01 ~]# sqlcmd -S localhost -U SA
Password:
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
------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------
50953691-E5FF-4C9C-BAF6-C7CA9163D48D vlsr01                                                                                                                                                                                                                                                           PRIMARY
97EB2CB2-D914-4BE5-A4DC-73852C2A5450 vlsr02                                                                                                                                                                                                                                                           SECONDARY
7AB78C29-520F-40C7-B87F-06623E8E6558 vlsr03                                                                                                                                                                                                                                                           SECONDARY
(3 rows affected)
1> exit

#zapytania kierowane do węzła Secondary z dostępem tylko do odczytu
[root@vlsr01 ~]# sqlcmd -S 192.168.100.101 -U SA -K readonly -d AG01_DB -Q 'select @@servername'
Password:
--------------------------------------------------------------------------------------------------------------------------------
vlsr03
(1 rows affected)

#zapytania kierowane do węzła Secondary (wtórnego) z dostępem tylko do odczytu (routing okrężny)
[root@vlsr01 ~]# sqlcmd -S 192.168.100.101 -U SA -K readonly -d AG01_DB -Q 'select @@servername'
Password:
--------------------------------------------------------------------------------------------------------------------------------
vlsr02
(1 rows affected)

#zapytania kierowane do węzła podstawowego bez dostępu tylko do odczytu
[root@vlsr01 ~]# sqlcmd -S 192.168.100.101 -U SA -d AG01_DB -Q 'select @@servername'
Password:
--------------------------------------------------------------------------------------------------------------------------------
vlsr01
(1 rows affected)

[9] Możliwe jest zweryfikowanie lub zmiana stanu [Always On AG] za pomocą SSMS na kliencie Windows.