SQL Server 2019 for Linux – 创建always on群集

激活 SQL always on

/opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
systemctl restart mssql-server.service

为Pacemaker创建SQL登陆名

Use [master]
GO
CREATE LOGIN pcslogin
WITH PASSWORD = 'Pass@123'
GO

Use [master]
GO
GRANT ALTER, CONTROL, VIEW, DEFINITION ON AVAILABILITY_GROUP::sqlag to pcslogin
GO
GRANT VIEW SERVER STATE TO pcslogin
GO

为SQL always on启用群集代理

<strong>yum install mssql-server-ha</strong>

创建数据库主密钥

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@123';
GO

在节点1 SQLAG01上创建证书

CREATE CERTIFICATE sqlag01_Cert
WITH SUBJECT = 'sql01 AG Certificate';

GO

BACKUP CERTIFICATE sqlag01_Cert
TO FILE = '/var/opt/mssql/data/sqlag01_Cert.cer';

GO

CREATE ENDPOINT SQLAG
STATE = STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE sqlag01_Cert,
    ROLE = ALL);

GO

在节点2 SQLAG02上创建证书

CREATE CERTIFICATE sqlag02_Cert
WITH SUBJECT = 'sql02 AG Certificate';

GO

BACKUP CERTIFICATE sqlag02_Cert
TO FILE = '/var/opt/mssql/data/sqlag02_Cert.cer';

GO

CREATE ENDPOINT SQLAG
STATE = STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE sqlag02_Cert,
    ROLE = ALL);

GO

在节点3 SQLAG03上创建证书

CREATE CERTIFICATE sqlag03_Cert
WITH SUBJECT = 'sqlag03 AG Certificate';

GO

BACKUP CERTIFICATE sqlag03_Cert
TO FILE = '/var/opt/mssql/data/sqlag03_Cert.cer';

GO

CREATE ENDPOINT SQLAG
STATE = STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE sqlag03_Cert,
    ROLE = ALL);

GO

将SQLAG01上的证书同步到其他节点中

for a in {01..03} ; do scp /var/opt/mssql/data/sqlag01_Cert.cer sqlag$a:/var/opt/mssql/data/sqlag01_Cert.cer ; done

将SQLAG02上的证书同步到其他节点中

for a in {01..03} ; do scp /var/opt/mssql/data/sqlag02_Cert.cer sqlag$a:/var/opt/mssql/data/sqlag02_Cert.cer ; done

将SQLAG03上的证书同步到其他节点中

for a in {01..03} ; do scp /var/opt/mssql/data/sqlag03_Cert.cer sqlag$a:/var/opt/mssql/data/sqlag03_Cert.cer ; done

将所有权和与复制的证书文件相关联的组更改为 mssql

登陆到SQLAG01中

chown mssql:mssql sqlag02_Cert.cer
chown mssql:mssql sqlag03_Cert.cer

登陆到SQLAG02中

chown mssql:mssql sqlag01_Cert.cer
chown mssql:mssql sqlag03_Cert.cer

登陆到SQLAG03中

chown mssql:mssql sqlag01_Cert.cer
chown mssql:mssql sqlag02_Cert.cer

创建用于验证端点的用户

USE [MASTER]
GO
CREATE LOGIN AVG_LOGIN
WITH PASSWORD = 'Pass@123'; 
GO

USE [MASTER]
GO
CREATE USER AVG_LOGIN
FOR LOGIN AVG_LOGIN
GO

登陆SQLAG01将用户与证书关联

CREATE CERTIFICATE sqlag02_cert 
AUTHORIZATION AVG_LOGIN 
FROM FILE = '/var/opt/mssql/data/sqlag02_cert.cer' 
GO

CREATE CERTIFICATE sqlag03_cert 
AUTHORIZATION AVG_LOGIN 
FROM FILE = '/var/opt/mssql/data/sqlag03_cert.cer' 
GO

登陆SQLAG02将用户与证书关联

CREATE CERTIFICATE sqlag01_cert 
AUTHORIZATION AVG_LOGIN 
FROM FILE = '/var/opt/mssql/data/sqlag01_cert.cer' 
GO

CREATE CERTIFICATE sqlag03_cert 
AUTHORIZATION AVG_LOGIN 
FROM FILE = '/var/opt/mssql/data/sqlag03_cert.cer' 
GO

登陆SQLAG03将用户与证书关联

CREATE CERTIFICATE sqlag01_cert 
AUTHORIZATION AVG_LOGIN 
FROM FILE = '/var/opt/mssql/data/sqlag01_cert.cer' 
GO

CREATE CERTIFICATE sqlag02_cert 
AUTHORIZATION AVG_LOGIN 
FROM FILE = '/var/opt/mssql/data/sqlag02_cert.cer' 
GO

允许用户连接端点

USE [MASTER] 
GO
GRANT CONNECT ON ENDPOINT::SQLAG
TO [AVG_LOGIN];   
GO

创建always on可用性组

登陆SQLAG01，使用SQL Server Management studio创建可用性组

输入可用性组名称，注意群集类型需要选择为EXTERNAI

选择可用性组的数据库

将节点添加到可用性组中

由于本次是测试数据库，数据较少，我们选择自动种子设定

可用性组验证结果

完成可行用组创建

查看可用性组属性，检查always on状态