SQL Server 基礎概念集

原文地址：http://blog.csdn.net/zjcxc/archive/2005/07/06/415711.aspx 作者：鄒建
1、如何理解SQL Server的執行個體

        在項目實施過程中，不少使用者會有這樣的需求：要求開發一套基於SQL Server的新系統，這套系統驗收通過後，要和一個原有的SQL Server系統合并，共用一個伺服器，所以不能為新系統提供單獨的伺服器（資金上不允許為開發提供單獨的伺服器）。但是要保障新系統的開發而又不影響舊系統的正常運行，所以要求新系統使用單獨的SQL Server服務。在SQL Server 2000以前的版本中，這樣的要求是無法滿足的，但在SQL Server 2000中，就可以在原有SQL Server執行個體的基礎上，再添加一個執行個體來解決問題。
        所謂的“執行個體”，就是一個SQL Server資料庫引擎。SQL Server 2000支援在同一台電腦上同時運行多個SQL Server資料庫引擎執行個體。每個SQL Server資料庫引擎執行個體各有一套不為其他執行個體共用的系統及使用者資料庫。應用程式串連同一台電腦上的SQL Server資料庫引擎執行個體的方式與串連其他電腦上啟動並執行SQL Server資料庫引擎的方式基本相同。由於執行個體各有一套不為其他執行個體共用的系統及使用者資料庫，所以各執行個體的運行是獨立的，一個執行個體的運行不會受其他執行個體啟動並執行影響，也不會影響其他執行個體的運行。在一台電腦上安裝多個SQL Server執行個體，就相當於把這台電腦類比成多個資料庫伺服器，而且這些類比的資料庫伺服器是獨立且同時啟動並執行。
        執行個體包括預設執行個體和具名執行個體兩種。一台電腦上最多隻有一個預設執行個體，也可以沒有預設執行個體，預設執行個體名與電腦名稱相同，修改電腦名稱會同步修改預設執行個體名（SQL Server 7.0隻能被安裝為預設執行個體，在修改電腦名稱後，會導致SQL Server服務無法啟動，需要執行SQL Server安裝程式進行自動修複才能解決啟動問題），用戶端串連預設執行個體時，將使用安裝SQL Server執行個體的電腦名稱。
        在同一台電腦上安裝SQL Server 7.0和SQL Server 2000時，由於SQL Server 7.0隻能安裝為預設執行個體，所以應該先安裝SQL Server 7.0，將SQL Server 2000安裝為具名執行個體。或者在安裝SQL Server 2000的時候，指定安裝為具名執行個體。 

