MS SQL Server 快速指南

---

---

MS SQL Server - 概述

本章介紹 SQL Server，討論其用法、優勢、版本和元件。

什麼是 SQL Server？

• 它是由微軟開發的一種軟體，根據 RDBMS 的規範實現。

• 它也是一種 ORDBMS。

• 它是平臺相關的。

• 它既是基於 GUI 的軟體，也是基於命令列的軟體。

• 它支援 SQL（SEQUEL）語言，這是一種 IBM 產品，非過程化的、通用的資料庫語言，並且不區分大小寫。

SQL Server 的用法

• 建立資料庫。
• 維護資料庫。
• 透過 SQL Server Analysis Services (SSAS) 分析資料。
• 透過 SQL Server Reporting Services (SSRS) 生成報表。
• 透過 SQL Server Integration Services (SSIS) 執行 ETL 操作。

SQL Server 的版本

SQL Server 元件

SQL Server 採用客戶端-伺服器架構，因此它支援兩種型別的元件 - (a) 工作站和 (b) 伺服器。

• 工作站元件安裝在每個裝置/SQL Server 操作員的機器上。這些只是與伺服器元件互動的介面。例如：SSMS、SSCM、Profiler、BIDS、SQLEM 等。

• 伺服器元件安裝在集中式伺服器上。這些是服務。例如：SQL Server、SQL Server 代理、SSIS、SSAS、SSRS、SQL 瀏覽器、SQL Server 全文搜尋等。

SQL Server 例項

• 例項是 SQL Server 的一個安裝。
• 例項是同一軟體的完全副本。
• 如果我們安裝“n”次，則將建立“n”個例項。
• SQL Server 中有兩種型別的例項：a) 預設 b) 命名。
• 在一臺伺服器上僅支援一個預設例項。
• 在一臺伺服器上支援多個命名例項。
• 預設例項將伺服器名稱作為例項名稱。
• 預設例項服務名稱為 MSSQLSERVER。
• 2000 版支援 16 個例項。
• 2005 及更高版本支援 50 個例項。

例項的優勢

• 在一臺機器上安裝不同的版本。
• 降低成本。
• 分別維護生產、開發和測試環境。
• 減少臨時資料庫問題。
• 分離安全許可權。
• 維護備用伺服器。

MS SQL Server - 版本

SQL Server 提供多種版本。本章列出了多個版本及其功能。

• 企業版 - 這是最高階的版本，具有完整的功能集。

• 標準版 - 當不需要高階功能時，它的功能少於企業版。

• 工作組版 - 適用於大型公司的遠端辦事處。

• Web 版 - 專為 Web 應用程式設計。

• 開發人員版 - 類似於企業版，但僅許可給一個使用者用於開發、測試和演示。它可以輕鬆升級到企業版，而無需重新安裝。

• Express 版 - 這是免費的入門級資料庫。它只能使用 1 個 CPU 和 1 GB 記憶體，資料庫的最大大小為 10 GB。

• Compact 版 - 這是用於移動應用程式開發的免費嵌入式資料庫。資料庫的最大大小為 4 GB。

• 資料中心版 - 新版 SQL Server 2008 R2 的主要變化是資料中心版。資料中心版沒有記憶體限制，並且支援超過 25 個例項。

• 商業智慧版 - 商業智慧版是 SQL Server 2012 中的新版本。此版本包括標準版中的所有功能，並支援高階 BI 功能，如 Power View 和 PowerPivot，但它缺乏對高階可用性功能的支援，如 AlwaysOn 可用性組和其他聯機操作。

• 企業版評估版 - SQL Server 評估版是獲得 SQL Server 的功能齊全且免費例項以進行學習和開發解決方案的好方法。此版本從安裝之日起內建 6 個月的有效期。

MS SQL Server - 安裝

SQL Server 支援兩種型別的安裝 -

• 獨立安裝
• 基於群集的安裝

檢查

• 檢查伺服器的 RDP 訪問許可權。
• 檢查伺服器的作業系統位數、IP 和域。
• 檢查您的帳戶是否在管理員組中以執行 setup.exe 檔案。
• 軟體位置。

需求

• 版本、版本、SP 和修補程式（如果有）。
• 資料庫引擎、代理、SSAS、SSIS、SSRS 的服務帳戶（如果有）。
• 命名例項名稱（如果有）。
• 二進位制檔案、系統、使用者資料庫的位置。
• 身份驗證模式。
• 排序規則設定。
• 功能列表。

2005 的先決條件

• 安裝支援檔案。
• .net framework 2.0。
• SQL Server 本機客戶端。

2008 和 2008R2 的先決條件

• 安裝支援檔案。
• .net framework 3.5 SP1。
• SQL Server 本機客戶端。
• Windows Installer 4.5/更高版本。

2012 和 2014 的先決條件

• 安裝支援檔案。
• .net framework 4.0。
• SQL Server 本機客戶端。
• Windows Installer 4.5/更高版本。
• Windows PowerShell 2.0。

安裝步驟

步驟 1 - 從 https://www.microsoft.com/en-us/evalcenter/download-sql-server-2019 下載評估版

下載軟體後，根據您的下載（32 位或 64 位）選項，將提供以下檔案。

ENU\x86\SQLFULL_x86_ENU_Core.box

ENU\x86\SQLFULL_x86_ENU_Install.exe

ENU\x86\SQLFULL_x86_ENU_Lang.box

或

ENU\x86\SQLFULL_x64_ENU_Core.box

ENU\x86\SQLFULL_x64_ENU_Install.exe

ENU\x86\SQLFULL_x64_ENU_Lang.box

注意 - X86（32 位）和 X64（64 位）

步驟 2 - 雙擊“SQLFULL_x86_ENU_Install.exe”或“SQLFULL_x64_ENU_Install.exe”，它將提取“SQLFULL_x86_ENU”或“SQLFULL_x86_ENU”資料夾中安裝所需的 檔案。

