1 / 41

Course Topics

Course Topics. 02 | Maintain Instances and Databases. George Squillace | Senior Technical Trainer – New Horizons Great Lakes Richard Currey | Senior Technical Trainer – New Horizons United. Module 2 Overview. Transparent Data Encryption Database Compression Server and Database Options

bert-beck
Download Presentation

Course Topics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related