基于证书的sqlserver镜像搭建

192.168.3.13  node1

192.168.3.10  node2

use master
go
create master key encryption by password='$a123456'
go

主库上执行：

use master
go
create certificate mirror01_cert with subject='mirror01 certificate',expiry_date='2099-1-1'
go

镜像库上执行：

use master
go
create certificate mirror02_cert with subject='mirror02 certificate',expiry_date='2099-1-1'
go

主库上执行：

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

镜像库上执行：

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

主库上执行：

use master
go
backup certificate mirror01_cert to file = 'D:\cert\mirror01_cert.cer'
go

镜像库上执行：

use master
go
backup certificate mirror02_cert to file = 'D:\cert\mirror02_cert.cer'
go

主库上执行：

use master
go
create login mirror02_login with password='abc@123456'
go

镜像库上执行：

use master
go
create login mirror01_login with password='abc@123456'
go

主库上执行：

use master
go
create user mirror02_user for login mirror02_login
go

镜像库上执行：

use master
go
create user mirror01_user for login mirror01_login
go

主库上执行：

use master
go
create certificate mirror02_cert
authorization mirror02_user
from file='D:\cert\mirror02_cert.cer'
go

镜像库上执行：

use master
go
create certificate mirror01_cert
authorization mirror01_user
from file='D:\cert\mirror01_cert.cer'
go

命令：

主库上执行：

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror02_login];
go

镜像库上执行：

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror01_login];
go

1.完整备份

BACKUP DATABASE [test] TO  DISK = N'D:\bak\test.bak' WITH NOFORMAT, INIT,  NAME = N'test-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'test' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'test' )

if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“test”的备份信息。', 16, 1) end

RESTORE VERIFYONLY FROM  DISK = N'D:\bak\test.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

GO

2.事物日志备份

BACKUP LOG [test] TO  DISK = N'D:\bak\test_log.tran' WITH NOFORMAT, INIT,  NAME = N'test-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'test' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'test' )

if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“test”的备份信息。', 16, 1) end

RESTORE VERIFYONLY FROM  DISK = N'D:\bak\test_log.tran' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

GO

1.还原完整备份

USE [master]

RESTORE DATABASE [test] FROM  DISK = N'D:\bak\test.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

2.还原事物日志

RESTORE LOG [test] FROM  DISK = N'D:\bak\ test.tran' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

镜像库上执行：(做完这个操作后,数据库的状态:正在恢复)

use master
go
ALTER DATABASE test SET PARTNER = 'TCP://node1:5022';
go

主库上执行：(做完这个操作,镜像就搭建好了)

use master
go
ALTER DATABASE test SET PARTNER = 'TCP://node2:5022';
go

第一步:脱离镜像
alter database test set partner off

第二步;修改数据库的状态为recovery
restore database test with recovery