无AD域搭建server 2016搭建always on

因为没有AD域服务器，这里可以通过本地DNS解析来完成
1、配置host文件
192.168.153.131 WIN-TEU6L60VL5Q.swp.com
192.168.153.132 WIN-AHD72IOQ24H.swp.com
192.168.153.133 win-cluster.swp.com
192.168.153.134 sqlcluster.swp.com
2、分别在两台机器上更改主机名

3、添加故障转移功能

4、关闭服务器的防火墙

5、创建故障转移集群





后边篇幅比较长就不截图了
6、分别在两个节点数据库上创建证书，并且彼此还原对方的证书
将证书copy到对方服务器

主节点

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Admin@123456'
CREATE CERTIFICATE cer_alwayson_021
WITH SUBJECT='alwayson 021 local certificate',
EXPIRY_DATE='9999-12-31'
EXEC xp_create_subdir 'D:\data\mssql\cerficates'
BACKUP CERTIFICATE cer_alwayson_021
TO FILE='D:\data\mssql\cerficates\cer_alwayson_021.cer'
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE cer_alwayson_021, 
ENCRYPTION = REQUIRED ALGORITHM AES, 
ROLE = ALL)
CREATE LOGIN alwayson_user 
WITH PASSWORD='Admin@123456',
CHECK_POLICY=OFF
USE MASTER
GO
CREATE USER alwayson_user FOR LOGIN alwayson_user

CREATE CERTIFICATE cer_alwayson_022
AUTHORIZATION alwayson_user
FROM FILE='D:\data\mssql\cerficates\cer_alwayson_022.cer'

--step3:grant connection right
GRANT CONNECT ON ENDPOINT:: Endpoint_Mirroring TO alwayson_user

另外一个节点

--step1
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Admin@123456'
CREATE CERTIFICATE cer_alwayson_022
WITH SUBJECT='alwayson 022 local certificate',
EXPIRY_DATE='9999-12-31'
EXEC xp_create_subdir 'D:\data\mssql\cerficates'
BACKUP CERTIFICATE cer_alwayson_022
TO FILE='D:\data\mssql\cerficates\cer_alwayson_022.cer'
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE cer_alwayson_022, 
ENCRYPTION = REQUIRED ALGORITHM AES, 
ROLE = ALL)

CREATE LOGIN alwayson_user 
WITH PASSWORD='Admin@123456',
CHECK_POLICY=OFF
USE MASTER
GO
CREATE USER alwayson_user FOR LOGIN alwayson_user

CREATE CERTIFICATE cer_alwayson_021
AUTHORIZATION alwayson_user
FROM FILE='D:\data\mssql\cerficates\cer_alwayson_021.cer'

--step3:grant connection right
GRANT CONNECT ON ENDPOINT:: Endpoint_Mirroring TO alwayson_user

7、开启alwayson


ps:开启后要重启sqlserver的服务
8、创建可用性组
ps:在操作之前记得要备份同步的数据库







9、创建完成