如何在没有域的环境中搭建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-JBRHIAJPMG2file

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

4.2选择数据库

      说明：在走AlwaysOn之前，数据库必须在完整模式下，且已经做过完整备份。

4.3指定副本，单击【添加副本】把所有的成员节点添加进来；

       4.3.1 添加副本

自动故障转移：运行在哪些节点间建立高可用（SQL Server 2016已经支持在3个节点了） 。

同步提交：AlwaysOn同步的模式，对应的还有异步模式，当选择了自动故障转移时，必须勾选同步提交；如想详细了解同步和异步模式的区别，可参考这个链接：http://www.cnblogs.com/i6first/p/4139670.html 。

可读辅助副本：当该节点为辅助副本时，能否接受只读请求以及以哪种方式接受只读请求（只读意向）。

 4.3.2配置端点

步骤3中创建的端点在这里可以查看到：

 4.3.3配置侦听器

4.4 选择数据同步的方式

       我选择的是完整，指定了一个共享目录，在后面安装环节中，主副本会把数据库备份到这个位置，然后辅助副本从此位置拿到备份文件后还原，已实现数据同步。

4.5后面的【验证】、【摘要】直接选择Next（下一步）即可。

      正常情况完成步骤4.5以后AlwaysOn就搭建完了，但我在两次实际验证过程中发现还需要继续后面的步骤，可能是我的环境的问题，请读者根据需要参考。

4.6将可用副本联机

     在下图所示的位置，右击脱机的节点（红色向下的标记），选择联接到可用性组；