SQL2008双机热备方案

Question

公司要上一套系统，DB用SQL2008，怎么实现双机热备？

一种方案是用windows的故障转移群集搭配SQL自己的群集功能，这种方案需要有共享存储，我现在在虚拟测试环境没办法做实验，所以暂时先不考虑这个。

另外一种是用镜像的方式做双机热备，DB都放在服务器上，不用外接存储，主节点服务器DB实时复制到备用节点中，主节点故障后自动跳到备用节点，不会出现服务中断的问题。这种方式能否实现，该如何操作？因为没做过这种，所以思路有些乱，需要高手们给点指引，谢谢啦。

 

Answer 1

你说的故障转移群集和共享存储是HA，微软集群不是个什么好东西，个人看法

只热备的话应该部署镜像

---

Try SQL Server 2008 QQ:315054403 [e-mail]

Answer 2

Agree, books online tells you how to set db mirroring step by step.

Answer 3

微软集群不是个什么好东西，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我：

Answer 4

各有各立场和观察角度吧

如果你玩下虚拟化平台，再回头看下微软集群

对MS Office/Win/SQL Server偏爱，对MSCS偏厌

---

Try SQL Server 2008 QQ:315054403 [e-mail]

Answer 5

你说的故障转移群集和共享存储是HA，微软集群不是个什么好东西，个人看法

只热备的话应该部署镜像

---

Try SQL Server 2008 QQ:315054403 [e-mail]

不管MSCS是不是好东西，SQL的Failover cluster是依赖于它的。

做过大数据量高性能系统的就会知道，Mirror不见得比Failover cluster好用。

另外，没有什么是服务不中断的，只是时间长短而已。Hyper-V虚拟机做共享存储非常简单。

---

想不想时已是想，不如不想都不想。

Answer 6

怡红公子大侠说得对，根据实际情况选择合适的解决方案

节省成本就使用Mirror

高一点使用MSCS

最高一点 虚拟化

---

给我写信： QQ我：

Answer 7

Any reason give VM highest ranking? Don't know anyone use VM in large environment.

Answer 8

Any reason give VM highest ranking? Don't know anyone use VM in large environment.

看实际情况了，有些大客户会有很多小系统的。比如海关、医院。

---

想不想时已是想，不如不想都不想。

Answer 9

不一定是非常大的系统

---

给我写信： QQ我：

Answer 10

That's why bothers me. How come VM get highest ranking, what's your standard?

Answer 11

公司要上一套系统，DB用SQL2008，怎么实现双机热备？

一种方案是用windows的故障转移群集搭配SQL自己的群集功能，这种方案需要有共享存储，我现在在虚拟测试环境没办法做实验，所以暂时先不考虑这个。

另外一种是用镜像的方式做双机热备，DB都放在服务器上，不用外接存储，主节点服务器DB实时复制到备用节点中，主节点故障后自动跳到备用节点，不会出现服务中断的问题。这种方式能否实现，该如何操作？因为没做过这种，所以思路有些乱，需要高手们给点指引，谢谢啦。

你好，
利用镜像来实现双机热备份这个方案是可行的，详细的信息你可以参考下面这篇文档，这篇文章图文并茂的告诉你如何配置数据库镜像：http://www.databasejournal.com/features/mssql/article.php/3828341/Database-Mirroring-in-SQL-Server-2008.htm 。

Thanks，
Amy Peng

---

立刻免费下载TechNet论坛好帮手

Answer 12

你好，你的方法是不是要有3台服务器安装SQL，一台主机，一台备机，一台做见证，安装完SQL后，打开SQL输入你提供的命令，来实现镜像功能？因为以前没做过这块，所以比较小白，想细致了解一下，高手有空来指点指点，谢谢了。

Answer 13

你好，你的方法是不是要有3台服务器安装SQL，一台主机，一台备机，一台做见证，安装完SQL后，打开SQL输入你提供的命令，来实现镜像功能？因为以前没做过这块，所以比较小白，想细致了解一下，高手有空来指点指点，谢谢了。

你好，

那个见证服务器是可选的，你可以选择安装也可以不安装，见证服务器的作用就是，如果主机出错，那见证服务器就会自动地实现故障转移，然后使备机转化成主机，代替主机继续工作，如果你不安装的就只好出错的时候，自己手动转移了。

也不是说要输入命令，SSMS 里这些都有的，你直接点击就可以了，因为我配置的时候也不愿意敲代码，不过这里有现成的你可以直接复制就好。
这篇文档可以手把手教你如果配置，不用写命令，请参考：http://www.doc88.com/p-690922020761.html 。

