What steps must you go through to enable transparent data encryption?

1. Create the master encryption key
2. Create the certificate protected by the master key
3. Create a Database Encryption Key (DEK) and protect it using the cert
4. Encrypt the database

What two things you need to set up in order to enable table partitioning?

Which object do you need to create first before you can set up table partitioning?

1. Partition Function
• Defines how the table using the partition will be split
• Syntax: CREATE PARTITION FUNCTION <name> (<datatype>) AS RANGE LEFT/RIGHT FOR VALUES (<value>)
  
  
2. Partition Scheme
• Defines what filegroups the partitions will fall into
• Requires a Partition Function to operate 
• Syntax: CREATE PARTITION SCHEME <name> AS PARTITION <partitionFunctionName>

What does the DBCC CHECKDB command do?

This command checks the database for corruption and can fix them (to a limited extent - constraints are ignored during a REPAIR operation).  Generates a lot of I/O.

NOTE: Some system databases cannot be fixed with DBCC CHECKDB

In a SIMPLE recovery model database, what happens when the CHECKPOINT command is run? 

What about in a DB at the FULL recovery model?

SIMPLE: The transaction log is truncated.

FULL: The transaction log is truncated as far back as when the last log backup was taken (after a CHECKPOINT).

Can you install SQL Server on Windows Server 2003?

Nope!

In what circumstances can you NOT detach a database (when you are upgrading SQL Server, for instance)?

• The DB is replicated and published
• The DB is mirrored
• The database has snapshots in place
• The DB is a system DB

What is the difference between the CREATE ROLE and CREATE SERVER ROLE commands?

The CREATE ROLE statment refers to a database-level role, while the CREATE SERVER ROLE refers to a server role.

1. A SQL Server Audit

2. An Audit Specification

Say you run the following statement:

GO

sp_configure "c2 audit mode", 1;

GO

What is the next statement you must run to make sure this takes effect?

True or False?  You can create server-level audits and database-level audits.

What commands must you run to create an audit?

True.

You must run the CREATE SERVER AUDIT SPECIFICATION and CREATE DATABASE AUDIT SPECIFICATION before an audit will run.

• Asynchronous-commit mode: the primary will not wait for secondaries to harden the log (write log records to disk) and will run with minimum transaction latency.
  
  
• Synchronous-commit mode: Each transaction is applied to the secondary replica before being written to the local log file. The primary verifies that the transaction has been applied to the secondary before entering a SYNCHRONIZED state.

For a planned manual failover, what state must the replica DB's be in?

What type of failover mode must these databases be in?

In order to automatically set up AlwaysOn, what type of accounts do the SQL Server accounts need to be?

If this isn't available, what must you create to support it?

What version of SQL Server is capable of online index rebuilds?

What two datatypes prevent an online index rebuild from happening?

SQL Server Enterprise.

XML and Spacial datatypes cannot be online rebuilt.

You need to restore two damaged pages to a database and then bring the database current again using a transaction log backup. 

What are the steps you must follow to do this?

1. RESTORE DATABASE <db_name> PAGE <pages> FROM <full_backup> WITH NORECOVERY
    
2. RESTORE LOG <log_backup>
   
   
3. BACKUP LOG <db_name> TO <new_log_backup>
   
   
4. RESTORE LOG <db_name> FROM <new_log_backup> WITH RECOVERY

ALTER DATABASE <db_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

This forces the database to fail over to the mirror.

1. Sysadmin

2. SQLAgentUserRole

3. SQLAgentOperatorRole

What is the difference between RAID 1 and RAID 0?

If you care more about performance than redundancy, what would you pick?

RAID 1 is disk mirroring.   RAID 0 is striping.

RAID 0 is more applicable for pure performance. 

1. Enable FILESTREAM and FILESTREAM I/O in SQL Config Manager
    
2. Run the EXEC sp_configure filestream_access_level command, then RECONFIGURE
   
   
3. Restart the DBMS Instance
    
4. Create a FILESTREAM filegroup
   
   
5. Add FILESTREAM files to the filegroup 

• Standard
• Enterprise
• Business Intelligence

Say you need to divy up a CPU by 20% for request type A, 30% for request type B, and the rest for other queries using resource governor.

How many resource pools do you need for this?  How many workload groups?

Further, what function must you create to place requests in the right workload group?

You need two of each.

The resource pool is the physcal resource (%CPU), and you need the workload groups to contain categories for requests A & B.

Finally, you need a classifier function that will rout requests to the appropriate workload group / resource pool.

If you need to restore a database that was encrypted with TDE (Transparent Data Encryption), what two items do you need to recover the DB?

Further, where is the database encryption key (DEK) stored?

1) Private Key

2) Server Certificate

The DEK is stored in the server cert, so you do not need it to restore the database.

1) Restore the database in NORECOVERY mode

2) Back up existing transaction log into a new file

3) Restore archive transaction log backup

4) Restore new transaction log backup

5) RESTORE LOG WITH RECOVERY

What command do you need to run to change the ownership of an ENDPOINT?

What about changing the security type used for the ENDPOINT?

1) ALTER AUTHORIZATION

2) ALTER ENDPOINT