Question
-
公司要上一套系统，DB用SQL2008，怎么实现双机热备？
一种方案是用windows的故障转移群集搭配SQL自己的群集功能，这种方案需要有共享存储，我现在在虚拟测试环境没办法做实验，所以暂时先不考虑这个。
另外一种是用镜像的方式做双机热备，DB都放在服务器上，不用外接存储，主节点服务器DB实时复制到备用节点中，主节点故障后自动跳到备用节点，不会出现服务中断的问题。这种方式能否实现，该如何操作？因为没做过这种，所以思路有些乱，需要高手们给点指引，谢谢啦。
Friday, November 09, 2012 7:42 AM
Answers
-
你好，你的方法是不是要有3台服务器安装SQL，一台主机，一台备机，一台做见证，安装完SQL后，打开SQL输入你提供的命令，来实现镜像功能？因为以前没做过这块，所以比较小白，想细致了解一下，高手有空来指点指点，谢谢了。
你好，
那个见证服务器是可选的，你可以选择安装也可以不安装，见证服务器的作用就是，如果主机出错，那见证服务器就会自动地实现故障转移，然后使备机转化成主机，代替主机继续工作，如果你不安装的就只好出错的时候，自己手动转移了。
也不是说要输入命令，SSMS 里这些都有的，你直接点击就可以了，因为我配置的时候也不愿意敲代码，不过这里有现成的你可以直接复制就好。
这篇文档可以手把手教你如果配置，不用写命令，请参考：http://www.doc88.com/p-690922020761.html 。
有什么不清楚的，在问我们，大家相互学习啦。
Thanks,
Amy Peng
 立刻免费下载TechNet论坛好帮手
