Rock的SQL筆記本

今天要分享的經驗是一個很久以前遇見的問題，當時的環境是

Windows 2003R2+SQL2005。後來有解決該問題，但由於環

境算非主流所以我也沒寫在部落格中。直到今天在另一個

Windows2008R2+SQL2008R2又遇見類似問題，所以我順手

紀錄一下。

 

我之前有4部Windows 2003R2+SQL2005在同一網域，Agent跟SQL服務帳號

都是Domain的Admin，不知何時開始，發現該網域4台主機的備份維護計畫執

行時間都很久，就算DB只有幾20MB，備份也要花1分半鐘，備份LOG就算只

有幾KB也是要花那麼久的時間。且登入本機執行SQL SSMS時，也是慢到爆，做

任何動作都很慢，但做過一次後再做第二次則速度又很快。

 

 

如上圖，備份個133KB的檔案就花了1分35秒的時間。

 

由於只有該網域SQL有問題，因此初判該網域可能有問題。

 

測試一 : 先確認是否為Agent帳戶認證的問題。但利用SQL Agent的帳號登入

主機時速度很快，似乎Domain的認證又沒問題。

 

測試二 : 將維護計畫產生的JOB，OWNER跟CONNECTION都改成本機帳號，

不用原來Agent用的網域帳號，再測試還是要花1分半鐘，失敗。

 

測試三 : 撰寫TSQL來備份資料庫，會發現備份時間非常快速。

 

將測試結果詢問微軟顏瑞宏老師，老師表示TSQL不需做.NET Framework的載

入初始化動作，而SSIS是靠.NET Framework執行的，所以TSQL當然會比較快。

此時我們有點線索了就是.NET Framework這一個關鍵字。

 

經過跟顏瑞宏老師的討論後，得知SSIS執行時會載入.NET Framework，因此方

向改為朝SSIS跟.NET Framework。Google中搜尋SSIS慢的關鍵字，發現類似問

題都是在SQL2005。狀況一樣，SSMS執行很慢，SSIS也很慢。經過漫長爬問找

到這一篇文章

http://ajaxjhu.pixnet.net/blog/post/35918618-management-studio%E9%96%8B%E5%95%9F%E9%81%8E%E9%95%B7%E7%9A%84%E5%95%8F%E9%A1%8C

 

該文章解釋大致原因如下:

 

之所以發生這個問題，是因為受影響的電腦無法連線至

http://crl.microsoft.com 網站。這個問題是因為下列情

形而造成： 當 Microsoft .NET Framework 啟動 SSIS 服

務時，.NET Framework會呼叫 CryptoAPI 函數以確認指

派給 SQL Server 組件檔案的憑證。

 

CryptoAPI 函數會檢查可在 http://crl.microsoft.com網站

上取得的「憑證撤銷清單」(CRL)。這個動作需要網際網路

連線。如果網際網路連線遭到封鎖，可能會捨棄傳出的 HTTP

要求，因而不會傳回錯誤訊息。此外，長延遲會造成 CRL 檢

查逾時。「服務控制管理員」(SCM) 判斷 SSIS 服務花太長的

時間啟動。因此，SCM 會回報錯誤訊息，而 SSIS 服務也不會

啟動。

 

看到上面文章後，赫然想到。由於之前曾遭欲駭客攻擊入侵，防火牆已經不允

許SQL主機對外的80 PORT Connection，而SSIS運作過程中會去crl.microsoft.com

做80的連接，因此當連接不上時會一直等待。因此備份的維護計畫大多時間就

是耗費在等待，然後才做備份。

 

因此我的解法就是在hosts.ini中加入一筆資料是127.0.0.1 crl.microsoft.com，

當.NET Framework 呼叫 CryptoAPI去連接crl.microsoft.com時就會馬上收到

失敗的回傳訊息，而不會傻傻的一直等待。

(設定方式，如下圖所示)

 

 

完成hosts.ini設定後，我們再執行備份的維護計畫就可以看見時間明顯縮短了。(如下圖)

 

 

 而今天遇見的問題是某台SQL的Reporting Service啟動失敗(如下圖)

 

 

爬了一些文找問題，有看見幾篇有談到跟等待及逾時有關的問題。忽然

想起之前遇過SSIS執行過慢的問題，因此死馬當活馬醫的修改一下

hosts.ini檔。如下圖所示，SSRS又可以正常啟動了。

 

後記 :

在我的案例中，會發生這種奇怪狀況的SQL Server都是原先可以

對外做80 Port連線的主機。後來因為安全性問題封掉對外網路連線後

才發生這種狀況。至於本來就無法連線的主機倒是沒有這樣的問題發生

，筆記一下。

 

我是ROCK

rockchang@mails.fju.edu.tw