专业网站运营平台
首页 数据库 MsSql 正文
我要投稿

mssql2005数据库镜像搭建教程

MsSql
2023-12-01 0 243

一 概述

数据库镜像是SQL SERVER 2005用于提高数据库可用性的新技术。数据库镜像将事务日志记录直接从一台服务器传输到另一台服务器，并且能够在出现故障时快速转移到备用服务器。可以编写客户端程序自动重定向连接信息，这样一旦出现故障转移就可以自动连接到备用服务器和数据库。

优势:数据库镜像可以在不丢失已提交数据的前提下进行快速故障转移，无须专门的硬件，并且易于配置和管理。

二 环境准备

操作系统:Window 2003 enterprise sp2(至少两台,如要启用自动故障转移,必需三台)

SQL版本:MSSQL SERVER 2005 SP3

检查SQL SERVER版本:

exec xp_msver

select SERVERPROPERTY(\’productlevel\’)

数据库准备:准备一个数据库:ccerp_jzt ,备份此数据库还原到另外一台机器上,另外一台必须是with no recovery

这里我假设服务器A,B,C

A为主体服务器,B为镜像服务器,C为见证服务器

mssql2005数据库镜像搭建教程

A服务器

use master

go

restore filelistonly from disk=N\’f:\\databak\\ccerp_jzt_backup_200911250100.bak\’

restore database ccerp_jzt from disk=N\’f:\\databak\\ccerp_jzt_backup_200911250100.bak\’ with replace,recovery,

move \’ccerp_ydswzip_Data\’ to \’d:\\data\\ccerp_jzt.mdf\’,

move \’ccerp_ydswzip_Log\’ to \’d:\\data\\ccerp_jzt_log.ldf\’

exec sp_helpdb \’ccerp_jzt\’

backup database ccerp_jzt to disk =N\’f:\\databak\\sk.bak\’ with init

–更改恢复模式

alter database ccerp_jzt set recovery full

B服务器:

CREATE DATABASE ccerp_jzt

ON

( NAME = Sales_dat,

FILENAME = \’d:\\data\\ccerp_jzt.mdf\’,

SIZE = 10

)

LOG ON

( NAME = \’ccerp_jzt_log\’,

FILENAME = \’d:\\data\\ccerp_jzt_log.ldf\’,

SIZE = 5MB

)

GO

restore filelistonly from disk=N\’f:\\xxzx\\data\\sk.bak\’

use master

go

restore database ccerp_jzt from disk=N\’f:\\xxzx\\data\\sk.bak\’ with replace,norecovery,

exec sp_helpdb \’ccerp_jzt\’

C服务器只要装上SQL SERVER 2005就可以,无需其他准备

准备完成后如下图所示：

mssql2005数据库镜像搭建教程

三 三种模式的搭建

数据库镜像要建立必需得建立信任关系,那么在WIN环境下建立信任关系可以通过三种方式:域帐户,证书信任,windows 匿名登陆,现就前两种模式做配置说明.

3.1 域帐户模式:

3.1.1 更改mssqlserver服务的的登陆方式为域帐户登陆方式:

进入windows服务管理控制台,更改服务登陆帐户,使域账户有更改MSSQL SERVER服务状态的权限．三台机器都做同样设置

mssql2005数据库镜像搭建教程

将域帐户赋予sysadmin角色

mssql2005数据库镜像搭建教程


3.1.2 建立端点:

通过图形界面建立端点：

启动SQLWB，按图一直下一步

mssql2005数据库镜像搭建教程mssql2005数据库镜像搭建教程mssql2005数据库镜像搭建教程mssql2005数据库镜像搭建教程mssql2005数据库镜像搭建教程

用域帐户登陆

如果成功则：

mssql2005数据库镜像搭建教程


3.2 证书模式

3.2.1建立证书&端点

