SQL Server数据库镜像安装

搭建SQL Server数据库镜像（证书）

本次实验进行SQL server数据库镜像的搭建，采用证书的搭建模式

以下是搭建SQL server数据库镜像的环境设置

以192.168.210.125为主体服务器，192.168.210.126为镜像服务器，192.168.210.127为见证服务器。

还要对三个环境进行防火墙的设置，需要打开端口1433和5022

2.1创建证书：

2.1.1先对主体服务器进行以下操作:

USE master;  

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'hzmc321#'; 

GO  

--为主体服务器 host_A 实例制作一个证书。 

 

--DROP CERTIFICATE host_A_cert 

CREATE CERTIFICATE host_A_cert  

WITH SUBJECT = 'host_A certificate',START_DATE = '6/19/2019',EXPIRY_DATE = '01/01/2099'; 

GO  

 

--使用该证书为服务器实例创建一个镜像端点。 

 

--DROP ENDPOINT Endpoint_Mirroring 

CREATE ENDPOINT Endpoint_Mirroring 

STATE = STARTED 

AS TCP ( 

LISTENER_PORT=5022 

, LISTENER_IP = ALL 

)  

FOR DATABASE_MIRRORING (  

AUTHENTICATION = CERTIFICATE host_A_cert 

, ENCRYPTION = REQUIRED ALGORITHM AES 

, ROLE = PARTNER 

); 

GO 

 

--备份host_A 证书，并将其复制到镜像服务器 host_B 和见证服务器 host_C 

 

BACKUP CERTIFICATE host_A_cert TO FILE = 'c:\DbMirror\host_A.cer'; 

GO 

2.1.2在对镜像服务器进行操作：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'hzmc321#';

GO

 

--为镜像服务器 host_B 实例制作一个证书。

CREATE CERTIFICATE host_B_cert

WITH SUBJECT = 'host_B certificate',START_DATE = '6/19/2015',EXPIRY_DATE = '01/01/2099';

GO

 

--在 host_B 中为服务器实例创建一个镜像端点。

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5022

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE host_B_cert

, ENCRYPTION = REQUIRED ALGORITHM AES

, ROLE = PARTNER

);

GO

 

--备份 host_B 证书，并将其复制到主体服务器 host_A 和见证服务器 host_C 上

BACKUP CERTIFICATE host_B_cert TO FILE = 'c:\DbMirror\host_B.cer';

GO

2.1.3再对见证服务器进行操作：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'hzmc321#';

GO

 

 

--为此服务器实例制作一个证书。

CREATE CERTIFICATE host_C_cert

WITH SUBJECT = 'host_C certificate',START_DATE = '3/12/2015',EXPIRY_DATE = '01/01/2099';

GO

 

--使用该证书为服务器实例创建一个镜像端点。

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5022

, LISTENER_IP = ALL

)

 

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE host_C_cert

, ENCRYPTION = REQUIRED ALGORITHM AES

, ROLE = WITNESS

);

GO

 

--备份 host_C 证书，并将其复制到主体服务器 host_A 和镜像服务器 host_C 上

 

BACKUP CERTIFICATE host_C_cert TO FILE = 'c:\dbmirror\host_C.cer';

GO

2.2配置三者之间的连接：

2.2.1主体服务器：

--在 host_A 上为镜像服务器 host_B 创建一个登录名。

 

USE master;

CREATE LOGIN host_B_login WITH PASSWORD = 'hzmc321#';

GO

 

--创建一个使用该登录名的用户。

CREATE USER host_B_user FOR LOGIN host_B_login;

GO

 

--使证书与该用户关联。

CREATE CERTIFICATE host_B_cert

AUTHORIZATION host_B_user

FROM FILE = 'c:\DbMirror\host_B.cer'

GO

 

--授予对远程镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];

GO

 

--在主体服务器 host_A 上为见证服务器 host_C 创建一个登录名。

USE master;

CREATE LOGIN host_C_login WITH PASSWORD = 'hzmc321#';

GO

 

--创建一个使用该登录名的用户。

CREATE USER host_C_user FOR LOGIN host_C_login;

 

GO

 

--使证书与该用户关联。

CREATE CERTIFICATE host_C_cert

AUTHORIZATION host_C_user

FROM FILE = 'c:\DbMirror\host_C.cer'

GO

 

--授予对远程镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login];

GO

 

--主体服务器上创建用于本机的登录名(MS可以省略)

USE master;

CREATE LOGIN host_A_login WITH PASSWORD= 'hzmc321#';

GO

 

--创建一个使用该登录名的用户。

CREATE USER host_A_user FOR CERTIFICATE host_A_cert;

