SQL Server 2017 : Read Scale-out Availability Group2017/10/17

Configure Read Scale-out Availability Group.

This example is based on the environment like follows.
Configure 3 servers to create a cluster, one is a Primary node and other two nodes are secondary with read-only access.

-----------+-----------------------------+-----------------------------+------------
           |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]	Install SQL Server on all nodes, refer to here.
[2]	Enable Always On Availability Group and create a user for mirroring on all nodes. Replace 'password' to strong one.

[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]	Create certificates on Primary node and copy them to other secondary nodes. Replace 'password', 'pvk_password' to strong one.

[root@node01 ~]#

sqlcmd -S localhost -U SA

Password:

1> create master key encryption by password = 'password';
2> create certificate dbm_certificate with subject = 'dbm';
3> backup certificate dbm_certificate
4> to file = '/var/opt/mssql/data/dbm_certificate.cer'
5> with private key (
6> file = '/var/opt/mssql/data/dbm_certificate.pvk',
7> encryption by password = 'pvk_password'
8> );
9> go
1> exit

[root@node01 ~]#

cd /var/opt/mssql/data

[root@node01 data]#

scp dbm_certificate.* root@node02.srv.world:/var/opt/mssql/data/

root@node02.srv.world's password:
dbm_certificate.cer                           100%  667   614.7KB/s   00:00
dbm_certificate.pvk                           100% 1212     1.0MB/s   00:00

[root@node01 data]#

scp dbm_certificate.* root@node03.srv.world:/var/opt/mssql/data/

root@node03.srv.world's password:
dbm_certificate.cer                           100%  667   581.6KB/s   00:00
dbm_certificate.pvk                           100% 1212   935.1KB/s   00:00

[root@node01 data]#

ssh root@node02.srv.world 'chown mssql. /var/opt/mssql/data/dbm_certificate.*'

[root@node01 data]#

ssh root@node03.srv.world 'chown mssql. /var/opt/mssql/data/dbm_certificate.*'

[4]	Create certificates on all secondary nodes. Replace 'password' to strong one. For 'pvk_password', specify the same password you set on Primary node above.

[root@node02 ~]#

sqlcmd -S localhost -U SA

Password:

1> create master key encryption by password = 'password';
2> create certificate dbm_certificate authorization dbm_user
3> from file = '/var/opt/mssql/data/dbm_certificate.cer'
4> with private key (
5> file = '/var/opt/mssql/data/dbm_certificate.pvk',
6> decryption by password = 'pvk_password'
7> );
8> go
1> exit

[5]	Set Endpoint for mirroring on all nodes.

[root@node01 ~]#

sqlcmd -S localhost -U SA

Password:

1> create endpoint [Hadr_endpoint]
2> as tcp (listener_ip = (0.0.0.0), listener_port = 5022)
3> for data_mirroring (
4> role = all,
5> authentication = certificate dbm_certificate,
6> encryption = required algorithm aes
7> );
8> alter endpoint [Hadr_endpoint] state = started;
9> grant connect on endpoint::[Hadr_endpoint] to [dbm_login];
10> go
1> exit

# if Firewalld is running, allow the port

[root@node01 ~]#

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

[root@node01 ~]#

firewall-cmd --reload

[6]	Create Availability Group on primary node.

[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> ),
14> N'node02' with (
15> endpoint_url = N'tcp://10.0.0.52:5022',
16> availability_mode = asynchronous_commit,
17> failover_mode = manual,
18> seeding_mode = automatic,
19> primary_role (read_only_routing_list=(('node01','node03'), 'node02')),
20> secondary_role (
21> allow_connections = read_only,
22> read_only_routing_url = N'tcp://10.0.0.52:1433')
23> ),
24> N'node03' with (
25> endpoint_url = N'tcp://10.0.0.53:5022',
26> availability_mode = asynchronous_commit,
27> failover_mode = manual,
28> seeding_mode = automatic,
29> primary_role (read_only_routing_list=(('node01','node02'), 'node03')),
30> secondary_role (
31> allow_connections = read_only,
32> read_only_routing_url = N'tcp://10.0.0.53:1433')
33> );
34> alter availability group [AG01] grant create any database;
35> go
1> exit

[7]	Join in Availability Group on all secondary nodes.

