sql server 2008镜像安装 sql server 数据库镜像

转载

mob64ca1403528a 2023-09-17 21:48:57

文章标签 sql server 2008镜像安装 sql server 数据库服务器 login 文章分类 SQL Server 数据库

数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。

要进行数据库镜像所需的最小需求包括了两个不同的SQL Server运行环境。主服务器被称为“主机”，第二个服务器被称作“备机”。主机数据库就是你实际用着的数据库，镜像数据库就是你的数据库的备用拷贝。当事务写入你的基本服务器的时候，他们也同样被传送到并写入你的镜像数据库中。

除了基本和镜像之外，你还可以引入另一个可选的组件，名为“见证”。见证服务器是第三个SQL Server 2005/2008运行实例，它是在判断什么时候进行错误恢复的时候，用于基本和镜像之间内部交流。只有当你想实现自动错误恢复的时候用到这个选项。它实现了2比1投票的能力，当我的一个组件不可达，并因此需要进行错误恢复的时候。见证服务器只有在你想实现自动错误恢复的时候才需要用到。

实现互通可以使用域或证书来实现，非域环境下使用证书配置数据库镜像(如不需要见证服务器，省略掉主体服务器和镜像服务器建立见证服务器的登录、见证服务器的配置、主体服务器设置见证服务器三个环节即可)。

1、环境

主机名	主机操作系统	主机SQL配置	主机IP	初始镜像角色
host_A	Windows Server 2003	Sql Server 2005 SP3	192.168.1.21	主体服务器
host_B	Windows Server 2003	Sql Server 2005 SP3	192.168.1.254	镜像服务器
host_C	Windows Server 2003	Sql Server 2005 SP3	192.168.1.22	见证服务器

2、配置出站连接

主要工作是为服务器实例制作证书：

(一)为主机配置出站连接：

/********************************************************

此脚本在主体服务器执行

********************************************************/

USE master;

--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OA@asp.net';
GO

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

--DROP CERTIFICATE host_A_cert
CREATE CERTIFICATE host_A_cert 
WITH SUBJECT = 'host_A certificate',START_DATE = '05/01/2010',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 = 'D:/dbmirror/host_A.cer';
GO

（二）为镜像服务器配置出站连接

/***********************************************
在镜像服务器 host_B 执行此脚本
***********************************************/

USE master;

--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OA@asp.net';
GO

--为镜像服务器 host_B 实例制作一个证书。
--DROP CERTIFICATE host_B_cert
CREATE CERTIFICATE host_B_cert
WITH SUBJECT = 'host_B certificate',START_DATE = '05/01/2010',EXPIRY_DATE = '01/01/2099';
GO

--在 host_B 中为服务器实例创建一个镜像端点。
--DROP ENDPOINT Endpoint_Mirroring
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 oa-canoe_cert TO FILE = 'D:/dbmirror/host_B.cer';
GO

(三)为见证服务器配置出站连接

/****************************
见证服务器 host_C 执行
*****************************/

--ALTER DATABASE MirrorDB SET PARTNER OFF
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OA@asp.net';
GO

--为此服务器实例制作一个证书。
--DROP CERTIFICATE host_C_cert
CREATE CERTIFICATE host_C_cert
WITH SUBJECT = 'host_C certificate',START_DATE = '05/01/2010',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_C_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = WITNESS
);
GO

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

BACKUP CERTIFICATE oa-tjcfw_cert TO FILE = 'D:/dbmirror/host_C.cer';
GO

注意：

1. 证书必须指定过期时间，如果未指定，则将 EXPIRY_DATE 设置为 START_DATE 一年之后的日期。

2. 如果出现 “默认情况下，数据库镜像是被禁用的。当前提供的数据库镜像仅供评估使用，并不应使用于生产环境中。若要以评估为目的启用数据库镜像，请在启动过程中使用跟踪标志1400。有关跟踪标志和启动选项的详细信息，请参阅SQL Server 联机丛书”错误，请执行 DBCC TRACEON(1400,-1) 并安装SP3补丁即可

3、配置入站连接

