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

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