新手學SQL Server 2012「自主資料庫(Contained Database)」(1)

by Derrick 17. 四月 2012 14:00

前言


在過去,若是要將SQL Server資料庫搬移到另外一個「執行個體」上,需要考慮到不少事情,例如:登入帳戶的同步問題,tempdb系統資料庫所使用的「定序」等問題,這都會讓資料庫管理師在這方面需要費心維護與處理。
在SQL Server 2012版本上新推出了「自主資料庫」,將可以大幅簡化這些問題,也能夠與SQL Azure資料庫有更緊密的整合。

認識「自主資料庫」


在SQL Server 2012版本上新推出了「自主資料庫(Contained Database)」的功能。使用「自主資料庫」,可以讓此資料庫與「執行個體」層級之間是獨立隔離的,將來在搬移「自主資料庫」到另外一個「執行個體」時,無需額外的管理組態作業,並且提供了資料庫層級的使用者驗證機制。
在SQL Server 2012上提供了以下的方式來協助你將資料庫與「執行個體」之間做隔離:

  • 原本在「執行個體」層級上與資料庫有關的「中繼資料」(存放在master系統資料庫),大部分改為在「自主資料庫」內就可以設定維護。
  • 「自主資料庫」內的中繼資料都將使用相同「定序」來定義。
  • 使用者登入驗證將改在資料庫層級上執行,減少與執行個體登入帳戶的相依性。
  • 相關的動態管理檢視、XEvent等,也會加入了對「自主資料庫」相關資訊的回應與處理。

在「自主資料庫」內所使用到功能,稱為:「內含項目(CONTAINMENT)」。依據所使用到的功能,將可以分為:

  • 「完成自主資料庫(fully contained database)」。
  • 「部分自主資料庫(Partially contained databases)」。

若全部的功能都能夠在資料庫內部處理的,稱為「完全自主資料庫」。若有部分功能,仍位於資料庫外部或是仰賴與資料庫外部的功能來互動執行,稱為「部分自主資料庫」,也就是被視為「非內含性(Uncontained)」。目前,SQL Server 2012版本所提供的「自主資料庫」,是屬於「部分自主資料庫」。

在「自主資料庫」上,新提供了幾種使用者類型來登入存取資料庫的資源,請參考下表的說明:

類型

說明

具有密碼之自主資料庫使用者

此為資料庫層級的驗證機制。

設定為:「有密碼的SQL使用者(SQL user with password)」,來建立所需的使用者帳戶。

Windows主體

此為資料庫層級信任Windows驗證機制。

已授權的Windows使用者和已授權之Windows群組的成員可以直接連接至資料庫,而且不需要在master系統資料庫上有登入帳戶。

設定為:「Windows使用者(Windows user)」。

表1:自主資料庫內,新提供的使用者類型

「自主資料庫」可以解決一些過去使用「非自主資料庫」相關聯的問題與複雜性,請參考下圖所示:

01_執行個體與資料庫之間的關聯

圖1:搬移資料庫到另外一個執行個體


在圖1中,若要將資料庫搬移到另外一個「執行個體」上,目前在「部分自主資料庫」上,仍有些許的外部資源仍是未內含的,例如:連結伺服器、SQL Server Agent「作業」等,需要資料庫管理師自行在另一個「執行個體」上建立這些物件。

「部分自主資料庫」不支援的功能


目前「部分自主資料庫」不支援以下的功能:
  • 複寫、異動資料擷取,或是變更追蹤。

實作練習一:建立「自主資料庫」

準備工作:


1.建立以下的資料夾:C:\myAdmin\DB。

需要先在執行個體層級上啟用「自主資料庫驗證(contained database authentication)」後,才能建立或是附加「自主資料庫」。

任務一:在伺服器層級上啟用自主資料庫驗證


步驟01. 執行SSMS管理工具,使用「物件總管」連線目標伺服器。
步驟02. 點選伺服器名稱,滑鼠右鍵,選擇「屬性」。


