注：搭了2次，所以代码中IP可能不一致，请自行替换下即可

    大致步骤以 --1  --2 标出，跳跃下看，因为是从主、副SSMS里复制的

    参考了宋云剑翻译的SQL SERVER 2012/14管理，ITPUB上jieyancai的贴子

1.所有成员都必须是Windows Server 2016

2.所有成员创建相同的帐号、密码，且属于本地Administrators组

3.所有SVR POWERSHELL：new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1

  重启

4.所有成员设置DNS后缀 noad.com

5.服务器都有一个共同的DNS服务器地址进行注册（或者自己添加A记录），包含DNS后缀。如果没有共同的DNS服务器进行注册，那就在每台写上host记录

6.检查防火墙响应的协议是否打开

7.安装群集角色

7.New-Cluster –Name <Cluster Name> -Node <Nodes to Cluster> -–StaticAddress <Cluster IP> -AdministrativeAccessPoint DNS

  示例：New-Cluster –Name NOADCluster -Node Node1,Node2 -NoStorage -StaticAddress 192.168.112.130 -AdministrativeAccessPoint DNS

仲裁配置：

  见证类型部队与工作组群集和多域群集建议采用云见证或磁盘见证。不支持文件共享见证。

群集验证中以下信息可以忽略：

  群集配置-验证资源状况

  系统配置-验证AD配置

建议在群集中的每一个节点都采用相同的配置，例如相同的补丁，相同的策略等。

确保群集所有节点的信息可以在权威DNS服务器上得到解析。

在工作组群集中是不支持Get-ClusterDiagnostics命令进行群集诊断的。

这时打开故障转移群集控制台可以看见创建的工作组群集。

hosts:

N1: 192.168.112.140  N1.noad.com

N2: 192.168.112.141  N2.noad.com

打开网络发现

Storage: 192.168.112.135

N1: 192.168.112.136  169.254.192.168

N2: 192.168.112.137  169.254.77.185

建群集、开防火墙

--1.

Create Database DB1

ON PRIMARY (Name='DB1',FileName='C:\Data\DB1.mdf',Size=100MB,FileGrowth=10MB)

LOG ON (Name='DB1_Log',FileName='C:\Data\DB1_Log.ldf',Size=100MB,FileGrowth=10MB);

Create Database DB2

ON PRIMARY (Name='DB2',FileName='C:\Data\DB2.mdf',Size=100MB,FileGrowth=10MB)

LOG ON (Name='DB2_Log',FileName='C:\Data\DB2_Log.ldf',Size=100MB,FileGrowth=10MB);

GO

Alter Database DB1 SET RECOVERY FULL;

Alter Database DB2 SET RECOVERY FULL;

GO

Use Master

Go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'dba123!@#'

Go

CREATE CERTIFICATE CertA WITH SUBJECT = 'Cert A'

Go

CREATE ENDPOINT Endpoint_Mirroring

 STATE = STARTED

   AS TCP (LISTENER_PORT=7022,LISTENER_IP = ALL) 

   FOR DATABASE_MIRRORING 

   (AUTHENTICATION = CERTIFICATE CertA,

    ENCRYPTION = REQUIRED ALGORITHM AES,

    ROLE = ALL);

Go

BACKUP CERTIFICATE CertA TO FILE = '\\N2\Setup\CertA.cer';

--Copy CertA.cer 到备用机

GO

--3.

CREATE LOGIN LoginB WITH PASSWORD = 'dba123!@#';

Go

CREATE USER UserB FOR LOGIN LoginB

Go

CREATE CERTIFICATE CertB AUTHORIZATION UserB FROM FILE='\\N2\Setup\CertB.cer'

Go

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [LoginB];

---------

Go

ALTER DATABASE DB1 SET TRUSTWORTHY ON;

ALTER DATABASE DB2 SET TRUSTWORTHY ON;

Go

--5.

Backup Database DB1 TO Disk='\\N2\Setup\DB1.Bak' With Format;

Backup Database DB2 TO Disk='\\N2\Setup\DB2.Bak' With Format;

--7

GO

CHECKPOINT;

Backup Log DB1 TO Disk='\\N2\Setup\DB1_Log.Bak';

