SQL Server AlwaysOn

参考1：https://www.cnblogs.com/lyhabc/p/4678330.html

参考2：https://blog.csdn.net/weixin_40464436/article/details/89846065

参考3：https://blog.csdn.net/weixin_38357227/article/details/79033494

1.准备三台服务器

域控：win1

节点1：node1

节点2：node2

 

2.域控端，配置网络

去掉ipv6

修改ipv4,dns指向：127.0.0.1

设置客户端ip，注意要设置网关，禁用TCP/IP上的NetBIOS

 

3.域控端，安装AD域服务

服务器角色：Active Directory 域服务

配置：添加新林

 

4.域控端，添加域用户

Active Directory 用户和计算机--users--新建--用户

隶属于：administrators,dnsadmins,domain controllers,domain users,users，domain Computers,domain Admins

 

 

 

 

5.域控端，关闭防火墙

 

检查AD域服务和Netlogon服务是否正常启动

 

 

 

 

 

6.节点1，设置ip

去掉ipv6

修改ipv4 DNS指向：域控IP

禁用NetBIOS:高级--wins--禁用TCP/IP上的NetBIOS

 

7.节点1，加域

此电脑--属性--更改设置--域

在域控服务器DNS管理器中查看

 

8.节点1，添加组

管理员账号登录

打开计算机管理-》本地用户和组，选择组，选中Administrators组，右键-》添加到组

输入域用户

 

 

 

 

 

9.节点1，关闭防火墙

 

10.节点1，安装故障转移集群

 

11.节点2，重复以上所有节点1操作

 

12.节点1，使用域用户登录

 

13.节点1，打开故障转移集群管理器

13.1验证配置

 

 

 

 

 

 

13.2选择节点1和节点2（不用域控）

 

 

 

 

 

 

 

 

 

13.3管理群集的访问点：指定一个未使用的ip

 

 

 

 

 

 

 

 

 

 

 

 

 

14.节点2，打开故障转移集群管理器

连接节点1管理群集的访问点

 

15.域控端，建立共享文件夹

添加everyone和域用户所有权限

 

16.节点1，配置群集仲裁设置

打开故障转移集群管理器--右击访问点--更多操作--配置群集仲裁设置

 

 

选择仲裁见证--配置文件共享见证--文件共享路径

 

 

 

 

 

17.节点1和节点2，安装sqlserver

 修改SQL server代理服务和SQL server服务，登录身份为：域用户

 

 

 

修改SQL server服务：启用alwayson

 

 

 

重启服务

 

18.节点1和节点2，添加域用户到sqlserver

sa用户登录--安全性--登录名--新建登录名--搜索域用户--服务器角色（选中sysadmin）

 

 

 

 

 

 

 

19.节点1，新建数据库

备份--拷贝到节点2

--在节点1上执行
DECLARE @CurrentTime VARCHAR(50), @FileName VARCHAR(200)
SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120 ),'-','_'),' ','_'),':','')

        
--(test 数据库完整备份)
SET @FileName = 'D:\DB\Backup\test_FullBackup_' + @CurrentTime+'.bak'
BACKUP DATABASE [testDB]
TO DISK=@FileName WITH FORMAT ,COMPRESSION


  --(test 数据库日志备份) 
SET @FileName = 'D:\DB\Backup\test_logBackup_' + @CurrentTime+'.bak'
BACKUP log [testDB]
TO DISK=@FileName WITH FORMAT ,COMPRESSION

　　

 

20.节点2，还原数据库

选项--恢复状态--选择：RESTORE WITH NORECOVERY

数据库显示：正在还原...

--在节点2上执行

USE [master]
RESTORE DATABASE [testDB] FROM  DISK = N'D:\DB\Backup\test_FullBackup_2022_09_24_100309.bak' WITH  FILE = 1, 
MOVE N'testDB' TO N'D:\DB\testDB.mdf', 
MOVE N'testDB_log' TO N'D:\DB\testDB_log.ldf', 
NOUNLOAD,NORECOVERY,  REPLACE,  STATS = 5

