Ubuntu上配置SQL Server Always On Availability Group(2)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**'; CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm'; BACKUP CERTIFICATE dbm_certificate TO FILE = '/var/opt/mssql/data/dbm_certificate.cer' WITH PRIVATE KEY ( FILE = '/var/opt/mssql/data/dbm_certificate.pvk', ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**' );

f)        把证书的备份复制到所有的非Primary节点上，同时使用它创建证书：

先在Primary节点上执行如下命令复制证书的备份到其它节点上：

cd /var/opt/mssql/data scp dbm_certificate.* root@**<node2>**:/var/opt/mssql/data/

Note：如果遇到Permission denied，可以使用sz和rz命令通过主机来传输文件。

再在目的端Secondary节点上执行如下命令给用户mssql添加足够的权限：

cd /var/opt/mssql/data chown mssql:mssql dbm_certificate.*

最后在目的端Secondary节点上利用备份的证书创建证书：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**'; CREATE CERTIFICATE dbm_certificate AUTHORIZATION dbm_user FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer' WITH PRIVATE KEY ( FILE = '/var/opt/mssql/data/dbm_certificate.pvk', DECRYPTION BY PASSWORD = '**<Private_Key_Password>**' );

g)      在所有节点上创建database mirroring endpoint：

CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = **<5022>**) FOR DATA_MIRRORING ( ROLE = ALL, AUTHENTICATION = CERTIFICATE dbm_certificate, ENCRYPTION = REQUIRED ALGORITHM AES ); ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED; GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];

Note：这里Listener IP暂时不能修改，只能是0.0.0.0，目前有BUG，未来可能会修复。

h)      在Primary节点上创建AG：

CREATE AVAILABILITY GROUP [UbuntuAG] WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL) FOR REPLICA ON N'**<node1>**' WITH ( ENDPOINT_URL = N'tcp://**<node1>**:**<5022>**', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC ), N'**<node2>**' WITH ( ENDPOINT_URL = N'tcp://**<node2>**:**<5022>**', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC ); ALTER AVAILABILITY GROUP [UbuntuAG] GRANT CREATE ANY DATABASE;

Note：执行过程中可能会出现这个警告”Attempt to access non-existent or uninitialized availability group with ID”，暂时忽略即可，未来版本可能会修复。

下图中UbuntuAG2是新创建的AG，Secondary节点还处于OFFLINE状态：

i)        把其它Secondary节点加入到AG中：