步驟 3 - 單擊“SQLFULL_x86_ENU”或“SQLFULL_x64_ENU_Install.exe”資料夾，然後雙擊“SETUP”應用程式。

為了便於理解，這裡我們使用了 SQLFULL_x64_ENU_Install.exe 軟體。

步驟 4 - 單擊“setup”應用程式後，將開啟以下螢幕。

步驟 5 - 單擊上面螢幕左側的“安裝”。

步驟 6 - 單擊上面螢幕右側的第一個選項。將開啟以下螢幕。

步驟 7 - 單擊“確定”，將彈出以下螢幕。

步驟 8 - 單擊“下一步”以獲取以下螢幕。

步驟 9 - 確保選中產品金鑰選擇，然後單擊“下一步”。

步驟 10 - 選中複選框以接受許可證選項，然後單擊“下一步”。

步驟 11 - 選擇 SQL Server 功能安裝選項，然後單擊“下一步”。

步驟 12 - 選擇“資料庫引擎服務”複選框，然後單擊“下一步”。

步驟 13 - 輸入命名例項（這裡我使用了 TestInstance），然後單擊“下一步”。

步驟 14 - 單擊上面螢幕上的“下一步”，將出現以下螢幕。

步驟 15 - 選擇上面列出的服務的帳戶名稱和啟動型別，然後單擊“排序規則”。

步驟 16 - 確保選中正確的排序規則選擇，然後單擊“下一步”。

步驟 17 - 確保選中身份驗證模式選擇和管理員，然後單擊“資料目錄”。

步驟 18 - 確保選擇上述目錄位置，然後單擊“下一步”。將出現以下螢幕。

步驟 19 - 單擊上面螢幕上的“下一步”。

步驟 20 - 單擊上面螢幕上的“下一步”以獲取以下螢幕。

步驟 21 - 確保正確選中上述選擇，然後單擊“安裝”。

安裝成功，如上圖所示。單擊“關閉”以完成。

MS SQL Server - 架構

為了便於理解，我們將 SQL Server 的架構分為以下幾個部分 -

• 通用架構
• 記憶體架構
• 資料檔案架構
• 日誌檔案架構

通用架構

客戶端 - 請求發起的來源。

查詢 - SQL 查詢，它是高階語言。

邏輯單元 - 關鍵字、表示式和運算子等。

網路資料包 - 與網路相關的程式碼。

協議 - 在 SQL Server 中，我們有 4 種協議。

• 共享記憶體（用於本地連線和故障排除）。

• 命名管道（用於 LAN 連線中的連線）。

• TCP/IP（用於 WAN 連線中的連線）。

• VIA - 虛擬介面介面卡（需要供應商設定特殊的硬體，並且從 SQL 2012 版本開始已棄用）。

伺服器 - SQL 服務安裝和資料庫駐留的位置。

關係引擎 - 實際執行操作的地方。它包含查詢解析器、查詢最佳化器和查詢執行器。

查詢解析器（命令解析器）和編譯器（翻譯器） - 檢查查詢的語法，並將查詢轉換為機器語言。

查詢最佳化器 - 以查詢、統計資訊和代數樹作為輸入，生成執行計劃作為輸出。

執行計劃 - 類似於路線圖，包含查詢執行過程中所有步驟的順序。

查詢執行器 - 在這裡，查詢將藉助執行計劃逐步執行，並且還會聯絡儲存引擎。

儲存引擎 - 負責在儲存系統（磁碟、SAN 等）上儲存和檢索資料、資料操作、鎖定和管理事務。

SQL OS - 位於主機（Windows 作業系統）和 SQL Server 之間。資料庫引擎上執行的所有活動都由 SQL OS 處理。SQL OS 提供各種作業系統服務，例如記憶體管理（處理緩衝池、日誌緩衝區）以及使用阻塞和鎖定結構進行死鎖檢測。

檢查點程序 - 檢查點是一個內部程序，它將緩衝區快取中所有髒頁（已修改的頁）寫入物理磁碟。此外，它還將日誌緩衝區中的日誌記錄寫入物理檔案。將緩衝區快取中的髒頁寫入資料檔案也稱為髒頁硬化。

它是一個專用程序，由 SQL Server 在特定時間間隔自動執行。SQL Server 為每個資料庫單獨執行檢查點程序。檢查點有助於在意外關機或系統崩潰/故障的情況下減少 SQL Server 的恢復時間。

SQL Server 中的檢查點

在 SQL Server 2012 中，有四種類型的檢查點：

• 自動 - 這是最常見的檢查點，它作為後臺程序執行，以確保 SQL Server 資料庫可以在恢復間隔 - 伺服器配置選項定義的時間限制內恢復。

• 間接 - 這是 SQL Server 2012 中的新功能。它也執行在後臺，但為了滿足為配置了該選項的特定資料庫指定的使用者指定的目標恢復時間。一旦選擇了給定資料庫的 Target_Recovery_Time，它將覆蓋為伺服器指定的恢復間隔，並避免對該資料庫進行自動檢查點。

• 手動 - 此檢查點與其他任何 T-SQL 語句一樣執行，一旦發出檢查點命令，它將執行到完成。手動檢查點僅對當前資料庫執行。您還可以指定 Checkpoint_Duration，它是可選的 - 此持續時間指定您希望檢查點完成的時間。

• 內部 - 作為使用者，您無法控制內部檢查點。在特定操作上發出，例如

  • 關閉會在所有資料庫上啟動檢查點操作，除非關閉不乾淨（使用 nowait 關閉）。

  • 如果恢復模型從完整/批次日誌更改為簡單。

  • 在進行資料庫備份時。

  • 如果您的資料庫處於簡單恢復模型中，則檢查點程序會在日誌滿 70% 或基於伺服器選項 - 恢復間隔時自動執行。

  • 用於新增或刪除資料/日誌檔案的 ALTER DATABASE 命令也會啟動檢查點。

  • 當資料庫的恢復模型為批次日誌並且執行了最小日誌記錄操作時，也會發生檢查點。

  • 資料庫快照建立。

