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'