-
Marked as answer by
Rik1012
Tuesday, November 13, 2012 1:50 AM
Monday, November 12, 2012 2:52 AM
All replies
-
你说的故障转移群集和共享存储是HA，微软集群不是个什么好东西，个人看法
只热备的话应该部署镜像
Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
Friday, November 09, 2012 9:56 AM
-
Agree, books online tells you how to set db mirroring step by step.
Friday, November 09, 2012 1:47 PM
-
微软集群不是个什么好东西，dgdba大侠你也太偏激了吧
我做过SQL2005的镜像，不过步骤跟2008应该差不多，给你一些示例代码，网上找的，自己亲自测试成功
数据库：S_C_SC
网段：192.168.1.X
--SQL2005数据库镜像的步骤
--1、检查SQL Server 2005数据库
--只有SQL Server 2005 标准版、企业版和开发版才可以建立数据镜像。
--其他版本即Express只能作为见证服务器
--要保证打上SP2补丁 SELECT SERVERPROPERTY('productlevel')
--2、主备实例互通
--实现互通可以使用域或证书来实现，考虑实现的简单，以下选取证书的方式实现。注意：
实现“主备数据库实例互通”的操作只需要做一次，每一对主备
实例（不是数据库）做一次互通。
-----------------------------------------------------------
--1、设置镜像数据库为完整恢复模式
alter DATABASE S_C_SC set recovery FULL
--2、创建证书（主备可并行执行）
--主机执行
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'joe';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '2012-09-25';
--备机执行
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'joe';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate' ,
START_DATE = '2012-09-25';
--见证执行
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'joe';
CREATE CERTIFICATE HOST_C_cert WITH SUBJECT = 'HOST_C certificate' ,
START_DATE = '2012-09-25';
--3、创建连接的端点（主备可并行执行）
--主机执行：
USE master
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 );
--备机执行：
USE master
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 );
--见证执行:
USE master
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 );
--4、备份证书以备建立互联（主备可并行执行）
--主机执行：
USE master
--BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\HOST_A_cert.cer';
--备机执行：
USE master
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';
--见证执行:
USE master
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'D:\SQLBackup\HOST_C_cert.cer';
--5、互换证书
--
--将备份到D:\SQLBackup\的证书进行互换，即HOST_A_cert.cer复制(是复制不是剪切)
--到备机的D:\SQLBackup\。HOST_B_cert.cer复制(是复制不是剪切)到主机的D:\SQLBackup--见证的证书HOST_C_cert.cer复制到主机和备机,主机和备机复制到见证
--6、添加登陆名、用户（主备见证可并行执行）
--以下操作只能通过命令行运行，通过图形界面无法完成。（截至文档编写结束，SQL Server2005的版本号为SP2）
--主机执行：
USE master
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 = 'D:\SQLBackup\HOST_B_cert.cer';
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]
USE master
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 = 'D:\HOST_C_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO
--HOST_B_cert是第一步创建的证书
--备机执行：
USE master
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 = 'D:\SQLBackup\HOST_A_cert.cer';
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
USE master
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 = 'D:\HOST_C_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO
--见证执行:
USE master
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 = 'D:\SQLBackup\HOST_A_cert.cer';
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
USE master
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 = 'D:\SQLBackup\HOST_B_cert.cer';
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
--注意: 这里添加的登录名和用户是添加在master数据库里的不是要做镜像的那个数据库
--这里添加的登录名和用户是用来做连接的，下面建立镜像关系才是修复孤立用户才是
--修复要做镜像的那个数据库的孤立用户!!!!!!!!!!!!!!!
--建立镜像关系----------------------------------------------
--以下步骤是针对每个数据库进行的，例如：如果主机中有5个数据库以下过程就要执行5次。
--7、 手工同步登录名和密码
--在前面提到数据库镜像的缺点之一是无法维护登录名，所以需要我们手工维护登录解决孤立用户。
--在主数据库中执行如下语句：
USE master;
select sid,name,dbname from syslogins WHERE dbname='要做镜像的数据库名'
USE master;
select sid,name,dbname from syslogins WHERE dbname='S_C_SC'
--查找出要做镜像的那个数据库里面有哪些用户名和sid，例如：上述的’myuser’
--在备库中执行如下语句：
USE master;
exec sp_addlogin
@loginame = '<LoginName>',
@passwd = '<Password>',
@sid = <sid> ;
--8、准备备机数据库
--主库要备份两个bak文件
--第一个：完整备份
--第二个：事务日志备份 ，截断事务日志
--（1）先在备机还原完整备份，“restore with norecovery”和 覆盖现有数据库
--做了第一步之后，S_C_SC-》任务-》还原-》事务日志 按钮才可用
--（2）再还原事务日志，“restore with norecovery” 和时间点：最近状态
--如果执行成功备机数据库将会变成 "正在还原"
--由于是实验，没有为服务器配置双网卡，IP地址与图有点不一样，但是原理一样。
--9、必须要在镜像数据库中先设置好伙伴后，才能在主体服务器执行
--再在主体服务器实例上，将 备机上的服务器实例设置为伙伴
--备机执行
USE master
ALTER DATABASE S_C_SC SET PARTNER = 'TCP://192.168.1.100:5022'
--主机执行
USE master
ALTER DATABASE S_C_SC SET PARTNER = 'TCP://192.168.1.103:5022'
--10、在主机执行设置见证服务器
ALTER DATABASE S_C_SC SET WITNESS = 'TCP://192.168.1.101:5022';
GO
--此时主：S_C_SC(主体，已同步) 备: S_C_SC(镜像，已同步/正在还原)
--TCP://192.168.1.100:主
--TCP://192.168.1.103:备
--如果删除不了正在还原的备库，那么可以重启SQL服务，就可以删除了！！！！！！！！！！
--故障解决: ping ip telnet 5022端口
-------------------------测试------------------------------
--默认情况下，事务安全级别的设置为 FULL，即同步运行模式，而且SQL Server 2005 标准版只支持同步模式。
--关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。
USE master;
ALTER DATABASE S_C_SC SET PARTNER SAFETY FULL --（默认）事务安全,同步模式 镜像的更改和主体的更改都同步
ALTER DATABASE S_C_SC SET PARTNER SAFETY OFF --事务不安全,异步模式 只有主体的更改同步
--1、主备互换
--主机停掉SQL服务
--2、主服务器Down掉,备机紧急启动并且开始服务
--备机执行：
USE master;
ALTER DATABASE S_C_SC SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
ALTER DATABASE S_C_SC SET ONLINE
--3、开启主机的SQL服务，原来的主服务器恢复,可以继续工作,需要重新设定镜像
--备机执行：
USE master;
ALTER DATABASE S_C_SC SET PARTNER RESUME; --恢复镜像
ALTER DATABASE S_C_SC SET PARTNER FAILOVER; --切换到主机
--4、原来的主服务器恢复,可以继续工作
--查看当前服务器做了镜像partner的那个服务器
SELECT * FROM sys.database_mirroring
-------------------------------删除镜像---------------------
--查看终端点
select * from sys.endpoints
--删除某终端点(终端点不带引号)
drop ENDPOINT endpoint_Mirroring
--删除证书 在master | Security | Certificates
--删除用户 在master | User
--然后可以删除登录名 drop login <login_name>
--修改master key :
alter master key drop encryption by service master key
-- 删除master key :
drop master key
--删除镜像的命令:
ALTER DATABASE S_C_SC set partner OFF
RESTORE DATABASE S_C_SC WITH recovery --放弃事务，立刻还原
-------------解除数据库镜像---------------------------------
ALTER DATABASE S_C_SC SET PARTNER OFF
给我写信：
 QQ我：