• 延遲寫入器程序 - 延遲寫入器出於完全不同的原因將髒頁推送到磁碟，因為它需要釋放緩衝池中的記憶體。當 SQL Server 遇到記憶體壓力時，就會發生這種情況。據我所知，這是由內部程序控制的，並且沒有設定。

SQL Server 不斷監控記憶體使用情況以評估資源爭用（或可用性）；它的工作是確保始終有足夠的可用空間。作為此過程的一部分，當它注意到任何此類資源爭用時，它會觸發延遲寫入器透過將髒頁寫入磁碟來釋放一些記憶體中的頁。它採用最近最少使用 (LRU) 演算法來確定要重新整理到磁碟的頁。

如果延遲寫入器始終處於活動狀態，則可能表示記憶體瓶頸。

記憶體架構

以下是記憶體架構的一些主要特徵。

• 所有資料庫軟體的主要設計目標之一是最大程度地減少磁碟 I/O，因為磁碟讀取和寫入是最耗費資源的操作之一。

• Windows 中的記憶體可以使用虛擬地址空間呼叫，該空間由核心模式（作業系統模式）和使用者模式（SQL Server 等應用程式）共享。

• SQL Server“使用者地址空間”分為兩個區域：MemToLeave 和緩衝池。

• MemToLeave (MTL) 和緩衝池 (BPool) 的大小由 SQL Server 在啟動時確定。

• 緩衝區管理是實現 I/O 高效率的關鍵組成部分。緩衝區管理元件包含兩種機制：緩衝區管理器用於訪問和更新資料庫頁，緩衝池用於減少資料庫檔案 I/O。

• 緩衝池進一步細分為多個部分。其中最重要的是緩衝區快取（也稱為資料快取）和過程快取。緩衝區快取將資料頁儲存在記憶體中，以便可以從快取中檢索經常訪問的資料。另一種方法是從磁碟讀取資料頁。從快取中讀取資料頁透過最大程度地減少所需的 I/O 操作（本質上比從記憶體中檢索資料慢）來最佳化效能。

• 過程快取儲存儲存過程和查詢執行計劃，以最大程度地減少生成查詢計劃的次數。您可以使用 DBCC PROCCACHE 語句查詢有關過程快取中大小和活動的資訊。

緩衝池的其他部分包括：

• 系統級資料結構 - 儲存有關資料庫和鎖的 SQL Server 例項級資料。

• 日誌快取 - 預留用於讀取和寫入事務日誌頁。

• 連線上下文 - 與例項的每個連線都有一個小的記憶體區域來記錄連線的當前狀態。此資訊包括儲存過程和使用者定義函式引數、遊標位置等。

• 堆疊空間 - Windows 為 SQL Server 啟動的每個執行緒分配堆疊空間。

資料檔案架構

資料檔案架構包含以下元件：

檔案組

可以將資料庫檔案組合到檔案組中，以進行分配和管理。任何檔案都不能屬於多個檔案組。日誌檔案永遠不是檔案組的一部分。日誌空間與資料空間分開管理。

SQL Server 中有兩種型別的檔案組：主檔案組和使用者定義檔案組。主檔案組包含主資料檔案和未專門分配到其他檔案組的任何其他檔案。系統表的所有頁面都分配在主檔案組中。使用者定義檔案組是在建立資料庫或更改資料庫語句中使用檔案組關鍵字指定的任何檔案組。

每個資料庫中的一個檔案組充當預設檔案組。當 SQL Server 將頁面分配給在建立時未指定檔案組的表或索引時，這些頁面將從預設檔案組分配。要將預設檔案組從一個檔案組切換到另一個檔案組，它應該具有 db_owner 固定資料庫角色。

預設情況下，主檔案組是預設檔案組。使用者應該具有 db_owner 固定資料庫角色才能分別備份檔案和檔案組。

檔案

資料庫有三種類型的檔案：主資料檔案、輔助資料檔案和日誌檔案。主資料檔案是資料庫的起點，並指向資料庫中的其他檔案。

每個資料庫都有一個主資料檔案。我們可以為主資料檔案指定任何副檔名，但推薦的副檔名是.mdf。輔助資料檔案是該資料庫中除主資料檔案之外的檔案。某些資料庫可能有多個輔助資料檔案。某些資料庫可能沒有單個輔助資料檔案。輔助資料檔案的推薦副檔名是.ndf。

日誌檔案儲存用於恢復資料庫的所有日誌資訊。資料庫必須至少有一個日誌檔案。一個數據庫可以有多個日誌檔案。日誌檔案的推薦副檔名是.ldf。

資料庫中所有檔案的位置都記錄在主資料庫和資料庫的主檔案中。大多數情況下，資料庫引擎使用主資料庫中的檔案位置。

檔案有兩個名稱：邏輯名稱和物理名稱。邏輯名稱用於在所有 T-SQL 語句中引用檔案。物理名稱是 OS_file_name，它必須遵循作業系統的規則。資料和日誌檔案可以放置在 FAT 或 NTFS 檔案系統上，但不能放置在壓縮的檔案系統上。一個數據庫最多可以有 32,767 個檔案。

擴充套件

擴充套件是為表和索引分配空間的基本單位。一個擴充套件是 8 個連續頁或 64KB。SQL Server 有兩種型別的擴充套件：統一擴充套件和混合擴充套件。統一擴充套件僅由單個物件組成。混合擴充套件由多達八個物件共享。

頁