[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

[8]	Add Listener and Database to Availability Group on primary node.

[root@node01 ~]#

sqlcmd -S localhost -U SA

Password:

1> alter availability group [AG01] add listener 'AG01_listener'
2> ( with ip (
3> ('10.0.0.51', '255.255.255.0'),
4> ('10.0.0.52', '255.255.255.0'),
5> ('10.0.0.53', '255.255.255.0') ),
6> port = 1433
7> );
8> 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 304 pages for database 'AG01_DB', file 'AG01_DB' on file 1.
Processed 3 pages for database 'AG01_DB', file 'AG01_DB_log' on file 1.
BACKUP DATABASE successfully processed 307 pages in 0.163 seconds (14.714 MB/sec).
1> exit

# verify possible to see the database on secondary nodes

[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                      2017-10-18 19:20:18.740

(1 rows affected)

[9]	It's OK all. 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
------------------------------------ ----------------------- ----------------
ACDB4168-C9FD-493F-89C0-FBE067E85BA5 node01                  PRIMARY
E8B30CD1-BB51-4314-AC39-C3326ECA67C2 node02                  SECONDARY
A08550D5-BD92-4A4A-9678-9CC962E37431 node03                  SECONDARY

(3 rows affected)
1> exit

[root@node01 ~]#

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

Password:

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

(1 rows affected)

[root@node01 ~]#

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

Password:

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

(1 rows affected)

[root@node01 ~]#

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

Password:

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

(1 rows affected)

# if access to secondary nodes not with read-only, it is denied like follows

[root@node01 ~]#

sqlcmd -S 10.0.0.52 -U SA -d AG01_DB -Q 'select @@servername'

Password:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : The target database ('AG01_DB') is in an availability 
group and is currently accessible for connections when the application intent is set to read only. 
For more information about application intent, see SQL Server Books Online..

[10]	If you'd like to switch primary to another node, run like follows. For example, make node02 be primary.

[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
------------------------------------ ----------------------- ----------------
ACDB4168-C9FD-493F-89C0-FBE067E85BA5 node01                  PRIMARY
E8B30CD1-BB51-4314-AC39-C3326ECA67C2 node02                  SECONDARY
A08550D5-BD92-4A4A-9678-9CC962E37431 node03                  SECONDARY

(3 rows affected)

# change availability_mode
1> alter availability group [AG01] modify replica
2> on N'node01' with (availability_mode = synchronous_commit);
3> go

1> alter availability group [AG01] modify replica
2> on N'node02' with (availability_mode = synchronous_commit);
3> go

1> alter availability group [AG01] modify replica
2> on N'node03' with (availability_mode = synchronous_commit);
3> go

# change role to secondary
1> alter availability group [AG01] set (role = secondary);
2> go

1> exit

# move to the node you'd like to make it primary and run failover

[root@node02 ~]#

sqlcmd -S localhost -U SA

Password:

1> alter availability group [AG01] force_failover_allow_data_loss;
2> go

# change availability_mode
1> alter availability group [AG01] modify replica
2> on N'node01' with (availability_mode = asynchronous_commit);
3> go

1> alter availability group [AG01] modify replica
2> on N'node02' with (availability_mode = asynchronous_commit);
3> go

1> alter availability group [AG01] modify replica
2> on N'node03' with (availability_mode = asynchronous_commit);
3> go

# go to all secondary nodes and resume database mirroring

[root@node01 ~]#

sqlcmd -S localhost -U SA

Password:

1> alter database [AG01_DB] set hadr resume;
2> go

# goto primary node and show current state

[root@node02 ~]#

sqlcmd -S localhost -U SA

Password:

1> select L.replica_id,L.replica_server_name,R.synchronization_state_desc,R.synchronization_health_desc
1> from sys.availability_replicas as L
2> left join sys.dm_hadr_database_replica_states as R on L.replica_id = R.replica_id
3> go
replica_id                           replica_server_name  synchronization_state_desc  synchronization_health_..
------------------------------------ -------------------- --------------------------- -------------------------
ACDB4168-C9FD-493F-89C0-FBE067E85BA5 node01               SYNCHRONIZING               HEALTHY
E8B30CD1-BB51-4314-AC39-C3326ECA67C2 node02               SYNCHRONIZED                HEALTHY
A08550D5-BD92-4A4A-9678-9CC962E37431 node03               SYNCHRONIZING               HEALTHY

(3 rows affected)

[11]	It's possible to verify or change state of [Always On AG] with SSMS on Windows client.

Matched Content