Microsoft SQL Server 2012 管理 (2): 執行個體與資料庫管理

標籤：style   blog   color   os   io   strong   ar   for   資料   

1.加密資料庫

/*Module 2 Implementing Transparent Data Encryption*/-- 2.1 Create DataBase Master KeyUSE Master;GOCreate Master Key Encryption By Password=‘[email protected]‘-- 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 KeyUSE master;GOCreate Certificate TDE_Cert with subject=‘TDE_Encryption_Cert‘-- 2.3 Create Database Encryption key for a User DatabaseUSE TinyDBGOCreate Database Encryption Key with Algorithm=AES_256Encryption by Server Certificate TDE_Cert-- The are other algorithm choices but AES_256 is the STRONGEST-- 2.4 Protect User DatabaseUSE TinyDBGOAlter Database TinyDBSet ENCRYPTION ON-- 2.5 FollowUp/*Back up all keys in the hierarchy to a safe placeIn practice TEST moving/restoring the database to another instance.*/

 2. 壓縮資料

/*Implementing Data Compression*/Create Database DBWithRedundantDataGOUSE DBWithRedundantDataGO--Create a Table Assigning Row CompressionCreate Table GreatForRowCompression(Col1 int,Col2 char(5),Col3 char(3),Col4 char(2)) WITH (DATA_Compression=ROW)--Create a Table Assigning Page CompressionCreate 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;GOCreate Table dbo.T1 (Col1 int Identity, COl2 Varchar(20) default ‘T1‘)Insert T1 default valuesGO 5-- 2.2 Chnage Avalablity optionsAlter 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 T1Alter 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 T1Alter database TinyDB    Set ONLINE -- The Default OptionAlter 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=1Alter database TinyDB    Set READ_WRITE -- the Default OptionAlter database TinyDB    Set SINGLE_USER    -- Only one authoritative user can connect to the database    -- Userd when DBCC CheckD repair_allow_data_loss is usedAlter database TinyDB    Set RESTRICTED_USERAlter database TinyDB    Set MULTI_USER -- the Default Option

 

Microsoft SQL Server 2012 管理 (2): 執行個體與資料庫管理