它是 MS SQL Server 中資料儲存的基本單位。頁的大小為 8KB。每個頁的開頭是 96 位元組的標頭，用於儲存系統資訊，例如頁的型別、頁上的可用空間量以及擁有該頁的物件的 ID。SQL Server 中有 9 種類型的資料頁。

• 資料 - 包含所有資料行，除了文字、ntext 和影像資料。

• 索引 - 索引條目。

• 文字/影像 - 文字、影像和 ntext 資料。

• GAM - 有關已分配擴充套件的資訊。

• SGAM - 有關係統級已分配擴充套件的資訊。

• 頁面空閒空間 (PFS) - 有關頁面上可用空閒空間的資訊。

• 索引分配對映 (IAM) - 有關表或索引使用的擴充套件的資訊。

• 批次更改對映 (BCM) - 有關自上次備份日誌語句以來透過批次操作修改的擴充套件的資訊。

• 差異更改對映 (DCM) − 包含自上次備份資料庫語句以來發生更改的範圍的資訊。

日誌檔案架構

SQL Server 事務日誌在邏輯上執行，就像事務日誌是一系列日誌記錄一樣。每個日誌記錄都由日誌序列號 (LSN) 標識。每個日誌記錄都包含其所屬事務的 ID。

資料修改的日誌記錄要麼記錄執行的邏輯操作，要麼記錄修改資料的先前映像和後續映像。先前映像是在執行操作之前的資料副本；後續映像是在執行操作之後的資料副本。

恢復操作的步驟取決於日誌記錄的型別 -

• 記錄的邏輯操作。
  • 要向前回滾邏輯操作，請再次執行該操作。
  • 要向後回滾邏輯操作，請執行相反的邏輯操作。
• 記錄的先前映像和後續映像。
  • 要向前回滾操作，請應用後續映像。
  • 要向後回滾操作，請應用先前映像。

事務日誌中記錄了不同型別的操作。這些操作包括 -

• 每個事務的開始和結束。

• 每個資料修改（插入、更新或刪除）。這包括系統儲存過程或資料定義語言 (DDL) 語句對任何表的更改，包括系統表。

• 每個擴充套件和頁面分配或取消分配。

• 建立或刪除表或索引。

回滾操作也會記錄在日誌中。每個事務都會在事務日誌上預留空間，以確保存在足夠的日誌空間來支援由顯式回滾語句或遇到錯誤引起的回滾。此預留空間在事務完成後釋放。

從必須存在於成功資料庫範圍回滾的第一個日誌記錄到最後一個寫入的日誌記錄的日誌檔案部分稱為日誌的活動部分或活動日誌。這是資料庫完全恢復所需的日誌部分。活動日誌的任何部分都不能被截斷。此第一個日誌記錄的 LSN 稱為最小恢復 LSN (Min LSN)。

SQL Server 資料庫引擎在內部將每個物理日誌檔案劃分為多個虛擬日誌檔案。虛擬日誌檔案沒有固定大小，並且物理日誌檔案沒有固定數量的虛擬日誌檔案。

資料庫引擎在建立或擴充套件日誌檔案時動態選擇虛擬日誌檔案的大小。資料庫引擎試圖保持少量虛擬檔案。管理員無法配置或設定虛擬日誌檔案的大小或數量。虛擬日誌檔案影響系統性能的唯一時間是物理日誌檔案由較小的大小和 growth_increment 值定義時。

size 值是日誌檔案的初始大小，growth_increment 值是在每次需要新空間時新增到檔案中的空間量。如果由於許多小的增量導致日誌檔案增長到很大，則它們將具有許多虛擬日誌檔案。這會減慢資料庫啟動以及日誌備份和還原操作的速度。

我們建議您為日誌檔案分配接近所需最終大小的 size 值，並使用相對較大的 growth_increment 值。SQL Server 使用預寫日誌 (WAL)，它保證在將關聯的日誌記錄寫入磁碟之前，不會將任何資料修改寫入磁碟。這維護了事務的 ACID 屬性。

MS SQL Server - 管理工作室

SQL Server Management Studio 是一個工作站元件\客戶端工具，如果我們在安裝步驟中選擇工作站元件，則將安裝此工具。這允許您從圖形介面連線和管理 SQL Server，而不必使用命令列。

為了連線到 SQL Server 的遠端例項，您將需要此軟體或類似軟體。它由管理員、開發人員、測試人員等使用。

以下方法用於開啟 SQL Server Management Studio。

第一種方法

開始 → 所有程式 → MS SQL Server 2012 → SQL Server Management Studio

第二種方法

轉到執行並鍵入 SQLWB（對於 2005 版本）SSMS（對於 2008 及更高版本）。然後點選回車。

SQL Server Management Studio 將以以下快照所示的方式開啟，無論使用上述哪種方法。

MS SQL Server - 登入資料庫

登入是訪問 SQL Server 的簡單憑據。例如，您在登入 Windows 或甚至您的電子郵件帳戶時提供使用者名稱和密碼。此使用者名稱和密碼構建了憑據。因此，憑據僅僅是使用者名稱和密碼。

SQL Server 允許四種類型的登入 -

• 基於 Windows 憑據的登入。
• 特定於 SQL Server 的登入。
• 對映到證書的登入。
• 對映到非對稱金鑰的登入。

在本教程中，我們感興趣的是基於 Windows 憑據的登入和特定於 SQL Server 的登入。

基於 Windows 憑據的登入允許您使用 Windows 使用者名稱和密碼登入到 SQL Server。如果您需要建立自己的憑據（使用者名稱和密碼），則可以建立特定於 SQL Server 的登入。

要建立、更改或刪除 SQL Server 登入，您可以採用以下兩種方法之一 -

• 使用 SQL Server Management Studio。
• 使用 T-SQL 語句。

以下方法用於建立登入 -

第一種方法 – 使用 SQL Server Management Studio

步驟 1 − 連線到 SQL Server 例項後，展開登入資料夾，如下面的快照所示。

