数据库镜像技术实现SQLServer2005双机热备

1、、配置主备机1、连接方式1. 同网段（域环境）适用于域环境中，相对证书方式，配置相对简单，省去了建立密钥、证书及多次为用户 授权的过程2. 同网段（非域）：证书方式配置相对较繁琐，基本步骤：1. 各服务器生成主密钥 （MASTER KEY）2. 各服务器生成服务证书（CERTIFICATE）3. 使用服务证书生成端点（ENDPOINT）4. 将证书备份为文件，并复制到其他服务器5. 各服务器对其他服务器生成访问授权1）生成登陆2）生成用户3）绑定证书到用户4）授予相关用户对ENDPOINT的连接访问权6. 主机上备份数据库（最好加上日志备份）7. 镜像机上还原数据库（norecovery ）8

2、. 为镜像机配置镜像伙伴9. 为主机配置镜像伙伴和见证服务器2、检查SQL Server 2005 数据库只有SQL Server 2005标准版、企业版和开发版才可以建立数据镜像。其他版本即Express只能作为见证服务器。如果实在不清楚什么版本，执行如下语句查看：select vers ion ;若要对此数据库进行数据库镜像，必须将它更改为使用完整恢复模式。若要用Tran sact-SQL实现此目的，请使用 ALTER DATABASE 语句：USE master;ALTER DATABASE < DatabaeName >SET RECOVERY FULL ;二、同网段（非域

3、）主备实例互通注意：实实现互通可以使用域或证书来实现，考虑实现的简单，以下选取证书的方式实现。现 主备数据库实例互通”的操作只需要做一次，例如为了将两个 SQL Server 2005 的实例 中的5个数据库建成镜像关系，则只需要做一次以下操作就可以了；或者这样理解：每 对主备实例(不是数据库)做一次互通。1、创建证书-主体服务器执行：USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test_1234'CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A

4、certificate' START_DATE = '07/18/2012'-镜像服务执行：USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test_1234'CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate' START_DATE = '07/18/2012'-见证服务器执行：USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD

5、= 'test_1234'CREATE CERTIFICATE HOST_C_cert WITH SUBJECT = 'HOST_C certificate' START_DATE = '07/18/2012'MASTER这个MASTER KEY 对于每个sql server 实例唯一的，如果数据库中已经创建了KEY，可以用如下的sql删除之后重新执行：DROP MASTER KEY2、创建连接的端点-主体服务器执行:CREATE ENDPOINT Endpoint_MirroringSTATE = STARTEDASTCP ( LISTENER

6、_PORT = 5022, LISTENER_IP = ALL )FORDATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION= REQUIRED ALGORITHM AES , ROLE = ALL );-镜像服务器执行：CREATE ENDPOINT Endpoint_MirroringSTATE = STARTEDASTCP ( LISTENER_PORT = 5022 , LISTENER*= ALL )FORDATABASE_MIRRORING(AUTHENTICATION = CERTIFIC

7、ATE HOST_B_cert , ENCRYPTION= REQUIRED ALGORITHM AES , ROLE = ALL );-见证服务器执行：CREATE ENDPOINT Endpoint_MirroringSTATE = STARTEDASTCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL )FORDATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE HOST_C_cert , ENCRYPTION= REQUIRED ALGORITHM AES , ROLE = ALL );3、备份证书以备建立

8、互联-主体服务器执行：BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:SQLBackupHOST_A_cert.cer'-镜像服务器执行：BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:SQLBackupHOST_B_cert.cer'-见证服务器执行：BACKUP CERTIFICATE HOST_C_cert TO FILE = 'D:SQLBackupHOST_C_cert.cer'4、互换证书将备份到的证书进行互换，即HOST_A_cert.cer 、HOST

9、_C_cert.cer复制到镜像服务器的。HOST B cert.cer 、HOST C cert.cer 复制到主体服务器的。HOST A cert.cer 、HOST B cert.cer复制到见证服务器的SQL5、添加登陆名、用户以下操作只能通过命令行运行，通过图形界面无法完成。（截至文档编写结束,Server2005的不定号为SP2)-主体服务器执行：CREATE LOGINHOST_B_login WITH PASSWORD = 'test_1234'CREATE USER HOST_B_user FOR LOGIN HOST_B_login;CREATE CERTI

10、FICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:SQLBackupHOST_B_cert.cer'GRANT CONNECT ON ENDPOINT:Endpoint_MirroringTO HOST_B_login;CREATE LOGIN HOST_C_login WITH PASSWORD = 'test_1234'CREATE USER HOST_C_user FOR LOGIN HOST_C_login;CREATE CERTIFICATE HOST_C_cert AUTHORIZ