GO



--注意一定要用NORECOVERY来还原备份
USE [master]
RESTORE DATABASE [testDB] FROM  DISK = N'D:\DB\Backup\test_logBackup_2022_09_24_100309.bak' WITH  FILE = 1, 
NOUNLOAD,NORECOVERY,  REPLACE,  STATS = 5

GO

　　

消息 3234，级别 16，状态 2，第 2 行
逻辑文件 'Business' 不是数据库 'Business' 的一部分。请使用 RESTORE FILELISTONLY 来列出逻辑文件名。
消息 3013，级别 16，状态 1，第 2 行
RESTORE DATABASE 正在异常终止。

需先建一个空的数据库再进行还原

 

21.节点1，配置alwayson高可用性

新建可用性组向导

 

 

 

 

 

 

 

指定副本--添加副本--勾选自动故障转移和同步提交（主和辅助）--可读辅助副本：是（辅助）

 

 

端点：端点URL改成IP，sql server 服务账号是域用户

 

 

 

 

 

选择数据同步：仅联接

 

 

 

22.节点1，配置可用性组侦听器

添加侦听器，端口1433，静态IP

IPv4地址：填一个未使用的IP

 

 

用侦听器DNS名称登录sqlserver

 

 

 

 

 

23.节点1，手动故障转移（节选可不配）

右击（主要）--故障转移

 

 

连接副本

 

查看节点2，已经转移

节点2--右击（主要）--属性--角色（辅助）--可读性辅助副本：是

 

 

AlwaysOn相关视图

--通过这两个视图可以查询AlwaysOn延迟
SELECT  b.replica_server_name ,
        a.*
FROM    sys.dm_hadr_database_replica_states a
        INNER JOIN sys.availability_replicas b ON a.replica_id = b.replica_id 

--可用性组所在Windows故障转移集群
SELECT * FROM sys.dm_hadr_cluster;
SELECT * FROM sys.dm_hadr_cluster_members ;
SELECT * FROM sys.dm_hadr_cluster_networks;
SELECT * FROM sys.dm_hadr_instance_node_map;
SELECT * FROM sys.dm_hadr_name_id_map

--可用性组
SELECT * FROM sys.availability_groups;
SELECT * FROM sys.availability_groups_cluster;
SELECT * FROM sys.dm_hadr_availability_group_states ;
SELECT * FROM sys.dm_hadr_automatic_seeding
SELECT * FROM sys.dm_hadr_physical_seeding_stats

--可用性副本
SELECT * FROM sys.availability_replicas;
SELECT * FROM sys.[availability_read_only_routing_lists]
SELECT * FROM sys.dm_hadr_availability_replica_cluster_nodes;
SELECT * FROM sys.[dm_hadr_availability_replica_cluster_states]
SELECT * FROM sys.[dm_hadr_availability_replica_states]

--可用性数据库
SELECT * FROM sys.availability_databases_cluster;
SELECT * FROM sys.dm_hadr_database_replica_cluster_states;
SELECT * FROM sys.[dm_hadr_auto_page_repair]
SELECT * FROM sys.[dm_hadr_database_replica_states]

--可用性组listener
SELECT * FROM sys.availability_group_listener_ip_addresses;
SELECT * FROM sys.availability_group_listeners;
SELECT * FROM sys.dm_tcp_listener_states;



--添加只读路由列表
ALTER AVAILABILITY GROUP [agtest2]
MODIFY REPLICA ON N'WIN-5PMSDHUI0KQ' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS= READ_ONLY));


ALTER AVAILABILITY GROUP [agtest2]
modify REPLICA ON N'WIN-5PMSDHUI0KQ' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://192.168.66.157:1433'))

ALTER AVAILABILITY GROUP [agtest2]
MODIFY REPLICA ON N'WIN-4AE61RVA6UV' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS= READ_ONLY));

ALTER AVAILABILITY GROUP [agtest2]
modify REPLICA ON N'WIN-4AE61RVA6UV' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://192.168.66.158:1433'))