Microsoft SQL Server 2012 管理 (2): 实例与数据库管理
1.加密数据库
/* Module 2 Implementing Transparent Data Encryption */ -- 2.1 Create DataBase Master Key USE Master; GO Create Master Key Encryption By Password='SuperKey@currentMachine' -- The password above must adhere to the windows password policy -- could also use a hardware encryption module. -- 2.2 Create a Srever Certificate Derived from Database Master Key USE master; GO Create Certificate TDE_Cert with subject='TDE_Encryption_Cert' -- 2.3 Create Database Encryption key for a User Database USE TinyDB GO Create Database Encryption Key with Algorithm=AES_256 Encryption by Server Certificate TDE_Cert -- The are other algorithm choices but AES_256 is the STRONGEST -- 2.4 Protect User Database USE TinyDB GO Alter Database TinyDB Set ENCRYPTION ON -- 2.5 FollowUp /* Back up all keys in the hierarchy to a safe place In practice TEST moving/restoring the database to another instance. */
2. 压缩数据
/* Implementing Data Compression */ Create Database DBWithRedundantData GO USE DBWithRedundantData GO --Create a Table Assigning Row Compression Create Table GreatForRowCompression ( Col1 int ,Col2 char(5) ,Col3 char(3) ,Col4 char(2) ) WITH (DATA_Compression=ROW) --Create a Table Assigning Page Compression Create Table GreatForPageCompression ( Col1 int ,Col2 char(5) ,Col3 char(3) ,Col4 char(2) ) WITH (DATA_Compression=PAGE) /* Keep in mind ALTER TABLE and ALTER INDEX can be used to implement compression when those obects already exist. */
3. 数据库可用性
/* Change various database option and refresh the Mgmt */ -- 2.1 Setup: Add a Table and a couple of rows. USE TinyDB; GO Create Table dbo.T1 (Col1 int Identity, COl2 Varchar(20) default 'T1') Insert T1 default values GO 5 -- 2.2 Chnage Avalablity options Alter database TinyDB Set OFFLINE -- The Database is Absolutely inaccessible --Cleanly shus down the database without having to DETACH --Refresh the Databasees node in Mgmt Studio to notice the change --Try this query to see what happens... Select * from T1 Alter database TinyDB Set EMERGENCY -- limited access (Only SysAdmins). This might be useful for -- Transaction Log repairs with DBCC. --Try this query to see what happens... Select * from T1 Alter database TinyDB Set ONLINE -- The Default Option Alter database TinyDB Set READ_ONLY -- Cannot make changes to the database -- Try this query to see what happens... UPDATA T1 set Col2='dd' where Col1=1 Alter database TinyDB Set READ_WRITE -- the Default Option Alter database TinyDB Set SINGLE_USER -- Only one authoritative user can connect to the database -- Userd when DBCC CheckD repair_allow_data_loss is used Alter database TinyDB Set RESTRICTED_USER Alter database TinyDB Set MULTI_USER -- the Default Option