步驟03. 在「伺服器屬性」視窗,在左邊「選取頁面」窗格,點選「進階」頁籤,在右邊窗格,在「內含項目」區域,設定「啟用自主資料庫」方塊為:True(預設值是:False),點選「確定」。請參考下圖所示:

02_設定啟用自主資料庫 [1]

圖2:設定啟用自主資料庫驗證

在圖2中,設定「啟用自主資料庫」這項執行個體層級的屬性,是無需重新啟動執行個體,就可以直接使用的屬性。
或是,執行以下的範例程式碼來在執行個體層級上啟用自主資料庫驗證:

USE master
GO
-- 01 查詢是否已經啟用自主資料庫:contained database authentication
SELECT name N'組態選項的名稱', value N'針對這個選項所設定的值', value_in_use N'這個選項目前有效的執行值',
    description N'組態選項的描述', is_dynamic N'1= 在執行 RECONFIGURE 陳述式時的有效變數', is_advanced N'1 = 只有在設定 show advanced 選項時,才會顯示變數'
FROM sys.configurations
WHERE name='contained database authentication'
GO
-- 02_設定啟用自主資料庫:contained database authentication
EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
-- 03 查詢是否已經啟用自主資料庫:contained database authentication
SELECT name N'組態選項的名稱', value N'針對這個選項所設定的值', value_in_use N'這個選項目前有效的執行值',
    description N'組態選項的描述', is_dynamic N'1= 在執行 RECONFIGURE 陳述式時的有效變數', is_advanced N'1 = 只有在設定 show advanced 選項時,才會顯示變數'
FROM sys.configurations
WHERE name='contained database authentication'
GO

範例程式碼1:在執行個體層級,啟用自主資料庫驗證

任務二:建立自主資料庫


步驟01. 執行SSMS管理工具,使用「物件總管」來建立新的資料庫,在「新增資料庫」視窗,輸入以下的參數值,請參考下圖所示:

  • 在「一般」頁面,資料庫名稱,輸入:ContainedDB01。
  • 在下方的路徑區域,設定資料檔案與交易記錄檔案的存放路徑是:C:\myAdmin\DB。
  • 點選「選項」頁面,在「內含項目類型」方塊,設定為:部分(預設值是:無)。

03_3_建立自主資料庫 
圖3:建立自主資料庫


在圖3中,設定「內含項目類型」的屬性為:部分,你也可以觀察到在中間的「內含項目」區域下,各項屬性都已經可以變更,包含有:「兩位數年份截止」、「巢狀觸發程序已啟用」、「預設全文檢索語言LCID」、「預設語言」以及「轉換非搜尋字」等五項屬性。
而上述的這些屬性,在先前的SQL Server版本上,原本皆只能在執行個體層級上才允許調整的屬性,如今在「自主資料庫」上,改為在資料庫層級上就可以組態設定。

或是,執行以下的範例程式碼來建立「自主資料庫」:

USE master
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'ContainedDB01')
BEGIN
    ALTER DATABASE [ContainedDB01]
        SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [ContainedDB01]
