SQL Server 2016 AlwaysON 非域部署脚本

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

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

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

1.所有成员都必须是Win Svr 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