積沙成塔

SQL Server 2012 自主資料庫（Contained DB）提供您在搬移資料庫到不同執行個體時，可以讓使用者不須經過 SQL Server 執行個體的驗證，來直接連接至資料庫，讓資料庫移轉或部署作業更加簡便，本文將透過 SQL Server 2012 – Working with Contained Databases (SQL 145) 這個 SQL Server Virtual Lab 來告訴您，如何使用自主資料庫。

---

【Exercise 1】

在這個練習將告訴您如何建立自主資料庫。

• 首先由【Start > All Programs > Microsoft SQL Server 2012 > SQL Server Management Studio】開啟 SSMS。

• 於【Connect to Server】視窗中，輸入 Server name 為【SQLONE】，選擇 Authentication 為【Windows Authentication】，然後按 Connect。

• 接下來要啟用執行個體的自主資料庫功能，由【Object Explorer > SQLONE > Properties】開啟 Server Properties 視窗。

• 將【Server Properties > Advanced > Enable Contained Database】設定為 True。

• 接著重新啟動執行個體。

• 接著我們要建立一個新的資料庫，請由【Object Explorer > SQLONE > Databases > New Database】開啟 New Database 視窗。

• 於【New Database】視窗中輸入 Database name 為【ContainedDB】。

• 點選【Options】後將【Containment type】設定為【Partial】。

• 然後要建立 Contained User，請由【Object Explorer > SQLONE > ContainedDB > Security > Users > New User】，來開啟 Database User - New 視窗。

• 於【Database User - New】視窗中，設定 User type 為【SQL user with password】，User name 為【ContainedUser】，Password 和 Confirm password 都設定為【pass@word1】。

• 點選【Membership】後，勾選【db_owner】，然後按 OK。

• 接著關閉現有的連線。

• 嘗試改用上述建立的 ContainedUser 來登入 SQLONE。

記得將 Connect to database 設定為自主資料庫【ContainedDB】。

若順利連接，您將看到如下圖的結果。

• 接著來驗證一下 ContainedUser 可以做哪些事情，請點選【New Query】來開啟查詢編輯器。

• 於查詢編輯器中輸入下列 T-SQL，您會發現您沒有權限修改資料庫的 Containment type，有關部分自主資料庫所支援的功能，請見 Modified Features (Contained Database)。
  
  

     1:  ALTER DATABASE ContainedDB

     2:  SET ConTAINMENT = NONE;

• 接著嘗試執行下列 T-SQL 敘述。
  
  

     1:  ALTER DATABASE CURRENT 

     2:  SET MULTI_USER;

• 嘗試使用下列T-SQL 修改 ContainedDB 的相容性層級為 SQL SERVER 2012（110）。
  
  

     1:  ALTER DATABASE ContainedDB

     2:  SET compatibility_level = 110

• 透過 sys.dm_db_uncontained_entities DMV 來查看資料庫中所使用的所有非自主物件（uncontained object），所謂的非自主物件指的是在自主資料庫中可以用來跨越資料庫界線的物件。
  
  

     1:  select *

     2:  from sys.dm_db_uncontained_entities

 

---

【Exercise 2】

這個練習將告訴您如何將自主資料庫搬移到其他資料庫伺服器，並且可以順利正常的運作。

• 首先由【Start > All Programs > Microsoft SQL Server 2012 > SQL Server Management Studio】開啟 SSMS。

• 於【Connect to Server】視窗中，輸入 Server name 為【SQLONE】，選擇 Authentication 為【Windows Authentication】，然後按 Connect。

• 點選【New Query】開啟查詢編輯器，輸入下列 T-SQL 敘述，來備份 ContainedDB，但由於 Lab 的手冊上的路徑在虛擬機器中並不存在，因此筆者將備份路徑稍作調整（如下列程式碼），您也可以依照手冊上的路徑來備份，但前提是必須先建立好要備份的目的資料夾。
  
  

     1:  BACKUP DATABASE ContainedDB TO

     2:  DISK = 'C:\LabFiles\Setup\ContainedDB\ContainedDB.bak'

 

• 切換至 SQLTWO 虛擬機器，一樣開啟 SSMS，後於物件總管中連接至 Database Engine。

• 於【Connect to Server】視窗中，輸入 Server name 為【SQLTWO】，選擇 Authentication 為【Windows Authentication】，然後按 Connect。