一台電腦上可以安裝多個具名執行個體，用戶端串連具名執行個體時，必須使用以下電腦名稱與具名執行個體的執行個體名組合的格式：
computer_name\instance_name

 2、用戶端如何與SQL Server互動

        從用戶端發送Transact-SQL語句到SQL Server伺服器，到伺服器處理完成並將結果返回給用戶端的完整過程如下：
        （1）用於SQL Server的Microsoft OLE DB提供者、SQL Server ODBC驅動程式或DB-Library DLL產生TDS資料包。
        （2）TDS資料包被傳遞給SQL Server用戶端Net-Library。
        （3）用戶端Net-Library將TDS資料包封裝為網路通訊協定資料包。
        （4）在伺服器上，伺服器Net-Library接收網路通訊協定資料包。並且由ODS（Open Data Services）負責析取TDS資料包，並將結果傳遞給關聯式資料庫伺服器。
        （5）關聯式資料庫伺服器負責處理TDS資料包的內容。
        （6）如果需要將結果返回給用戶端時，則由ODS將處理結果產生TDS資料包，並傳遞給伺服器Net-Library。
        （7）伺服器Net-Library負責TDS資料包封裝為網路通訊協定資料包，然後傳遞迴用戶端Net-Library。
        （8）用戶端Net-Library將網路通訊協定資料還原成TDS資料包，並轉給上層的資料庫介面，例如Microsoft OLE DB提供者、SQL Server ODBC驅動程式或DB-Library DLL等。
        （9）Microsoft OLE DB提供者、SQL Server ODBC驅動程式或DB-Library DLL將TDS資料包還原為應用程式可以接受的“結果集”。
        無論存取SQL Server的應用程式是否與SQL Server執行個體在同一台單機上，應用程式與SQL Server執行個體通訊都會經過上述過程。

  3、TDS、NET-Library和網路通訊協定

         TDS（表格式資料流協議）：SQL Server使用一個稱為表格式資料流 (TDS) 的應用程式級協議進行用戶端應用程式與 SQL Server 之間的通訊。TDS 資料包封裝在 Net-Library 所使用的協議棧產生的資料包中。
        例如，如果使用的是 TCP/IP 通訊端 Net-Library，TDS 資料包就封裝在基礎協議的 TCP/IP 資料包中。將結果集發送回應用程式的資料包的內容取決於是否在傳遞給資料庫引擎的 Transact-SQL 陳述式中指定了 FOR XML： 
       如果沒有指定 FOR XML，資料庫引擎將關係結果集發送回應用程式。TDS 資料包中包含結果行集，每行包括一列或多列，就像在 SELECT 語句的挑選清單中指定的那樣。 
       如果指定了 FOR XML，資料庫引擎將 XML 文檔以流的形式返回給應用程式。在 TDS 資料包中，XML 文檔被格式化為單個的、長 Unicode 值，每個資料包的大小約為 4 KB。 
       可以配置 SQL Server 資料包大小，即 TDS 資料包大小。大多數用戶端上的 TDS 資料包大小預設為 4 KB（DB-Library 應用程式預設為 512 位元組），經過測試證明，在幾乎所有的方案中，這是最佳的 TDS 資料包大小。TDS 資料包大小可以比基礎協議中的資料包大。如果是這種情況，發送資料包的電腦上的協議棧自動將 TDS 資料包分解成適合協議資料包大小的單元，用戶端電腦上的協議棧則在接收電腦上將 TDS 資料包重新組合在一起。

          Net-Library和網路通訊協定： SQL Server使用一個稱為 Net-Library 的動態連結程式庫 (DLL) 與特定的網路通訊協定通訊。匹配的 Net-Library 對在用戶端和伺服器電腦上必須是活動的，才能支援所需的網路通訊協定。
        例如，若要使用戶端應用程式能通過 TCP/IP 與特定的 SQL Server 執行個體通訊，必須在客戶電腦上將用戶端 TCP/IP 通訊端 Net-Library (DBNETLIB.dll) 配置為與那台伺服器串連，同時伺服器 TCP/IP 通訊端 Net-Library (SSNETLIB.dll) 必須在伺服器電腦上監聽。 
        對於一對 Net-Library，本身並不支援用戶端/伺服器串連。用戶端和伺服器也必須都運行支援 Net-Library 的協議棧。
        例如，如果伺服器 TCP/IP 通訊端 Net-Library 在伺服器電腦上監聽，並且客戶電腦上的用戶端 TCP/IP 通訊端 Net-Library 被配置成與那台伺服器串連，那麼，用戶端只有在兩台電腦上都安裝了 TCP/IP 協議棧時才能串連到伺服器上。
        Net-Library由SQL Server安裝程式安裝，而網路通訊協定由作業系統安裝程式安裝。

 4、伺服器標識

       當應用程式需要訪問某個SQL Server執行個體的時候，必須要知道被伺服器伺服器的唯一標識。這就類似於找一個人，您必須知道要找的人叫什麼名字，或者其他一些能在你尋找的範圍內，能唯一標識這個人的標誌，比如身份證編號。
        下面通過一些例子來說明常見的可用於訪問 SQL Server 服務器標識：
        1、 串連本機上的 SQL Server 的預設執行個體有有效伺服器標識可以是： 
               原生電腦名稱、(local)、localhost、127.0.0.1、.、不指定伺服器名、本機IP地址
        2、 串連本機上的 SQL Server 的具名執行個體有有效伺服器標識可以是： 
               原生電腦名稱\執行個體名、.\執行個體名、本機IP地址\執行個體名 
        3、 串連其他電腦上的 SQL Server 的預設執行個體有有效伺服器標識可以是： 
               伺服器的電腦名稱、伺服器的IP地址
        4、 串連其他電腦上的 SQL Server 的具名執行個體有有效伺服器標識可以是： 
               伺服器的電腦名稱\執行個體名、伺服器的IP地址\執行個體名

