sqlserver2016 alwayson on windows2016 without doma

1.主机集群安装信息

主机名N1 N2

PUBLIC IP 192.168.208.15/24 192.168.208.16/24

PRIVATE IP 192.168.122.172/24 192.168.122.173/24

集群名称CLS

集群IP 192.168.208.17

共享目录C:\emp

注意目录用户权限，要求N1和N2的SQLSERVER启动用户均可访问\\\\N1\emp 操作系统介质Windows Server2016

http://care.dlservice.microsoft.com/dl/download/F/8/3/F83C7D26-787A-4F43-82B

0-7C7BF8A12791/14393.0.160715-1616.RS1_RELEASE_SERVER_EVAL_XFRE_ZH-C

N.ISO

数据库介质Sqlserver2016

http://care.dlservice.microsoft.com/dl/download/1/8/2/18237033-702C-4848-9DB

0-9BB61DC69A74/SQLServer2016-x-CHS.iso

2.安装注意点：

●两节点hosts文件修改：

192.168.208.15 n1.jyc.com

192.168.208.16 n2.jyc.com

192.168.208.15 n1

192.168.208.16 n2

●两个节点sqlserver2016安装完成后需启用tcp/ip 1433

●两个节点sqlserver服务启动用户选择administrator密码一致

●节点1的c:\emp目录(也可选择别的目录)共享，两节点sqlserver启动服务的用户都有

保证读写权限●两节点安装故障转移群集功能和telnet客户端（方便测试端口）

●两节点关闭防火墙

3.安装配置过程：

3.1两节点N1,N2执行创建允许非管理员的授权（可选，如果启动服务的用户选择非administrator的话，需要执行该操作）：

PS C:\\Users\\Administrator> new-itemproperty -path HKLM:\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Policies\\System -Name LocalAccountTokenFilterPolicy -Value 1

3.2节点N1执行（创建集群）：

Powershell操作方式：

new-cluster -name cls –Node n1,n2 -StaticAddress 192.168.208.17 -NoStorage-AdministrativeAccessPoint DNS

也可以使用故障群集管理器操作：

3.3节点N1创建证书

-- ==================================== -- Execute the following code on N1

-- ==================================== USE masterGO

-- Create a database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd1!' GO

-- Create a new certificate

CREATE CERTIFICATE CLS_Certificate_N1_Private

WITH SUBJECT = 'CLS_Certificate_Private - N1',

START_DATE = '20160101'

GO

-- Backup the public key of the certificate to the filesystem BACKUP CERTIFICATE CLS_Certificate_N1_Private

TO FILE = 'c:\emp\\CLS_Certificate_N1_Public.cert'

GO

-- Create an endpoint for the Availability Group

CREATE ENDPOINT CLS_Endpoint

STATE = STARTED

AS TCP

(

LISTENER_PORT = 5022

)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE CLS_Certificate_N1_Private, ROLE = ALL,

ENCRYPTION = REQUIRED ALGORITHM AES

)

GO

3.4节点N2创建证书

-- ====================================

-- Execute the following code on N2

-- ====================================

USE master

GO

-- Create a database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd1!' GO

-- Create a new certificate

CREATE CERTIFICATE CLS_Certificate_N2_Private

WITH SUBJECT = 'CLS_Certificate_Private - N2',

START_DATE = '20160101'

GO

-- Backup the public key of the certificate to the filesystem BACKUP CERTIFICATE CLS_Certificate_N2_Private

TO FILE = 'c:\emp\\CLS_Certificate_N2_Public.cert'GO

-- Create an endpoint for the Availability Group CREATE ENDPOINT CLS_Endpoint

STATE = STARTED

AS TCP

(

LISTENER_PORT = 5022

)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE CLS_Certificate_N2_Private, ROLE = ALL,

ENCRYPTION = REQUIRED ALGORITHM AES

)

GO

3.5两节点创建登录互信，之前在各节点创建的证书两节点互相拷贝存放一份：

3.5.1节点N1创建用户登录

-- Execute the following code on N1

-- ====================================

-- Create login for the other node

CREATE LOGIN N2Login WITH PASSWORD = 'passw0rd1!'

GO

-- Create user for the login

CREATE USER N2User FOR LOGIN N2Login

GO

-- Import the public key portion of the certificate from the other nodeCREATE CERTIFICATE CLS_Certificate_N2_Public AUTHORIZATION N2User

FROM FILE = 'c:\emp\\CLS_Certificate_N2_Public.cert'

GO

-- Grant the CONNECT permission to the login

GRANT CONNECT ON ENDPOINT::CLS_Endpoint TO N2Login

GO

3.5.2节点N2创建用户登录

-- ====================================

-- Execute the following code on N2

-- ====================================

-- Create login for the other node

CREATE LOGIN N1Login WITH PASSWORD = 'passw0rd1!'

GO

-- Create user for the login

CREATE USER N1User FOR LOGIN N1Login

GO

-- Import the public key portion of the certificate from the other node CREATE CERTIFICATE CLS_Certificate_N1_Public AUTHORIZATION N1User

FROM FILE = 'c:\emp\\CLS_Certificate_N1_Public.cert'

GO

-- Grant the CONNECT permission to the login

GRANT CONNECT ON ENDPOINT::CLS_Endpoint TO N1LoginGO

3.6最后通过smss图形化操作创建可用性组即可。参考：http://blog.sina.com.cn/s/blog_14d5a51a90102wue6.html

3.6.1节点N1创建测试数据库，完整模式。

然后备份到c盘temp目录下：j.bak

创建可用性组向导。

3.6.2 节点N2还原数据库（restore with norecovery）

3.6.3节点N2数据库联接到可用性组

3.6.4节点N1操作表同步测试：

3.6.5节点N2验证同步结果：

3.6.6主节点配置(可选操作)：

两节点hosts文件添加名称解析：

测试链接：

4.主要参考文档：

https://www.sqlpassion.at/archive/2016/01/11/how-to-create-a-sql-server-availability-group-wit hout-an-active-directory-domain/