步驟 2 − 右鍵單擊登入，然後單擊新建登入，將開啟以下螢幕。

步驟 3 − 填寫登入名、密碼和確認密碼列，如上圖所示，然後單擊確定。

登入將建立，如下面的影像所示。

第二種方法 – 使用 T-SQL 指令碼

Create login yourloginname with password='yourpassword'

要建立登入名為 TestLogin 且密碼為 'P@ssword' 的登入，請執行以下查詢。

Create login TestLogin with password='P@ssword'

MS SQL Server - 建立資料庫

資料庫是表、檢視、儲存過程、函式、觸發器等物件的集合。

在 MS SQL Server 中，提供了兩種型別的資料庫。

• 系統資料庫
• 使用者資料庫

系統資料庫

安裝 MS SQL Server 時會自動建立系統資料庫。以下是系統資料庫的列表 -

• Master
• Model
• MSDB
• Tempdb
• Resource（在 2005 版本中引入）
• Distribution（僅用於複製功能）

使用者資料庫

使用者資料庫由使用者（有權建立資料庫的管理員、開發人員和測試人員）建立。

以下方法用於建立使用者資料庫。

方法 1 – 使用 T-SQL 指令碼或還原資料庫

以下是建立 MS SQL Server 中資料庫的基本語法。

Create database <yourdatabasename>

或

Restore Database <Your database name> from disk = '<Backup file location + file name>

示例

要建立名為 'Testdb' 的資料庫，請執行以下查詢。

Create database Testdb

或

Restore database Testdb from disk = 'D:\Backup\Testdb_full_backup.bak'

注意 − D:\backup 是備份檔案的位置，Testdb_full_backup.bak 是備份檔名

方法 2 – 使用 SQL Server Management Studio

連線到 SQL Server 例項並右鍵單擊資料庫資料夾。單擊新建資料庫，將出現以下螢幕。

在資料庫名稱欄位中輸入您的資料庫名稱（例如：要建立名稱為 'Testdb' 的資料庫），然後單擊確定。Testdb 資料庫將建立，如下面的快照所示。

MS SQL Server - 選擇資料庫

在繼續使用以下任何方法之前，請根據您的操作選擇您的資料庫。

方法 1 – 使用 SQL Server Management Studio

示例

要執行查詢以選擇名為 'msdb' 的資料庫上的備份歷史記錄，請選擇 msdb 資料庫，如下面的快照所示。

方法 2 – 使用 T-SQL 指令碼

Use <your database name>

示例

要執行您的查詢以選擇名為 'msdb' 的資料庫上的備份歷史記錄，請透過執行以下查詢來選擇 msdb 資料庫。

Exec use msdb

該查詢將開啟 msdb 資料庫。您可以執行以下查詢以選擇備份歷史記錄。

Select * from backupset

MS SQL Server - 刪除資料庫

要從 MS SQL Server 中刪除您的資料庫，請使用 drop database 命令。以下兩種方法可用於此目的。

方法 1 – 使用 T-SQL 指令碼

以下是從 MS SQL Server 中刪除資料庫的基本語法。

Drop database <your database name>

示例

要刪除資料庫名稱 'Testdb'，請執行以下查詢。

Drop database Testdb

方法 2 – 使用 MS SQL Server Management Studio

連線到 SQL Server 並右鍵單擊要刪除的資料庫。單擊刪除命令，將出現以下螢幕。

單擊確定以從 MS SQL Server 中刪除資料庫（在本例中，名稱為 Testdb，如上圖所示）。

MS SQL Server - 建立備份

備份 是資料/資料庫等的副本。備份 MS SQL Server 資料庫對於保護資料至關重要。MS SQL Server 備份主要有三種類型 - 完全或資料庫、差異或增量以及事務日誌或日誌。

備份資料庫可以使用以下兩種方法之一完成。

方法 1 – 使用 T-SQL

完全型別

Backup database <Your database name> to disk = '<Backup file location + file name>'

差異型別

Backup database <Your database name> to 
   disk = '<Backup file location + file name>' with differential

日誌型別

Backup log <Your database name> to disk = '<Backup file location + file name>'

示例

以下命令用於將名為 'TestDB' 的資料庫完全備份到 'D:\' 位置，備份檔名 'TestDB_Full.bak'

Backup database TestDB to disk = 'D:\TestDB_Full.bak'

以下命令用於將名為 'TestDB' 的資料庫差異備份到 'D:\' 位置，備份檔名 'TestDB_diff.bak'

Backup database TestDB to disk = 'D:\TestDB_diff.bak' with differential

以下命令用於將名為 'TestDB' 的資料庫日誌備份到 'D:\' 位置，備份檔名 'TestDB_log.trn'

Backup log TestDB to disk = 'D:\TestDB_log.trn'

方法 2 – 使用 SSMS（SQL SERVER Management Studio）

步驟 1 − 連線到名為 'TESTINSTANCE' 的資料庫例項並展開資料庫資料夾，如下面的快照所示。

步驟 2 − 右鍵單擊 'TestDB' 資料庫並選擇任務。單擊備份，將出現以下螢幕。

步驟 3 − 選擇備份型別（完全\差異\日誌）並確保檢查目標路徑，即建立備份檔案的位置。選擇左上角的選項以檢視以下螢幕。

步驟 4 − 單擊確定以建立 'TestDB' 資料庫完全備份，如下面的快照所示。

MS SQL Server - 恢復資料庫

還原 是將資料從備份複製並將其應用於日誌事務的過程。還原是您對備份執行的操作。獲取備份檔案並將其恢復為資料庫。

還原資料庫選項可以使用以下兩種方法之一完成。

方法 1 – T-SQL

語法

Restore database <Your database name> from disk = '<Backup file location + file name>'

示例

