Sqlserver 2014 主從兩節(jié)點用腳本完成alwayson搭建

環(huán)境：
Sqlserver兩節(jié)點已經(jīng)完成故障轉(zhuǎn)移集群搭建：
節(jié)點01：以下簡稱01，為主節(jié)點
節(jié)點02：以下簡稱02，為輔助節(jié)點
數(shù)據(jù)庫服務(wù)的啟動賬號必須建立在數(shù)據(jù)庫用戶里面。

01：創(chuàng)建 EndPoint 并賦權(quán)
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 [數(shù)據(jù)庫服務(wù)啟動賬號，這里設(shè)置為service01]

go

02：創(chuàng)建 EndPoint并賦權(quán)

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：主節(jié)點數(shù)據(jù)庫備份
use master

GO

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

GO

02：還原數(shù)據(jù)庫備份
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：主節(jié)點數(shù)據(jù)庫日志備份
BACKUP log [test] TO  DISK = N'D:\dbbackup\test_log.bak' WITH COMPRESSION, NOFORMAT, NOINIT

GO

02：還原數(shù)據(jù)庫日志備份
restore log [test] from disk=N'D:\dbbackup\test_log.bak'

with norecovery,stats=5

go

01：主節(jié)點創(chuàng)建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：設(shè)置副本
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
查看同步狀態(tài)即可。
OK。

新加數(shù)據(jù)庫：
01：可用性組添加數(shù)據(jù)庫
ALTER AVAILABILITY GROUP [test_AlwaysOn] ADD DATABASE [xxx];
02：設(shè)置副本
ALTER DATABASE [xxx] SET HADR AVAILABILITY GROUP = [test_AlwaysOn];

新加節(jié)點：
02：加節(jié)點

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：連節(jié)點
ALTER AVAILABILITY GROUP test_AlwaysOn JOIN;

查詢數(shù)據(jù)庫備份執(zhí)行腳本：
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

查詢數(shù)據(jù)庫還原執(zhí)行腳本：
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上還原了數(shù)據(jù)庫日志備份之后測試下兩邊的5022端口就可以：
02：
ALTER DATABASE test SET PARTNER = N'TCP://主機(jī)IP:5022'
01：
ALTER DATABASE test SET PARTNER = N'TCP://備機(jī)IP:5022'