步骤
- 搭建域环境
- 配置故障转移集群
- 安装数据库
- 配置AlwaysOn
以下是系统规划
用途 |
public IP |
private IP |
名称 |
|
节点1 |
192.168.1.51 |
10.0.0.51 |
sqldb51 |
sqldb51.xxzx.com |
节点2 |
192.168.1.52 |
10.0.0.52 |
sqldb52 |
sqldb52.xxzx.com |
节点3 |
192.168.1.53 |
10.0.0.53 |
sqldb52 |
sqldb53.xxzx.com |
集群 |
192.168.1.61 |
|
sqldbcluster |
|
侦听器 |
192.168.1.62 |
|
sqldbvip |
|
域控制器+DNS |
192.168.1.50 |
10.0.0.50 |
AD |
AD.xxzx.com |
搭建域环境
public和private网卡相关设置
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1ODZkOTM0NTY5Mi5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1N2YzYjcyMTc5OS5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1OWE1ODc5MTY5Ny5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1N2NmMzE5OTcwMS5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1NzliNzA2MzMzNC5wbmc%3D.png?w=700&webp=1)
三台数据库节点主机加入域
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1N2Q0MTM1ODIzMy5wbmc%3D.png?w=700&webp=1)
创建域sqluser用户,并加入到三台数据库节点主机的Administrators组中
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1N2EzMzc2OTA3MS5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1ODA0MzU4NTYzNi5wbmc%3D.png?w=700&webp=1)
配置故障转移集群
三台节点主机上安装故障转移集群,创建集群
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1OGM3N2I5MDM2Ni5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1NmQ1MTg3MTcyNi5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1N2NlMDM1MjkzNi5wbmc%3D.png?w=700&webp=1)
安装数据库
三台节点主机上安装SQL Server 2022
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1NmVkZDYzNzg4Ny5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1NzgwZmQ3OTkwNy5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1OTE1MzYyMjg4LnBuZw%3D%3D.png?w=700&webp=1)
注意下面这个坑
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1NjI0YzA0OTA1My5wbmc%3D.png?w=700&webp=1)
安装SMSS,过程略
配置AlwaysOn
SQLDB51主机上创建共享目录 D:\SHARE
三台节点主机启用AlwaysOn功能,重启数据库引擎
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1ODFhYTI0MDA3OS5wbmc%3D.png?w=700&webp=1)
在SQLDB51主机上还原数据库,设置恢复模式为完全,并进行一次完整备份
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1OTAxYzEzMDA3My5wbmc%3D.png?w=700&webp=1)
创建AlwaysOn
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1NzU0ZDY0NjYyNC5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1ODJjMzI4NTEyMi5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1YzcyY2M0OTY5OC5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1ODRkZTI0NjEwNi5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1NmE0MTU2MTY5LnBuZw%3D%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1NmNhYmY1MzkxNC5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1ODQxZWUyODM3MC5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1ODA0MTgyOTQzNS5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1NmViMDMyNzY1Ni5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1N2VmMzQzMzY5LnBuZw%3D%3D.png?w=700&webp=1)
添加侦听器VIP
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1NzMwOWU1Njc2MS5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1ODc1Y2YxMTUxNi5wbmc%3D.png?w=700&webp=1)
切换测试
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1ODhkMTQxOTU1NS5wbmc%3D.png?w=700&webp=1)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUyOTU3XzY0MDk4YWY1N2ZiMjUyNzUyMi5wbmc%3D.png?w=700&webp=1)
配置读写分离
SELECT * FROM master.sys.availability_replicas
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUzMjQ2XzY0MDk4YjllOTI4NGUyOTgyMC5wbmc%3D.png?w=700&webp=1)
ALTER AVAILABILITY GROUP SQLDBAG
MODIFY REPLICA ON N'SQLDB51'
WITH(SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://SQLDB51.xxzx.com:1433'))
ALTER AVAILABILITY GROUP SQLDBAG
MODIFY REPLICA ON N'SQLDB52'
WITH(SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://SQLDB52.xxzx.com:1433'))
ALTER AVAILABILITY GROUP SQLDBAG
MODIFY REPLICA ON N'SQLDB53'
WITH(SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://SQLDB53.xxzx.com:1433'))
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUzMjQ2XzY0MDk4YjllN2EzN2U5ODcwMS5wbmc%3D.png?w=700&webp=1)
ALTER AVAILABILITY GROUP SQLDBAG
MODIFY REPLICA ON
N'SQLDB51' WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(('SQLDB51','SQLDB52','SQLDB53'))))
ALTER AVAILABILITY GROUP SQLDBAG
MODIFY REPLICA ON
N'SQLDB52' WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(('SQLDB51','SQLDB52','SQLDB53'))))
ALTER AVAILABILITY GROUP SQLDBAG
MODIFY REPLICA ON
N'SQLDB53'WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(('SQLDB51','SQLDB52','SQLDB53'))))
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUzMjQ2XzY0MDk4YjllNmY1Y2U3NDc5Mi5wbmc%3D.png?w=700&webp=1)
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
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUzMjQ2XzY0MDk4YjllNmU2MmIzOTE4OC5wbmc%3D.png?w=700&webp=1)
#sqldbvip为侦听器名称,BA为数据库名
sqlcmd -S sqldbvip -K ReadOnly -d BA -Q "select @@servername"
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUzMjQ2XzY0MDk4YjllOGJhMTA4MDk0Ny5wbmc%3D.png?w=700&webp=1)
备注
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUzMjQ2XzY0MDk4YjllN2I1MDcyMzU4OS5wbmc%3D.png?w=700&webp=1)
主角色中的连接
- 如果当前server是primary角色时,primary instance允许所有连接(如:读/写/管理)
- 如果当前server是primary角色时,primary instance只允许读/写连接(如果通过ssms连接,将报错、sqlcmd也是报错)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUzMjQ2XzY0MDk4YjllODM5ZGM1MzY2OC5wbmc%3D.png?w=700&webp=1)
可读辅助副本
- 是:如果当前server是primary角色时,所有的secondary servers都是可以看的(通过ssms能看结构、数据,但不能更改)
- 仅读意向:如果当前server是primary角色时,所有的secondary servers只允许读连接(需要在建立连接时加入key来标明为只读连接:Applicatinotallow=ReadOnly)
- 否:如果当前server是primary角色时,所有的secondary servers都不可以看(通过ssms能连接,但是看不了,会报错,如下)
![Windows2022+SQL2022 三节点AlwaysOn搭建 Windows2022+SQL2022 三节点AlwaysOn搭建](https://image.shishitao.com:8440/aHR0cHM6Ly9zMi41MWN0by5jb20vaW1hZ2VzL2Jsb2cvMjAyMzAzLzA5MTUzMjQ2XzY0MDk4YjllNmYyZjU5NDQ5MC5wbmc%3D.png?w=700&webp=1)