以下命令用於還原名為 'TestDB' 的資料庫，備份檔名 'TestDB_Full.bak' 可在 'D:\' 位置找到，如果您要覆蓋現有的資料庫。

Restore database TestDB from disk = ' D:\TestDB_Full.bak' with replace

如果您要使用此還原命令建立新資料庫，並且目標伺服器中沒有類似的資料、日誌檔案路徑，則使用 move 選項，如下面的命令所示。

確保 D:\Data 路徑存在，如以下命令中用於資料和日誌檔案所示。

RESTORE DATABASE TestDB FROM DISK = 'D:\ TestDB_Full.bak' WITH MOVE 'TestDB' TO 
   'D:\Data\TestDB.mdf', MOVE 'TestDB_Log' TO 'D:\Data\TestDB_Log.ldf'

方法 2 – SSMS（SQL SERVER Management Studio）

步驟 1 − 連線到名為 'TESTINSTANCE' 的資料庫例項並右鍵單擊資料庫資料夾。單擊還原資料庫，如下面的快照所示。

步驟 2 − 選擇裝置單選按鈕並單擊省略號以選擇備份檔案，如下面的快照所示。

步驟 3 − 單擊確定，將彈出以下螢幕。

步驟 4 − 選擇左上角的檔案選項，如下面的快照所示。

步驟 5 − 選擇左上角的選項，然後單擊確定以還原 'TestDB' 資料庫，如下面的快照所示。

MS SQL Server - 建立使用者

使用者指的是 MS SQL Server 資料庫中的帳戶，用於訪問資料庫。

可以使用以下兩種方法之一建立使用者。

方法 1 – 使用 T-SQL

語法

Create user <username> for login <loginname>

示例

要建立使用者名稱 'TestUser' 並將其對映到 TestDB 資料庫中的登入名 'TestLogin'，請執行以下查詢。

create user TestUser for login TestLogin

其中“TestLogin”是作為登入建立的一部分建立的登入名。

方法 2 – 使用 SSMS（SQL Server Management Studio）

注意 − 在建立使用者帳戶之前，我們必須首先使用任何名稱建立登入名。

讓我們使用名為“TestLogin”的登入名。

步驟 1 − 連線 SQL Server 並展開資料庫資料夾。然後展開名為“TestDB”的資料庫（我們將在其中建立使用者帳戶）並展開安全資料夾。右鍵單擊使用者，然後單擊“新建使用者”以檢視以下螢幕。

步驟 2 − 在使用者名稱欄位中輸入“TestUser”，然後單擊省略號以選擇名為“TestLogin”的登入名，如下面的快照所示。

步驟 3 − 單擊“確定”以顯示登入名。再次單擊“確定”以建立“TestUser”使用者，如下面的快照所示。

MS SQL Server - 分配許可權

許可權是指管理主體對可保護物件訪問級別規則。您可以在 MS SQL Server 中授予、撤銷和拒絕許可權。

要分配許可權，可以使用以下兩種方法之一。

方法 1 – 使用 T-SQL

語法

Use <database name>
Grant <permission name> on <object name> to <username\principle>

示例

要為名為“TestDB”資料庫中名為“TestTable”的物件上的名為“TestUser”的使用者分配選擇許可權，請執行以下查詢。

USE TestDB
GO
Grant select on TestTable to TestUser

方法 2 – 使用 SSMS（SQL Server Management Studio）

步驟 1 − 連線到例項並展開資料夾，如下面的快照所示。

步驟 2 − 右鍵單擊 TestUser 並單擊“屬性”。將出現以下螢幕。

步驟 3 單擊“搜尋”並選擇特定選項。單擊“物件型別”，選擇“表”，然後單擊“瀏覽”。選擇“TestTable”並單擊“確定”。將出現以下螢幕。

步驟 4 選擇“選擇”許可權下“授予”列的複選框，然後單擊“確定”，如上圖快照所示。

步驟 5 選擇授予“TestUser”的“TestDB”資料庫中“TestTable”的許可權。單擊“確定”。

MS SQL Server - 監控資料庫

監視是指檢查資料庫狀態、設定（可以是所有者姓名、檔名、檔案大小、備份計劃等）。

SQL Server 資料庫主要可以透過 SQL Server Management Studio 或 T-SQL 進行監視，還可以透過各種方法進行監視，例如建立代理作業和配置資料庫郵件、第三方工具等。

可以檢查資料庫狀態是聯機還是處於任何其他狀態，如下面的快照所示。

根據以上螢幕，所有資料庫都處於“聯機”狀態。如果任何資料庫處於任何其他狀態，則將顯示該狀態，如下面的快照所示。

MS SQL Server - 服務

MS SQL Server 提供以下兩種服務，這對於資料庫建立和維護是必須的。還列出了可用於不同目的的其他附加服務。

• SQL Server
• SQL Server 代理

其他服務

• SQL Server 瀏覽器
• SQL Server 全文搜尋
• SQL Server Integration Services
• SQL Server Reporting Services
• SQL Server Analysis Services

可以使用以下方法使用上述服務。

啟動服務

要啟動任何服務，可以使用以下兩種方法之一。

方法 1 – Services.msc

步驟 1 − 轉到“執行”，鍵入 services.msc 並單擊“確定”。將出現以下螢幕。

步驟 2 − 要啟動服務，請右鍵單擊服務，然後單擊“啟動”按鈕。服務將啟動，如下面的快照所示。

方法 2 – SQL Server 配置管理器

步驟 1 − 使用以下過程開啟配置管理器。

開始→所有程式→MS SQL Server 2012→配置工具→SQL Server 配置管理器。

步驟 2 − 選擇服務名稱，右鍵單擊並單擊“啟動”選項。服務將啟動，如下面的快照所示。

停止服務

要停止任何服務，可以使用以下三種方法之一。

方法 1 - Services.msc

步驟 1 − 轉到“執行”，鍵入 services.msc 並單擊“確定”。將出現以下螢幕。

