五餅二魚工作室

Install and Configure (19%)

Maintain Instances and Databases (17%)

Optimize and Troubleshoot (14%)

Manage Data (19%)

Implement Security (18%)

Implement High Availability (12%)

組態管理員 - Configuration Manager ( MSDN : http://msdn.microsoft.com/zh-tw/library/ms174212(v=sql.110).aspx )

• 用來管理 SQL Server 的相關服務、設定 SQL Server 所用的網路通訊協定，以及管理 SQL Server 用戶端電腦的網路連接組態。
• 雖然透過 Windows 的服務管理也可以更改 SQL Server 相關服務的帳戶名稱和密碼，但 SQL Server 組態管理員也會執行其他組態，例如：設定 Windows 登錄中的權限，使新的帳戶能夠讀取 SQL Server 設定。
• 目前 SQL Server 支援 Shared Memory ( 共用記憶體)、TCP/IP 與 Named Pipes (具名管道) 通訊協定。
• 除了 SQL Server 相關服務之啟動設定、服務帳號的變更、調整設定通訊協定外，另外在 SQL Server 2012 的 Configuration Manager 也有加入 AlwaysON 和 FILESTREAM 的啟動設定。

Server Core

• Windows 2008 之後提供 Server Core 模式，雖然 SQL Server 2012 可以安裝在 Server Core 模式下，但會有部分功能不支援
• 支援的有以下幾種：
  • Database Engine Services
  • SQL Server Replication
  • Full-Text Search Analysis Services
  • Analysis Services
  • Client Tools Connectivity
  • Integration Services Server
• 不支援的有：
  • Reporting Services
  • SQL Server Data Tools (SSDT)
  • Client Tools Backward Compatibility
  • Client Tools SDK
  • SQL Server Books Online
  • Distributed Replay Controller
  • SQL Client Connectivity SDK
  • Master Data Services
  • Data Quality Services

AFFINITY ( MSDN : http://msdn.microsoft.com/zh-tw/library/ms189629(v=SQL.110).aspx )

• NUMA 架構 ( MSDN : http://technet.microsoft.com/zh-tw/library/ms345357.aspx )
• 可以透過 SSMS 進行設定要使用那些 CPU，Affinity mask  (也稱為 CPU affinity mask)  和 I/O affinity mask 這兩者不能一起設定在同個 CPU 上面。
• 除了 SSMS 之外，也可以透過指令的方式來進行設定，非特殊狀況下一般都是設定由 SQL Server 自行決定

-- 設定為 AUTO
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=AUTO;

-- 指定只有兩個 Core 進行運算
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=2,3 

Database Mail ( MSDN : http://msdn.microsoft.com/zh-tw/library/ms189635.aspx )

• 可以在Database Mail 設定檔中指定多個 SMTP 伺服器。如果第一個 SMTP 伺服器無法使用時，會依序往下測試其他的 SMTP 伺服器。
• 若要傳送 Database Mail，該使用者必須是 msdb 資料庫中 DatabaseMailUserRole 資料庫角色的成員。
• 一般在透過 SSMS 建立 Database Mail 的時候就會自己啟動，除此之外也可以透過 sp_configure 這個預存程序指定 Database Mail XPs 選項進行啟動

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

Resource Governor - 資源管理員 ( MSDN : http://msdn.microsoft.com/zh-tw/library/bb933866.aspx )

• 資源管理員只能控管單一 SQL Server Database Engine 的執行個體其 CPU 使用率和記憶體使用量，並無法管控 Analysis Services、Integration Services 和 Reporting Services 的資源使用。
• 如果要考慮多台 SQL Server 之間資源使用量的分配，可以另外搭配 Windows System Resource Manager (WSRM) 來做分配。

-- 啟用資源管理員
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
-- 關閉資源管理員
ALTER RESOURCE GOVERNOR DISABLE;
GO
-- 建立資源集區
CREATE RESOURCE POOL pProductionProcessing
WITH
( MIN_CPU_PERCENT = 50, MAX_CPU_PERCENT = 100 )
GO
-- 建立工作負擔群組
CREATE WORKLOAD GROUP gProductionProcessing USING pProductionProcessing
WITH
( IMPORTANCE = MEDIUM )
GO
-- 指定分類函數
ALTER RESOURCE GOVERNOR with ( CLASSIFIER_FUNCTION = dbo.fnTimeClassifier )
ALTER RESOURCE GOVERNOR RECONFIGURE

全文檢索 ( MSDN : http://msdn.microsoft.com/zh-tw/library/cc879306(v=SQL.110).aspx )

• 每個資料表或索引檢視表只允許有一個全文檢索索引，若要對資料表建立全文檢索索引，該資料表必須有單一的非 Null 唯一資料行。
• 每個全文檢索目錄 ( FULLTEXT CATALOG) 可以有多個全文檢索索引 (FULLTEXT INDEX)，但一個全文檢索索引只能屬於單一全文檢索目錄。
• 按照預設值，或者是建立 FULLTEXT 的時候加入參數 CHANGE_TRACKING AUTO，則資料表或者是檢視更動資料的時候，全文檢索會使用非同步的方式自行更新索引；如果擔心太頻繁更新群文檢索索引的情況下，可以指定參數參數 CHANGE_TRACKING MANUAL，利用定時執行 ALTER FULLTEXT INDEX … START UPDATE POPULATION 來更新全文檢索索引。

CREATE FULLTEXT CATALOG production_catalog;
GO
-- 建立在 Production.ProductReview 表格上的 FullText Index，配合索引 PK_ProductReview_ProductReviewID 
CREATE FULLTEXT INDEX ON Production.ProductReview
 ( 
  ReviewerName,  EmailAddress,  Comments 
 ) 
  KEY INDEX PK_ProductReview_ProductReviewID ON production_catalog; 
GO
-- 刪除在 Production.ProductReview 表格上的 FullText Index
DROP FULLTEXT INDEX ON Production.ProductReview

FILESTREAM ( MSDN : http://msdn.microsoft.com/zh-tw/library/gg471497.aspx )

• FILESTREAM 可讓 SQL Server 架構應用程式在檔案系統上儲存非結構化的資料，讓 Database Engine 與 NTFS 檔案系統整合，將 varbinary(max) 資料當做檔案儲存在檔案系統上。一方面可以使用 Transact-SQL 陳述式來SELECT、INSERT、UPDATE、DELETE和備份 FILESTREAM 資料；已可以利用 Win32 檔案系統 API 來做資料存取。
• 系統預設不會自動啟用 FILESTREAM，必須使用 SQL Server 組態管理員做設定並同時到 SQL Server Management Studio 下利用 sp_configure 來啟用 FILESTREAM。

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

• 如果檔案小於 1MB，則建議使用 varbinary(max)；但如果檔案大小會超過 varbinary(max) 的上限 2GB ，且快速讀取是非常重要的情況下，則 FILESTREAM 則會是較佳的選擇。
• 若要指定資料行應該將資料儲存在檔案系統上，要在 varbinary(max) 資料行上指定 FILESTREAM 屬性。才會讓 Database Engine 將該資料行的所有資料都儲存在檔案系統上，而不是儲存在資料庫檔案中。

-- 加入一個具有為 FILESTREAM 的 FILEGROUP 到資料庫中
ALTER DATABASE Litware2012 ADD
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM;
GO
-- 指定 FILESTREAM 存放目錄
ALTER DATABASE Litware2012 ADD FILE (
NAME = FileStrmFile,
FILENAME = 'C:\FSTRM' )
TO FILEGROUP FileStreamFileGroup
GO

FILETABLE ( MSDN : http://msdn.microsoft.com/zh-tw/library/ff929144.aspx )

• 可將 Windows 檔案命名空間的支援以及與 Windows 應用程式的相容性提供給儲存在 SQL Server 中的檔案資料。FileTable 可讓應用程式整合其儲存和資料管理元件，並且透過非結構化資料和中繼資料提供整合式 SQL Server 服務。FileTable 功能是根據 SQL Server FILESTREAM 技術所建立。
• 除了透過 FileTable 以外，沒有 FILESTREAM 資料的非交易式存取 ( There is no non-transactional access to FILESTREAM data except through FileTables ).

-- 簡單的方式
CREATE TABLE DocStore as FileTable
GO

-- 指定存放目錄和定序
CREATE TABLE DocumentStore AS FileTable
    WITH ( 
          FileTable_Directory = 'DocumentTable',
          FileTable_Collate_Filename = database_default
         );
GO

資料庫檔案與檔案群組 ( MSDN : http://msdn.microsoft.com/zh-tw/library/ms189563.aspx )

• 一個資料庫至少由兩個實體檔案組成，一個是主要資料檔 ( MDF ) 和一個交易記錄檔 ( LDF )
• 一個資料庫在邏輯上一定會有一個檔案群組 ( PRIMARY FILEGROUP )，每個檔案群組內至少會有一個資料檔，包含有主要資料檔的就是主要群組，如果沒有特別更改的狀況下，他也是預設檔案群組。
• 當建立資料庫物件的時候，如果沒有指定存放的檔案群組時，則會放在預設檔案群組上。
• 如果要移動 INDEX 存放到不同的 FILEGROUP 的時候，必須利用 CREATE INDEX 的指定配合 DROP_EXISTING = ON 的選項和指定新的 FILEGROUP

Contained Database ( MSDN : http://msdn.microsoft.com/zh-tw/library/ff929071(SQL.110).aspx )

相關範例可以參考 http://www.sqldbadiaries.com/2011/02/01/contained-databases-in-sql-server-denali/

-- 啟用自主資料庫
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE ;
GO
-- 將資料庫轉換成部分自主資料庫
ALTER DATABASE [Accounting] SET CONTAINMENT = PARTIAL
GO

Login

• 建立 ( MSDN : http://msdn.microsoft.com/zh-tw/library/aa337562.aspx )
• SQL Server 支援四種的登入型式：Windows-authenticated login、SQL Server–authenticated login、Certificate、Asymmetric key

-- 建立 SQL 登入帳號
CREATE LOGIN [WindowsDomain\Account] FROM WINDOWS;
GO
-- 建立 SQL 登入帳號
CREATE LOGIN Test0 WITH PASSWORD = '<enterStrongPasswordHere>' MUST_CHANGE;
GO
-- 建立憑證
CREATE CERTIFICATE TestCert
WITH SUBJECT = 'TestCert certificate in master database', EXPIRY_DATE = '01/01/2018';
GO
CREATE LOGIN Test1 FROM CERTIFICATE TestCert;
GO
-- 建立金鑰
CREATE ASYMMETRIC KEY Test2 WITH ALGORITHM = RSA_2048;
GO
CREATE LOGIN Test2 FROM ASYMMETRIC KEY Test2;
GO

-- 修改登入
ALTER LOGIN testlogin DISABLE;
GO
-- 刪除登入
DROP LOGIN testlogin;
GO
-- 限制帳號連線和該帳號所賦予其他的帳號也一併限制
DENY CONNECT SQL TO testlogin CASCADE;
GO