Sql数据库镜像实战记录

 备份数据库A：

 

 

在数据库B还原：

restore database dbName
from disk='E:\db_backup\XXXX.bak'
with  
NORECOVERY, REPLACE
go 

 

说明：A是主数据库，B是从数据库

1. 先设置A：

--创建主密钥

USE master;

--DROP MASTER KEY;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxx';

GO

CREATE LOGIN MIR_M_LOGIN WITH PASSWORD = xxxxxx';

GO

CREATE USER MIR_M_USER FOR LOGIN MIR_M_LOGIN;

GO

--创建数据库证书

USE master;

CREATE CERTIFICATE MIR_HOST

   WITH SUBJECT = 'MIR_HOST certificate for database mirroring',

      start_date = '01/01/2008',EXPIRY_DATE = '10/31/2099' ;

GO

--建立出站端点

CREATE ENDPOINT EMP_HOST

   STATE = STARTED

   AS TCP (LISTENER_PORT=5025, LISTENER_IP = ALL )

   FOR DATABASE_MIRRORING (

      AUTHENTICATION = CERTIFICATE MIR_HOST

      , ENCRYPTION = REQUIRED ALGORITHM RC4

      , ROLE = ALL

   );

GO

 

--配置端点

GRANT CONNECT ON ENDPOINT::EMP_HOST TO [MIR_M_LOGIN];

GO

--激活端点

ALTER ENDPOINT EMP_HOST STATE=STARTED

GO

 

--保存导出证书，拷贝到主服务器，将需要在主服务器导入

BACKUP CERTIFICATE MIR_HOST TO FILE = 'D:\webroot\MIR_HOST.cer';

GO

2.设置B：

USE master;

--创建主密钥

--drop MASTER KEY;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxx';

GO

CREATE LOGIN MIR_M_LOGIN WITH PASSWORD = 'xxxxxx';

GO

USE master;

CREATE USER MIR_M_USER FOR LOGIN MIR_M_LOGIN;

GO

--创建数据库证书

CREATE CERTIFICATE MIR_GUEST

   WITH SUBJECT = 'MIR_GUEST certificate for database mirroring',

      start_date = '01/01/2008',EXPIRY_DATE = '10/31/2099' ;

GO

--建立出站端点

CREATE ENDPOINT EMP_GUEST

   STATE = STARTED

   AS TCP (LISTENER_PORT=5025, LISTENER_IP = ALL)

   FOR DATABASE_MIRRORING (

      AUTHENTICATION = CERTIFICATE MIR_GUEST

      , ENCRYPTION = REQUIRED ALGORITHM RC4

      , ROLE = ALL

   );

GO

 

--配置端点

GRANT CONNECT ON ENDPOINT::EMP_GUEST TO [MIR_M_LOGIN];

GO

--激活端点

ALTER ENDPOINT EMP_GUEST STATE=STARTED

GO

 

--保存导出证书，拷贝到从服务器，将需要在从服务器导入

BACKUP CERTIFICATE MIR_GUEST TO FILE = 'D:\webroot\MIR_GUEST.cer';

GO

 

3.互换证书

4.导入证书：

CREATE CERTIFICATE MIR_GUEST

   AUTHORIZATION MIR_M_USER

   FROM FILE = 'D:\webroot\MIR_GUEST.cer'

GO

 5。在B数据库运行（记得先从后主）

 

ALTER DATABASE GGMall SET PARTNER = N'TCP://xxxxxxxxx'
GO 

6.在A数据库运行

ALTER DATABASE GGMall SET PARTNER = N'TCP://xxxxxxxxxx'
GO 

 

如果在这时出现说日志过久的错误的时候，可以先去A服务器上备份事务日志，然后在B服务器上还原。

在镜像建立好以后，2边的登录名的SID是不一致的。我们需要在B服务器上运行：

USE 数据库名
exec sp_change_users_login @Action='Report'
先找出有哪里孤立用户
USE 数据库名
exec sp_change_users_login @Action='update_one', @UserNamePattern='user', @LoginName='user';