MSSQL連接字串,你真的清楚嗎?

    幾年前當我第一次面試時,考官發現我是個新手於是他讓我寫個連接字串,雖然當時就知道X種連接字串的寫法,但是當時卻沒能寫對一個,工作多年後我仍然不能寫一個正確的連接字串,但我知道打發新人時,讓他寫連接字串是個不錯的方法.

   以下是一個常用的ADO.NET 串連MSSQL的字串
  "Data Source=.;Initial Catalog=MyDBA;Integrated Security=SSPI"
  在IIS 環境下 我們也許會這麼寫
  "Data Source=.;Initial Catalog=MyDBA;User Id=使用者名稱;pwd=一般人我不告訴他;"
  
使用高效的協議(雖然我們一直這麼做,但一直沒注意)
  首先要搞清楚我們是使用什麼方式與MSSQL資料庫服務溝通的,當然微軟產品的好處是即使你不知道,他也會幫你選擇一個最合適的方式,在IIS跟MSSQL同台伺服器的情況下,預設使用共用記憶體.
圖1)

要確認ADO.Net建立的是什麼方式的串連可以通過,企業管理器中->管理->進程資訊

可以看到ADO.NET提供者,.Net SqlClient data Provider

使用LPC(本地調用,即共用記憶體)

 如果你把上面的連接字串改成"Data Source=127.0.0.1\SQL2K;Initial Catalog=MyDBA;Integrated
Security=SSPI"
注意我的電腦上安裝了多個SQL2000執行個體,其中一個為SQL2K ,使用上面的連接字串開啟一個串連後,再查看進程資訊,發現LPC 現在變成了Tcp/IP(注意是.Net SqlClient Data provider對應的行)這個表明現在你啟用了TCP/IP來串連資料庫,
當然在IIS跟SQL同伺服器的情況下建議你使用LPC,這樣速度跟效能都比較優越.

在連接字串中使用連接埠
一般異地串連時我們多使用tcp/ip協議,預設連接埠是1433,有時候你可能想改變這個連接埠,為此你需要使用"伺服器網路工具 + 生產力"(在開始->Microsoft SQL Server下面),在裡面配置Tcp/ip 協議並指定一個新的連接埠.

    這裡指定了8888 為執行個體SQL2K的新連接埠,注意請不要將"隱藏伺服器(H)"選擇中,儲存更改後就可以下面的連接字串進行串連了"Data Source=127.0.0.1,8888;Initial Catalog=MyDBA;User ID=sa ",同樣在進程資訊裡你能看到ADO.NET的一條使用者名稱為sa的TCP/IP串連.但是當我們試圖從遠端連線這台SQL伺服器時通常會出現問題,一般伺服器上都有防火牆,而且只開放web,ftp,遠端桌面等幾個有限的連接埠,而你上面指定8888未在其列,因此當你在異地來串連資料庫時會報錯,(另外連接字串如"Data Source=60.188.86.49,8888;Initial Catalog=MyDBA;User ID=sa;pwd=" ,中需要注意的是連接埠號碼跟IP或網域名稱之間用逗號","隔開)為解決此問題你需要在防火牆中開放8888連接埠.
   完成後,你可以使用上面IP+連接埠的形式訪問資料庫了,不過你在使用以上連接字串前,是否嘗試過類似
"Data Source=www.wow52.cn\SQL2K;Initial Catalog=MyDBA;User ID=sa;pwd=;" 形式的連接字串呢,這裡採用網域名稱加執行個體名的樣子,可能你嘗試了下發現行不通,反正我當時是這樣做過但發現不行,雖然感覺這個形式很直觀,但是怎麼就不行呢?
   當然如果你細心的話,回過頭去看上文有個"Data Source=127.0.0.1\SQL2K;Initial Catalog=MyDBA;Integrated
Security=SSPI"形式的連接字串,這個是IP+執行個體的形式,本質上已經是網域名稱+執行個體的形式了,那麼為什麼不行呢?問題同樣是出在防火牆上面,不過這次你需要開放的是一個UDP連接埠1434以給運行SQL Server 解析協議 (SSRP)的服務使用,當你使用網域名稱+執行個體名訪問時,解析服務會解析成 IP+連接埠的形式進行資料庫連接. 現在你通過企業管理器中的進程資訊查看時,可以看到一個TCP/IP串連了,
SQL Server 解析協議 在SQL2005中被SQL Server Browser 取代,在需要進行遠端連線,並且採用網域名稱或IP+執行個體名的形式時,你需要啟動這個服務,並且在防火牆中開放相應連接埠. 最後關於連接埠部分,還有一點是SQL2000,跟SQL2005多支援動態連接埠,也就是把連接埠設定為0(SQL2005 具體參考組態工具),這個時候為使資料庫能在異地正確訪問,你需要在防火牆中以添加程式的形式把sqlserver.exe添加進去,如執行個體SQL2k的程式位置是
D:\Program Files\Microsoft SQL Server\MSSQL$SQL2K\Binn\sqlservr.exe,具體取決於你的安裝位置,SQL2005可以在伺服器網路設定工具裡查看. 除了需要添加sqlserver.exe程式外,你還需要開放1434 UDP連接埠,或者在使用SQL2005時把C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe 添加到防火牆裡, sqlbrowser.exe 使用的也是1434 UDP連接埠.

