在aws上搭建ad无域环境
windows server 2016 可以搭建无域环境
关闭防火墙

注意事项：

1，两个节点的Windos Server 2016 都已Administrator账户运行，并且两台服务器的Administrator密码相同。

2，两个节点的SQL Server 2016 服务启动账户都设置成Administrator

一、搭建无域

环境

windows server 2016 base
java 1.7
sqlserver 2017

windows server 2016 base
java 1.7
sqlserver 2017

1.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

eth0

eni

管理ip

管理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.com

172.31.41.49 sqlcluster
172.31.41.50 master.bb.com
172.31.41.53 slave.bb.com

1.4 修改主机名并加入域

每个节点的计算机不需要加入域，但需要添加DNS后缀，且每个节点的后缀必须要相同

修改主机名并加入域

重启系统

1.5 关闭防火墙

all node 执行

关闭防火墙

设置允许应用或功能通过windows防火墙

还是在刚刚的防火墙中点击设置允许应用或功能通过windows防火墙

file and print

1.6 配置ip和dns

master/slave 都执行

配置ip和dns

1.7统一修改admin密码

win + R -----> lusrmgr.msc

修改admin密码

添加用户，在每个节点上都添加一个用户DCadmin，且用户名以及密码每个节点都一致

user

二、安装win集群

2.1 安装Windows故障转移群集

所有节点都需要安装

安装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-ClusterResource

Power Shell 脚本：  New-Cluster -Name JF-AlwaysOn -Node MASTER, SLAVE -StaticAddress 172.31.41.49 -AdministrativeAccessPoint DNS 

--获取集群名
Get-Cluster  

--群集详情  
Get-ClusterResource

CLUS

使用Power Shell 安装群集后，在故障转移群集管理器中是看不到群集信息的，需要手动连接到群集，如下面图所示

CLUS

打开故障转移群集管理器后，在管理器处右键鼠标，选择“连接到群集”

在弹出的对话框中手动输入群集的名称

conn

这样就能在故障转移群集管理器中看到群集的信息了。同样的方法在slave的节点上再重复设置即可在slave上看到群集信息

conn

2.3配置SQL Server AlwaysOn

AlwaysOn

1在master/slave上启用AlwaysOn

AlwaysOn

启用AlwaysOn会要求重启服务，重启就可以

重启服务后，查看服务器属性，确保 HADR 为 True

HADR

既然节点没有加入域，那么就不能用域认证，只能用证书认证，因此需要创建证书和端点

因此在配置可用性组前先在各节点配置证书认证信任

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.endpoints

select * from sys.certificates
select * from sys.endpoints

3.配置可用性组

先备份下完整数据库，进行可用性组

HADR

4.最后创建侦听器

HADR

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))
GO

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))
GO

三、sqlserver 读写分离

http://note.youdao.com/noteshare?id=ce71cc7ffa4b4c50107724451a5b2fd1

3-Docker

1.安装

4-Containerd

5-Swarm

6-KVM

2-资源对象

2-Pod

3-Deployment

4-Service

3-存储

1- 存储卷概念

2-NFS

备份

4-网络

OpenELB

6-安装

2.kubeadm安装

8-备份

9-常用操作

10-Yaml配置

12-CICD

13-Ingress

1-Ingress_nginx

2-Higress

14-云k8s

15-Kubernetes故障排查

1-Go

2-Go框架

3-Go编译

5-Go文档

6-Go日志

7-Web前端开发

4-vue

5-vue架构

FAQ

1-PG

0-体系结构

1-插件安装

2-安装

3-基础

4-用户管理

5-集群

6-备份和恢复

7-监控

8-优化

9-FAQ

4-Mongodb

1-安装

3-DDL

4-备份管理

5-集群管理

5-MQ

4-备份

5-集群

6-监控

6-Etcd

0-原理

1-DDL

2-用户管理

3-备份

4-集群

1-nginx

2-ssl证书

3-proxy

1-Service

2-DNS

3-VPN

4-Git

5-Gitlab

7-Ansible

9-GateWay

10-Storage

1-Minio

2-ZFS

11-Monitor

12-Logs

1-vector

13-环境变量

CMD

1-aliCloud

一、搭建无域

1.1规划

1.2添加辅助ip

1.3 配置hosts文件

1.4 修改主机名并加入域

1.5 关闭防火墙

1.6 配置ip和dns

1.7统一修改admin密码

二、安装win集群

2.1 安装Windows故障转移群集

2.2 加入群集

2.3配置SQL Server AlwaysOn

1在master/slave上启用AlwaysOn

2.创建证书

3.配置可用性组

4.最后创建侦听器

三、sqlserver 读写分离