其他說明：
        1、 可以通過在 SQL Server 執行個體名稱的開始處添加 tcp:、 np:、 lpc:、或 rpc: 來指定您的連接字串中的協議，例如：tcp:jack
        2、 可以將一個連接埠號碼添加到連接字串（例如 MyServer\MyInstance,1433），用於指定 SQL Server 執行個體正在偵聽的特定連接埠 
        3、 可以通過 SQL Server 的用戶端網路工具 + 生產力，為要串連的 SQL Server 指定別名，以便使用簡短且友好的名稱來訪問 SQL Server。

 5、登入ID與身分識別驗證

        登入ID是賬戶標識符，用來控制對任何SQL Server 2000系統的存取權限。SQL Server 2000隻有在驗證了指定的登入ID有效後，才完成串連。這種登入驗證稱為身分識別驗證。
        錄屬性中，有一個是預設資料庫。當一個登入串連到SQL Server時，這個預設資料庫就變成該串連的當前資料庫，除非該串連請求指定另一個資料庫作為當前資料庫。
        用於登入SQL Server的賬戶在SQL Server中（使用SQL Server身分識別驗證）建立，或者在Windows NT 4.0或Windows 2000中建立並被授予登入許可權（使用Windows身分識別驗證）。
        SQL Server執行個體必須驗證每個串連請求所提供的登入ID是否具有訪問該執行個體的許可權。這一過程稱為身分識別驗證。SQL Server 2000使用兩類身分識別驗證：Windows身分識別驗證和SQL Server身分識別驗證。每一種身分識別驗證都有不同類別的登入ID。
Windows 身分識別驗證
        Windows身分識別驗證的過程如下：
        （1）SQL Server 2000 sysadmin固定伺服器角色成員必須首先向SQL Server 2000指定所有允許串連到SQL Server 2000的Windows NT或Windows 2000賬戶或組。當使用Windows身分識別驗證時，在串連到SQL Server 2000時不必指定登入ID或密碼。使用者對SQL Server 2000的存取權限由Windows NT或Windows 2000賬戶或組控制，當登入到用戶端上的Windows作業系統時需接受身分識別驗證。
        （2）用戶端使用有效Windows賬戶成功登入Windows後，Windows開啟信任連接。信任連接的屬性包括開啟串連的用戶端的Windows NT和Windows 2000組及使用者賬戶。
        （3）當用戶端串連到SQL Server 2000時，SQL Server 2000用戶端軟體向SQL Server 2000請求Windows信任連接，SQL Server 2000從信任連接屬性中得到使用者的賬戶資訊，並將它們與定義為有效SQL Server 2000登入的Windows賬戶相匹配。如果SQL Server 2000找到匹配的項，則接受這個串連，否則不接受這個串連。
當使用Windows 2000身分識別驗證串連到SQL Server 2000時，Windows NT或Windows 2000組或使用者賬戶就是登入的使用者標識（登入ID）。
SQL Server身分識別驗證
        SQL Server身分識別驗證的過程如下：
        （1）sysadmin固定伺服器角色成員首先向SQL Server 2000指定所有有效SQL Server 2000登入賬戶和密碼。這些登入賬戶和密碼與使用者的Windows賬戶或網路賬戶無關。
        （2）當串連到SQL Server 2000時，使用者必須提供SQL Server 2000登入賬戶和密碼。系統將通過使用者的SQL Server 2000登入賬戶在SQL Server 2000中標識使用者。

注意：
        Windows 98、Windows me系統中安裝的SQL Server僅支援SQL Server使用者。

添加用於Windows身分識別驗證登入ID的方法如下：
1、    加Windows使用者
         理工具--電腦管理--使用者和組--右鍵使用者--建立使用者--建立一個登陸windows的使用者