Backup Log DB2 TO Disk='\\N2\Setup\DB2_Log.Bak';

--8

CREATE AVAILABILITY GROUP MyAG 

   FOR DATABASE DB1, DB2 

   REPLICA ON 

      'N1' WITH 

         (ENDPOINT_URL = 'TCP://N1.noad.com:7022',

         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

         FAILOVER_MODE = AUTOMATIC),

      'N2' WITH 

         (ENDPOINT_URL = 'TCP://N2.noad.com:7022',

         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

         FAILOVER_MODE = AUTOMATIC); 

GO

--9

ALTER AVAILABILITY GROUP MyAG 

 MODIFY REPLICA ON 'N2' WITH (SECONDARY_ROLE  (ALLOW_CONNECTIONS = ALL));

ALTER AVAILABILITY GROUP MyAG 

 MODIFY REPLICA ON 'N2' WITH (FAILOVER_MODE=MANUAL);

ALTER AVAILABILITY GROUP MyAG 

 MODIFY REPLICA ON 'N2' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

GO

--11  监听器

ALTER AVAILABILITY GROUP MyAG 

  Add Listener 'MyLT' (WITH IP (('192.168.112.120','255.255.255.0')),PORT=1433);

--副本路径

ALTER AVAILABILITY GROUP MyAG

 MODIFY REPLICA ON N'N1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://N1.noad.com:1433'));

ALTER AVAILABILITY GROUP MyAG

 MODIFY REPLICA ON N'N2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://N2.noad.com:1433'));

--只读路由

ALTER AVAILABILITY GROUP MyAG

 MODIFY REPLICA ON N'N1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('N2','N1')));

ALTER AVAILABILITY GROUP MyAG

 MODIFY REPLICA ON N'N2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('N1','N2')));

GO

--TEST:

Use DB1;

Create Table T1 (ID INT,Qty INT,Constraint PK_T1 Primary key (ID));

INSERT T1 (ID,Qty) Values (1,10);

INSERT T1 (ID,Qty) Values (2,20);

INSERT T1 (ID,Qty) Values (3,30);

Use DB2;

Create Table T1 (ID INT,Qty INT,Constraint PK_T1 Primary key (ID));

INSERT T1 (ID,Qty) Values (1,10);

INSERT T1 (ID,Qty) Values (2,20);

INSERT T1 (ID,Qty) Values (3,30);

--2

Use Master

Go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'dba123!@#';

GO

CREATE CERTIFICATE CertB WITH SUBJECT = 'CertB for Database Mirroring';

Go

CREATE ENDPOINT Endpoint_Mirroring

 STATE = STARTED

 AS TCP (LISTENER_PORT=7022,LISTENER_IP = ALL) 

 FOR DATABASE_MIRRORING 

  (AUTHENTICATION = CERTIFICATE CertB,

   ENCRYPTION = REQUIRED ALGORITHM AES,

   ROLE = ALL);

Go

BACKUP CERTIFICATE CertB TO FILE = 'C:\Setup\CertB.cer';

--Copy CertB.cer 到正式机

GO

--4

CREATE LOGIN LoginA WITH PASSWORD = 'dba123!@#';

Go

CREATE USER UserA FOR LOGIN LoginA;

Go

CREATE CERTIFICATE CertA AUTHORIZATION UserA FROM FILE = 'C:\Setup\CertA.cer';

Go

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [LoginA];

GO

--6

ALTER AVAILABILITY GROUP MyAG JOIN;

GO

--8

Restore Database DB1 From Disk='C:\Setup\DB1.Bak' With NORECOVERY;

Restore Database DB2 From Disk='C:\Setup\DB2.Bak' With NORECOVERY;

GO

Restore Log DB1 From Disk='C:\Setup\DB1_Log.bak' With NORECOVERY;

Restore Log DB2 From Disk='C:\Setup\DB2_Log.bak' With NORECOVERY;

GO

10

ALTER DATABASE DB1 SET HADR AVAILABILITY GROUP = MyAG;

GO

ALTER DATABASE DB2 SET HADR AVAILABILITY GROUP = MyAG;

GO

转载自：http://blog.csdn.net/luckyrandom/article/details/53089058