参与数据库镜像会话的服务器必须彼此信任。对于本地通信而言，例如一个域内的通信，信任意味着SQL Server实例登陆账号必须有权限连接到其他镜像服务器，也包括endpoints。首先在每个服务器上使用CREATE LOGIN命令，然后使用GRANT CONNECT ON ENDPOINT命令．非信任域之间的通信必须使用证书。如果使用CREATE CERTIFICATE语句创建自签名的证书，基本上所有数据镜像证书的要求都可以满足。确认在CREATE CERTIFICATE语句中将证书标记为ACTIVE FOR BEGIN_DIALOG。

一　建立证书：

镜像服务器上执行：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = \’TEST\’;

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT=\’HOST_A certificate\’, START_DATE=\’2010-03-10\’;

主体服务器上执行：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = \’TEST\’;

CREATE CERTIFICATE HOST_B_cert WITH SUBJECT=\’HOST_B certificate\’, START_DATE=\’2010-03-10\’;

见证服务器上执行：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = \’TEST\’;

CREATE CERTIFICATE HOST_C_cert WITH SUBJECT=\’HOST_C certificate\’, START_DATE=\’2010-03-10\’;


二　建立端点：

镜像服务器上执行：


–create mirror endpoint on primary A

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 = ALL );

主体服务器上执行：

–Create endpoint on mirror server 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 = ALL );

见证服务器上执行：

–Create endpoint on witness server C

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 );

SELECT * FROM sys.database_mirroring_endpoints;

证书互备：

镜像服务器上执行：

–backup certificate

BACKUP CERTIFICATE HOST_A_cert TO FILE = \’e:\\HOST_A_cert.cer\’

主体服务器上执行

–backup certificate

BACKUP CERTIFICATE HOST_B_cert TO FILE = \’e:\\HOST_B_cert.cer\’

见证服务器上执行：

BACKUP CERTIFICATE HOST_c_cert TO FILE = \’e:\\HOST_C_cert.cer\’

将备份到的证书进行互换，即HOST_A_cert.cer复制到B机的e:\\ 将HOST_B_cert.cer复制到A机的E:\\,也就是每台服务器有三个证书

三：建立登陆用户：

镜像服务器上执行：

–Create user

CREATE LOGIN HOST_B_login WITH PASSWORD = \’test\’;

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = \’e:\\HOST_B_cert.cer\’;

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

CREATE LOGIN HOST_C_login WITH PASSWORD = \’test\’;

CREATE USER HOST_C_user FOR LOGIN HOST_c_login;

CREATE CERTIFICATE HOST_c_cert AUTHORIZATION HOST_c_user FROM FILE = \’e:\\HOST_c_cert.cer\’;

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

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

–query user sid

select loginname,name,sid From syslogins

主体服务器上执行：

–Create user

CREATE LOGIN HOST_A_login WITH PASSWORD = \’test\’;

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = \’e:\\HOST_A_cert.cer\’;

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

— add witness user

CREATE LOGIN HOST_C_login WITH PASSWORD = \’test\’;

CREATE USER HOST_C_user FOR LOGIN HOST_c_login;

CREATE CERTIFICATE HOST_c_cert AUTHORIZATION HOST_c_user FROM FILE = \’e:\\HOST_c_cert.cer\’;

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

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

–query sid

select loginname,name,sid From syslogins

见证服务器上执行：

–Create user

CREATE LOGIN HOST_A_login WITH PASSWORD = \’test\’;

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = \’e:\\HOST_A_cert.cer\’;

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

–add user host_b_login to have pemission to access witness

CREATE LOGIN HOST_B_login WITH PASSWORD = \’test\’;

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = \’e:\\HOST_B_cert.cer\’;

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

grant connect on endpoint::endpoint_mirroring to HOST_C_login

USE master;

exec sp_addlogin

@loginame = \’HOST_B_login\’,

@passwd = \’test\’,

@sid = 0x1A914CA3D1D00C4793EBC96E4C4F4352 ;

ALTER DATABASE ccerp_jzt SET PARTNER = \’TCP://192.168.137.32:5022\’;

四．建立镜像：

先在镜像服务器上执行：

ALTER DATABASE ccerp_jzt SET PARTNER = \’TCP://192.168.137.44:5022\’;