2、    在SQL Server中授予其登入的許可權
        企業管理器--SQL執行個體--安全性--右鍵登入--建立登入
                --[常規]項,點"名稱"後的"..."按紐,選擇上面建立的使用者
                --"身分識別驗證"選擇"windows身分識別驗證"
                --"域"選擇使用者所在的域
                --"安全性訪問"選擇"允許訪問"
                --確定
3、   登入
        登出windows,用你建立的使用者登陸作業系統.
        然後查詢分析器登陸SQL,串連時的身分識別驗證選擇"windows身分識別驗證"
        這時登陸的使用者就是建立的使用者了.

添加用於SQL Server身分識別驗證登入ID的方法如下：
        企業管理器--SQL執行個體--安全性--右鍵登入--建立登入
                --[常規]項,"名稱"中輸入使用者名稱
                --"身分識別驗證"選擇"sql server身分識別驗證",輸入該使用者的密碼
                --確定,再次輸入密碼

 6、身分識別驗證模式

        SQL  Server支援兩種身分識別驗證模式：
        （1） 僅Windows 
                  該種方式僅允許授予登入許可權的合法Windows賬戶訪問SQL Server，SQL Server使用者將被禁止登入。
        （2）SQL Server 和 Windows
                 該種方式授予登入許可權的合法Windows賬戶和SQL Server使用者訪問SQL Server。

注意：
        由於Windows 98、Windows me系統僅支援SQL Server身分識別驗證，因此只能使用“SQL Server 和 Windows2000 ”的身分識別驗證模式

設定SQL Server身分識別驗證模式的方法如下：
可以在安裝SQL Server時，設定設定SQL Server身分識別驗證模式，在SQL Server安裝完成後，可以通過下面的方法修改SQL Server身分識別驗證模式：
        企業管理器--右鍵SQL Server執行個體--屬性
                --[安全性]項中，選擇“僅Windows”或者“SQL Server 和 Windows2000”
                --確定
                --重新啟動SQL Server服務，使修改生效

 

7、解決常見的串連問題

 

一. "SQL Server 不存在或訪問被拒絕"

    這個是最複雜的,錯誤發生的原因比較多,需要檢查的方面也比較多，一般說來,有以下幾種可能性:：
        1、SQL Server名稱或IP地址拼字有誤
        2、伺服器端網路設定有誤
        3、用戶端網路設定有誤
    要解決這個問題,我們一般要遵循以下的步驟來一步步找出導致錯誤的原因.

    ============= 首先,檢查網路物理串連 =============

    ping <伺服器IP地址/伺服器名稱>
    如果 ping <伺服器IP地址> 不成功,說明物理串連有問題,這時候要檢查硬體裝置,如網卡,HUB,路由器等.
    還有一種可能是由於用戶端和伺服器之間安裝有防火牆軟體造成的,比如 ISA Server.防火牆軟體可能會屏蔽對ping,telnet 等的響應
    因此在檢查串連問題的時候,我們要先把防火牆軟體暫時關閉,或者開啟所有被封閉的連接埠.

    如果ping <伺服器IP地址> 成功而,ping <伺服器名稱> 失敗
    則說明名字解析有問題,這時候要檢查 DNS 服務是否正常.
    有時候用戶端和伺服器不在同一個區域網路裡面,這時候很可能無法直接使用伺服器名稱來標識該伺服器,這時候我們可以使用HOSTS檔案來進行名字解析,
    具體的方法是:
        1、使用記事本開啟HOSTS檔案（一般情況下位於C:\WINNT\system32\drivers\etc）.
             添加一條IP地址與伺服器名稱的對應記錄,如：172.168.10.24 myserver
        2、或在 SQL Server 的用戶端網路工具 + 生產力裡面進行配置,後面會有詳細說明.

