Course topics
Download
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

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


Configuring maximum degree of parallelism maxdop
Configuring Parallelism (MAXDOP)Maximum Degree of Parallelism (MAXDOP)



Topic automation sql agent jobs and agent security
Topic: Automation: SQL Agent Jobs and Agent Security Parallelism (MAXDOP)

  • 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 Parallelism (MAXDOP)

  • Basic job architecture

    • Jobs

    • Steps and subsystems

      • T-SQL

      • CMD Exec

      • Windows PowerShell

      • SSIS

      • Analysis Services

    • Schedules

    • Operators


Job step security
Job Step Parallelism (MAXDOP)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 Parallelism (MAXDOP)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 Parallelism (MAXDOP)

  • 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?


Configuring operators agent jobs job steps and schedules
Configuring Operators, Agent Jobs, Job Steps, Parallelism (MAXDOP)and Schedules


Agent alerts
Agent Alerts Parallelism (MAXDOP)

  • 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 Parallelism (MAXDOP)


Topic database mail
Topic: Database Mail Parallelism (MAXDOP)

  • 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