如何在沒有域的環境中搭建AlwaysOn（二）

對DBA而言，不需要域就可以搭建SQL Server AlwaysOn是Windows Server 2016中最令人興奮的功能了，它不僅可以降低搭建的成本，而且還減少了部署和運維的工作量。上篇博客已給大家分享過：要在沒有域的環境中搭建AlwaysOn需要兩個步驟， 1）.搭建基於windows ...

對DBA而言，不需要域就可以搭建SQL Server AlwaysOn是Windows Server 2016中最令人興奮的功能了，它不僅可以降低搭建的成本，而且還減少了部署和運維的工作量。

上篇博客已給大家分享過：要在沒有域的環境中搭建AlwaysOn需要兩個步驟，

1）.搭建基於windows server 2016 工作組環境的故障轉移集群；

2）.搭建AlwaysOn；

步驟1已在上篇博客中介紹了，因此本文將分享AlwaysOn的搭建方法：

1.安裝SQL Server 2016

在所有節點上安裝SQL Server 2016。

2.啟用AlwaysOn功能

在所有節點上啟用AlwaysOn功能，方法：

打開SQL Server配置管理器，在對應的SQL Server實例屬性中，勾選【啟用AlwaysOn可用性組】，如下圖：

3.在每個節點上創建alwaysOn的通訊端點（鏡像端點）

在windows server 2016之前，配置端點的加密的方式有兩種：域用戶授權和證書加密，升級到windows server 2016後，如果不使用域搭建AlwaysOn，那麼就只能選擇證書加密的方式了。

3.1創建一個共用目錄，允許AlwaysOn的所有節點均可以讀寫該目錄；

共用目錄用來存放端點通訊的證書，在後續的步驟中將會用到。示例為：\\WIN-JBRHIAJPMG2\file

3.2創建端點

為了方便，我把創建證書和安裝證書的過程用兩個存儲來實現。（這個兩個存儲過程來自網上，不是我寫的）

3.2.1將如下兩個存儲過程在每個節點的SQL Server實例中執行一遍：

CREATE PROCEDURE CreateEndpointCert
 @ShareName SYSNAME ,
 @StrongPassword SYSNAME
AS BEGIN
 
--This must be executed in the context of Master
 IF (DB_NAME() <> 'master')
 BEGIN
  PRINT N'This SP must be executed in master.  USE master and then retry.'
  RETURN (-1)
 END

    DECLARE @DynamicSQL varchar(1000);
    DECLARE @CompName varchar(250);
 DECLARE @HasMasterKey INT;
    SELECT @CompName = CONVERT(SysName, SERVERPROPERTY('MachineName'));

-- Only create a master key if it doesn't already exist

 SELECT @HasMasterKey = is_master_key_encrypted_by_server from sys.databases where name = 'master'
 IF (@HasMasterKey = 0)
 BEGIN
 --Create a MASTER KEY to encrypt the certificate.
  SET @DynamicSQL = CONCAT('CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' ,  QUOTENAME(@StrongPassword, ''''));
  EXEC (@DynamicSQL)
 END

--Create the certificate to authenticate the endpoint
    SET @DynamicSQL = CONCAT('CREATE CERTIFICATE ', QUOTENAME(@CompName + '-Cert'), ' WITH SUBJECT = ', QUOTENAME(@CompName, '''')) ;
    EXEC (@DynamicSQL);

    --Create the database mirroring endpoint authenticated by the certificate.
    SET @DynamicSQL =
        CONCAT('CREATE ENDPOINT Endpoint_Mirroring
            STATE = STARTED
            AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
            FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE ',QUOTENAME(@CompName + '-Cert'), ' , ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)');
    EXEC (@DynamicSQL);

   --Back up the certificate to a common network share for import into other nodes in the cluster
    SET @DynamicSQL = CONCAT('BACKUP CERTIFICATE ',QUOTENAME(@CompName + '-Cert'),' To FILE = ', QUOTENAME( @ShareName + '\SQL-' + @CompName + '.cer', ''''));
    EXEC (@DynamicSQL);
END
GO

說明：

CreateEndpointCert 接受2個參數，分別為共用路徑和證書密碼，其作用：創建證書，創建基於證書加密通訊的端點，備份證書到共用路徑；

CREATE PROCEDURE InstallEndpointCert
    @CompName SYSNAME,
 @ShareName SYSNAME,
 @StrongPassword SYSNAME
AS BEGIN
    DECLARE @DynamicSQL varchar(1000);
    DECLARE @MyCompName varchar(250);
    SELECT @MyCompName = CONVERT(SysName, SERVERPROPERTY('MachineName'));
   --Don't need to create LOGINs for the local system
    IF (@MyCompName <> @CompName)
    BEGIN
        SET @DynamicSQL = CONCAT('CREATE LOGIN ', QUOTENAME (@CompName + '-Login'), ' WITH PASSWORD= ', QUOTENAME( @StrongPassword, ''''));
        EXEC (@DynamicSQL);

        SET @DynamicSQL = CONCAT('CREATE USER ', QUOTENAME( @CompName + '-User'), ' FOR LOGIN ', QUOTENAME(@CompName + '-Login'));
        EXEC (@DynamicSQL);

        SET @DynamicSQL = CONCAT('CREATE CERTIFICATE ', QUOTENAME(@CompName +'-Cert'), ' AUTHORIZATION ', QUOTENAME(@CompName +'-User'), ' FROM FILE = ', QUOTENAME(@ShareName + '\SQL-' + @CompName + '.cer' , ''''));
        EXEC (@DynamicSQL);

        SET @DynamicSQL = CONCAT('GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ', QUOTENAME(@CompName +'-Login'));
        EXEC (@DynamicSQL);
 END
END
GO

說明：

InstallEndpointCert 接受3個參數，分別為主機名、共用路徑（必須跟CreateEndpointCert的共用路徑相同)和密碼。其作用：在SQL Server中為每個節點（除自身外）創建一個對應的登錄用戶和資料庫用戶（為了便於管理），然後利用共用路徑中證書來授予該資料庫用戶可以訪問對應節點的端點；

3.2.2 創建端點

首先依次在每個節點上執行CreateEndpointCert，創建證書，

然後在每個節點上執行InstallEndpointCert，InstallEndpointCert的第一個參數就是主機名，也就是說，除了自身外，需要將其他所有節點的主機名作為參數都執行一次InstallEndpointCert。

說明：

CreateEndpointCert 接受2個參數，分別為共用路徑和證書密碼，其作用：創建證書，創建基於證書加密通訊的端點，備份證書到共用路徑；

InstallEndpointCert 接受3個參數，分別為主機名、共用路徑（必須跟CreateEndpointCert的共用路徑相同)和密碼（資料庫用戶的密碼）。

4.新建可用組

打開SSMS，登錄到SQL Server，展開【AlwaysOn搞可用性】，根據嚮導創建AlwaysOn。

4.1指定可用組名稱