============= 其次,使用 telnet 命令檢查SQL Server伺服器工作狀態 =============

        telnet <伺服器IP地址> 1433
    如果命令執行成功,可以看到螢幕一閃之後游標在左上方不停閃動,這說明 SQL Server 服務器工作正常,並且正在監聽1433連接埠的 TCP/IP 串連
    如果命令返回"無法開啟串連"的錯誤資訊,則說明伺服器端沒有啟動 SQL Server 服務,
    也可能伺服器端沒啟用 TCP/IP 協議,或者伺服器端沒有在 SQL Server 預設的連接埠1433上監聽.

==接著,我們要到伺服器上檢查伺服器端的網路設定,檢查是否啟用了具名管道.是否啟用了 TCP/IP 協議等等 ==

    可以利用 SQL Server 內建的伺服器網路使用工具來進行檢查.
         點擊:程式 -- Microsoft SQL Server -- 伺服器網路使用工具
     開啟該工具後,在"常規"中可以看到伺服器啟用了哪些協議.
    一般而言,我們啟用具名管道以及 TCP/IP 協議.
    點中 TCP/IP 協議,選擇"屬性",我們可以來檢查 SQK Server 服務預設連接埠的設定
    一般而言,我們使用 SQL Server 預設的1433連接埠.如果選中"隱藏伺服器",則意味著用戶端無法通過枚舉伺服器來看到這台伺服器,起到了保護的作用,但不影響串連.

============= 接下來我們要到用戶端檢查用戶端的網路設定 =============

    我們同樣可以利用 SQL Server 內建的用戶端網路使用工具來進行檢查,
    所不同的是這次是在用戶端來運行這個工具.
        點擊:程式 -- Microsoft SQL Server -- 用戶端網路使用工具
    開啟該工具後,在"常規"項中,可以看到用戶端啟用了哪些協議.
    一般而言,我們同樣需要啟用具名管道以及 TCP/IP 協議.
    點擊 TCP/IP 協議,選擇"屬性",可以檢查用戶端預設串連連接埠的設定,該連接埠必須與伺服器一致.
    單擊"別名"選項卡,還可以為伺服器配置別名.伺服器的別名是用來串連的名稱,
    串連參數中的伺服器是真正的伺服器名稱,兩者可以相同或不同.別名的設定與使用HOSTS檔案有相似之處.
    通過以上幾個方面的檢查,基本上可以排除第一種錯誤.

二. "無法串連到伺服器,使用者xxx登陸失敗"

    該錯誤產生的原因是由於SQL Server使用了"僅 Windows"的身分識別驗證方式， 因此使用者無法使用SQL Server的登入帳戶（如 sa ）進行串連.解決方案如下所示:：
    1、在伺服器端使用企業管理器,並且選擇"使用 Windows 身分識別驗證"串連上 SQL Server
        操作步驟:
        在企業管理器中
                --右鍵你的伺服器執行個體(就是那個有綠色表徵圖的)
                --編輯SQL Server註冊屬性
                --選擇"使用windows身分識別驗證"
                --選擇"使用SQL Server身分識別驗證"
                --登入名稱輸入:sa,密碼輸入sa的密碼
                --確定
    2、設定允許SQL Server身份登入
        操作步驟:
        在企業管理器中
                --展開"SQL Server組",滑鼠右鍵點擊SQL Server伺服器的名稱
                --選擇"屬性"
                --再選擇"安全性"選項卡
                --在"身分識別驗證"下,選擇"SQL Server和 Windows ".
                --確定,並重新啟動SQL Server服務.
    在以上解決方案中,如果在第 1 步中使用"使用 Windows 身分識別驗證"串連 SQL Server 失敗，那就通過修改註冊表來解決此問題:
        1、點擊"開始"-"運行",輸入regedit,斷行符號進入登錄編輯程式
        2、依次展開登錄機碼,瀏覽到以下註冊表鍵:
                [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer]
        3、在螢幕右方找到名稱"LoginMode",雙擊編輯雙位元組值
        4、將原值從1改為2,點擊"確定"
        5、關閉登錄編輯程式
        6、重新啟動SQL Server服務.
    此時,使用者可以成功地使用sa在企業管理器中建立SQL Server註冊,
    但是仍然無法使用Windows身分識別驗證模式來串連SQL Server.
    這是因為在 SQL Server 中有兩個預設的登入帳戶:
        BUILTIN\Administrators
        <機器名>\Administrator 被刪除.
    要恢複這兩個帳戶,可以使用以下的方法:
        1、開啟企業管理器,展開伺服器組,然後展開伺服器
        2、展開"安全性",右擊"登入",然後單擊"建立登入"
        3、在"名稱"框中,輸入 BUILTIN\Administrators
        4、在"伺服器角色"選項卡中,選擇"System Administrators" 
        5、點擊"確定"退出
        6、使用同樣方法添加 <機器名>\Administrator 登入.