以上所講述在我的MSSQL2000裡都不行!
  

   如果你不幸遇到這樣的情況,最重要的一點是查看你sql的版本,請在查詢分析器裡運行 Select @@Version,或者看協助菜單中的"關於..." 如果版本不是8.00.2039(現在說的是SQL2000),那麼趕緊升級把,下個SQLSP4來打上.
如果打上補丁還無濟於事,那麼請確定你在SQL Server網路工具 + 生產力中啟用了TPC/IP協議,並在防火牆中開放了相關連接埠.

連接字串中的一些屬性
 
  Connection lifetime 串連存留時間,預設為0,表示無限長,單位為秒.在SQL叢集環境中我們才設定為具體的數值.

  Connection timeout 串連產生時間,預設為15秒,通俗的講當你conn.Open等15秒後還沒串連成功,那麼就會拋出一個連線逾時的錯誤,這也是為什麼其他錯誤來的那麼突然,而資料庫連接逾時錯誤,非要你等15秒的原因了.

  Pooling 是否使用串連池, 預設是起用的,使用Pooling=false來禁止.

  Min Pool Size,Max Pool Size 預設情況下最小是0,最大是100.

  關於pooling屬性在上面所有的連接字串中多是預設開啟的,串連池能有效提高資料庫訪問效能,因為建立一個串連需要消耗很多資源,尤其是進行異地訪問時(一般指區域網路,最好不要進行跨internet的資料庫訪問)更加如此,因此你在多數情況下不需要顯式的禁止(也許你沒在意微軟一直幫你開通著), 事實上在你使用 conn.open() ,conn.close() 多少數情況下不是真的開啟一個串連,而是向串連池中取一條可用串連,跟歸還一條串連,conn.open操作只有在池中沒有可用串連時才會建立一條串連,當然如果池中可用接數目達到Max Pool Size時,他將不再建立新的串連,而是等待一條可用串連的到來,這個時間是Connection timeout. 當然你的conn.Open操作涉及的串連池,取決於你的連接字串,比如:
"Data Source=127.0.0.1\SQL2K;Initial Catalog=MyDBA;Integrated
Security=SSPI"
與
"Data Source=127.0.0.1\SQL2K;Initial Catalog=MyDBA;Integrated
Security=SSPI;" ,注意後面多了";"
會開啟兩個獨立的串連池,ADO.NET 判斷字串是否相同的依據是對字串進行2進位的比較,所以任何變動(空格,大小寫)都會被判斷為不同的連接字串.
有如下代碼
-----------------------------------
string strConn="Data Source=127.0.0.1\SQL2K;Initial Catalog=MyDBA;Integrated
Security=SSPI;";
for(int i=0;i<10;i++){
  SqlConnection conn=new SqlConnection(strConn);
  conn.Open();
  conn.Close()
}
跟
string strConn="Data Source=127.0.0.1\SQL2K;Initial Catalog=MyDBA;Integrated
Security=SSPI;";
for(int i=0;i<10;i++){
  strConn +=" "; //空格
  SqlConnection conn=new SqlConnection(strConn);
  conn.Open();
  conn.Close()
}
兩段代碼分別運行,看企業管理器->管理->進程資訊(請重新整理先) ,可以看到前面的只啟用了一個進程,而後面的則啟用了10個進程,每個進程對應一條串連.前一段代碼10個open +close()操作使用的是同一個串連池中的同一個串連,而後一個則開啟了10個串連池,每個串連池中有一個串連.

再看下面的代碼
            for (int i = 0; i <= 10; i++)
            {
                Thread t = new Thread(new ThreadStart(Command));
                t.Start();
            }
---------------------
//Command定義
private void Command(){
string strConn="Data Source=127.0.0.1\SQL2K;Initial Catalog=MyDBA;Integrated
Security=SSPI;";
  Sqlconnection conn=new SqlConnection(strConn);
 SqlCommand cmd=new SqlCommand("一個運行比較長的查詢",conn);
  conn.Open();
  cmd.ExecuteNonQuery();
  
  conn.Close()
}
  運行後可以看,企業管理器->管理->進程資訊裡會開啟10個到MyDBA資料庫的串連進程,但是需要注意的是,這10個進程是屬於同個串連池的,為了證明這一點你可以將上面的連接字串改為
 private void command(){
string strConn="Data Source=127.0.0.1\SQL2K;Initial Catalog=MyDBA;Integrated
Security=SSPI;Max pool Size=5;"; //加了Max Pool Size=5;
  SqlConnection conn=new SqlConnection(strConn);
 SqlCommand cmd=new SqlCommand("一個運行比較長的查詢",conn);
  conn.Open();
  cmd.ExecuteNonQuery();
  
  conn.Close()
}
結果可以看到他只開啟了5個到MyDBA資料庫的串連,這證明他們是同個串連池的.當然這裡能成功示範的前提是,cmd執行的任務時間要在15秒內,不然會出現後5個線程連線逾時,另外使用SQL2000 的跟蹤工具(SQL2005中是,SQL Server Profiler)是你觀察以上實驗的一個絕好的工具!