course topics
Download
Skip this Video
Download Presentation
Course Topics

Loading in 2 Seconds...

play fullscreen
1 / 41

Course Topics - PowerPoint PPT Presentation


  • 56 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Course Topics' - bert-beck


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2
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
Module 2 Overview
  • Transparent Data Encryption
  • Database Compression
  • Server and Database Options
  • Affinity and Parallelism
  • Automation: SQL Agent Jobs and Alerts
  • Database Mail
topic transparent data encryption
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”
what problems does tde solve and how does it solve them
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
basics on sql server encryption and key hierarchy
Basics on SQL Server Encryption and Key Hierarchy
  • Service Master Key
  • Master Key
  • Certificate
  • Database Key
how is tde applied
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
key protection issues and potential tde gotchas
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
topic database compression
Topic: Database Compression
  • What Is Data Compression?
  • Which Database Objects Can Be Compressed?
  • How Is Data Compression Implemented?
what is dat a compression
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
which database objects can be compressed
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
how is database compression implemented
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
topic server and database options
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
available server level options
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?

how to change server level options
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
available database level options
Available Database-level Options
  • Option categories
    • Auto options
    • Cursor options
    • Database availability options
    • External access options
    • Recovery options
    • Snapshot isolation options
    • SQL options
how to change server and database options
How to Change Server and Database Options
  • Code
    • ALTER DATABASE DBx SET ____
  • GUI
    • Can’t change every option
topic affinity and parallelism
Topic: Affinity and Parallelism
  • What Are CPU Affinity and IO Affinity?
  • ParallelismDefined
  • Configuring CPU Affinity, IO Affinity, and Maximum Degree of Parallelism (MAXDOP)
what are cpu affinity and io affinity
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
parallelism defined
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)
configuring cpu affinity io affinity and maximum degree of parallelism maxdop
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
topic automation sql agent jobs and agent security
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
automation agent jobs
Automation: Agent Jobs
  • Basic job architecture
    • Jobs
    • Steps and subsystems
      • T-SQL
      • CMD Exec
      • Windows PowerShell
      • SSIS
      • Analysis Services
    • Schedules
    • Operators
job step security
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?
job step security proxies and credentials
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
job administration and delegation security
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?

agent alerts
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?

topic database mail
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
ad