Friday, November 09, 2012 2:02 PM
-
各有各立场和观察角度吧
如果你玩下虚拟化平台，再回头看下微软集群
对MS Office/Win/SQL Server偏爱，对MSCS偏厌
Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
Saturday, November 10, 2012 3:09 AM
-
你说的故障转移群集和共享存储是HA，微软集群不是个什么好东西，个人看法
只热备的话应该部署镜像
Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
不管MSCS是不是好东西，SQL的Failover cluster是依赖于它的。
做过大数据量高性能系统的就会知道，Mirror不见得比Failover cluster好用。
另外，没有什么是服务不中断的，只是时间长短而已。Hyper-V虚拟机做共享存储非常简单。
想不想时已是想，不如不想都不想。
Saturday, November 10, 2012 5:29 AM
-
怡红公子大侠说得对，根据实际情况选择合适的解决方案
节省成本就使用Mirror
高一点使用MSCS
最高一点 虚拟化
给我写信：
QQ我：
Saturday, November 10, 2012 5:52 AM
-
Any reason give VM highest ranking? Don't know anyone use VM in large environment.
Saturday, November 10, 2012 8:52 PM
-
Any reason give VM highest ranking? Don't know anyone use VM in large environment.
看实际情况了，有些大客户会有很多小系统的。比如海关、医院。
想不想时已是想，不如不想都不想。
Sunday, November 11, 2012 12:34 AM
-
不一定是非常大的系统
给我写信：
QQ我：
Sunday, November 11, 2012 2:03 AM
-
That's why bothers me. How come VM get highest ranking, what's your standard?
Sunday, November 11, 2012 7:40 PM
-
Monday, November 12, 2012 1:37 AM
-
你好，你的方法是不是要有3台服务器安装SQL，一台主机，一台备机，一台做见证，安装完SQL后，打开SQL输入你提供的命令，来实现镜像功能？因为以前没做过这块，所以比较小白，想细致了解一下，高手有空来指点指点，谢谢了。
Monday, November 12, 2012 2:24 AM
-
你好，你的方法是不是要有3台服务器安装SQL，一台主机，一台备机，一台做见证，安装完SQL后，打开SQL输入你提供的命令，来实现镜像功能？因为以前没做过这块，所以比较小白，想细致了解一下，高手有空来指点指点，谢谢了。
你好，
那个见证服务器是可选的，你可以选择安装也可以不安装，见证服务器的作用就是，如果主机出错，那见证服务器就会自动地实现故障转移，然后使备机转化成主机，代替主机继续工作，如果你不安装的就只好出错的时候，自己手动转移了。
也不是说要输入命令，SSMS 里这些都有的，你直接点击就可以了，因为我配置的时候也不愿意敲代码，不过这里有现成的你可以直接复制就好。
这篇文档可以手把手教你如果配置，不用写命令，请参考：http://www.doc88.com/p-690922020761.html 。
有什么不清楚的，在问我们，大家相互学习啦。
Thanks,
Amy Peng
立刻免费下载TechNet论坛好帮手
-
Marked as answer by
Rik1012
Tuesday, November 13, 2012 1:50 AM
Monday, November 12, 2012 2:52 AM
-
你好，
那个见证服务器是可选的，你可以选择安装也可以不安装，见证服务器的作用就是，如果主机出错，那见证服务器就会自动地实现故障转移，然后使备机转化成主机，代替主机继续工作，如果你不安装的就只好出错的时候，自己手动转移了。
HI，AMY，你提供的资料太有用了，非常感谢你的帮助，不过我在做镜像的时候出了个问题，说是无法连接到镜像服务器（我账号没验证发布了图。。闷）
我根据提示去查了一下我的远程连接设置，基本都是正常的，但是在主机的SQL设置中，查看数据库方面的选项里面没有发现RemoteAccessEnable项目，是不是有哪里还需要设置？在备机打开数据库方面选项的时候却提示不具备权限，我的SQL管理账号是加在Domain Admins里面的，权限应该够了吧。
Monday, November 12, 2012 8:24 AM
-
Monday, November 12, 2012 9:09 AM
-
哈哈，终于搞定了，原来是sql server browser禁用了，没有搭见证服务器的已经弄好了，手动测试故障转移也成功了，接下来做个见证服务器测试。
太感谢AMY了，另外我还有点疑问想请教一下，这种SQL镜像是同时基于数据库和服务器的吗，我的意思是，如果主机上的DB坏了，他能启用备机的DB，如果主机的服务器挂了，备机是否也能正常转换角色。
Monday, November 12, 2012 9:43 AM
-
DATABASE MIRRORING是基于数据库级别的,Cluster是基于Instance级别的。镜像可以自动转移也可以手动转移，在于你选的是High-performance还是High-safety模式。在DB
Connection中可以使用Failover Partner参数
Monday, November 12, 2012 1:49 PM
-
哈哈，终于搞定了，原来是sql server browser禁用了，没有搭见证服务器的已经弄好了，手动测试故障转移也成功了，接下来做个见证服务器测试。
太感谢AMY了，另外我还有点疑问想请教一下，这种SQL镜像是同时基于数据库和服务器的吗，我的意思是，如果主机上的DB坏了，他能启用备机的DB，如果主机的服务器挂了，备机是否也能正常转换角色。
Depends on mirror config. If you set HS mode with witness, will switch all mirrored dbs to partner in case primary server goes down.
Monday, November 12, 2012 2:15 PM
-
哈哈，终于搞定了，原来是sql server browser禁用了，没有搭见证服务器的已经弄好了，手动测试故障转移也成功了，接下来做个见证服务器测试。
太感谢AMY了，另外我还有点疑问想请教一下，这种SQL镜像是同时基于数据库和服务器的吗，我的意思是，如果主机上的DB坏了，他能启用备机的DB，如果主机的服务器挂了，备机是否也能正常转换角色。
你好，
很高兴你的镜像环境搭建成功了，数据库镜像是基于数据库级别的，如果有见证服务器的话，那主机上的DB 坏了的话或者是主机的服务器坏了的话，就会自动进行角色切换，这样备机就会成为主机，如果没有见证服务器的话，那就只能自己手动将备机转化为主机了。
Thanks，
Amy Peng
立刻免费下载TechNet论坛好帮手
Tuesday, November 13, 2012 1:32 AM
-
十分感谢众多高手的指点，测试环境搭建顺利，故障转移也没有问题，这种方法比用微软的群集方便很多啊，不用心跳，不用装MSCS，再次感谢各位的帮忙，特别感谢AMY版主，O(∩_∩)O
-
Marked as answer by
Rik1012
Tuesday, November 13, 2012 1:45 AM
-
Unmarked as answer by
Rik1012
Tuesday, November 13, 2012 1:46 AM
-
Marked as answer by
Rik1012
Tuesday, November 13, 2012 1:48 AM
-
Unmarked as answer by
Rik1012
Tuesday, November 13, 2012 1:49 AM
Tuesday, November 13, 2012 1:44 AM
-
恭喜LZ
给我写信：
QQ我：
Tuesday, November 13, 2012 2:35 AM
-
十分感谢众多高手的指点，测试环境搭建顺利，故障转移也没有问题，这种方法比用微软的群集方便很多啊，不用心跳，不用装MSCS，再次感谢各位的帮忙，特别感谢AMY版主，O(∩_∩)O
DB mirroring is completely different from clustering, one is for HA and the other is for DR.
Tuesday, November 13, 2012 2:53 AM
-
十分感谢众多高手的指点，测试环境搭建顺利，故障转移也没有问题，这种方法比用微软的群集方便很多啊，不用心跳，不用装MSCS，再次感谢各位的帮忙，特别感谢AMY版主，O(∩_∩)O
Hi Rik1012,
哈哈，真开心你的所有问题都解决了，以后有什么问题，欢迎你来我们中文SQL论坛提问哈。大家相互学习哈。
Thanks,
Amy Peng
立刻免费下载TechNet论坛好帮手
|
