iT邦幫忙

第 12 屆 iT 邦幫忙鐵人賽

DAY 23
1
AI & Data

一名合格的DBA要從底層一步步爬起系列 第 23

《Day 23》SQLServer 鏡像建置

測試環境

Hostname IP
mirror-primary 172.31.31.160
mirror-standby 172.31.31.166

需求

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

Primary

對資料庫做完整備份

https://ithelp.ithome.com.tw/upload/images/20200926/20129969shogIVWOgX.png

Standby

還原資料庫(Norecovery狀態)

https://ithelp.ithome.com.tw/upload/images/20200926/20129969smNeKh4S0s.png

Primary

建立金鑰與憑證

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

https://ithelp.ithome.com.tw/upload/images/20200926/20129969gQBsI1sJ7J.png

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)
https://ithelp.ithome.com.tw/upload/images/20200926/20129969ClrpYwKLW4.png

還原至Standby

https://ithelp.ithome.com.tw/upload/images/20200926/20129969cQd89HUj4Z.png

建立鏡像

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

Primary

建立鏡像

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

https://ithelp.ithome.com.tw/upload/images/20200926/20129969sUWaSHmHBh.png


上一篇
《Day 22》SQLServer Log shipping建置
下一篇
《Day24》SQLServer資料庫快照
系列文
一名合格的DBA要從底層一步步爬起30

尚未有邦友留言

立即登入留言