Course Topics

1. Course Topics

2. 02 | Maintain Instances and Databases George Squillace | Senior Technical Trainer – New Horizons Great Lakes Richard Currey | Senior Technical Trainer – New Horizons United

3. Module 2 Overview • Transparent Data Encryption • Database Compression • Server and Database Options • Affinity and Parallelism • Automation: SQL Agent Jobs and Alerts • Database Mail

4. Topic: Transparent Data Encryption

5. Topic:Transparent Data Encryption • What Problems Does Transparent Data Encryption (TDE) Solve and How Does It Solve Them? • Basics on SQL Server Encryption and Key Hierarchy • How Is TDE Applied? • Key Protection Issues and Potential TDE “Gotchas”

6. What Problems Does TDE Solve and How Does It Solve Them? • Encryption in general • Protecting data in motion (e.g., SSL, TLS, IPSec) • Protecting data at rest (e.g., EFS, BitLocker, TDE) • Provides “real-time I/O encryption and decryption of the data and log files” in the event the media is stolen • Protects data “at rest” in the event that a data file, log file, or backup file is stolen • Leverages the SQL Server key hierarchy

7. Basics on SQL Server Encryption and Key Hierarchy • Service Master Key • Master Key • Certificate • Database Key

8. How Is TDE Applied? • Create a Database Master key • This may also create a Service Master Key(if it didn’t already exist) • Create a certificate based on the Master Key • Create a database encryption key • Set encryption to ON

9. Key Protection Issues and Potential TDE “Gotchas” • Inability to recover database • Inability to move database • Performance overhead of 3-8% • Precautions • Backup the Service Master Key • Backup your Master Key • Backup your Database Key • Reference • How to Restore a TDE-enabled backup

10. Implementing Transparent Data Encryption

11. Topic: Database Compression

12. Topic: Database Compression • What Is Data Compression? • Which Database Objects Can Be Compressed? • How Is Data Compression Implemented?

13. What Is Data Compression? • Definition: reducing the size that certain databases occupy by trading CPU cycles (abundance) in order to reduce I/O effort (constrained) • Compression options • Page-by-page • Row-by-row • Not to be confused with backup compression

14. WhichDatabase Objects Can Be Compressed? • Heap (unclustered table) • Clustered table • Nonclustered index • Indexed view • Partitions of a partitioned table; each partition can vary the compression type • NTFS compression for FILESTREAM objects • Backup files

15. How Is Database Compression Implemented? • Row compression • How it works • Code: CREATE TABLE T1 (c1 int, c2 nvarchar(50) ) WITH (DATA_COMPRESSION = ROW); • Page compression • How it works • Code: CREATE TABLE T2 (c1 int, c2 nvarchar(50) ) WITH (DATA_COMPRESSION = PAGE); • Estimate effect using sp_estimate_data_compression_savings • Restrictions • There are a zillion restrictions

16. Implementing Database Compression

17. Topic: Server and Database Options

18. Topic: Server and Database Options • Available Server-level Options • How to Change Server-level Options • Available Database-level Options • How to Change Server and Database Options

19. Available Server-level Options • 17 basic options • 69 total options • Sample options • Minimum server memory (MB) and minimum server memory • Ad hoc distributed queries • xp_CMDShell Not all options have been placed “within reach.” A special setting actually enables access to special settings. Do you follow?

20. How to Change Server-level Options • GUI (some options) • sp_Configure for basic options • sp_Configure for advanced options • First, turn on Show Advanced Options • Then list them • Then activate desired option(s) • RECONFIGURE • Notable: authentication mode can’t be switched this way but xp_regwrite can be used

21. Available Database-level Options • Option categories • Auto options • Cursor options • Database availability options • External access options • Recovery options • Snapshot isolation options • SQL options

22. How to Change Server and Database Options • Code • ALTER DATABASE DBx SET ____ • GUI • Can’t change every option

23. Using ALTER DATABASE to Change Database Availability

24. Topic: Affinity and Parallelism

25. Topic: Affinity and Parallelism • What Are CPU Affinity and IO Affinity? • ParallelismDefined • Configuring CPU Affinity, IO Affinity, and Maximum Degree of Parallelism (MAXDOP)

26. What Are CPU Affinity and IO Affinity? • CPU affinity • Binds processors to specific threads • IO affinity • When should one change the default configuration? • Apartment threading • NUMA

27. Parallelism Defined • Definition: Using multiple CPU threads to process a query • Sometimes desirable, sometimes not • “Index operations that create or rebuild an index, or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan.” • Factors involved in determining whether or not the query optimizer chooses a parallel execution plan • Computer or VM requires more than one CPU or CPU core • Threads must be available • Certain operations ignore the possibility of parallelism • Estimated query cost compared to cost threshold for parallelism setting • Availability of statistics • MAXDOP “Settable” at: • Instance level • Query level • Cost threshold for parallelism (server option)

28. Configuring CPU Affinity, IO Affinity, and Maximum Degree of Parallelism (MAXDOP) • CPU affinity • ALTER SERVER CONFIGURATION • Don’t configure the same processor for both thread scheduling and I/O processing

29. Configuring Maximum Degree of Parallelism (MAXDOP)

30. Topic: Automation: SQL Agent Jobs and Alerts

31. Topic: Automation: SQL Agent Jobs and Agent Security • Automation: Agent Jobs • Job Step Security • Job Step Security: Proxies and Credentials • Job Administration and Delegation Security • Agent Alerts

32. Automation: Agent Jobs • Basic job architecture • Jobs • Steps and subsystems • T-SQL • CMD Exec • Windows PowerShell • SSIS • Analysis Services • Schedules • Operators

33. Job Step Security • T-SQL job steps • Job owned by sysadmin? • EXECUTE AS • Job not owned by sysadmin? • Other job step subsystems • Job owned by sysadmin? • Proxy • Job not owned by sysadmin?

34. Job Step Security: Proxies and Credentials • Why use advanced job step security? • Component Precedence • Job step points to an available agent proxy • Agent proxy points to a credential (server–level principal) • Credential points to a Windows user account assigned privileges

35. Job Administration and Delegation Security • What if I don’t want job administrators to be sysadmin members? • Three available agent database roles, only in MSDB: • SQLAgentUserRole • Can manage owned jobs and schedules • SQLAgentReaderRole • User role inclusive • View multi-server jobs and properties • List all available jobs, schedules and their properties • SQLAgentOperatorRole • Reader role inclusive • View properties of operators and proxies Why doesn’t the Agent node appear here?

36. Configuring Operators, Agent Jobs, Job Steps, and Schedules

37. Agent Alerts • Basic alert architecture • Alert type • Event Alert • WMI Alert • Performance Object • Response • Notification of an operator • Execute job Thought question:What could executing a job possibly accomplish?

38. Creating a Performance Condition Alert

39. Topic: Database Mail

40. Topic: Database Mail • Requirement: SMTP server • Potentially configured for relay • Database mail profiles • Private • Public • Profiles provide limitations on attachment extensions and attachment size • Calls msdb.dbo.sp_send_dbmail • Don’t forget to maintain database mail history by using • dbo.sysmail_delete_mailitems_sp • dbo.sysmail_delete_log_sp