(一)为主体服务器配置入站连接：

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

USE master;
--DROP LOGIN host_B_login
CREATE LOGIN host_B_login WITH PASSWORD = 'OA@asp.net';
GO

--创建一个使用该登录名的用户。
--DROP USER host_B_user
CREATE USER host_B_user FOR LOGIN host_B_login;
GO

--使证书与该用户关联。
--DROP CERTIFICATE host_B_cert
CREATE CERTIFICATE host_B_cert
AUTHORIZATION host_B_user
FROM FILE = 'D:/dbmirror/host_B.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];
GO

--在主体服务器 host_A 上为见证服务器 host_C 创建一个登录名。
USE master;
--DROP LOGIN host_C_login
CREATE LOGIN host_C_login WITH PASSWORD = 'OA@asp.net';
GO

--创建一个使用该登录名的用户。
--DROP USER host_C_user
CREATE USER host_C_user FOR LOGIN host_C_login;

GO

--使证书与该用户关联。
--DROP CERTIFICATE host_C_cert
CREATE CERTIFICATE host_C_cert
AUTHORIZATION host_C_user
FROM FILE = 'D:/dbmirror/host_C.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login];
GO

--主体服务器上创建用于本机的登录名(MS可以省略)
USE master;
--DROP LOGIN host_A_login
CREATE LOGIN host_A_login WITH PASSWORD= 'OA@asp.net';
GO

--创建一个使用该登录名的用户。
--DROP USER host_A_user
CREATE USER host_A_user FOR CERTIFICATE host_A_cert;
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];
GO

(二)为镜像服务器配置入站连结：

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

USE master;
--DROP LOGIN host_A_login
CREATE LOGIN host_A_login WITH PASSWORD = 'OA@asp.net';
GO

--创建一个使用该登录名的用户。
--DROP USER host_A_user
CREATE USER host_A_user FOR LOGIN host_A_login;
GO

--使证书与该用户关联。
--DROP CERTIFICATE host_A_cert
CREATE CERTIFICATE host_A_cert
AUTHORIZATION host_A_user
FROM FILE = 'D:/dbmirror/host_A.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];
GO

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

USE master;
--DROP LOGIN host_C_login
CREATE LOGIN host_C_login WITH PASSWORD = 'OA@asp.net';
GO

--创建一个使用该登录名的用户。
--DROP USER host_C_user 
CREATE USER host_C_user FOR LOGIN host_C_login;
GO

--使证书与该用户关联。
--DROP CERTIFICATE host_C_cert
CREATE CERTIFICATE host_C_cert
AUTHORIZATION host_C_user
FROM FILE = 'D:/dbmirror/host_C.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login];
GO

--在镜像服务器 host_B 上为本机创建一个登录名(MS可以省略)
USE master;
--DROP LOGIN host_B_login
CREATE LOGIN host_B_login WITH PASSWORD = 'OA@asp.net';
GO

--创建一个使用该登录名的用户。
--DROP USER host_B_user
CREATE USER host_B_user FOR CERTIFICATE host_B_cert;
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];
GO

(三)为见证服务器配置入站连接：

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

USE master;
--DROP LOGIN host_A_login
CREATE LOGIN host_A_login WITH PASSWORD = 'OA@asp.net';
GO

--创建一个使用该登录名的用户。
--DROP USER host_A_user
CREATE USER host_A_user FOR LOGIN host_A_login;
GO

--使证书与该用户关联。
--DROP CERTIFICATE host_A_cert
CREATE CERTIFICATE host_A_cert
AUTHORIZATION host_A_user
FROM FILE = 'D:/dbmirror/host_A.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];
GO

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

USE master;
--DROP LOGIN host_B_login
CREATE LOGIN host_B_login WITH PASSWORD = 'OA@asp.net';
GO

--创建一个使用该登录名的用户。
--DROP USER host_B_user
CREATE USER host_B_user FOR LOGIN host_B_login;
GO

--使证书与该用户关联。
--DROP CERTIFICATE host_B_cert
CREATE CERTIFICATE host_B_cert
AUTHORIZATION host_B_user
FROM FILE = 'D:/dbmirror/host_B.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];
GO

