SQLServer的主從服務(wù)器配置和歷史痕跡清理

1、我們做web應(yīng)用的MySQL接觸的比較多，大家都知道MySQL的備份或者負載均衡我們會采用主從復(fù)制技術(shù)。最近工作中接觸到了SQLServer,老板要求也搭起類似的環(huán)境。從而我做了以下研究：一般情況下，SQLServer不叫主從復(fù)制，MS喜歡叫它日志傳送功能。其實質(zhì)是由兩臺服務(wù)器的代理作業(yè)完成的，首先基于源庫服務(wù)器的備份機制，然后將事務(wù)日志定期傳送到目的服務(wù)器，從庫服務(wù)器接收并完成數(shù)據(jù)庫還原操作名詞解釋：源庫服務(wù)器主庫服務(wù)器目標服務(wù)器從庫服務(wù)器傳送/接收一一主從服務(wù)器之間的數(shù)據(jù)傳輸【通過磁盤共享】還原一一執(zhí)行日志文件實現(xiàn)機制1 .登入賬號由SQLServer2000DTS封裝，將主登入賬號轉(zhuǎn)移到

2、從服務(wù)器，且執(zhí)行各服務(wù)器間登入帳號SID之解析動作，由Agent完成2 .日志傳送主從服務(wù)器的數(shù)據(jù)同步，主要依賴于數(shù)據(jù)庫事務(wù)日志傳送功能。簡單的說就是：備份作業(yè)一>復(fù)制作業(yè)一>還原作業(yè)一>警報作業(yè)我們可以通過在數(shù)據(jù)創(chuàng)建維護計劃中勾選將事務(wù)日志傳送到其它的SQLServer(日志傳送)”選項。那么，主服務(wù)器SQLServer代理會生成兩個作業(yè)：1) .完整備份作業(yè)2) .事務(wù)日志備份作業(yè)從服務(wù)器SQLServer代理會生成五個作業(yè)：1) .事務(wù)日志備份作業(yè)2) .日志傳送復(fù)制作業(yè)3) .日志還原作業(yè)4) .日志備份警報作業(yè)5) .日志恢復(fù)警報作業(yè)3 .角色變更通過 SQL Se

3、rver MSDB預(yù)置存儲過程 sp_change_primary_role對主服務(wù)器降級對從服務(wù)器升級通知監(jiān)控服務(wù)器角色變更通過SQLServerMSDB預(yù)置存儲過程sp_change_secondary_role通過SQLServerMSDB預(yù)置存儲過程sp_change_monitor_role4 .角色互換通過一系列SQLServerMSDB預(yù)置存儲過程清除之前日志傳送信息在新主服務(wù)器和從服務(wù)器之間創(chuàng)建新的日志傳送維護計劃，參考日志傳送實施步驟a.準備工作，創(chuàng)建配置存儲目錄完整備份目錄，事務(wù)日志備份目錄，預(yù)留從日志復(fù)制目錄需要設(shè)置屬性為共享，權(quán)限為完全控制b.創(chuàng)建數(shù)據(jù)庫維護計劃在創(chuàng)建數(shù)

4、據(jù)庫備份計劃中將事務(wù)日志傳送到其它的SQLServer(日志傳送)”，只有選中此項才會啟動日志傳送作業(yè)按照提示下一步，根據(jù)上面的存儲配置選擇對應(yīng)的目錄和網(wǎng)絡(luò)路徑，網(wǎng)絡(luò)路徑主要用來提供日志復(fù)制使用c.配置完整備份周期和同步周期d.配置數(shù)據(jù)清理周期e.手動查看下主從服務(wù)器的SQLServer代理是否正常啟動，作業(yè)是否創(chuàng)建成功5 .角色變更a.主服務(wù)器建立登入賬號SQLServer2000DTS(package)封裝，通過主服務(wù)器SQLServer代理工作b.降級主服務(wù)器(demote),使用msdb.pdo.sp_change_primary_role預(yù)存儲過程，將主服務(wù)器從讀/寫模式轉(zhuǎn)換為讀備援

5、模式，準備隨時接受交易日志的備份資料USEmasterGOEXECmsdb.dbo.sp_change_primary_roledb_name='dbname',backup_log=1,©terminate=1,final_state=3,access_level=1c.升級從服務(wù)器（promote）,使用msdb.pdo.sp_change_secondary_role預(yù)存儲過程，將從服務(wù)器升級為復(fù)原狀態(tài)，一旦復(fù)原不會再回存同步日志restoredatabasedbnamewithrecoveryUSEmasterGOEXECmsdb.dbo.sp_change_

6、secondary_roledb_name='dbname',do_load=1,force_load=1,final_state=1,access_level=1,©terminate=1,keep_replication=0,stopat=nulld.監(jiān)控服務(wù)器角色變更，使用msdb.pdo.sp_change_monitor_role將監(jiān)控服務(wù)器系統(tǒng)表中的主從配置刪除，修改主服務(wù)器名稱為新主服務(wù)器名稱（即舊從服務(wù)器）USEmasterGOEXECmsdb.dbo.sp_change_monitor_roleprimary_server='hostname

