SQLServer 镜像功能完全实现

标签: sqlserver 镜像 功能 | 发表时间:2011-09-05 18:46 | 作者:小刚qq Bloger
出处:http://www.cnblogs.com/

折腾SQLServer 镜像搞了一天,终于有点成果,现在分享出来,之前按网上做的出了很多问题。现在尽量把所遇到的问题都分享出来。

在域环境下我没配置成果,也许是域用户的原因,因为我在生产环境下搞的,更改域用户需要重启SQLServer ,所以这个方法放弃了,只能用证书形式。

环境:

主机:192.168.10.2  (代号A)

镜像:192.168.10.1 (代号B,为了一会说明方便)

(条件有限我没有搞见证服务器。)两台服务器上的都是SQLServer2005

首先配置主机

主机上执行以下SQL

--创建主机数据库主密钥 USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'; GO --在10.2上为数据库实例创建证书 CREATE CERTIFICATE As_A_cert WITH SUBJECT = 'As_A_cert', START_DATE = '09/02/2011', EXPIRY_DATE = '01/01/2099'; GO --在10.2上使用上面创建的证书为数据库实例创建镜像端点 CREATE ENDPOINT Endpoint_As STATE = STARTED AS TCP ( LISTENER_PORT=5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE As_A_cert, ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL ); GO

注:这里要注意设置数据库的镜像端口。5022.

--备份10.2上的证书并拷贝到10.1上 BACKUP CERTIFICATE As_A_cert TO FILE = 'D:\As_A_cert.cer'; GO

注:备份证书A,并将证书A拷贝到镜像服务器B上。

配置镜像服务器

USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'; GO --在10.1 B上为数据库实例创建证书 CREATE CERTIFICATE As_B_cert WITH SUBJECT = 'As_B_cert', START_DATE = '09/2/2011', EXPIRY_DATE = '01/01/2099'; GO --在10.1 B上使用上面创建的证书为数据库实例创建镜像端点 CREATE ENDPOINT Endpoint_As STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE As_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO --备份10.1 B上的证书并拷贝到10.2 A上 BACKUP CERTIFICATE As_B_cert TO FILE = 'D:\As_B_cert.cer'; GO

同样将备份的证书B 拷贝到A服务器上。

建立用于镜像登录的账户

在A上执行

--交换证书, --同步 Login CREATE LOGIN B_login WITH PASSWORD = 'password'; CREATE USER B_user FOR LOGIN B_login; CREATE CERTIFICATE As_B_cert AUTHORIZATION B_user FROM FILE = 'D:\As_B_cert.cer'; GRANT CONNECT ON ENDPOINT::Endpoint_Bs TO [B_login];

在B上执行

--交换证书, --同步 Login CREATE LOGIN A_login WITH PASSWORD = 'password'; CREATE USER A_user FOR LOGIN A_login; CREATE CERTIFICATE As_A_cert AUTHORIZATION A_user FROM FILE = 'D:\As_A_cert.cer'; GRANT CONNECT ON ENDPOINT::Endpoint_As TO [A_login];

记得两台服务器的端口5022是不被占用的,并且保证两个服务器可以连接

以后步骤执行没问题,镜像已经完成一半了。

接下来完整备份A服务器上的Test库

--主机执行完整备份 USE master; ALTER DATABASE Test SET RECOVERY FULL; GO BACKUP DATABASE Test TO DISK = 'D:\SQLServerBackups\Test.bak' WITH FORMAT; GO BACKUP LOG Test TO DISK = 'D:\SQLServerBackups\Test.bak'; GO --将备份文件拷贝到B上。

一定要执行完整备份。

在B服务器上完整还原数据库

这里问题多多。一个一个说。

如果我们直接执行如下SQL.

RESTORE DATABASE Test FROM DISK = 'D:\Back\Test.bak' WITH NORECOVERY GO RESTORE LOG Test FROM DISK = 'D:\Back\Test_log.bak' WITH FILE=1, NORECOVERY GO

可能会报:

消息 3154,级别 16,状态 4,第 1 行
备份集中的数据库备份与现有的 'Test’数据库不同。
消息 3013,级别 16,状态 1,第 1 行

可能是两个数据库的备份集名称不同导致,找了半天原因未果,所以采用下面sp_addumpdevice方法来做。