4、配置镜像数据库用户信息
数据库镜像的缺点之一是无法维护登录名，所以需要我们手工维护登录。
通常来说数据库都将会有若干个用户作为访问数据库的用户，并且数据库会有相应的登录名，但是在备机中缺少与之相对应的登录名，例如某业务系统使用 'uhost' 作为登录名访问数据库，但是在

备机中没有 'uhost' 这个登录名，因此一旦主备切换，业务系统就无法登录数据库了，这种情况称为"孤立用户".

在主数据库中执行如下语句

USE master; select sid,name from syslogins;

查找出相应的用户名和sid，例如：上述的 'uhost'

在备数据库中执行如下语句：

USE master; exec sp_addlogin @loginame = '<LoginName>', @passwd = '<Password>', @sid = <sid> ;

这里的 'LoginName' 即主数据库中的登录名，sid即是上述通过SQL语句查找出的sid。

例如,查询得到的sid和name如下所示。

sid name
---------------------------------- -----------------
50x074477739DCA0E499C29394FFFC4ADE4 uhost

则建立登录名的SQL语句：

USE master; exec sp_addlogin @loginame = 'uhost', @passwd = 'OA@asp.net', @sid = 0x074477739DCA0E499C29394FFFC4ADE4;

到此为止可以认为备机数据库的环境已经与主机同步了

在主体服务器 host_A 上备份数据库，镜像只支持完全恢复模式，在备份数据库之前检查恢复的模式,需要同时完整备份数据库和事务日志。

USE MASTER; GO BACKUP DATABASE DBMirror TO DISK = 'D:/dbmirror/DB.bak' WITH INIT GO BACKUP LOG Credit TO DISK = 'D:/dbmirror/DB_log.bak' WITH INIT GO

将DB.bak、DB_log.bak复制到镜像服务器 host_B 上，复制到 host_B 的镜像服务器实例上以NORECOVERNY选项进行恢复.
注意：这里数据库必须备份两次，一次全备份和一次事务日志备份，在镜像服务器上还原数据库必须使用NORECOVERNY选项进行恢复.

5、配置镜像伙伴

(一)在镜像服务器上配置

--在镜像服务器 host 实例上设置其伙伴，使其伙伴成为主体服务器 ALTER DATABASE DBMirror SET PARTNER = 'TCP://192.168.1.21:5022'; GO

(二)在主体服务器上配置

--必须要在镜像数据库中先设置好伙伴后，才能在主体服务器执行 --在主体服务器 host_A 实例上设置镜像服务器为伙伴 ALTER DATABASE DBMirror SET PARTNER = 'TCP://192.168.1.254:5022'; GO

(三)在主体服务器上配置见证服务器

--设置见证服务器，在主体服务器 host_A 实例上设置见证服务器 ALTER DATABASE DBMirror SET WITNESS = 'TCP://192.168.1.22:5022'; GO

至此，镜像服务器配置成功，测试操作：
1、主备互换

--主机执行： USE master; ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;

2、主服务器Down掉,备机紧急启动并且开始服务

--备机执行： USE master; ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

3、原来的主服务器恢复,可以继续工作,需要重新设定镜像

--备机执行： USE master; ALTER DATABASE <DatabaseName> SET PARTNER RESUME; --恢复镜像 ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER; --切换主备

默认情况下，事务安全级别的设置为 FULL，即同步运行模式，而且SQL Server 2005 标准版只支持同步模式。
关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。

USE master; ALTER DATABASE <DatabaseName> SET PARTNER SAFETY FULL; --事务安全,同步模式 ALTER DATABASE <DatabaseName> SET PARTNER SAFETY OFF; --事务不安全,异步模式

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：分布式垂直应用架构分布式架构dynamo

下一篇：mysql游标遍历千万数据 mysql游标遍历结果集

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

sql server 2008镜像安装 sql server 数据库镜像

sql server 2008镜像安装 sql server 数据库镜像

51CTO博客