SRE文档在aws上搭建ad无域环境
windows server 2016 可以搭建无域环境
关闭防火墙
注意事项：
1，两个节点的Windos Server 2016 都已Administrator账户运行，并且两台服务器的Administrator密码相同。
2，两个节点的SQL Server 2016 服务启动账户都设置成Administrator
一、搭建无域
环境
windows server 2016 base
java 1.7
sqlserver 2017windows server 2016 base
java 1.7
sqlserver 20171.1规划
| 名称 | ip | 辅助ip (监听器添加一个或者两个) | 描述 |
|---|---|---|---|
| master | 172.31.41.50 | 172.31.41.54, 172.31.41.55 | 主数据库 |
| slave | 172.31.41.53 | 172.31.41.51, 172.31.41.52 | 从数据库 |
| ad群集 | 172.31.41.49 | x.x.x.x | 加入集群时ip地址 |
| AlwaysOn | 172.31.41.50 | x.x.x.x | 共享地址用于ssl证书和数据库备份地址 |
| ListenIP | 172.31.41.54or172.31.41.51 | x.x.x.x | 对外访问ip |
1.2添加辅助ip
- eth0------> eni
- eni
- 管理ip
1.3 配置hosts文件
- master/ slave 都执行
C:\Windows\System32\drivers\etc 打开文件hosts
添加群集信息
172.31.41.49 sqlcluster
172.31.41.50 master.bb.com
172.31.41.53 slave.bb.com172.31.41.49 sqlcluster
172.31.41.50 master.bb.com
172.31.41.53 slave.bb.com1.4 修改主机名并加入域
- 每个节点的计算机不需要加入域，但需要添加DNS后缀，且每个节点的后缀必须要相同
重启系统
1.5 关闭防火墙
- all node 执行
设置允许应用或功能通过windows防火墙
还是在刚刚的防火墙中点击设置允许应用或功能通过windows防火墙
file and print
1.6 配置ip和dns
- master/slave 都执行
1.7统一修改admin密码
win + R -----> lusrmgr.msc
- 添加用户，在每个节点上都添加一个用户DCadmin，且用户名以及密码每个节点都一致
二、安装win集群
2.1 安装Windows故障转移群集
- 所有节点都需要安装
重启系统
2.2 加入群集
在DB1这个节点上，用以管理员方式运行Power Shell ，使用脚本创建Windows群集，也可以使用图形创建群集
注意：如果登陆Windows Server 2016服务器的账户不是Administrator，需要先以管理员方式运行PowerShell，执行下面的命令：
new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
因为我登陆Windows Server 2016用的是Administrator，所以跳过执行上面的命令，直接运行下面的命令：
Power Shell 脚本： New-Cluster -Name JF-AlwaysOn -Node MASTER, SLAVE -StaticAddress 172.31.41.49 -AdministrativeAccessPoint DNS
--获取集群名
Get-Cluster
--群集详情
Get-ClusterResourcePower Shell 脚本： New-Cluster -Name JF-AlwaysOn -Node MASTER, SLAVE -StaticAddress 172.31.41.49 -AdministrativeAccessPoint DNS
--获取集群名
Get-Cluster
--群集详情
Get-ClusterResource
使用Power Shell 安装群集后，在故障转移群集管理器中是看不到群集信息的，需要手动连接到群集，如下面图所示
打开故障转移群集管理器后，在管理器处右键鼠标，选择“连接到群集”
在弹出的对话框中手动输入群集的名称
这样就能在故障转移群集管理器中看到群集的信息了。同样的方法在slave的节点上再重复设置即可在slave上看到群集信息
2.3配置SQL Server AlwaysOn
1在master/slave上启用AlwaysOn
启用AlwaysOn会要求重启服务，重启就可以
- 重启服务后，查看服务器属性，确保 HADR 为 True
既然节点没有加入域，那么就不能用域认证，只能用证书认证，因此需要创建证书和端点
- 因此在配置可用性组前先在各节点配置证书认证信任
2.创建证书
- 分别在两个节点数据库上创建证书，并且彼此还原对方的证书，SQL代码
-共享文件夹路径： ---\\MASTER\SQLAlwaysOnShare
--节点一上执行：创建主密钥/证书/端点，备份证书。
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'JFAlwaysOnShare2016'; ----administrator密码
GO
CREATE CERTIFICATE Cert_DB01
WITH SUBJECT = 'Cert_DB01',
START_DATE = '2017-12-01',EXPIRY_DATE = '2099-12-31';
GO
BACKUP CERTIFICATE Cert_DB01
TO FILE = '\\MASTER\SQLAlwaysOnShare\Cert_DB01.cer';
GO
CREATE ENDPOINT [SQLAG_Endpoint]
AUTHORIZATION [MASTER\administrator]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING
(ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_DB01, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
--节点二上执行：创建主密钥/证书，备份证书。
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'JFAlwaysOnShare2016';
GO
CREATE CERTIFICATE Cert_DB02
WITH SUBJECT = 'Cert_DB02',
START_DATE = '2017-12-01',EXPIRY_DATE = '2099-12-31';
GO
BACKUP CERTIFICATE Cert_DB02
TO FILE = '\\MASTER\SQLAlwaysOnShare\Cert_DB02.cer';
GO
CREATE ENDPOINT [SQLAG_Endpoint]
AUTHORIZATION [SLAVE\administrator]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING
(ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_DB02, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
--节点一上执行：创建节点二的证书
USE master;
GO
CREATE CERTIFICATE Cert_DB02
FROM FILE = '\\MASTER\SQLAlwaysOnShare\Cert_DB02.cer';
GO
--节点二上执行：创建节点一的证书
USE master;
GO
CREATE CERTIFICATE Cert_DB01
FROM FILE = '\\MASTER\SQLAlwaysOnShare\Cert_DB01.cer';
GO-共享文件夹路径： ---\\MASTER\SQLAlwaysOnShare
--节点一上执行：创建主密钥/证书/端点，备份证书。
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'JFAlwaysOnShare2016'; ----administrator密码
GO
CREATE CERTIFICATE Cert_DB01
WITH SUBJECT = 'Cert_DB01',
START_DATE = '2017-12-01',EXPIRY_DATE = '2099-12-31';
GO
BACKUP CERTIFICATE Cert_DB01
TO FILE = '\\MASTER\SQLAlwaysOnShare\Cert_DB01.cer';
GO
CREATE ENDPOINT [SQLAG_Endpoint]
AUTHORIZATION [MASTER\administrator]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING
(ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_DB01, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
--节点二上执行：创建主密钥/证书，备份证书。
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'JFAlwaysOnShare2016';
GO
CREATE CERTIFICATE Cert_DB02
WITH SUBJECT = 'Cert_DB02',
START_DATE = '2017-12-01',EXPIRY_DATE = '2099-12-31';
GO
BACKUP CERTIFICATE Cert_DB02
TO FILE = '\\MASTER\SQLAlwaysOnShare\Cert_DB02.cer';
GO
CREATE ENDPOINT [SQLAG_Endpoint]
AUTHORIZATION [SLAVE\administrator]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING
(ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_DB02, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
--节点一上执行：创建节点二的证书
USE master;
GO
CREATE CERTIFICATE Cert_DB02
FROM FILE = '\\MASTER\SQLAlwaysOnShare\Cert_DB02.cer';
GO
--节点二上执行：创建节点一的证书
USE master;
GO
CREATE CERTIFICATE Cert_DB01
FROM FILE = '\\MASTER\SQLAlwaysOnShare\Cert_DB01.cer';
GO- 删除证书秘钥
--删除终结点
drop ENDPOINT [group0_endpoint];
go
--删除证书
DROP CERTIFICATE CERT1;
--删除主密钥
drop master key
go--删除终结点
drop ENDPOINT [group0_endpoint];
go
--删除证书
DROP CERTIFICATE CERT1;
--删除主密钥
drop master key
go- 查看证书与终结点
select * from sys.certificates
select * from sys.endpointsselect * from sys.certificates
select * from sys.endpoints3.配置可用性组
- 先备份下完整数据库，进行可用性组
4.最后创建侦听器
select name,* from sys.availability_groups
SELECT * FROM sys.dm_hadr_cluster_members;
SELECT * FROM master.sys.availability_replicas
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'MASTER' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SLAVE','MASTER')));
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SLAVE' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MASTER','SLAVE')));
SELECT ar.replica_server_name ,
rl.routing_priority ,
( SELECT ar2.replica_server_name
FROM sys.availability_read_only_routing_lists rl2
JOIN sys.availability_replicas AS ar2 ON rl2.read_only_replica_id = ar2.replica_id
WHERE rl.replica_id = rl2.replica_id
AND rl.routing_priority = rl2.routing_priority
AND rl.read_only_replica_id = rl2.read_only_replica_id
) AS 'read_only_replica_server_name'
FROM sys.availability_read_only_routing_lists rl
JOIN sys.availability_replicas AS ar ON rl.replica_id = ar.replica_id
SELECT * FROM [General]..sysusers
SELECT * FROM [storm]..sysusers
CREATE LOGIN [samaster] WITH PASSWORD=N'master123',
SID =0x99AD266AFD26F841B3E49EF9633B0D4B, DEFAULT_DATABASE=[storm],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
USE [master]
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'MASTER' WITH (PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE))
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'MASTER' WITH (PRIMARY_ROLE(ALLOW_CONNECTIONS = all))
GOselect name,* from sys.availability_groups
SELECT * FROM sys.dm_hadr_cluster_members;
SELECT * FROM master.sys.availability_replicas
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'MASTER' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SLAVE','MASTER')));
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SLAVE' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MASTER','SLAVE')));
SELECT ar.replica_server_name ,
rl.routing_priority ,
( SELECT ar2.replica_server_name
FROM sys.availability_read_only_routing_lists rl2
JOIN sys.availability_replicas AS ar2 ON rl2.read_only_replica_id = ar2.replica_id
WHERE rl.replica_id = rl2.replica_id
AND rl.routing_priority = rl2.routing_priority
AND rl.read_only_replica_id = rl2.read_only_replica_id
) AS 'read_only_replica_server_name'
FROM sys.availability_read_only_routing_lists rl
JOIN sys.availability_replicas AS ar ON rl.replica_id = ar.replica_id
SELECT * FROM [General]..sysusers
SELECT * FROM [storm]..sysusers
CREATE LOGIN [samaster] WITH PASSWORD=N'master123',
SID =0x99AD266AFD26F841B3E49EF9633B0D4B, DEFAULT_DATABASE=[storm],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
USE [master]
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'MASTER' WITH (PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE))
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'MASTER' WITH (PRIMARY_ROLE(ALLOW_CONNECTIONS = all))
GO三、sqlserver 读写分离
http://note.youdao.com/noteshare?id=ce71cc7ffa4b4c50107724451a5b2fd1