SQL Server 2022 : Always On Availability Group2023/12/07

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

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 

dbm_certificate.cer                           100%  667   614.7KB/s   00:00
dbm_certificate.pvk                           100% 1212     1.0MB/s   00:00

dbm_certificate.cer                           100%  667   581.6KB/s   00:00
dbm_certificate.pvk                           100% 1212   935.1KB/s   00:00

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:~# 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 

1> alter availability group [AG01] join with (cluster_type = none);
2> alter availability group [AG01] grant create any database;
3> go
1> exit

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 

name                            create_date
------------------------------- -----------------------
AG01_DB                         2023-11-24 13:27:47.830

(1 rows affected)

# वर्तमान स्थिति
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
------------------------------------ --------------------------- ------------------------------------------------------------
2A12BC5A-64EE-474C-8748-E0A9F48BAE1D node01                      PRIMARY
BB59A594-7C5D-4D35-81B0-25B0EB6A6D73 node02                      SECONDARY
474D1BC7-08D0-47B0-BF3A-59D8BE6C69A6 node03                      SECONDARY

(3 rows affected)

1> exit

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

(1 rows affected)

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

(1 rows affected)

--------------
node01

(1 rows affected)