SQL Server 2012： 有時只要一部分就好
SQL Server 2012 將支援部分自主資料庫，如此應可減緩一些移轉與彙總問題。
Denny Cherry
部分自主資料庫可解決自 SQL Server 4.2 版以來與 SQL Server 資料庫相關的一些主要的彙總與移轉問題。 所幸，這將是 SQL Server 2012 推出的眾多新功能之一。
部分自主資料庫所解決的第一個問題是不需要識別資料庫所用的 SQL Server 登入，即可將資料庫從 SQL Server 其中一個執行個體移至另一個執行個體。 當中也涵蓋了在目的地資料庫執行個體上建立該些登入。
SQL Server 2012 是使用內含 SQL Server 登入來完成此動作。 這是在自主資料庫內建立但沒有對應 SQL Server 登入的 SQL Server 使用者。 自主資料庫內含的這名使用者在自主資料庫內存有密碼，因此驗證可如預期運作。
第二個獲得解決的問題，有效排解了自主資料庫內資料表與暫存資料表之間的定序衝突。 SQL Server 在自主資料庫定序時，會自動在其範圍內建立暫存資料表。 如此一來，可讓相同的 SQL Server 執行個體裝載不同定序的資料庫，而不需要修改 CREATE TABLE 陳述式來指定定序或是在 JOIN 陳述式的語法內指定 COLLATE 陳述式。
與一些需要將資料庫相容性層級設定為最新值的新功能不同，部分自主資料庫自目前版本一直到 SQL Server 2005 都有支援。不過就另一方面來說，自主資料庫功能尚不完整。 它在 SQL Server 2012 CTP3 版本與產品在 2012 年初至年中進入發行製造階段之間應可提供完整功能。
設定部分自主資料庫
您必須先使用 sp_configure 系統預存程序變更伺服器設定，才能將資料庫從非內含變更為部分自主。 請使用 sp_configure 系統預存程序將 [自主資料庫驗證] 設定從 0 變更為 1，然後使用 RECONFIGURE 陳述式來啟動新設定，如下所示：
EXEC sp_configure 'contained database authentication', 1 RECONFIGURE GO
啟用 [自主資料庫驗證] 設定之後，您就可以將特定資料庫變更為部分自主。 您可以使用如 [圖 1] 中所示的 SQL Server Management Studio、ALTER DATABASE 陳述式，或將新資料庫建立為自主資料庫：
ALTER DATABASE: USE [master] GO ALTER DATABASE [Cont] SET CONTAINMENT=PARTIAL GO CREATE DATABASE: CREATE DATABASE [Cont1] CONTAINMENT=PARTIAL GO
若要建立新的部分自主資料庫或更改資料庫使其成為部分自主，請連接到物件總管中的 SQL Server 執行個體。 按一下滑鼠右鍵以建立新資料庫，或選擇現有資料庫。 選擇屬性 (看您是建立新資料庫或是變更現有資料庫而定)。 無論是哪一種情況，都請選擇 [選項] 索引標籤，然後將 [內含項目類型] 下拉清單從 [無] 變更為 [部分]。
.jpg)
[圖 1] [資料庫內容] 視窗可讓您變更資料庫的內含項目設定。
若要將現有的資料庫從非內含變更為部分自主，請使用 T/SQL 和 ALTER DATABASE 陳述式。 為了順利變更資料庫內含項目設定，ALTER DATABASE 陳述式必須能夠對整個資料庫採取獨佔鎖定。 因此，您將需要對使用此一特定資料庫的業務單位排定簡短的服務中斷。
避免暫存資料表定序錯誤
新的部分自主資料庫允許相同的 SQL Server 執行個體上存在多個不同的定序。 聯結暫存資料表時不用擔心定序問題。 您可以拿一個 Microsoft SQL Server 執行個體然後建立不同定序的部分自主資料庫來測試。 然後在部分自主資料庫內建立一個實體資料表和一個暫存資料表。 將資料載入這兩個資料表，然後嘗試聯結它們。
使用 Albanian_100_CI_AI_KS_WS 資料庫定序建立資料庫，而執行個體使用的是 SQL_Latin1_General_CP1_CI_AS 定序，如 [圖 2] 中所示。 這會在新資料庫內建立一個名為 dbo.Employee 的資料表，並在資料表中載入三個資料列。 它也會建立一個名為 #emp 的暫存資料表，並在資料表中插入單一資料列。 當資料表在查詢結尾聯結時，便會傳回一個資料列。 若是沒有將自主資料庫設定值設定為部分，它則會傳回錯誤。
[圖 2] 建立和使用部分自主資料庫。
use master GO CREATE DATABASE [Cont] CONTAINMENT = PARTIAL ONPRIMARY (NAME = N'Cont', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont.mdf', SIZE = 4096KB, FILEGROWTH= 1024KB) LOGON (NAME = N'Cont_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont_log.ldf', SIZE = 1024KB, FILEGROWTH = 10%) COLLATE Albanian_100_CI_AI_KS_WS GO Use Cont GO Create table Employee (EmployeeId INTPRIMARYKEY, LastName nvarchar(100), FirstName nvarchar(100)) GO Insert into Employee (EmployeeId,LastName,FirstName) values (1,'last1','first1'), (2,'last2','first2'),(3,'last3','first3') GO Create table #emp (LastName nvarchar(100)) GO Insert into #emp (LastName) values ('last1') GO select* from Employee join #emp on Employee.LastName = #emp.LastName
內含使用者
內含使用者與傳統的 SQL Server 登入類似，差別在於他們的登入與自主資料庫使用者並不相關。 請透過 SQL Server Management Studio 或 CREATE USER T/SQL 陳述式建立內含使用者。
若要使用 SQL Server Management Studio，請連接到物件總管中裝載自主資料庫的執行個體。 巡覽至 [資料庫] | {您的自主資料庫} | [安全性] | [使用者]。 以滑鼠右鍵按一下 [使用者] 資料夾，然後從內容功能表選取 [新增使用者]。 在 [使用者類型] 下拉清單中，選取 [有密碼的 SQL 使用者]。填入使用者名稱和密碼欄位 (請參閱 [圖 3])，以及預設的結構描述，還有您需要設定的任何資料庫角色成員資格。
.jpg)
[圖 3] 透過 SQL Server Management Studio 建立內含 SQL 使用者。
建立內含使用者之後，您可以檢查自主資料庫內的 sys.database_principals 目錄檢視。 查詢目錄檢視所傳回的資料錄集會顯示名為 authentication_type 的新資料行已設定為 2 的值。當使用者為內含使用者時，它還會顯示 authentication_type_desc 資料行已設定為 [DATABASE] 的值。
請使用 T/SQL 搭配 CREATE USER 陳述式建立自主資料庫使用者。 指定 WITH PASSWORD 陳述式，SQL Server 就知道您要建立的是內含使用者，而不是與執行個體層級的登入相關的使用者，如下所示：
CREATE USER MyContainedUser WITHPASSWORD = 'MyContainedUserPassword' GO
您可以建立內含 SQL 使用者，以及內含 Windows 登入。 請將 [使用者類型] 下拉清單 (請參閱 [圖 4]) 變更為 [Windows 使用者]，並輸入使用者名稱，而不要選擇登入。
.jpg)
[圖 4] 使用 SQL Server Management Studio 建立內含 Windows 登入。
您也可以透過 CREATE USER T/SQL 陳述式來建立內含 Windows 使用者，如下所示：
CREATE USER [CAPT-MAL\test] WITH DEFAULT_SCHEMA = [dbo] GO
像使用傳統 (非內含) 使用者一樣，使用內含登入來登入自主資料庫。 使用內含使用者有個重要的訣竅，就是在連接時，您必須在連接字串中指定資料庫名稱。 如果沒有指定，SQL Server 會假定嘗試進行連線的是傳統的 SQL Server 登入。 而要是登入不符合執行個體層級定義的使用者名稱和密碼，登入就會失敗。
按一下登入視窗底部的 [選項] 按鈕，在 SQL Server Management Studio 連接對話方塊中進行此動作。 選取 [連接屬性] 索引標籤。 在 [連接屬性] 索引標籤上，可在 [連接到資料庫] 下拉清單輸入自主資料庫的資料庫名稱 (請參閱 [圖 5])。
您無法以 [登入] 索引標籤上指定的內含使用者查詢 SQL Server 執行個體上裝載的資料庫清單。 該名內含使用者必須先向包含其使用者名稱與密碼的自主資料庫進行驗證後，才具備存取主資料庫的權利。 有基於此，您必須知道資料庫名稱並手動將它輸入到方塊中。
.jpg)
[圖 5] SQL Server Management Studio 連接對話方塊視窗的 [連接屬性] 索引標籤。
您可以從這兩項功能中看出，將資料庫執行個體彙總到 SQL Server 2012 的執行個體要容易得多了。 這種做法需要花點時間和測試，以確保您的資料庫應用程式與自主資料庫功能完全相容。 但是一旦您啟用自主資料庫設定後，後續的資料庫移轉和彙總作業就會順暢許多，而這正是關鍵所在。
.jpg)
Denny Cherry是一名 MVP 兼獨立顧問，在 Microsoft SQL Server、Hyper-V、vSphere 及企業儲存解決方案方面擁有十多年的經驗。 他目前持有數項 SQL Server 2000 至 2008 版本相關的 Microsoft 認證，包括微軟高級技術專家 (Microsoft Certified Master)。他在 SQL Server 管理方面著有數本相關書籍及數十篇相關技術文章。