none
SQL2008双机热备方案 RRS feed

  • 问题

  • 公司要上一套系统,DB用SQL2008,怎么实现双机热备?

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

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

     

    2012年11月9日 7:42

答案

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

    你好,

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

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

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

    Thanks,
    Amy Peng


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

    • 已标记为答案 Rik1012 2012年11月13日 1:50
    2012年11月12日 2:52
    版主

全部回复

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

    只热备的话应该部署镜像


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2012年11月9日 9:56
  • Agree, books online tells you how to set db mirroring step by step.
    2012年11月9日 13:47
  • 微软集群不是个什么好东西,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我:点击这里给我发消息

    2012年11月9日 14:02
  • 各有各立场和观察角度吧

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

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


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2012年11月10日 3:09
  • 你说的故障转移群集和共享存储是HA,微软集群不是个什么好东西,个人看法

    只热备的话应该部署镜像


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

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

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

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


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

    2012年11月10日 5:29
    版主
  • 怡红公子大侠说得对,根据实际情况选择合适的解决方案

    节省成本就使用Mirror

    高一点使用MSCS

    最高一点 虚拟化


    给我写信: QQ我:点击这里给我发消息

    2012年11月10日 5:52
  • Any reason give VM highest ranking? Don't know anyone use VM in large environment.
    2012年11月10日 20:52
  • Any reason give VM highest ranking? Don't know anyone use VM in large environment.

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

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

    2012年11月11日 0:34
    版主
  • 不一定是非常大的系统

    给我写信: QQ我:点击这里给我发消息

    2012年11月11日 2:03
  • That's why bothers me. How come VM get highest ranking, what's your standard?
    2012年11月11日 19:40
  • 公司要上一套系统,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


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

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

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

    你好,

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

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

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

    Thanks,
    Amy Peng


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

    • 已标记为答案 Rik1012 2012年11月13日 1:50
    2012年11月12日 2:52
    版主
  • 你好,

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

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

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

    2012年11月12日 8:24
  • 你好,

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

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

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

    Thanks,
    Amy Peng


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

    2012年11月12日 9:09
    版主
  • 哈哈,终于搞定了,原来是sql server browser禁用了,没有搭见证服务器的已经弄好了,手动测试故障转移也成功了,接下来做个见证服务器测试。

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

    2012年11月12日 9:43
  • DATABASE MIRRORING是基于数据库级别的,Cluster是基于Instance级别的。镜像可以自动转移也可以手动转移,在于你选的是High-performance还是High-safety模式。在DB Connection中可以使用Failover Partner参数

    2012年11月12日 13:49
  • 哈哈,终于搞定了,原来是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.
    2012年11月12日 14:15
  • 哈哈,终于搞定了,原来是sql server browser禁用了,没有搭见证服务器的已经弄好了,手动测试故障转移也成功了,接下来做个见证服务器测试。

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

    你好,

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

    Thanks,
    Amy Peng


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

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

    • 已标记为答案 Rik1012 2012年11月13日 1:45
    • 取消答案标记 Rik1012 2012年11月13日 1:46
    • 已标记为答案 Rik1012 2012年11月13日 1:48
    • 取消答案标记 Rik1012 2012年11月13日 1:49
    2012年11月13日 1:44
  • 恭喜LZ

    给我写信: QQ我:点击这里给我发消息

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

    DB mirroring is completely different from clustering, one is for HA and the other is for DR. 
    2012年11月13日 2:53
  • 十分感谢众多高手的指点,测试环境搭建顺利,故障转移也没有问题,这种方法比用微软的群集方便很多啊,不用心跳,不用装MSCS,再次感谢各位的帮忙,特别感谢AMY版主,O(∩_∩)O

    Hi Rik1012,

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

    Thanks,
    Amy Peng


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



    2012年11月13日 3:02
    版主