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

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

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

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

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

5、模式，準(zhǔn)備隨時(shí)接受交易日志的備份資料USEmasterGOEXECmsdb.dbo.sp_change_primary_roledb_name='dbname',backup_log=1,©terminate=1,final_state=3,access_level=1c.升級(jí)從服務(wù)器（promote）,使用msdb.pdo.sp_change_secondary_role預(yù)存儲(chǔ)過程，將從服務(wù)器升級(jí)為復(fù)原狀態(tài)，一旦復(fù)原不會(huì)再回存同步日志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ù)器上解析登入賬號(hào)，賬號(hào)通過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ù)器賬號(hào)權(quán)限USEdbnameGOEXECsp_change_users_login'Update_One','UserName','LoginName'6 .角色互換a.在新主

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

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

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

12、te_log_shipping_plan_database清除從服務(wù)器記錄的傳送計(jì)劃select*fromlog_shipping_plansselect*fromlog_shipping_plan_databases存儲(chǔ)過程參數(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,賦值存儲(chǔ)過程的時(shí)候，需要手動(dòng)改為單

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

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