如何在没有域的环境中搭建AlwaysOn（二）

对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 + '-Certificate'), ' WITH SUBJECT = ', QUOTENAME(@CompName, ''''),',EXPIRY_DATE=',QUOTENAME(20991231 ,'''') )

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指定可用组名称