• 接著按【 New Query】開啟查詢編輯器後輸入，下列的 T-SQL 敘述，您會發現錯誤訊息，造成這個錯誤的原因在於自主驗證尚未被啟用。
  
  

     1:  RESTORE DATABASE ContainedDB FROM

     2:  DISK = '\\SQLONE\C$\LABFILES\SETUP\CONTAINEDDB\CONTAINEDDB.bak'

• 您必須執行下列的 T-SQL 敘述來啟用自主驗證，才可以將自主資料庫還原。
  
  

     1:  EXEC sp_configure 'show advanced options',1   

     2:  RECONFIGURE WITH OVERRIDE   

     3:  GO

     4:   

     5:  EXEC sp_configure 'contained database authentication',1   

     6:  RECONFIGURE WITH OVERRIDE

     7:  GO

• 還原成功後重新於物件總管中，利用 Contained User 來連接自主資料庫。

• 嘗試利用 ContainedUser 來登入 SQLTWO。

記得將 Connect to database 設定為自主資料庫【ContainedDB】。

若沒問題您將可以順利使用 ContainedUser 來登入 SQLTWO 的 ContainedDB。

---

【Exercise 3】

假設您有現有的資料庫想要移轉成為自主資料庫，那麼您可以參考這個練習所述的方法。

• 開啟 SSMS 後，請由【File > Open > File】來開啟這個練習所需要的 SQL 檔。

• 假設您依照手冊所述的路徑找不到檔案，請改至【C:\LabFiles\Setup\ContainedDB\Scripts】下找到【NewLogin.sql】，然後按 Open。

• 由於目前筆者開啟的虛擬機器中沒有 AdventureWorks 資料庫，只有 AdventureWorks2012，請自行將 T-SQL 中的 AdventureWorks 改為 【AdventureWorks2012】，然後按 F5 執行建立 Login。

• 一樣透過 sys.dm_db_uncontained_entities DMV 來查看資料庫中所使用的所有非自主物件（uncontained object），在這個步驟中您應該會看到六個非自主物件（如下圖）。
  
  

     1:  SELECT class_desc,statement_type,feature_name,feature_type_name

     2:  FROM sys.dm_db_uncontained_entities

 

• 利用下列 T-SQL 查詢之前建立且未被停用的 Login。
  
  

     1:  SELECT dp.name

     2:  FROM sys.database_principals dp

     3:  JOIN sys.server_principals sp

     4:  ON dp.sid = sp.sd

     5:  WHERE dp.authentication_type = 1

     6:  AND sp.is_disabled = 0

 

• 由【Object Explorer > master > New Query】開啟查詢編輯器後輸入下列 T-SQL 來變更 Containment type。
  
  

     1:  ALTER DATABASE AdventureWorks2012

     2:  SET CONTAINMENT=PARTIAL;

• 請於【Object Explorer > master > New Query】開啟查詢編輯器，輸入下列的 T-SQL，其中最重要的是利用 sp_migrate_user_to_contained 來將 NewUnContainedUser 轉換為 ContainedUser，並停用該 User 所對應的 Login。

   1:  --切換至AdventureWorks2012資料庫   

   2:  USE AdventureWorks2012   

   3:  GO   

   4:   

   5:  --利用 sp_migrate_user_to_contained 預存程序來將 UnContained User 移轉成為 Contained User   

   6:  EXEC sp_migrate_user_to_contained   

   7:  @username = N'NewUnContainedUser',   

   8:  @renew = N'keep_name',   

   9:  @disable_login = N'disable_login'  

  10:  GO  

  11:   

  12:  --檢查是否有位停用的 Login 對應到資料庫使用者  

  13:  SELECT dp.name  

  14:  FROM sys.database_principals dp  

  15:  JOIN sys.server_principals sp  

  16:  ON dp.sid = sp.sid  

  17:  WHERE dp.authentication_type = 1 

  18:  AND sp.is_disabled = 0

• 完成上述轉換後，您就可以利用 NewUnContainedUser 來登入 SQLONE 執行個體中的 AdvantureWorks2012 資料庫。

記得連線時將 Connect to database 設定為自主資料庫【AdvantureWorks2012 】。

若沒問題您將可以順利使用 NewUnContainedUser 來登入 SQLONE 的 AdvantureWorks2012 。

---

【參考資料】

• SQL Server 2012 – Working with Contained Databases (SQL 145)
• Contained Databases in SQL Server 2012
• Migrating to a Contained Database in SQL Server 2012