跳至主要内容

HowTo：架設公有雲上安全、合規且具高效率的企業級 MicroSoft SQL 服務

MS SQL Server Web 規格與作業系統
  • 規格: v.super (8 vCPU, 64 GB Memory, 100G HDD)
  • 作業系統: Windows Server 2016
  • 儲存空間: 1TB SSD
  • 備份儲存空間: TWCC

從本地端電腦連線 SQL Server Web​

1. 登入 SQL Server Web 後啟用 TCP/IP 連線設定​

  • 利用 Microsoft SQL Server 2016 下 SQL Server 2016 設定管理員進行設定

  • 啟用 TCP/IP 連線設定

2. 執行 Microsoft SQL Server Management Studio​

  • 打開工具列將 Microsoft SQL Server Tools 18，並執行 Microsoft SQL Server Management Studio 18

3. 進入 SQL Server Web 設定使用者帳號​

  • 點選左上角的連線，預設開啟的情況下會直接跳到下一步連線至伺服器的畫面

  • 填寫連線至伺服器內容並且連線

建議將驗證欄選擇選項 Windows 驗證，後再填入伺服器名稱 localhost

伺服器類型: 資料庫引擎(預設)
伺服器名稱: localhost
驗證: Windows 驗證(預設應為 SQL Server 驗證)

  • 連線成功

4. 設定伺服器安全性驗證​

在進行開啟最高權限 sa 與新增登入帳號時，需要先將伺服器安全性驗證設定成 SQL Server 及 Windows 驗證模式

  • 右鍵點取 Localhost，並點擊屬性

  • 開啟後點選安全性，將默認的預設 Windows 驗證模式更改成 SQL Server 及 Windows 驗證模式並進行確認

  • 重新啟動 SQL Server Web

5. 開啟 SQL Server Web 安全性群組​

連線 SQL Server Web 需要使用 1433 連接埠，該連接埠並未在 TWSC 虛擬運算個體的預設清單內，因此需要參考安全性群組的文件，開啟1433連接埠。

6. 開啟最高使用者帳號權限​

  • 於 TWSC 開啟的 MS SQL Server Web，預設最高權限 sa 並未被開啟，因此建議先開啟 MS SQL Server Web 權限，點擊安全性下的登入，找到 sa 的選項進行設定。

  • 設定 sa 驗證密碼

  • 設定 sa 的狀態，將登入選項改為已啟用

  • 測試連線成功

    請於伺服器名出處填入虛擬運算個體的公用 IP，並驗證方式選擇成 SQL Server 驗證

擴充 SQL Server Web 與備份儲存空間​

1. 建立資料碟增加儲存空間​

2. 掛載異地備份儲存空間​

TWSC 有提供雲端物件儲存服務，本文推薦以雲端物件儲存空間做為備份的儲存空間，將雲端物件儲存空間掛載到 SQL Server Web 時，請先建立好儲存體(bucket)。

  • 將雲端物件儲存體與 SQL Server Web 進行掛載

    以上圖中的儲存體 objsqlserver 作為掛載的儲存空間，推薦使用 TntDrive 將雲端物件儲存體掛載到SQL Server Web

  • 更改預設資料庫的儲存位置到掛載資料碟

    選取資料庫並且點選右鍵找到屬性

  • 設定預設路徑

  • 將資料與記錄檔存取到掛載的 SSD 中，本文範例為 D，備份位置為 TWSC 的雲端物件儲存空間為 E

  • 重新啟動

  • 進入伺服器屬性確認設定是否有被成功套用

警告
  1. 在完成每一次的備份設定後，務必要進行儲存的動作
  2. 務必重新啟動 SQL Server Web，路徑的設定才會生效

設定週期性備份​

  • 開啟 SQL Server Web Agent功能

    使用 SQL Server 2016 組態管理員啟用 SQL Server Agent

  • SSMS 內部設定

    於 SSMS 內設定備份時間與新增維護計畫，進入管理->維護計畫->新增維護計畫

週期性(異地)完整備份​

  • 設定週期性備份相關資料

    • 點選紅框內的按鍵進行備份細節的設定

    • 新增排程作業處設定備份的排程類型與時間，完成設定後可以在摘要與描述的地方確認自己設定內容再進行確認

信息

主要設定的參數如下:

  • 排程類型: 可進行設定，以本章節的目的，設定為重複執行
  • 頻率設定: 日、週與月選項，可以個人的需求進行設定，本文以每週為範例
  • 每日頻率: 此部分為備份當日的執行時間

  • 點開工具箱，選取備份資料庫工作並拖拉至下方箭頭指定處

  • 點擊備份資料庫工作進行備份類型、備份的資料庫與路徑的設定

  • 選擇備份類型與需要備份的資料庫

  • 設定備份路徑與副檔名

  • 確認資料有成功備份雲端物件空間的儲存體