11、ATION HOST_C_user FROM FILE = 'D:SQLBackupHOST_C_cert.cer'GRANT CONNECT ON ENDPOINT:Endpoint_MirroringTO HOST_C_login ;-镜像服务器执行：CREATE LOGIN HOST_A_login WITH PASSWORD = 'test_1234'CREATE USER HOST_A_user FOR LOGIN HOST_A_login;CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user

12、 FROM FILE = 'D:SQLBackupHOST_A_cert.cer'GRANT CONNECT ON ENDPOINT:E ndpoi nt_Mirrori ng TO HOST_A_logi n;CREATE LOGIN HOST_C_login WITH PASSWORD = 'test_1234'CREATE USER HOST_C_user FOR LOGIN HOST_Cogin;CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:SQLB

13、ackupHOST_C_cert.cer'GRANT CONNECT ON ENDPOINT:Endpoint_MirroringTO HOST_C_login ;-见证服务器执行：CREATE LOGIN HOST_A_login WITH PASSWORD = 'test_1234'CREATE USER HOST_A_user FOR LOGIN HOST_A_login;CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:SQLBackupHOST_A_cert.

14、cer'GRANT CONNECT ON ENDPOINT:E ndpoi nt_Mirrori ng TO HOST_A_logi n;CREATE LOGIN HOST_B_login WITH PASSWORD = 'test_1234'CREATE USER HOST_B_user FOR LOGIN HOST_B_login;CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:SQLBackupHOST_B_cert.cer'GRANT CONNECT

15、ON ENDPOINT:Endpoint_MirroringTO HOST_B_login;三、建立镜像关系1、准备备机数据库承接上文，该节是描述如何同步主备数据库内的数据。可以尝试从刚刚使用的全备文件进行还原，在还原数据的时候需要使用选上“with non recover 。女口图所示:0 X蓄:Uij*处鬲班力?>；lJMC “药 f kl*iMiia'«i«l t 憾 Stf iwXlll.C Mrc更 E kltalkct»«E t SQL 5trv«MtSHQJL .Rrtrs厂H 超見呼訖于叭入性电孤乩 天兀昭传自枣L

16、- grw 口T：* wajw.T:*，才即f Lll;,i-5ta»3-3 -：U . JTUIT im JUICEUBL勰盐磐gp站硼執*却鳄5如-皿赠期岫如果执行成功数据库将会变成这个样子:恥wDE （IE在还原.）建立镜像ALTER DATABASEmydatabaseSET PARTNER='TCP:/192.168.31.81:5022'-如果主体执行不成功，尝试在备机中执行如下语句：ALTER DATABASEmydatabaseSET PARTNER='TCP:/192.168.31.73:5022'-主体服务器执行:1连接蜚曲7<

17、;> JFIERYSEmiiQl (S?L Server 3-Q. 2Q4T -辭)日LJ数据库l±f _i系练数据库±1 LU数据库快鹽EJAdv«RtitrsWors屈删惴鼻踽昭蜩I+i TwTotfWr淖R町皿说就申f$EERYERO1. htp 4r IS er ke r $SKhYERJO 1 TempDE国Itst帀安全性耳I服务器对象+ Li羞制田匚j曽耀FF ,._j Uati ficat i on Serwi cest SQL Server 代理B j JEF.FERYSERVIR02 (SSL Server 9 0. 047 - JEFT

18、EEYYAdminislri 三LJ数据库出i_i系统数据库甲数据康快殁tJ DBMirror除像，己同步/正在还原.)i±i乙业性FH服务器对掠EJ丄臺制囹Cj管理口A 、 J Wnti fi r'st 1 nn £ fivwi L«a s如果执行成功，则主备数据库将会呈现如上图所示的图标。如果建立失败，提示类似数据库事务日志未同步，则说主备数据库的数据（日志）未同步，为保证主备数据库内的数据一致，应在主数据库中实施一次 事务日志”备份，并还原到备数据库上。备份事务日志”如图所示:亀宅舉缆 I还原事务日志时需在选项中选择"restore wit

19、h norecovery ”，如图所示:成功还原以后再执行建立镜像的SQL语句。三域帐户模式：3.1.1更改mssqlserver服务的的登陆方式为域帐户登陆方式：进入windows服务管理控制台,更改服务登陆帐户，使域账户有更改 MSSQL SERVE服 务状态的权限三台机器都做同样设置3.1.2 建立端点 : 通过图形界面建立端点： 启动 SQLW，B 按图一直下一步yIicrosoft SQL Server Ianageaent Studio文件0)編辑视閔&)工具Q)窗口社区C)帮助|畢學亘会I 口凰4 4 61、一- A3耳 / A之.丿朗唾询® 乜；峦叨阴ii a

