Microsoft SQL Server 2012 Management (2): Instance vs. database management

1. Encrypt the database

/*Module 2 Implementing Transparent Data encryption*/--2.1 Create DataBase Master Key UseMaster;GOCreateMasterKeyEncryption byPassword='[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 Key UseMaster;GOCreateCertificate Tde_cert withSubject='Tde_encryption_cert'--2.3 Create Database encryption key for a User database UseTinyDBGOCreate DatabaseEncryptionKey  withAlgorithm=aes_256encryption byServer Certificate Tde_cert--The IS and algorithm choices but aes_256 is the strongest--2.4 Protect User Database UseTinyDBGOAlter DatabaseTinyDBSetEncryption 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. Compressing data

/*Implementing Data Compression*/Create DatabaseDbwithredundantdataGO UseDbwithredundantdataGO--Create a Table assigning Row CompressionCreate Tablegreatforrowcompression (Col1int, Col2Char(5), Col3Char(3), Col4Char(2))  with(data_compression=ROW)--Create a Table assigning Page CompressionCreate Tablegreatforpagecompression (Col1int, Col2Char(5), Col3Char(3), Col4Char(2))  with(data_compression=PAGE)/*Keep in mind ALTER TABLE and alter INDEX can is used to implement compression when those obects already exist.*/

3. Availability of databases

/*Change various database option and refresh the MGMT*/--2.1 Setup:add A Table and a couple of rows. UseTinyDB;GOCreate TableDbo. T1 (Col1int Identity, COL2Varchar( -)default 'T1')InsertT1default ValuesGO 5--2.2 chnage avalablity OptionsAlter DatabaseTinyDBSetOFFLINE--The Database is absolutely inaccessible    --cleanly SHUs down the database without have to DETACH    --Refresh the Databasees node in Mgmt Studio to notice    --Try this query to see what happens ... Select * from T1Alter DatabaseTinyDBSetEMERGENCY--Limited access (only sysadmins). This might is useful for    --Transaction Log repairs with DBCC.        --Try this query to see what happens ... Select * from T1Alter DatabaseTinyDBSetONLINE--The Default OptionAlter DatabaseTinyDBSetread_only--cannot make changes to the database    --Try this query to see what happens ... Updata T1 set col2= ' dd ' where col1=1Alter DatabaseTinyDBSetRead_write--The Default OptionAlter DatabaseTinyDBSetSingle_user--Only one authoritative user can connect to the database    --userd when DBCC checkd repair_allow_data_loss is usedAlter DatabaseTinyDBSetRestricted_userAlter DatabaseTinyDBSetMulti_user--The Default Option

Microsoft SQL Server 2012 Management (2): Instance vs. database management