接着主体服务器执行：

ALTER DATABASE ccerp_jzt SET PARTNER = \’TCP://192.168.137.32:5022\’;

ALTER DATABASE ccerp_jzt SET witness = \’TCP://192.168.137.49:5022\’;

至此引证书建立完毕

mssql2005数据库镜像搭建教程

四、测试操作

mssql2005数据库镜像搭建教程

1、主备互换

–主机执行：

1USE master;2ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;

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

–备机执行：

1USE master;2ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

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

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

4、原来的主服务器恢复,可以继续工作

–默认情况下，事务安全级别的设置为 FULL，即同步运行模式，而且SQL Server 2005 标准版只支持同步模式。

–关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。

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

错误说明：消息1498，级别16，状态3，第1 行

默认情况下，数据库镜像是被禁用的。当前提供的数据库镜像仅供评估使用，并不应使用于生产环境中。若要以评估为目的启用数据库镜像，请在启动过程中使用跟踪标志1400。有关跟踪标志和启动选项的详细信息，请参阅SQL Server 联机丛书。

解决办法：没打SP1以上补丁．强烈建议打SP3

消息1475，级别16，状态2，第1 行

由于\”ccerp_jzt\” 数据库可能有尚未备份的大容量日志记录更改，所以无法启用数据库镜像。必须在镜像上还原主体数据库的上一次日志备份。

主体上：backup log ccerp_jzt to disk =\’e:\\log.trn\’ with no_truncate

镜像上：restore log ccerp_jzt from disk=\’e:\\log.trn\’ with norecovery

您可能感兴趣的文章:

  • 监视SQLServer数据库镜像[图文]
  • SQL数据库与oracle数据库镜像有什么不同对比
  • MySQL 数据库双向镜像、循环镜像(复制)
  • SQL Server误区30日谈 第10天 数据库镜像在故障发生后 马上就能发现
  • SQL Server 2008 R2数据库镜像部署图文教程
  • SQL Server 2008 数据库镜像部署实例之一 数据库准备
  • SQL Server 2008 数据库镜像部署实例之二 配置镜像，实施手动故障转移
  • SQL Server 2008 数据库镜像部署实例之三 配置见证服务器
  • SQL Server 2005 镜像构建手册(sql2005数据库同步镜像方案)
  • 利用SQL SERVER 2005数据库镜像实现可用性分析
  • 简述SQL Server 2005数据库镜像相关知识

收藏 (0) 打赏

感谢您的支持，我会继续努力的!

打开微信/支付宝扫一扫，即可进行扫码打赏哦，分享从这里开始，精彩与您同在
点赞 (0)

悠久资源 MsSql mssql2005数据库镜像搭建教程 https://www.u-9.cn/database/mssql/6876.html

mssql2005数据库镜像搭建教程
上一篇： SQL Server 2005 DTS导入平面数据出现错误解决方案
mssql2005数据库镜像搭建教程
下一篇： sql server 2005用户权限设置深入分析
常见问题

相关文章

SqlServer中Exists的使用小结
SqlServer中Exists的使用小结
MsSql
3周前 506
SQL SERVER自动备份以及自动清除设置图文教程
SQL SERVER自动备份以及自动清除设置图文教程
MsSql
3周前 685
SQL Server数据库数据文件的迁移步骤
SQL Server数据库数据文件的迁移步骤
MsSql
3周前 944
SQL Server定时收缩数据库日志为指定大小的示例代码
SQL Server定时收缩数据库日志为指定大小的示例代码
MsSql
3周前 1,035
猜你喜欢
发表评论
暂无评论
官方客服团队

为您解决烦忧 - 24小时在线 专业服务

联系官方团队 在线提交工单
TA的动态
总裁主题

分享最新WordPress教程共同学习，共同进步，共同成长！

QQ交流群
悠久资源

QQ交流群

您的支持，是我们最大的动力！
热门文章
热门评论
终身VIP会员限时钜惠
首页 首页 菜单 首页 首页