20、 H g 葷回降加:g1丘对象资源管理器Fftt列表)回报表©+1 192. 168. 136. 223 (SQL Server 9SJ 192.168. 136. 222 (SQL Server 9 匚二数据库L+ LJ系统数据库+ LJ数据库快照也UK+ 二安全性*二I服务器划t*二复制* 一.笞理+ Notifici直 SQL Serccerp_jzt (主体AD-SERVER数据库ccerpjzt 住体已同步)已同步)新建数据库). 新津杳询(Q)编写数据库脚本为$) 任务d)重命名他) 删除)刷新0) 属性©)分离CD)-.-脱机 联机Q)收缩G)备份©

21、). 还原毬)镜像迤):垢库关东窗伎送争务日志).9 0 40-6 - “)JMK口11£ KAffitnicesStr性 r 旬 « R rn件傑«丈文d = _三一三?31 i5»?KIWd«V 辛冋导>配誉数据库镜像安全向导it町;.恪础Mi圮芒除窃体jar -ccerpjxr的交童舌石. 您可以住用勺向与正下期绍鏗上3电安全诗肯委7ss如果成功则:I 血 CQJT-fflTWESS (SQL Server 9.O.4035 -匚J7T-TTMESS 田口数据库 旦Q宾全性*1 Lj服务器為象和丄复制田口育理r . _j Wotif

22、i cation Servicesf SQL 3 erver 代理|1 日2 l&g 1ST 32 出QL Serv&r 0. 4035 - CQjZT-TaTtlBSS曰Cj|数据库It.系统敷据库EtJ 一I数据库快照:十 ccerp_i it 住悴/正在司步I 国口安全性+. 服勢器对象±J LJ复制ccerp_jzt (上CQJSTLE-TSSTl l数据库屬瀋库笑紊anh ib-Bi :-> i-n nn?表视團一I同我词可编程性_ vi ce Erck«r 存储 _安全性用営理可 一J Hoti fi citi on S4t*vi«

23、;s±1 SQL灭m 代理| 油 1Q2. 1&8. 1ST 44 GQL 恥eh .0. 403E - fQJlTY工THE空 -I 口暫站库圧一i系轨数据库吐数第库快照.ccerp_ixt 触像#正在同步/正在恢复） 田LJ晏全性可一j服务欝对象ijJ _j复制闰口管理国口 Ho ti fi cati on Services3?SQL Server 代理四、测试操作1、主备互换-主机执行：1 USE master;2 ALTER DATABASE < DatabaseName > SET PARTNER FAILOVER;2、主服务器Down掉,备机紧急启动并

24、且开始服务-备机执行:1 USE master;2 ALTER DATABASE < DatabaseName > SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;3、原来的主服务器恢复，可以继续工作，需要重新设定镜像1 -备机执行：2 USE master;3 ALTER DATABASE < DatabaseName> SET PARTNER RESUME;-恢复镜像4 ALTER DATABASE < DatabaseName> SET PARTNER FAILOVER; -切换主备4、原来的主服务器恢复，可以继续工

25、作-默认情况下，事务安全级别的设置为FULL，即同步运行模式，而且SQL Server 2005标准版只支持同步模式。-关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。1 USE master;2 ALTER DATABASE < DatabaseName> SET PARTNER SAFETYFULL ;-事务安全,同步模式3 ALTER DATABASE < DatabaseName> SET PARTNER SAFETYOFF;-事务不安全,异步模式安装遇到的错误说明：消息1498，级别16，状态3，第1行默认情况下，数据库镜像是被禁用的。 当前提

26、供的数据库镜像仅供评估使用，并不应使用于生产环境中。若要以评估为目的启用数据库镜像，请在启动过程中使用跟踪标志1400。有关跟踪标志和启动选项的详细信息，请参阅 SQL Server 联机丛书。解决办法：没打SP1以上补丁 .强烈建议打 SP3消息1475，级别16，状态2，第1行由于"mydatabase"数据库可能有尚未备份的大容量日志记录更改，所以无法启用数据库镜像。必须在镜像上还原主体数据库的上一次日志备份。（Microsoft SQL Server ，错误：1478）没有进行事务日志备份所致。解决步骤如下：1. 先在主服务器备份数据库，数据库和日志各备份一次2. 在镜像服务器上还原数据库（WITH NORECOVERY（Microsoft SQL Server ，错误：1418）域名不正确，连接失败主数据库，镜像，见