用sp_addumpdevice来建立一个还原设备。这样就保证了该备份文件是数据这个数据库的。

exec sp_addumpdevice 'disk','Test_backup', 'E:\backup\Test.bak' exec sp_addumpdevice 'disk','Test_log_backup', 'E:\backup\Test_log.bak' go

成功之后我们来执行完成恢复

RESTORE DATABASE Test FROM Test_backup WITH DBO_ONLY, NORECOVERY,STATS; go RESTORE LOG Test FROM Test_log_backup WITH file=1, NORECOVERY; GO

这里如果之前备份过多次数据库的话,肯会产生多个备份集。所以这里的 file就不能指定为1了。

这个错误可能是:

消息 4326,级别 16,状态 1,第 1 行
此备份集中的日志终止于 LSN 36000000014300001,该 LSN 太早,无法应用到数据库

。可以还原包含 LSN 36000000018400001 的较新的日志备份。

可以通过这条语句来查询该备份文件的备份集

restore   headeronly   from   disk   =   'E:\backup\Test_log.bak'

找到最后一个的序号指定给file就可以。

还需要注意的是第一次完整恢复的时候需要指定NORECOVERY。

至此所有准备工作都已经完成我们开启镜像了

先在镜像服务器上执行

ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022';

成功之后再在主机上执行

ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022';

这样两台服务器的镜像就同步了。

1

删除镜像:

ALTER DATABASE Test SET PARTNER OFF

如果主机出现问题,在主机执行

USE MASTER Go ALTER DATABASE Test SET PARTNER FAILOVER Go

总结:

如果在建立镜像的时候中间的那个步骤出问题,需要重新执行的时候一定要把该删得东西删除掉。

--查询镜像
select * from sys.endpoints
--删除端口
drop endpoint Endpoint_As
--查询证书
select * from sys.symmetric_keys
--删除证书,先删除证书再删除主键
DROP CERTIFICATE As_A_cert
--删除主键
DROP MASTER KEY
--删除镜像
alter database <dbname> set partner off
--删除登录名
drop login <login_name>

sp_addumpdevice 的语法

sp_addumpdevice [ @devtype = ] 'device_type'

        , [ @logicalname = ] 'logical_name'

        , [ @physicalname = ] 'physical_name'

      ]

其中参数有:

@devtype:设备类型,可以支持的值为disk和tape,其中disk为磁盘文件;tape为

windows支持的任何磁带设备。

@logicalname:备份设备的逻辑名称,设备名称。

@physicalname:备份设备的物理名称,路径

参考:

http://msdn.microsoft.com/zh-cn/library/ms187495(v=sql.90).aspx

http://msdn.microsoft.com/zh-cn/library/ms187014.aspx

http://msdn.microsoft.com/zh-cn/library/ms186289.aspx

 

之前介绍的一篇SQLServer2005与SQLServer2008数据库同步 用到了订阅.发布.来同步数据。

