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'

亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>