END
GO
-- 建立「自主資料庫」:ContainedDB01
CREATE DATABASE [ContainedDB01]
CONTAINMENT = PARTIAL
ON  PRIMARY
( NAME = N'ContainedDB01', FILENAME = N'C:\myAdmin\DB\ContainedDB01.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ContainedDB01_log', FILENAME = N'C:\myAdmin\DB\ContainedDB01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [ContainedDB01] SET DEFAULT_FULLTEXT_LANGUAGE = 1028
GO
ALTER DATABASE [ContainedDB01] SET DEFAULT_LANGUAGE = 1028
GO
ALTER DATABASE [ContainedDB01] SET NESTED_TRIGGERS = ON
GO
ALTER DATABASE [ContainedDB01] SET TRANSFORM_NOISE_WORDS = OFF
GO
ALTER DATABASE [ContainedDB01] SET TWO_DIGIT_YEAR_CUTOFF = 2049
GO

範例程式碼2:建立自主資料庫

在範例程式碼2中,在CREATE DATABASE陳述式內,使用了新的引數:CONTAINMENT,並設定其值為:PARTIAL,這是用指定資料庫的內含項目狀態。可以設定引數值有:NONE表示為:非自主資料庫;PARTIAL表示為:部分自主資料庫,預設值是:NONE。而在ALTER DATABASE陳述式部分,則是用來設定「兩位數年份截止」、「巢狀觸發程序已啟用」、「預設全文檢索語言LCID」、「預設語言」以及「轉換非搜尋字」等五項屬性。
若尚未在伺服器層級設定啟用「自主資料庫」,則在建立資料庫時,將遭遇到以下的錯誤訊息:

訊息 12824,層級 16,狀態 1,行 1
必須將 sp_configure 值「自主資料庫驗證」設為 1,才能建立自主資料庫。您必須使用 RECONFIGURE 設定 value_in_use。

 
image

圖4:錯誤號碼:12824,尚未設定「自主資料庫驗證」屬性

若要查詢與「自主資料庫」有關的資料庫屬性,請使用以下的範例程式碼,並參考下圖所示:

-- 查詢與「自主資料庫」有關的資料庫屬性
USE master
GO
SELECT name N'資料庫', containment_desc N'內含項目狀態', two_digit_year_cutoff N'自主資料庫的兩位數年份截止', is_nested_triggers_on N'自主資料庫的巢狀觸發程序已啟用',
    default_language_name N'自主資料庫的預設語言', default_fulltext_language_name N'自主資料庫的預設全文檢索語言LCID',
    is_transform_noise_words_on N'自主資料庫的轉換非搜尋字', collation_name N'資料庫的預設定序'
FROM sys.databases
ORDER BY 2 DESC
GO

範例程式碼3:查詢「自主資料庫」的資料庫屬性

05_3_查詢「自主資料庫」的資料庫屬性
圖5:查詢「自主資料庫」的資料庫屬性

 

任務三:在自主資料庫上建立有密碼的SQL使用者


步驟01. 使用「物件總管」,選擇與展開先前建立的資料庫:ContainedDB01,展開「安全性」\「使用者」節點,滑鼠右鍵,選擇「新增使用者」。
步驟02. 「資料庫使用者-新增」視窗,在「使用者類型」方塊,下拉選擇:「有密碼的SQL使用者」。輸入以下參數值,請參考下圖所示,以及範例程式碼:

  • 在「使用者名稱」方塊,輸入:ContainedUser01。
  • 在「密碼」與「確認密碼」方塊,輸入:P@ssw0rd。

 06_建立「有密碼的SQL使用者」 [1]
圖6:建立「有密碼的SQL使用者」

步驟03. 在「資料庫使用者-新增」視窗,在左邊的「選取頁面」窗格,點選「成員資格」頁籤,在右邊的窗格,在「資料庫角色成員資格」區域,勾選「db_owner」,點選「確定」來建立此資料庫使用者帳戶,請參考下圖所示:

07_加入到資料庫角色:db_owner [1]

圖7:加入到資料庫角色:db_owner

-- 01_建立「有密碼的SQL使用者」
USE [ContainedDB01]
GO
CREATE USER [ContainedUser01]
    WITH PASSWORD=N'P@ssw0rd'
GO
-- 02_將此使用者加入到資料庫角色:db_owner
USE [ContainedDB01]
GO
ALTER ROLE [db_owner]
    ADD MEMBER [ContainedUser01]
GO

範例程式碼4:建立「有密碼的SQL使用者」

任務四:使用具有密碼的自主資料庫之使用者來登入系統


步驟01. 使用「物件總管」,點選左上角的「連接」,選擇類型:Database Engine,在「連接到伺服器」視窗,輸入以下的參數:

  • 在「伺服器名稱」方塊,輸入連線的目標伺服器。
  • 在「驗證」方塊,下拉選取:SQL Server驗證。
  • 在「登入」方塊,輸入:ContainedUser01。
  • 在「密碼」方塊:P@ssw0rd。

08_輸入「連接到伺服器」的登入資料 [1] 
圖8:輸入「連接到伺服器」的登入資料

步驟02. 在「連接到伺服器」視窗,點選「選項」。
步驟03. 在「連接屬性」頁面,在「連接到資料庫」方塊,輸入:ContainedDB01,點選「連接」,請參考下圖所示:

09_3_在「連接屬性」頁面,輸入「自主資料庫」的名稱

圖9:在「連接屬性」頁面,輸入「自主資料庫」的名稱

步驟04. 在「物件總管」視窗,展開目標伺服器,展開資料庫節點:ContainedDB01。請參考下圖所示:

 10_登入到「自主資料庫」 [1]
圖10:使用「有密碼的SQL使用者」來登入到「自主資料庫」


在圖10中,可以觀察到使用「有密碼的SQL使用者」登入到「自主資料庫」,此帳戶是隸屬於「資料庫角色」:db_owner,但是卻不具備任何執行個體層級的權限。

步驟05. 在SSMS管理工具上,點選此「自主資料庫」,滑鼠右鍵,嘗試檢視或是變更資料庫層級的屬性,將可能遭遇到以下的錯誤訊息,請參考下圖所示:

11_無法使用SSMS管理工具檢視或變更「自主資料庫」的資料庫屬性 [1]

圖11:無法使用SSMS管理工具檢視或變更「自主資料庫」的資料庫屬性


在圖11中,一旦使用「有密碼的SQL使用者」方式來登入到「自主資料庫」內,就無法使用SSMS管理工具來變更或是檢視此資料庫的相關屬性。

在使用「有密碼的SQL使用者」的資料庫使用者,在登入到系統時,必須要在「連接屬性」頁面上,特別明確指定所要連接到資料庫,若仍是使用<預設值>的參數設定,將在登入系統時,遭遇到以下的錯誤訊息,請參考下圖所示:

使用者 'ContainedUser01' 的登入失敗。 (Microsoft SQL Server, 錯誤: 18456)

12_登入失敗 [1] 
圖12:試圖由執行個體層級登入到資料庫層級,仍是失敗

在圖12中,若使用「有密碼的SQL使用者」的資料庫使用者來登入系統,卻沒有特別指定資料庫名稱時,就無法登入到目標資料庫上。雖然,這是一般正常的登入系統方式,也就是說,是先登入到執行個體層級,在登入到資料庫層級。

但是在「自主資料庫」上,使用「有密碼的SQL使用者」,這是屬於資料庫層級的使用者帳戶,所以無法利用此使用者帳戶登入到執行個體層級。而且,回傳的錯誤號碼是:18456,這與輸入錯誤的登入帳戶或是密碼輸入錯誤時相同。

在Visual Studio 2010開發工具上,若是要連線「自主資料庫」,則是與連接一般SQL Server資料庫的作法大同小異,請參考下圖13、14、15所示:
 13_VS2010_設定連接 [1]
圖13:設定與資料庫連接用的參數


在圖13中,在「加入連接」視窗,在「連接至資料庫」區域,在「選取或輸入資料庫名稱」方塊內,請自行輸入資料庫名稱,在連接到「自主資料庫」時,原本可以列舉連接的資料庫名稱之清單功能,將無法使用。

image 
圖14:選擇資料連接與設定連線字串是否要包含敏感性資料

 image
圖15:檢視App.config檔案內的連線字串設定

結語


在本期文章中,介紹了在伺服器層級上啟用自主資料庫驗證、建立自主資料庫、在自主資料庫上建立有密碼的SQL使用者以及使用具有密碼的自主資料庫之使用者來登入系統等主題。

Tags:

SQL Server資料庫 | 陳俊宇Derrick Chen

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List