本篇文章同步发布在我的个人博客(http://xiaogangblog.com/)

作者: 小刚qq 发表于 2011-09-05 18:46 原文链接

评论: 4 查看评论 发表评论


最新新闻:
· Google 13周年纪 – Google收购史(2011-09-06 07:33)
· 框架会使程序员变笨吗?(2011-09-06 07:22)
· 微软否认WP7未经用户许可收集地理位置信息(2011-09-06 07:20)
· 谷歌中国前高管创业发力推荐引擎(2011-09-06 07:17)
· 传三星意欲接手英特尔MeeGo操作系统(2011-09-06 07:15)

编辑推荐:数据库设计Step by Step (10)——范式化

网站导航:博客园首页  我的园子  新闻  闪存  小组  博问  知识库

相关 [sqlserver 镜像 功能] 推荐:

SQLServer 镜像功能完全实现

- Bloger - 博客园-首页原创精华区
折腾SQLServer 镜像搞了一天,终于有点成果,现在分享出来,之前按网上做的出了很多问题. 现在尽量把所遇到的问题都分享出来. 在域环境下我没配置成果,也许是域用户的原因,因为我在生产环境下搞的,更改域用户需要重启SQLServer ,所以这个方法放弃了,只能用证书形式. 主机:192.168.10.2  (代号A).

oracle、mysql和sqlserver分页

- - Oracle - 数据库 - ITeye博客
sql server row number分页:. mysql limit分页:. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

SQLServer索引的四个高级特性

- - CSDN博客数据库推荐文章
SQLServer索引的四个高级特性. 一、Index Building Filter(索引创建时过滤).         有一些索引非常低效的,比如经常查询状态为进行中的订单,订单有99%的状态是完成,1%是进行中 ,因此我们在订单状态字段上建了一个索引,性能是提高了,但是感觉索引中保存了99%的完成状态数据是永远不会查询到的,很浪费空间.

OS X Lion将会获得AirPlay镜像显示和iMessage功能

- yak007 - cnBeta.COM
根据知情人士透露的消息,苹果的AirPlay镜像显示和iMessage应用程序正在开发Mac OS X 的版本. 暂时这些项目都不会公开,推测会在将来的OS X Lion版本更新中出现. AirPlay镜像可以让Mac用户将屏幕显示的内容直接镜像到通过Apple TV链接的高清电视、外接显示器上. AirPlay视频传送将会和QuickTime X整合在一起.

Oracle、Db2、SqlServer、MySQL 数据库插入当前系统时间

- - CSDN博客推荐文章
例如有表table,table 中有两个字段:name 、makedate. 插入系统时间应为sysdate:. insert into table (name,makedate) values('测试',sysdate);. 插入系统时间应为current timestamp并且makedate数据类型为timestamp.

sqlserver数据库大型应用解决方案总结

- - 博客园_首页
随着互联网应用的广泛普及,海量数据的存储和访问成为了系统设计的瓶颈问题. 对于一个大型的互联网应用,每天百万级甚至上亿的PV无疑对数据库造成了相当高的负载. 对于系统的稳定性和扩展性造成了极大的问题. 负载均衡集群是由一组相互独立的计算机系统构成,通过常规网络或专用网络进行连接,由路由器衔接在一起,各节点相互协作、共同负载、均衡压力,对客户端来说,整个群集可以视为一台具有超高性能的独立服务器.

SqlServer索引的原理与应用 - 张龙豪

- - 博客园_首页
索引的用途:我们对数据查询及处理速度已成为衡量应用系统成败的标准,而采用索引来加快数据处理速度通常是最普遍采用的优化方法. 索引是什么:数据库中的索引类似于一本书的目录,在一本书中使用目录可以快速找到你想要的信息,而不需要读完全书. 在数据库中,数据库程序使用索引可以重啊到表中的数据,而不必扫描整个表.

B-Tree索引在sqlserver和mysql中的应用

- - CSDN博客数据库推荐文章
在谈论数据库性能优化的时候,通常都会提到“索引”,但很多人其实没有真正理解索引,并没有搞清楚索引为什么能加快检索速度,以至于在实践中并不能很好的应用索引. 事实上,索引可以说是最廉价而且十分有效一种优化手段,一般而言,设计优良的索引对查询性能优化确实能起到立竿见影的效果. 相信很多读者,都了解和使用过索引,可能也看过或者听过”新华字典“、”图书馆“之类比较通俗描述,但是对索引的存储结构和本质任然还比较迷茫.

sqlserver定位消耗资源比较高的sql

- - CSDN博客数据库推荐文章
sqlserver查看消耗资源的sql ,具体如下:. 可以使用SQL 快速定位数据库里CPU,IO 消耗比较高的SQL:. 定位消耗cpu,io 最高的进程信息. 定位该进程执行的 SQL 语句.      到查到的 db 里执行:. 也可以从事件探查器查看sql的执行计划. 作者:lihaiwenshu 发表于2014-10-21 17:06:52 原文链接.

SQLserver, Oracle 限制层数 递归查询和反向查询父记录

- - Oracle - 数据库 - ITeye博客
以前使用Oracle,觉得它的递归查询很好用,就研究了一下SqlServer,发现它也支持在Sql里递归查询. SqlServer2008版本的Sql如下:. 比如一个表,有id和pId字段,id是主键,pid表示它的上级节点,表结构和数据:. --下面的Sql是查询出1结点的所有子结点. select * from my1 --结果包含1这条记录,如果不想包含,可以在最后加上:where id <> 1.