五餅二魚工作室

Resource Governor

• There are two built-in workload group
  • Internal : This is used by database engine exclusives and user have no control.
  • Default : This is used by all the workloads which are not assigned to any other group.

資料來源 : http://blog.sqlauthority.com

Error Log

• 預設狀況下，SQL Server 會保留六個 Error Log，當每次重新啟動的時候就會自動產生一個新的，會將原本的 Errorlog 給 rename 成為 Errorlog.1 , 原本的 1 變成 2 , 把最後一個給刪除。
• 此部分可以修改機碼下面的 HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer 的 NumErrorLogs 的屬性值，最大值是 99。
• 如果在特殊狀況下要重新產生一個新的 Errorlog，可以透過 master 資料庫內的系統預存程序 sp_cycle_errorlog 來產生。
• 除了 SQL Server Instance 有 Errorlog 外，SQL Agent 也會有自己的 log，但如果要重新產生一個新的 Log 時，則是呼叫 msdb 資料庫內 sp_cycle_agent_errorlog 來產生

Contained Databases

• A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server
• SQL Server 2012 supports Partially Contained Databases
  1. Enable Contained Database

     sp_configure 'show advanced options',1
     GO
     RECONFIGURE WITH OVERRIDE
     GO
     sp_configure 'contained database authentication', 1
     GO
     RECONFIGURE WITH OVERRIDE
     GO

  2. Create Contained Database

     CREATE DATABASE [ContainedDatabase]
     CONTAINMENT = PARTIAL
     GO

  3. Create User in Contained Database

     USE [ContainedDatabase]
     GO
     CREATE USER ContainedUser WITH PASSWORD = 'password';
     GO

  4. Connect Contained Database ( 要指定 Default Database 為 Contained Database )

Transparent Data Encryption ( MSDN : http://msdn.microsoft.com/zh-tw/library/bb934049.aspx )

1. 建立主要金鑰

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<MasterKeyPasswordHere>';

2. 建立或取得受到主要金鑰保護的憑證

   CREATE CERTIFICATE ServerCertificate WITH SUBJECT = 'Server Certificate';
   GO

3. 建立資料庫加密金鑰，並使用憑證保護它

   CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = AES_128
   ENCRYPTION BY SERVER CERTIFICATE ServerCertificate;
   GO

4. 設定資料庫使用加密

   ALTER DATABASE AdventureWorks2012 SET ENCRYPTION ON;
   GO

如果要將該加密的資料庫移到別的主機，則要先將憑證備份並到目的端主機重新建立該憑證

-- 憑證備份
BACKUP CERTIFICATE TestSQLServerCert TO FILE = 'TestSQLServerCert'
WITH PRIVATE KEY 
(
    FILE = 'SQLPrivateKeyFile',
    ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO
-- 憑證還原
CREATE CERTIFICATE TestSQLServerCert FROM FILE = 'TestSQLServerCert'
WITH PRIVATE KEY 
(
    FILE = 'SQLPrivateKeyFile',
    DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO

Partitioned Tables and Indexes( MSDN : http://msdn.microsoft.com/zh-tw/library/ms190787.aspx )

• Partition function  決定資料分割的界限值，可以在函數上設定間隔值是屬於的哪一側 (左或右)。若未指定，LEFT 便是預設值。

  -- 分割為四個區塊
  CREATE PARTITION FUNCTION myRangePF1 (int)
  AS RANGE LEFT FOR VALUES (1, 100, 1000);
  GO

• Partition scheme  決定使用的 Partition Function 和所對應存放的 FileGroup；但如果指定 ALL，則只能指定一個 FileGroup。在 2012 版本中最多可以指定 15,000 個 FileGroup，之前版本最多只能 1000 個。

  CREATE PARTITION SCHEME myRangePS1
  AS PARTITION myRangePF1
  TO (test1fg, test2fg, test3fg, test4fg);
  

• Partitioning column

  CREATE TABLE PartitionTable (col1 int PRIMARY KEY, col2 char(10))
      ON myRangePS1 (col1) 

在使用上比較如果需要知道特定值是屬於哪個區塊內，可以用 $PARTITION  來配合 Partition Function 來使用，他會回傳一個從 1 開始的整數值，使用方式如下

SELECT $PARTITION.RangePF1 (10) ;
GO

如果需要將 Partition Table 的內部份資料移到其他 Table，則可以透過 Alter Table Switch Partition 的方式來進行

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

Copy Database Wizard

• 可以使用兩種方式進行資料庫複製：
  • Detach and Attach method : 速度快，但來源資料庫要被離線進行檔案複製。
  • SQL Management Object metho : 速度慢但可以讓資料庫還持續保持 online 的狀況。
• 除了複製資料庫之外， Copy Database Wizard 另外還可以針對其他像是登入帳號或者是 SQL Aent 的 JOB 進行複製，但 Source 來源資料庫一定要在 SQL 2000 以上才有支援。

Server Roles ( MSDN : http://msdn.microsoft.com/zh-tw/library/ms188659(SQL.110).aspx )

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

Application Roles ( MSDN : http://msdn.microsoft.com/zh-tw/library/ms190998(SQL.110).aspx )

-- 建立
CREATE APPLICATION ROLE weekly_receipts 
    WITH PASSWORD = '987Gbv8$76sPYY5m23' , DEFAULT_SCHEMA = Sales;
GO
-- 變更
ALTER APPLICATION ROLE weekly_receipts 
    WITH NAME = receipts_ledger;
GO
-- 刪除
DROP APPLICATION ROLE receipts_ledger
GO

-- 指定使用 Application Role
DECLARE @cookie varbinary(8000);
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM' , @fCreateCookie = true, @cookie = @cookie OUTPUT;
GO

-- 恢復到原本使用者
EXEC sp_unsetapprole @cookie;
GO