步驟 2 − 要停止服務，請右鍵單擊服務並單擊“停止”。選定的服務將停止，如下面的快照所示。

方法 2 – SQL Server 配置管理器

步驟 1 − 使用以下過程開啟配置管理器。

開始→所有程式→MS SQL Server 2012→配置工具→SQL Server 配置管理器。

步驟 2 − 選擇服務名稱，右鍵單擊並單擊“停止”選項。選定的服務將停止，如下面的快照所示。

方法 3 – SSMS（SQL Server Management Studio）

步驟 1 − 連線到例項，如下面的快照所示。

步驟 2 − 右鍵單擊例項名稱並單擊“停止”選項。將出現以下螢幕。

步驟 3 − 單擊“是”按鈕，將開啟以下螢幕。

步驟 4 − 在上述螢幕上單擊“是”選項以停止 SQL Server 代理服務。服務將停止，如下面的螢幕截圖所示。

注意

• 我們無法使用 SQL Server Management Studio 方法啟動服務，因為由於服務已停止狀態而無法連線。

• 在停止 SQL Server 服務時，我們不能排除停止 SQL Service 代理服務，因為 SQL Server 代理服務是依賴服務。

MS SQL Server - 高可用性技術

高可用性 (HA) 是一種解決方案/流程/技術，用於使應用程式/資料庫在計劃內或計劃外停機期間 24x7 可用。

主要而言，MS SQL Server 中有五個選項可以為資料庫實現/設定高可用性解決方案。

複製

源資料將透過複製代理（作業）複製到目標。物件級技術。

術語

• 釋出伺服器是源伺服器。
• 分發伺服器是可選的，並存儲訂閱伺服器的複製資料。
• 訂閱伺服器是目標伺服器。

日誌傳送

源資料將透過事務日誌備份作業複製到目標。資料庫級技術。

術語

• 主伺服器是源伺服器。
• 輔助伺服器是目標伺服器。
• 監視伺服器是可選的，將由日誌傳送狀態監視。

映象

主資料將透過網路事務基礎複製到輔助，藉助映象端點和埠號。資料庫級技術。

術語

• 主體伺服器是源伺服器。
• 映象伺服器是目標伺服器。
• 見證伺服器是可選的，用於進行自動故障轉移。

群集

資料將儲存在共享位置，該位置由主伺服器和輔助伺服器根據伺服器的可用性使用。例項級技術。需要使用共享儲存的 Windows 群集設定。

術語

• 活動節點是 SQL 服務正在執行的位置。
• 被動節點是 SQL 服務未執行的位置。

AlwaysOn 可用性組

主資料將透過網路事務基礎複製到輔助。資料庫級技術組。需要沒有共享儲存的 Windows 群集設定。

術語

• 主副本是源伺服器。
• 輔助副本是目標伺服器。

以下是配置 HA 技術（映象和日誌傳送）的步驟，但不包括群集、AlwaysOn 可用性組和複製。

步驟 1 − 獲取源資料庫的一個完整備份和一個 T-log 備份。

示例

要為“TESTINSTANCE”作為主伺服器和“DEVINSTANCE”作為輔助 SQL Server 的“TestDB”資料庫配置映象/日誌傳送，請編寫以下查詢以在源（TESTINSTANCE）伺服器上獲取完整備份和 T-log 備份。

連線到“TESTINSTANCE”SQL Server 並開啟新查詢，然後編寫以下程式碼並執行，如下面的螢幕截圖所示。

Backup database TestDB to disk = 'D:\testdb_full.bak'
GO
Backup log TestDB to disk = 'D:\testdb_log.trn'

步驟 2 − 將備份檔案複製到目標伺服器。

在本例中，我們只有一個物理伺服器和兩個已安裝的 SQL Server 例項，因此無需複製，但如果兩個 SQL Server 例項位於不同的物理伺服器中，我們需要將以下兩個檔案複製到安裝了“DEVINSTANCE”例項的輔助伺服器的任何位置。

步驟 3 − 使用“norecovery”選項在目標伺服器上使用備份檔案還原資料庫。

示例

連線到“DEVINSTANCE”SQL Server 並開啟“新建查詢”。編寫以下程式碼以還原名為“TestDB”的資料庫，該資料庫與主資料庫（“TestDB”）的名稱相同，用於資料庫映象。但是，我們可以為日誌傳送配置提供不同的名稱。在本例中，讓我們使用“TestDB”資料庫名稱。對於兩次（完整和 t-log 備份檔案）還原，使用“norecovery”選項。

Restore database TestDB from disk = 'D:\TestDB_full.bak'
with move 'TestDB' to 'D:\DATA\TestDB_DR.mdf',
move 'TestDB_log' to 'D:\DATA\TestDB_log_DR.ldf',
norecovery
GO
Restore database TestDB from disk = 'D:\TestDB_log.trn' with norecovery

重新整理“DEVINSTANCE”伺服器中的資料庫資料夾，以檢視已還原的資料庫“TestDB”，其還原狀態如下面的快照所示。

步驟 4 − 根據您的要求配置 HA（日誌傳送、映象），如下面的快照所示。

示例

右鍵單擊“TESTINSTANCE”SQL Server（為主伺服器）的“TestDB”資料庫，然後單擊“屬性”。將出現以下螢幕。

步驟 5 − 根據您的要求，選擇名為“映象”或“事務日誌傳送”（以紅色框突出顯示）的選項，如上圖所示，並按照系統本身引導的嚮導步驟完成配置。

MS SQL Server - 報表服務

報表是一個可顯示的元件。

用法

報表基本上用於兩個目的 - 公司內部運營和公司外部運營。

Reporting Services

這是一項用於建立和釋出各種報表的服務。

以下是開發任何報表所需的三個要求。

• 業務流程
• 佈局
• 查詢/過程/檢視

