SqlServer2005数据库镜像实践
|
?个人数据库镜像实践的代码 ? 参考 http://www.cnblogs.com/killkill/archive/2008/05/23/1205792.html ? http://kb.cnblogs.com/page/45937/ ? ======================?? 主機執行 ======================?? 備機執行 ======================?? 見證服務器執行 ? ? ? ? ? ? ? ====================================================================== 環境： ??????數據庫： ?????? 主體服務器IP:192.168.25.192? ?Microsoft?SQL?Server?2005??Standard?Edition???sp3?? /Windows? server2003 ????????備份服務器IP:192.168.25.165?? Microsoft?SQL?Server?2005??Standard?Edition???sp3??/Windows? server2003 ????????見證服務器IP:192.168.25.105?? Microsoft?SQL?Server?2005??Express?Edition???Sp3???/Windows? XP 步驟提示： ??????1.創建完證書后,需要A,B,C三臺機器互相交換證書 ?????2.執行到創建完證書后，需要在備機附加主機數據庫的完全備份? ====================================================================== ? ? ====================================================================== 創建證書 --查看數據庫版本 select?@@version; --修改mirror數據庫的備份模式 USE?master;? ALTER?DATABASE?mirror? SET?RECOVERY?FULL; --備份mirror數據庫 BACKUP?DATABASE?mirror? TO?DISK?=?'c:/mirror/mirror.bak'? WITH?FORMAT /* drop?CERTIFICATE?HOST_A_cert drop?MASTER?KEY? */ USE?master; CREATE?MASTER?KEY?ENCRYPTION?BY?PASSWORD?=?'jijunwu123'; --為此服務器創建一個證書 CREATE?CERTIFICATE?HOST_A_cert?WITH?SUBJECT?=?'HOST_A?certificate'?,? START_DATE?=?'11/17/2009';? ---------------------------------------------------------------------------------------------------------------------- /* drop?CERTIFICATE?HOST_B_cert drop?MASTER?KEY? */ USE?master; CREATE?MASTER?KEY?ENCRYPTION?BY?PASSWORD?=?'jijunwu123'; CREATE?CERTIFICATE?HOST_B_cert?WITH?SUBJECT?=?'HOST_B?certificate',? START_DATE?=?'11/17/2009'; ---------------------------------------------------------------------------------------------------------------------- /* drop?CERTIFICATE?HOST_C_cert drop??MASTER?KEY */ USE?master; CREATE?MASTER?KEY?ENCRYPTION?BY?PASSWORD?=?'jijunwu123'; CREATE?CERTIFICATE?HOST_C_cert?WITH?SUBJECT?=?'HOST_C?certificate',? START_DATE?=?'11/17/2009'; ====================================================================== 創建鏡像端點 /* drop??ENDPOINT?Endpoint_Mirroring? */ --使用證書為服務器創建一個鏡像端點 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); ---------------------------------------------------------------------------------------------------------------------- /* drop?ENDPOINT?Endpoint_Mirroring? */ CREATE?ENDPOINT?Endpoint_Mirroring? STATE?=?STARTED? AS? TCP?(?LISTENER_PORT=5022?,?LISTENER_IP?=?ALL?)? FOR? DATABASE_MIRRORING? (?AUTHENTICATION?=?CERTIFICATE?HOST_B_cert?,?ROLE?=?ALL?); ---------------------------------------------------------------------------------------------------------------------- /* drop?ENDPOINT?Endpoint_Mirroring? */ CREATE?ENDPOINT?Endpoint_Mirroring? STATE?=?STARTED? AS? TCP?(?LISTENER_PORT=5022?,?LISTENER_IP?=?ALL?)? FOR? DATABASE_MIRRORING? (?AUTHENTICATION?=?CERTIFICATE?HOST_C_cert?,?ROLE?=?WITNESS?); ? ====================================================================== 備份證書 ???備份證書后需要三臺機器互相交換證書，每臺機器都包括?證書A,C ? BACKUP?CERTIFICATE?HOST_A_cert?TO?FILE?=?'C:/mirror/HOST_A_cert.cer'; ---------------------------------------------------------------------------------------------------------------------- BACKUP?CERTIFICATE?HOST_B_cert?TO?FILE?=?'C:/mirror/HOST_B_cert.cer'; ---------------------------------------------------------------------------------------------------------------------- BACKUP?CERTIFICATE?HOST_C_cert?TO?FILE?=?'C:/mirror/HOST_C_cert.cer'; ====================================================================== 創建關聯用戶 /* drop?CERTIFICATE?HOST_B_cert drop?LOGIN?HOST_B_login drop?USER?HOST_B_user */ --在HOST上為HOST_B創建一個登錄名 CREATE?LOGIN?HOST_B_login?WITH?PASSWORD?=?'jijunwu123'; --創建一個使用該登錄名的用戶 CREATE?USER?HOST_B_user?FOR?LOGIN?HOST_B_login; --使證書與該用戶關聯 CREATE?CERTIFICATE?HOST_B_cert?AUTHORIZATION?HOST_B_user?FROM?FILE?=?'C:/mirror/HOST_B_cert.cer'; --授予对远程镜像端点的登录名的?CONNECT?权限 GRANT?CONNECT?ON?ENDPOINT::Endpoint_Mirroring?TO?[HOST_B_login]; /* drop?CERTIFICATE?HOST_C_cert drop?LOGIN?HOST_C_login drop?USER?HOST_C_user */ CREATE?LOGIN?HOST_C_login?WITH?PASSWORD?=?'jijunwu123'; CREATE?USER?HOST_C_user?FOR?LOGIN?HOST_C_login; CREATE?CERTIFICATE?HOST_C_cert?AUTHORIZATION?HOST_C_user?FROM?FILE?=?'C:/mirror/HOST_C_cert.cer'; GRANT?CONNECT?ON?ENDPOINT::Endpoint_Mirroring?TO?[HOST_C_login]; ---------------------------------------------------------------------------------------------------------------------- /* drop?CERTIFICATE?HOST_A_cert drop?LOGIN?HOST_A_login drop?USER?HOST_A_user */ CREATE?LOGIN?HOST_A_login?WITH?PASSWORD?=?'jijunwu123'; CREATE?USER?HOST_A_user?FOR?LOGIN?HOST_A_login; CREATE?CERTIFICATE?HOST_A_cert?AUTHORIZATION?HOST_A_user?FROM?FILE?=?'C:/mirror/HOST_A_cert.cer'; GRANT?CONNECT?ON?ENDPOINT::Endpoint_Mirroring?TO?[HOST_A_login]; /* drop?CERTIFICATE?HOST_C_cert drop?LOGIN?HOST_C_login drop?USER?HOST_C_user */ CREATE?LOGIN?HOST_C_login?WITH?PASSWORD?=?'jijunwu123'; CREATE?USER?HOST_C_user?FOR?LOGIN?HOST_C_login; CREATE?CERTIFICATE?HOST_C_cert?AUTHORIZATION?HOST_C_user?FROM?FILE?=?'C:/mirror/HOST_C_cert.cer'; GRANT?CONNECT?ON?ENDPOINT::Endpoint_Mirroring?TO?[HOST_C_login]; ---------------------------------------------------------------------------------------------------------------------- /* drop?CERTIFICATE?HOST_A_cert drop?LOGIN?HOST_A_login drop?USER?HOST_A_user */ CREATE?LOGIN?HOST_A_login?WITH?PASSWORD?=?'jijunwu123'; CREATE?USER?HOST_A_user?FOR?LOGIN?HOST_A_login; CREATE?CERTIFICATE?HOST_A_cert?AUTHORIZATION?HOST_A_user?FROM?FILE?=?'C:/mirror/HOST_A_cert.cer'; GRANT?CONNECT?ON?ENDPOINT::Endpoint_Mirroring?TO?[HOST_A_login]; /** drop?CERTIFICATE?HOST_B_cert drop?LOGIN?HOST_B_login drop?USER?HOST_B_user */ CREATE?LOGIN?HOST_B_login?WITH?PASSWORD?=?'jijunwu123'; CREATE?USER?HOST_B_user?FOR?LOGIN?HOST_B_login; CREATE?CERTIFICATE?HOST_B_cert?AUTHORIZATION?HOST_B_user?FROM?FILE?=?'C:/mirror/HOST_B_cert.cer'; GRANT?CONNECT?ON?ENDPOINT::Endpoint_Mirroring?TO?[HOST_B_login]; ? ? ? ====================================================================== 創建登錄用戶 ? 主機執行 USE?master; select?sid,name?from?syslogins; --查詢結果如圖,如果在程序中使用test登錄數據庫
? 備機執行 USE?master; exec?sp_addlogin? @loginame?=?'test',? @passwd?=?'test用戶的密碼',? @sid?=?0xC091ADF7CF65C84183FD67EB206CD835?; ? ====================================================================== 設置伙伴服務器 ? 備機中執行 ALTER?DATABASE??mirror??SET?PARTNER?=?'TCP://192.168.25.192:5022'; ---------------------------------------------------------------------------------------------------------------------- 主機中執行 --在主體中設置備份服務器為伙伴，使備份機做為初始鏡像服務器 ALTER?DATABASE?mirror?SET?PARTNER?=?'TCP://192.168.25.165:5022'; --設置見證服務器 ALTER?DATABASE?mirror?SET?WITNESS?=?'TCP://192.168.25.105:5022'; ? ====================================================================== 查看狀態 在主/備服務器中分別執行 select?mirroring_role_desc,mirroring_partner_name,mirroring_partner_instance,mirroring_witness_name,mirroring_witness_state_desc?from?sys.database_mirroring? where?database_id=?(select?database_id?from?sys.databases?where?name='mirror') ? 在見證服務器執行 ? 比較重要的，查看??mirroring_witness_state_desc???是否是??CONNECTED 如果不是，可以嘗試從新啟動見證服務器鏡像端點 --重新启动,鏡像端點 ALTER?ENDPOINT?Endpoint_Mirroring? STATE?=?STOPPED GO ? --?等5?秒钟 WAITFOR?DELAY?'00:00:05' GO ? ALTER?ENDPOINT?Endpoint_Mirroring? STATE?=?STARTED ? ====================================================================== 鏡像角色的修改 --主備互換，主機執行 ALTER?DATABASE?mirror??SET??PARTNER?FAILOVER ====================================================================== 服務器故障處理 --備機執行 --主服務器(主體)與見證服務器同時Down掉，備機緊急啟動并且開始服務 USE?master; ALTER?DATABASE?mirror??SET?PARTNER?FORCE_SERVICE_ALLOW_DATA_LOSS; ====================================================================== 主機恢復切換狀態 --備機執行 --原來主服務器恢復，可以繼續工作，需要從新設定鏡像 USE?master; --恢復鏡像 ALTER?DATABASE?BIMS_OFFLINE?SET?PARTNER?RESUME;?? --切換主備 ALTER?DATABASE?BIMS_OFFLINE?SET?PARTNER?FAILOVER;? ====================================================================== 程序中連接字符串的寫法 Data?Source=192.168.25.105;Failover?Partner=192.168.25.165; （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