說明:，以下註冊表鍵:：
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode
的值決定了SQL Server將採取何種身分識別驗證模式.
    1、表示使用"Windows 身分識別驗證"模式
    2、表示使用混合模式（Windows 身分識別驗證和 SQL Server 身分識別驗證）.

三.、提示連線逾時
    如果遇到第三個錯誤,一般而言表示用戶端已經找到了這台伺服器,並且可以進行串連，不過是由於串連的時間大於允許的時間而導致出錯.
    這種情況一般會發生在當使用者在Internet上運行企業管理器來註冊另外一台同樣在Internet上的伺服器，並且是慢速連線時,有可能會導致以上的逾時錯誤.有些情況下,由於區域網路的網路問題,也會導致這樣的錯誤.
    要解決這樣的錯誤,可以修改用戶端的連線逾時設定.
    預設情況下,通過企業管理器註冊另外一台SQL Server的逾時設定是 4 秒，而查詢分析器是 15 秒（這也是為什麼在企業管理器裡發生錯誤的可能性比較大的原因）.
    具體步驟為:
        企業管理器中的設定:
                1、在企業管理器中,選擇菜單上的"工具",再選擇"選項"
                2、在彈出的"SQL Server企業管理器屬性"視窗中,點擊"進階"選項卡
                3、在"串連設定"下的"登入逾時（秒）"右邊的框中輸入一個比較大的數字,如 20.
        查詢分析器中的設定:
        工具 -- 選項 -- 串連 -- 將登入逾時設定為一個較大的數字

 8、服務

        當安裝好SQL Server後，其實是在系統中加入了許多服務應用程式，彼此分工合作，配合完成各種資料處理，瞭解這些服務的作用，會有利於更好的使用和管理SQL Server。具體的說明如下表所示。 
                                   SQL Server 2000的各種服務和功能說明

服 務 名 稱	文 件 名	服 務 說 明
MSSQLSERVER	sqlserver.exe	SQL Server中最重要的服務，只要啟動了它，就可以完成大部分的資料庫處理。它負責資料存取、安全配置、交易管理等
SQLSERVERAGENT	sqlagent.exe	負責調度定期執行的活動（如資料庫維護、備份、複製等），以及通知系統管理員伺服器所發生的問題。如果不需要做這些處理工作，可以停止此服務
Microsoft Search	mssearch.exe	提供資料庫內全文檢索索引的功能，讓使用者可以針對資料欄位的內容以全文檢索索引的方式查詢，而非一般SQL文法提供的Like關鍵字過濾。這對於在大量文本的欄位中檢索某些關鍵字的情況下，比Like更有效率。需要注意的是，要進行全文檢索索引的話，需要進行全文檢索索引配置。如果沒有全文檢索索引的需要，可以停止此服務
Distributed Transaction Coordinator（MSDTC）	msdtc.exe	是一個交易管理員，它允許用戶端應用程式在一個事務中包含多個不同的資料來源。MSDTC協調在所有已在事務中登記的伺服器間提交的分散式交易，以確保所有伺服器上的全部更新為永久性的，或在發生錯誤時刪除所有更新。簡單地說，如果只在一個SQL Server執行個體內執行有事務的工作，則SQL Server自己可以處理，如果需要跨到其他的程式，就算是另一個SQL Server的執行執行個體，都需要通過MSDTC來協調完成事務。如果沒有跨到其他程式的交易處理，可以停止此服務