BIDS（直到 2008 R2 的 Business Intelligence Studio）和 SSDT（從 2012 年開始的 SQL Server Data Tools）是開發報表的環境。

以下是開啟 BIDS/SSDT 環境以開發報表的步驟。

步驟 1 − 根據 Microsoft SQL Server 程式組中的版本開啟 BIDS/SSDT。將出現以下螢幕。在本例中，SSDT 已開啟。

步驟 2 − 轉到左上角的“檔案”。單擊“新建”並選擇“專案”。將開啟以下螢幕。

步驟 3 − 在以上螢幕中，選擇左上角“商業智慧”下的“Reporting Services”，如下面的螢幕截圖所示。

步驟 4 − 在以上螢幕中，根據您的要求選擇“報表伺服器專案嚮導”（它將引導您逐步完成嚮導）或“報表伺服器專案”（它將用於選擇自定義設定）來開發報表。

MS SQL Server - 執行計劃

執行計劃將由查詢最佳化器在統計資訊和代數/處理器樹的幫助下生成。它是查詢最佳化器的結果，並說明如何執行您的工作/需求。

有兩種不同的執行計劃 - 估計和實際。

估計執行計劃表示最佳化器檢視。

實際執行計劃指示執行查詢的內容以及執行方式。

執行計劃儲存在稱為計劃快取的記憶體中，因此可以重複使用。每個計劃僅儲存一次，除非最佳化器決定查詢執行的並行性。

SQL Server 中有三種不同的執行計劃格式 - 圖形計劃、文字計劃和 XML 計劃。

SHOWPLAN 是想要檢視執行計劃的使用者所需的許可權。

示例 1

以下是檢視估計執行計劃的步驟。

步驟 1 − 連線到 SQL Server 例項。在本例中，“TESTINSTANCE” 是例項名稱，如下面的快照所示。

步驟 2 − 點選上面螢幕上的“新建查詢”選項，並編寫以下查詢。在編寫查詢之前，選擇資料庫名稱。在本例中，“TestDB” 是資料庫名稱。

Select * from StudentTable

步驟 3 − 點選上面螢幕上紅色方框中高亮顯示的符號，以顯示估計執行計劃，如下面的螢幕截圖所示。

步驟 4 − 將滑鼠放在上面螢幕上紅色方框上方的第二個符號（表掃描）上，以詳細顯示估計執行計劃。將出現以下螢幕截圖。

示例 2

以下是檢視實際執行計劃的步驟。

步驟 1 − 連線到 SQL Server 例項。在本例中，“TESTINSTANCE” 是例項名稱。

步驟 2 − 點選上面螢幕上看到的“新建查詢”選項，並編寫以下查詢。在編寫查詢之前，選擇資料庫名稱。在本例中，“TestDB” 是資料庫名稱。

Select * from StudentTable

步驟 3 − 點選上面螢幕上紅色方框中高亮顯示的符號，然後執行查詢以顯示實際執行計劃以及查詢結果，如下面的螢幕截圖所示。

步驟 4 − 將滑鼠放在螢幕上紅色方框上方的第二個符號（表掃描）上，以詳細顯示實際執行計劃。將出現以下螢幕截圖。

步驟 5 − 點選上面螢幕左上角的“結果”，以獲得以下螢幕。

MS SQL Server - 整合服務

此服務用於執行 ETL（提取、轉換和載入資料）和管理操作。BIDS（2008 R2 之前的商業智慧工作室）和 SSDT（2012 年開始的 SQL Server 資料工具）是開發包的環境。

SSIS 基本架構

解決方案（專案的集合）---> 專案（包的集合）---> 包（ETL 和管理操作的任務的集合）

在包下，以下元件可用：

• 控制流（容器和任務）
• 資料流（源、轉換、目標）
• 事件處理程式（傳送訊息、電子郵件）
• 包資源管理器（包中所有內容的單一檢視）
• 引數（使用者互動）

以下是開啟 BIDS\SSDT 的步驟。

步驟 1 − 根據版本從 Microsoft SQL Server 程式組中開啟 BIDS\SSDT。將出現以下螢幕。

步驟 2 − 上面的螢幕顯示 SSDT 已開啟。轉到上面影像左上角的“檔案”，然後點選“新建”。選擇“專案”，將開啟以下螢幕。

步驟 3 − 在上面螢幕的左上角選擇“商業智慧”下的“Integration Services”，以獲得以下螢幕。

步驟 4 − 在上面螢幕中，根據您的需求選擇“Integration Services 專案”或“Integration Services 匯入專案嚮導”來開發/建立包。

MS SQL Server - 分析服務

此服務用於分析海量資料並應用於業務決策。它還用於建立二維或多維業務模型。

在 SQL Server 2000 版本中，它被稱為 MSAS（Microsoft Analysis Services）。

從 SQL Server 2005 開始，它被稱為 SSAS（SQL Server Analysis Services）。

模式

有兩種模式：本機模式（SQL Server 模式）和 SharePoint 模式。

模型

有兩種模型：表格模型（用於團隊和個人分析）和多維模型（用於企業分析）。

BIDS（2008 R2 之前的商業智慧工作室）和 SSDT（2012 年開始的 SQL Server 資料工具）是用於處理 SSAS 的環境。

步驟 1 − 根據版本從 Microsoft SQL Server 程式組中開啟 BIDS\SSDT。將出現以下螢幕。

步驟 2 − 上面的螢幕顯示 SSDT 已開啟。轉到上面影像左上角的“檔案”，然後點選“新建”。選擇“專案”，將開啟以下螢幕。

步驟 3 − 在上面螢幕中，選擇“商業智慧”下的“Analysis Services”（如左上角所示）。將彈出以下螢幕。

步驟 4 − 在上面螢幕中，根據您的需求從列出的五個選項中選擇任何一個選項來處理 Analysis Services。