週期性(異地)差異備份​

請參考(情境應用範例 -> 資料庫設定 -> 差異備份)

情境應用範例​

本文利用常見的訂單資料庫做為範例，說明 SQL 伺服器於實際應用情形的操作方法。

警告

由於 TWSC 的虛擬運算個體所提供的系統碟只有 100G，因此強烈建議您需要額外掛載資料碟，可以依照個人需求選擇 SSD 或是 HDD。

資料庫桔構說明​

  • 資料庫共分為 4 個資料表，分別為客戶(customer)、產品(goods)、訂單數量(order_detail)與交易資訊(purchase_order)。

  • 客戶(customer)資料表裡面含有客戶名稱、所在地址與該筆資料建立的時間註記

  • 產品(goods)資訊表中的內容有產品品項、價格與數量

  • 訂單數量表(order_detail)中的內容含有品項與數量

  • 交易資訊表(purchase_order)裡面有幾項的記錄著客戶名稱、訂單總金額與建立時間

資料庫設定​

完整備份​

為確保資料的完整性，因此我們會先進行完整備份，將資料分為客戶與產品資訊、訂單交易紀錄進行完整的備份。依照上面文章的做法進行完整備份的設定。

警告
  1. 在完成每一次的備份設定後，務必要進行儲存的動作
  2. SQL Server Agent 必須要是啟動的狀態，才會進行備份

在實驗中我們要明確的告訴使用者，各類備份資料的情況。因此我們將完整備份的檔案儲存到儲存體中的資料夾 Full Database Backups

  • 新增維護計畫並進行命名

  • 設定週期性輩分時間

  • 每週四的 17:10 為本情境進行備份的時間

  • 開啟工具箱，並點擊備與拖拉份資料庫工作

  • 點擊備份資料庫動作

  • 設定備份類型

  • 選擇要進行備份的資料庫

  • 設定備份目的地，本情境已掛載的雲端物件儲存體做為備份的目的地，並選取已經在雲端物件儲存體中開好的資料夾 Full Database Backups

  • 進入雲端物件儲存體的資料夾內確認檔案有成功於指定的資料夾內

差異備份​

此時我們輸入第二筆訂單，進行資料庫的備份設定，此章節我們將要示範差異備份，將新加入的訂單資訊進行備份，確保訂單不會遺失

  • 確認訂單有加入資料庫中

  • 新增維護計畫

  • 設定作業排程，每天執行一次，設定為每日進行一次備份，並且於中午 12:00 執行

  • 開啟工具箱，並點擊備份資料庫工作

  • 選擇備份類型

  • 選擇需要備份的資料庫

  • 設定備份的目的地，這邊選擇掛載雲端物件儲存體的 E 槽差異備份的資料夾

  • 確認備份位置，並進行設定

  • 至雲端物件儲存體查看是否成功進行備份

交易紀錄備份​

第三張訂單進來，為確保訂單的完整性接下來會進行交易紀錄備份的動作，再輸入一筆訂單作為範例。

  • 搜尋並確認訂單有成功的入資料庫

  • 新增維護計畫

  • 輸入計畫名稱並確認

  • 編輯備份時間與內容

  • 開啟工具箱

  • 拖拉並點擊備份資料庫已進行編輯

  • 選擇備份資料庫

  • 選擇備份位置，完成後進行確認

  • 確認備份成功

備份及還原演練 (BCP)​

完整備份還原演練​

  • 建立還原資料庫的虛擬運算個體
  • 進行 SQL Server Web 權限設定
  • 還原資料庫

  • 登入 SQL 資料庫後，進行資料庫還原的動作

  • 選擇備份檔案的裝置

  • 加入資料庫檔案

  • 還原成功

  • 確認訂單數量表資料皆存在

差異備份還原演練​

警告

務必要將將完整備份與差異備份的備份檔一併進行還原

  • 選擇資料庫還原

  • 選擇備份檔案的裝置

  • 加入資料庫檔案，將完整備份與差異備份的備份檔一起選取

  • 還原成功

  • 確認差異備份的資料皆存在

交易紀錄還原演練​

  • 選擇資料庫還原

  • 選擇備份檔案的裝置

  • 加入資料庫檔案，將完整備份、差異備份與交易紀錄備份備份檔一起選取

  • 還原成功

  • 查詢訂單是否有還原成功