SQL 2008 T-Prep 上課心得(二)

Extensible Key Management：採用企業已有的金鑰架構，如協力廠商所提供的 Hardware Security Module(HSD)

Transparent Data Encryption：

• 整個資料庫通透地加密，應用程式不需要改寫，也感受不到
• 效率影響不大，但會與資料型態有關
• 可以用二進位編輯器搜尋資料庫檔案，或資料庫備份，若不加密可以直接找關鍵字，加密後找不到

增強整合式驗證：

• 在 SQL 2005 時，僅有 TCP/IP 連接可以使用 Kerberos，但 2008 的多種存取協定皆可
• 在 SQL 2005 時要將 Service Principal Name(SPN)註冊到 AD，但 2008 不用註冊 SPN 到 AD 就可使用 Kerberos

所有的行為都可以監控(Audit)：

• 以 DDL 語法設定監控
• 存取監控所儲存的資料可受安全控管
• 可設定控管資料輸出到檔案、Windows Application 或 Security Log
• 建立 Audit Specification 以規範伺服器(如 登入/登出)或資料庫(如 ISUD 加上 Execute、Receive) 要監控的行為
• 分 Server 和 Database 層級，但沒有 Column 層級
• 採用 Extended Events 架構建立，希望效率比 SQL Trace 快

空間索引(Spatial Index)

• 對於空間地理資訊建置特殊的索引結構

整合的全文檢索

• 檢索索引儲存在資料庫內
• 在 SQL 2005 的全文檢索只能完整備份，不能搭配資料做 recovery to point in time，只能資料庫做完 recovery to point in time 後，再重新 populate。SQL Server 2008 則因為完整做在 DB 內，也就是更動都會經過 transaction log，則全文檢索可以與備份/還原、log shipping、database mirroring 等經由 transaction log 實作的機制同步。
• 較佳的查詢效能，但不知製作檢索的效能如何？

Sparse Columns

• 欄位數量非常多，但大部分的內容是空的。例如 Windows Sharepoint Services 在建立文管時，有一大堆的檔案格式(如 word、excel、pdf、image…)，每種檔案格式的屬性皆不同，因此有一大堆欄位，對應各種不同的文件屬性。則新增任一份檔案時，大部分的欄位都是 NULL，僅有少數幾攔描述該類型檔案的屬性有設值。這可類比物流業的產品屬性。以往因為 Table 的 column 數有 1024 限制，且太多欄位太占 page 空間，解法可能是利用 XML、利用屬性/值放在 row、限制每種 entity 的 property 數量。
• DDL 的 CREATE TABLE 之 Column 定義多了 Sparse 屬性
• Sparse 欄位的內容若是空，則完全不占空間，在 2005 若欄位空最起碼佔 1 bit，以標示欄位。有 Sparse 欄位的資料表則正好相反，有值的 sparse 欄位需要額外 4 byte 的 metadata。所以真要大量 sparse 才會受惠，否則反而因為每欄多 4 byte 而損失效能
• 預計 SQL Server 2008 將支援每個 table 達 30000 個 column 
• 搭配 Filtered Index 後可以更有效率
• 提供 Column Set 特殊欄位，以 XML 呈現有值的欄位

create table t(c1 int primary key,c2 int,c3 int sparse,c4 nvarchar(10) sparse,
cs xml column_set FOR ALL_SPARSE_COLUMNS)

insert t(c1,c2,c3,c4) values(1,1,2,’a’)
insert t(c1,c2) values(2,1)
insert t(c1,c2,c3) values(3,1,3)
insert t(c1,c2,c4) values(4,1,’b’)

select *,cs.value(‘(/c3)[1]’,’int’)
from t
 where cs.exist(‘/c3’)=1

設定過濾條件的索引(Filtered Index)

• 僅增對符合 Where 條件的值建立索引，例如建立索引時，Where 某個欄位內容不可 NULL，則 Sparse Column 建出來的索引就小很多。
• 因為是對資料子集建索引，整個資料量少，統計的精確度更高
• sys.indexes 系統檢視以 has_filter 和 filter_definition 欄位呈現
• Kimberly Tripp’s 寫了一個　sp_helpindex2 可以更為精確地呈現索引的 include column 和 filter 條件

Change Tracking

• 同步追蹤資料變化
• 資料庫的 Recovery Mode 不限
• 需要資料表加上代表 row version 的欄位
• 當 DML 發生時，會自動記錄到系統

Change Data Capture

• 非同步追蹤資料變化
• 資料庫的 Recovery Mode 需 Full
• 與 Transactional Replication 機制類似，都是透過 Log Reader 讀出交易紀錄，CDC 會將結果放入到系統資料表。若透過 Transactional Replication 將資料表帶到目的端，若資料是 1 -> 2 -> 3，則目的端發生兩次變化，但若用 CDC，可以選擇僅要將 3 帶到目的端

FileStream：

• 將大型資料透過 SQL Server 存回檔案目錄，只能搭配 NTFS
• 讓資料的交易管理、安全、備份…等機制可以一體設計
• BLOB 不受限於 2G，而是 Windows 系統或硬碟實體大小的限制
• 可以直接透過 Win32 編輯 SQL Server 放在檔案系統上的檔案，但最好不要

 資料壓縮

• 以 CPU 換取硬碟與記憶體空間，由於載入較快，整體時間可能提升
• 分為 Row 和 Page 兩種壓縮
• 壓縮率取決於資料型態和資料內容的重複性，較難一概而論

資料表 Partition 增強

• 可以新的 Date-Only  資料型態當作切割欄位
• 增強對多執行緒的支援
• Partition-Level Lock Escalation，但這有可能導致死結

 

尚有 T-SQL 的 Merge 語法、Plan Freezing、Star Join 最佳化…等

廣告