7、instance1',secondary_server='hostnameinstance2',database='dbname',new_source='hostnameinstance2'e.從服務(wù)器上解析登入賬號，賬號通過sp_resolve_logins讀取，解析各服務(wù)器之間的差異USEmasterGOEXECsp_resolve_loginsdest_db='dbname',dest_path='d:',filename='syslogins.dat'f.清除監(jiān)控服務(wù)器主服務(wù)器信息

8、execmsdb.dbo.sp_delete_log_shipping_primaryprimary_server_name=N'hostnameslave'primary_database_name=N'dbname'delete_secondaries=1g.連接數(shù)據(jù)庫存取與權(quán)限，使用sp_change_users_login修改新主服務(wù)器賬號權(quán)限USEdbnameGOEXECsp_change_users_login'Update_One','UserName','LoginName'6 .角色互換a.在新主

9、服務(wù)器上刪除日志傳送b.刪除主從服務(wù)器數(shù)據(jù)庫維護計劃和相關(guān)作業(yè)c.維持所有事務(wù)日志文件d.在新主服務(wù)器配置新的數(shù)據(jù)庫維護計劃，配置新的日志傳送，參見H志傳送”備注如果主服務(wù)器宕機，以上角色變更”和角色互換”操作無法自動切換，需要人工干預(yù)，同時主服務(wù)器需要在啟動的情況下才可以切換只有服務(wù)器在安裝SQLServer2000前配置Microsoft集群服務(wù)器（MSCS）,才可以在SQLServer2000中創(chuàng)建故障自動轉(zhuǎn)移集群服務(wù)上面說的基本在網(wǎng)上都可以找到，我想說的DBA同學(xué)們在搭建環(huán)境的時候都會經(jīng)歷測試搭建到正式搭建的過程，那么就會遇到清理歷史痕跡的問題，因為日志傳送如果不清理是無法重做的。大家

10、可以看下擴展部分，手動日志傳送配置清理>>>>>>手動日志傳送清理我們知道SQLServer2005可以在屬性中勾掉日志傳送功能”選項，系統(tǒng)就會自動清理配置痕跡，但是SQLServer2000還沒有這么強大，我們需要通過手動清理才可以首先，我們先來了解下主服務(wù)器、從服務(wù)器、監(jiān)控服務(wù)器都會保留哪些痕跡主服務(wù)器：log_shipping_databases做主的實例信息log_shipping_monitor監(jiān)控器信息其余表都沒有記錄從服務(wù)器：log_shipping_plans計戈U信息log_shipping_plan_databases計戈U中從實例信息l

11、og_shipping_plan_history計劃歷史記錄表【略】其余表都沒有記錄監(jiān)控服務(wù)器：log_shipping_primaries主實例信息log_shipping_secondaries從實例信息其余表都沒有記錄知道了這些，我們就可以清晰的了解到在每個服務(wù)器上執(zhí)行哪些存儲過程是必要的根據(jù)下面存儲過程和系統(tǒng)表對應(yīng)關(guān)系，我們就可以輕松的完成歷史痕跡清理主服務(wù)器：sp_delete_log_shipping_database清除主服務(wù)器主實例配置select*fromlog_shipping_databases從服務(wù)器：sp_delete_log_shipping_plansp_dele

12、te_log_shipping_plan_database清除從服務(wù)器記錄的傳送計劃select*fromlog_shipping_plansselect*fromlog_shipping_plan_databases存儲過程參數(shù)，我們需要通過執(zhí)行SQL語句得到selecta.plan_id,a.plan_name,b.source_databasefromlog_shipping_plansa,log_shipping_plan_databasesbwherea.plan_id=b.plan_id;參數(shù)賦值注意：查詢結(jié)果給出的是xxxx-xxxx-xxxx,賦值存儲過程的時候，需要手動改為單

13、引號'xxxx-xxxx-xxxx，否則就會出現(xiàn)"MicrosoftODBCSQLServerDriver語法錯誤或違反訪問規(guī)則"問題！監(jiān)控服務(wù)器：sp_delete_log_shipping_primarysp_delete_log_shipping_secondary清除主從監(jiān)控配置select*fromlog_shipping_primariesselect*fromlog_shipping_secondaries其實個人感覺存儲過程不那么好用，有時候清除不掉，我們還得去跟存儲過程：sp_helptextsp_delete_log_shipping_plan;

14、使用這個命令可以查詢每個存儲過程的內(nèi)容，其實就是對一組表的delete操作我們也可以抽取出其中的delete語句，為where條件制定參數(shù)即可【個人感覺比存儲過程好用,操作簡單易懂，好調(diào)試八丁】日志傳送相關(guān)存儲過程和系統(tǒng)表sp_delete_log_shipping_database刪除日志傳送的數(shù)據(jù)庫信息sp_delete_log_shipping_monitor_info刪除監(jiān)控服務(wù)器信息sp_delete_log_shipping_monitor_job刪除監(jiān)控作業(yè)信息sp_delete_log_shipping_plan刪除日志傳送計戈Usp_delete_log_shipping_plan_database刪除日志傳送計劃數(shù)據(jù)庫sp_delete_log_shipping_primary刪除日志傳送主服務(wù)器信息sp_delete_log_shippi