潛水太久總要浮出水面的

 

其最主要是使用DNS及憑證做雙方溝通的橋樑，影片中採用的是分散式可用性群組但今天實作的不太一樣是讀取的向外延展可用性群組(Scale-out availability group)，官方文件是使用2台Linux，於是我在Azure中也建了2台Linux的SQL Server 2017，分別為sql2017-1、sql2017-2，為了實驗跨平台是否可行，所以也建了Windows 2016並安裝SQL 2017取名叫sql2017-3。

接下來要修改3台主機的hosts檔案讓其彼此能夠解析到，Linux的目錄在/etc/hosts，Windows則在C:\Windows\System32\drivers\etc

啟用 Always On 可用性群組並重新啟動 sqlserver

Linux執行以下指令

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

Windows在SQL Server組態管理員中開啟高可用性後重新啟動

啟用 AlwaysOn_health 事件工作階段(在每台SQL Server上執行)

ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

建立資料庫鏡像端點使用者(在每台SQL Server上執行)，請更換指令碼內的密碼

CREATE LOGIN dbm_login WITH PASSWORD = 'P@ssw0rd987';
CREATE USER dbm_user FOR LOGIN dbm_login;

建立憑證

第1台建立憑證後，要將主要金鑰和憑證dbm_certificate.cer及dbm_certificate.pvk這2個檔案複製到另外2台

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd987';
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 = 'P@ssw0rd987'
       );

Linux使用scp指令複製到第2台(在第1台執行)

cd /var/opt/mssql/data
scp dbm_certificate.* root@10.0.0.5:/var/opt/mssql/data/

因為複製後的權限是root，要執行變更權限讓mssql有權限可以讀取(在第2台執行)

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

Windows則使用WinSCP將檔案複製到第3台(在第3台執行)

在次要伺服器上建立憑證(在第2台及第3台執行)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd987';
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 = 'P@ssw0rd987'
);

在所有複本上建立資料庫鏡像端點(3台都要執行)，LISTENER_IP請務必使用0.0.0.0，Port預設使用5022

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];

開啟防火牆的5022 PORT(3台都要執行)

sudo firewall-cmd --zone=public --add-port=5022/tcp --permanent
sudo firewall-cmd --reload

建立可用性群組(在第1台執行)，先將2台Linux建立可用性群組

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'sql2017-1' WITH (
            ENDPOINT_URL = N'tcp://sql2017-1:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
            ),
        N'sql2017-2' WITH ( 
            ENDPOINT_URL = N'tcp://sql2017-2:5022', 
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
            );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

聯結至可用性群組的次要 SQL Server(在第2台執行)

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

將資料庫新增至可用性群組(在第1台執行)，建一個測試資料庫db1並將其加入到ag1的可用性群組

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1] 
   TO DISK = N'/var/opt/mssql/data/db1.bak';

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

此時在SSMS可以看到可用性群組有2台主機及可用性資料庫db1

查詢是否有複寫至第2台(在第2台執行)

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

驗證資料是否會同步，在db1建一個每一秒都會新增一筆資料的table t1(在第1台執行)

CREATE TABLE db1.dbo.t1(c1 int identity,c2 datetime)
GO

set nocount on
while 1=1 begin
	insert into db1.dbo.t1(c2 ) values(getdate())
	waitfor delay '00:00:01'
end
GO

檢查是否有複寫成功(在第2台執行)，若資料有持續增加表示有在同步複寫

SELECT TOP(10) *
FROM db1.dbo.t1
ORDER BY 1 DESC

接下來要驗證跨平台是否可行，將第3台加入複本(在第1台執行)

use master
ALTER AVAILABILITY GROUP [ag1] ADD REPLICA ON 
N'sql2017-3' WITH (
            ENDPOINT_URL = N'tcp://sql2017-3:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
)

聯結至可用性群組的次要 SQL Server(在第3台執行)

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

檢查SSMS是否有成功

檢查是否有複寫成功(在第3台執行)，若資料有持續增加表示有在同步複寫

SELECT TOP(10) *
FROM db1.dbo.t1
ORDER BY 1 DESC