GO

 

--授予对远程镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];

GO

2.2.2对镜像服务器进行操作

--在镜像服务器 host_B 上为主体服务器 host_A 创建一个登录名。

 

USE master;

CREATE LOGIN host_A_login WITH PASSWORD = 'hzmc321#';

GO

 

--创建一个使用该登录名的用户。

CREATE USER host_A_user FOR LOGIN host_A_login;

GO

 

--使证书与该用户关联。

CREATE CERTIFICATE host_A_cert

AUTHORIZATION host_A_user

FROM FILE = 'c:\Dbmirror\host_A.cer'

GO

 

--授予对远程镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];

GO

 

--在镜像服务器 host_B 上为见证服务器 host_C 创建一个登录名。

 

USE master;

CREATE LOGIN host_C_login WITH PASSWORD = 'hzmc321#';

GO

 

----创建一个使用该登录名的用户。

CREATE USER host_C_user FOR LOGIN host_C_login;

GO

 

----使证书与该用户关联。

CREATE CERTIFICATE host_C_cert

AUTHORIZATION host_C_user

FROM FILE = 'c:\Dbmirror\host_C.cer'

GO

 

----授予对远程镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login];

GO

 

--在镜像服务器 host_B 上为本机创建一个登录名(MS可以省略)

USE master;

CREATE LOGIN host_B_login WITH PASSWORD = 'hzmc321#';

GO

 

--创建一个使用该登录名的用户。

CREATE USER host_B_user FOR CERTIFICATE host_B_cert;

GO

 

--授予对远程镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];

GO

2.2.3见证服务器：

--在见证服务器 host_C 上为主体服务器 host_A 创建一个登录名。

 

USE master;

CREATE LOGIN host_A_login WITH PASSWORD = 'hzmc321#';

GO

 

--创建一个使用该登录名的用户。

CREATE USER host_A_user FOR LOGIN host_A_login;

GO

 

--使证书与该用户关联。

CREATE CERTIFICATE host_A_cert

AUTHORIZATION host_A_user

FROM FILE = 'c:\DbMirror\host_A.cer'

GO

 

--授予对远程镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];

GO

 

--在见证服务器 host_C 上为镜像服务器 host_B 创建一个登录名。

 

USE master;

CREATE LOGIN host_B_login WITH PASSWORD = 'hzmc321#';

GO

 

--创建一个使用该登录名的用户。

CREATE USER host_B_user FOR LOGIN host_B_login;

GO

 

--使证书与该用户关联。

CREATE CERTIFICATE host_B_cert

AUTHORIZATION host_B_user

FROM FILE = 'c:\DbMirror\host_B.cer'

GO

 

--授予对远程镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];

GO

2.3配置数据库登陆信息

可以自己创建一个新的用于数据库镜像的登录名，也可以使用原本有的。

查询用户名和对应的sid

USE master;

select sid,name from syslogins;

在镜像数据库执行(见证做)

USE master;

exec sp_addlogin

@loginame = 'testname',

@passwd = 'hzmc321#',

@sid = xxxxxxxxxxxxxxxxxxxxxxxxx ;

2.4在主体数据库备份数据库

USE MASTER;

GO

BACKUP DATABASE xxx（目标数据库）

TO DISK = 'c:\DbMirror\DB.bak'

WITH INIT

GO

BACKUP LOG xxx

TO DISK = 'c:\DbMirror\DB_log.bak'

WITH INIT

GO

 

 

2.5在镜像数据库

还原的时候必须把数据库和事务日志以NoRecovery的形式还原

再重新还原一次事务日志

同样以NoRecovery的形式还原

3.配置伙伴服务器

执行顺序为 镜像------主体-------见证

在镜像服务器配置

ALTER DATABASE xxx（目标数据库）

SET PARTNER = 'TCP://192.168.210.125:5022';

GO

在主体服务器配置

 ALTER DATABASE xxx

SET PARTNER = 'TCP://192.168.210.126:5022';

GO

注释：这个时候可能会报以下错误：

消息 1412，级别 16，状态 0，第 1 行

数据库 "test" 的远程副本尚未前滚到包含在数据库日志的本地副本中的时间点。

解决办法：重新从主体数据库备份事务日志并且在镜像数据库还原。

在主体服务器配置

ALTER DATABASE [xxx]

SET PARTNER = 'TCP://192.168.210.127:5022';

GO

 

完成阶段：数据库处于以下状态说明数据库镜像基本搭建成功

 

创建Sql server 维护计划：

在下图红框区域内选择或者修改计划备份的时间

• 维护计划
• 实验阶段
• 环境设置