測試環境

需求

• 兩台主機必須是相同Windows登入帳號密碼
• SQLServer需是相同版本
• SQLServer與SQLServer Agent以同帳號執行
• 資料庫必須是完整復原模式

Primary

對資料庫做完整備份

Standby

還原資料庫(Norecovery狀態)

Primary

建立金鑰與憑證

create master key encryption by password='1qaz@WSX';
select * from sys.symmetric_keys  --查看金鑰

create certificate sql_primary with subject='sql_primary certificate';

建立鏡像端點

create endpoint SQL_mirror
state=started as tcp(
listener_port=5022,
listener_ip=all
)
for database_mirroring
(
authentication=certificate sql_primary,
encryption=required algorithm aes,
role=all
)
go

備份憑證，將憑證複製到standby上

backup certificate sql_primary to file='[backup_path]';

Standy

建立金鑰與憑證

create master key encryption by password='1qaz@WSX';
select * from sys.symmetric_keys  --查看金鑰

create certificate sql_standby with subject='sql_standby certificate';

建立鏡像端點

create endpoint SQL_mirror
state=started as tcp(
listener_port=5022,
listener_ip=all
)
for database_mirroring
(
authentication=certificate sql_standby,
encryption=required algorithm aes,
role=all
)
go

備份憑證，將憑證複製到primary上

backup certificate sql_standby to file='[backup_path]';

Primary

建立Login與User

create login MirrorAdmin with password='1qaz@WSX'
go

create user MirrorAdmin for login MirrorAdmin
go
--建立憑證sql-standby
create certificate sql_standby authorization
MirrorAdmin from file='G:\bk\sql_standby.cer'
go

將鏡像權限給與MirrorAdmin

grant connect on endpoint::SQL_mirror to MirrorAdmin
go

Standby

建立Login與User

create login MirrorAdmin with password='1qaz@WSX'
go

create user MirrorAdmin for login MirrorAdmin
go

--建立憑證sql-primary
create certificate sql_primary authorization
MirrorAdmin from file='G:\bk\sql_primary.cer'
go

將鏡像權限給與MirrorAdmin

grant connect on endpoint::SQL_mirror to MirrorAdmin
go

備份資料庫交易紀錄檔(Primary)

還原至Standby

建立鏡像

alter database test01 set partner='tcp://172.31.31.160:5022';

Primary

建立鏡像

alter database test01 set partner='tcp://172.31.31.166:5022';
go