Sqlserver 2014 主从两节点用脚本完成alwayson搭建

千家信息网最后更新 2025年02月02日Sqlserver 2014 主从两节点用脚本完成alwayson搭建环境：
Sqlserver两节点已经完成故障转移集群搭建：
节点01：以下简称01，为主节点
节点02：以下简称02，为辅助节点
数据库服务的启动账号必须建立在数据库用户里面。

01：创建 EndPoint 并赋权
create endpoint [Hadr_endpoint]

authorization sa

state=started

as tcp(listener_port=5022,listener_ip=all)

for database_mirroring(role=all,authentication=windows, ENCRYPTION = REQUIRED ALGORITHM RC4)

go

GRANT CONNECT ON ENDPOINT:: [Hadr_endpoint] TO [数据库服务启动账号，这里设置为service01]

go

02：创建 EndPoint并赋权

create endpoint [Hadr_endpoint]

authorization sa

state=started

as tcp(listener_port=5022,listener_ip=all)

for database_mirroring(role=all,authentication=windows, ENCRYPTION = REQUIRED ALGORITHM RC4)

go

GRANT CONNECT ON ENDPOINT:: [Hadr_endpoint] TO [service01]

go

01：主节点数据库备份
use master

GO

BACKUP DATABASE [test] TO DISK = N'D:\dbbackup\test_full.bak' WITH COMPRESSION, NOFORMAT, NOINIT

GO

02：还原数据库备份
use master

go

restore database [test] from disk=N'D:\dbbackup\test_full.bak'

with

move 'test' to 'e:\SQL_Data\test.mdf'

,move 'test_log' to 'e:\SQL_Data\test_log.ldf'

,norecovery,replace,stats=5

go
01：主节点数据库日志备份
BACKUP log [test] TO DISK = N'D:\dbbackup\test_log.bak' WITH COMPRESSION, NOFORMAT, NOINIT

GO

02：还原数据库日志备份
restore log [test] from disk=N'D:\dbbackup\test_log.bak'

with norecovery,stats=5

go

01：主节点创建AG
CREATE AVAILABILITY GROUP [test_AlwaysOn]

WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)

FOR DATABASE [test]

REPLICA ON

N'test01' WITH (

ENDPOINT_URL = N'TCP://test01.pousheng.com:5022'

,FAILOVER_MODE = MANUAL

,AVAILABILITY_MODE = SYNCHRONOUS_COMMIT

,BACKUP_PRIORITY = 50

,SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))

,N'test02' WITH (

ENDPOINT_URL = N'TCP://test02.pousheng.com:5022'

,FAILOVER_MODE = MANUAL

,AVAILABILITY_MODE = SYNCHRONOUS_COMMIT

,BACKUP_PRIORITY = 50

,SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

02：加入alwayson
ALTER AVAILABILITY GROUP test_AlwaysOn JOIN;
02：设置副本
ALTER DATABASE [test] SET HADR AVAILABILITY GROUP = [test_AlwaysOn];
01：修改同步模式为异步
ALTER AVAILABILITY GROUP [test_AlwaysOn] MODIFY REPLICA ON N'test02' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT)
go
查看同步状态即可。
OK。

新加数据库：
01：可用性组添加数据库
ALTER AVAILABILITY GROUP [test_AlwaysOn] ADD DATABASE [xxx];
02：设置副本
ALTER DATABASE [xxx] SET HADR AVAILABILITY GROUP = [test_AlwaysOn];

新加节点：
02：加节点

ALTER AVAILABILITY GROUP [test_AlwaysOn]

ADD REPLICA ON 'test01'

WITH (

ENDPOINT_URL = 'TCP://test01.pousheng.com:5022',

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

FAILOVER_MODE = MANUAL,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)

);
01：连节点
ALTER AVAILABILITY GROUP test_AlwaysOn JOIN;

查询数据库备份执行脚本：
select 'backup database ['+name+'] to disk=N''F:\backup\'+name+'.bak'' with STATS=5' as cmd
from sys.databases with(nolock)
where database_id>=4
order by name

查询数据库还原执行脚本：
with tb as (
select 'RESTORE DATABASE '+db_name(database_id)+' FROM Disk=N''e:\backup\'+db_name(database_id)+'.bak'' WITH RECOVERY, ' as db,'MOVE '''+name+''' TO ''f:\sqldata\'+SUBSTRING(physical_name,len(physical_name)-CHARINDEX('\',REVERSE(physical_name))+2,CHARINDEX('\',REVERSE(physical_name))-1)+'''' as fl From sys.master_files with(nolock)
)
SELECT db+[values] FROM( SELECT DISTINCT db FROM tb)A
OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE(( SELECT fl FROM tb N WHERE db = A.db FOR XML AUTO ), '', ''), 1, 1, ''))N order by db
go

如果只是建立mirror，不加入alwayson集群
就在02上还原了数据库日志备份之后测试下两边的5022端口就可以：
02：
ALTER DATABASE test SET PARTNER = N'TCP://主机IP:5022'
01：
ALTER DATABASE test SET PARTNER = N'TCP://备机IP:5022' ALTER DATABASE test SET SAFETY OFF
如果报错的话：
可能是5022端口没开放，
或者是对端的数据库用户里面没有加入数据库服务的启动账号。