有什么不清楚的，在问我们，大家相互学习啦。

Thanks,
Amy Peng

---

立刻免费下载TechNet论坛好帮手

Answer 14

你好，

那个见证服务器是可选的，你可以选择安装也可以不安装，见证服务器的作用就是，如果主机出错，那见证服务器就会自动地实现故障转移，然后使备机转化成主机，代替主机继续工作，如果你不安装的就只好出错的时候，自己手动转移了。

HI，AMY，你提供的资料太有用了，非常感谢你的帮助，不过我在做镜像的时候出了个问题，说是无法连接到镜像服务器（我账号没验证发布了图。。闷）

我根据提示去查了一下我的远程连接设置，基本都是正常的，但是在主机的SQL设置中，查看数据库方面的选项里面没有发现RemoteAccessEnable项目，是不是有哪里还需要设置？在备机打开数据库方面选项的时候却提示不具备权限，我的SQL管理账号是加在Domain Admins里面的，权限应该够了吧。

Answer 15

你好，

你说你要找RemoteAccessEnable，打开SSMS后直接右击服务器名，然后点击“方面”—〉“服务器配置”然后设置RemoteAccessEnable 为True；

TCP/IP 记得要enable，你可以根据我下面给的文章，看看远程连接哪里没配置好（文章里就有介绍怎样设置RemoteAccessEnable）：http://jingyan.baidu.com/article/6c67b1d6ca06f02787bb1ed1.html 。

如果还是报错，希望你可以把错误信息发一下给我们。

Thanks,
Amy Peng

---

立刻免费下载TechNet论坛好帮手

Answer 16

哈哈，终于搞定了，原来是sql server browser禁用了，没有搭见证服务器的已经弄好了，手动测试故障转移也成功了，接下来做个见证服务器测试。

太感谢AMY了，另外我还有点疑问想请教一下，这种SQL镜像是同时基于数据库和服务器的吗，我的意思是，如果主机上的DB坏了，他能启用备机的DB，如果主机的服务器挂了，备机是否也能正常转换角色。

Answer 17

DATABASE MIRRORING是基于数据库级别的,Cluster是基于Instance级别的。镜像可以自动转移也可以手动转移，在于你选的是High-performance还是High-safety模式。在DB Connection中可以使用Failover Partner参数

Answer 18

哈哈，终于搞定了，原来是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.

Answer 19

哈哈，终于搞定了，原来是sql server browser禁用了，没有搭见证服务器的已经弄好了，手动测试故障转移也成功了，接下来做个见证服务器测试。

太感谢AMY了，另外我还有点疑问想请教一下，这种SQL镜像是同时基于数据库和服务器的吗，我的意思是，如果主机上的DB坏了，他能启用备机的DB，如果主机的服务器挂了，备机是否也能正常转换角色。

你好，

很高兴你的镜像环境搭建成功了，数据库镜像是基于数据库级别的，如果有见证服务器的话，那主机上的DB 坏了的话或者是主机的服务器坏了的话，就会自动进行角色切换，这样备机就会成为主机，如果没有见证服务器的话，那就只能自己手动将备机转化为主机了。

Thanks，
Amy Peng

---

立刻免费下载TechNet论坛好帮手

Answer 20

十分感谢众多高手的指点，测试环境搭建顺利，故障转移也没有问题，这种方法比用微软的群集方便很多啊，不用心跳，不用装MSCS，再次感谢各位的帮忙，特别感谢AMY版主，O(∩_∩)O

Answer 21

恭喜LZ

---

给我写信： QQ我：

Answer 22

十分感谢众多高手的指点，测试环境搭建顺利，故障转移也没有问题，这种方法比用微软的群集方便很多啊，不用心跳，不用装MSCS，再次感谢各位的帮忙，特别感谢AMY版主，O(∩_∩)O

DB mirroring is completely different from clustering, one is for HA and the other is for DR. 

Answer 23

十分感谢众多高手的指点，测试环境搭建顺利，故障转移也没有问题，这种方法比用微软的群集方便很多啊，不用心跳，不用装MSCS，再次感谢各位的帮忙，特别感谢AMY版主，O(∩_∩)O

Hi Rik1012,

哈哈，真开心你的所有问题都解决了，以后有什么问题，欢迎你来我们中文SQL论坛提问哈。大家相互学习哈。

Thanks,
Amy Peng

---

立刻免费下载TechNet论坛好帮手