SQL Server 2008 Designing, Optimizing, and Maintaining a Database Session 1

SQL Server 2008 Designing, Optimizing, and

Maintaining a Database

SQL Server 2008 Designing,

Optimizing, and Maintaining a

Database Course

The SQL Server 2008 Designing,

Optimizing, and Maintaining a

Database course will help you

prepare for 70-450 exam from

Microsoft. You will learn how to

design a database security server

solution, design a backup and

recovery solution, and design a

strategy to maintain and manage

databases. At the end of this course

you will understand how to install,

configure, maintain, and secure a

SQL Server 2008 database.

Benefits

•

Learn to design a backup and

recovery solution

•

Understand troubleshooting

and disaster planning

techniques

•

Prepare and pass 70-450

exam from Microsoft.

Session 1

Section A: Designing Database Solutions

• Salutations

• CPU Requirements • CPU Considerations • Choosing a CPU • Other CPU Types

• 32-Bit Limitations and Memory Usage

• 64-Bit Memory Usage • Gather Historical Information • Dynamic Management Views • Design Tips

• Non-Uniform Memory Access • Hardware NUMA

• Software NUMA • Benefits of NUMA

• Configuring SQL Server for Soft NUMA

Section B: Storage Considerations

• Types of Storage • Storage Management • Hardware-Based RAID • RAID 0 • RAID 1 • RAID 5 • RAID 10

Section C: Understanding Tempdb

• Tempdb • Using Tempdb • Optimizing Tempdb • Optimization Example

Section D: Designing SQL Server Instances • Multiple Instances • Understanding Multiple Instances • Maximum SQL Server Instances

• Benefits and Risks • Multi-Instance Memory • 32-Bit Server Memory • 64-Bit Server Memory

Section E: Other Instance Configuration Options

• Setting Instance Properties • Using sp_configure • Affinity Mask

• Memory Configuration • Maximum Server Memory • Maximum Degree of Parallelism • Collations • Windows Collations • SQL Server Collations • Managing Collations

• Using Case-Sensitive Collations • Sorting Case-Sensitive Data • Best Practices for Collation • Warnings

Section F: Understanding Physical Databases

• Creating Databases • Database Creation Effects • Exceptions to 8060 Limitations • Transaction Log

• Database Creation Options • Database Creation Syntax • Log Sizing Rules

• Physical Placement • Filegroup Introduction • Files and Filegroups • Why Use Filegroups? • Performance Considerations • LOB Placement

Section G: Creating Databases with Transact-SQL

• Simple Database Create • Adding Multiple Data Files • Adding Multiple Files and

Filegroups

About The Author

Wayne Snyder is recognized

worldwide as a SQL Server expert

and Microsoft Most Valued

Professional (MVP), with over 25

years of experience in project

management, database

administration, software design,

performance measurement and

capacity planning. He is a sought out

consultant, trainer, writer and

speaker, and produces a series of

web-based seminars on SQL Server

2005. Wayne has edited many SQL

Server books, contributes monthly to

SQL Server Magazine and is a

Managing Consultant for Mariner, a

Business Intelligence Company.

Session 2

Section A: FILESTREAM and Full-Text Indexes • Understanding FILESTREAM • FILESTREAM Recommendations • FILESTREAM Interoperability • FILESTREAM Setup • Enable FILESTREAM

• Create FILESTREAM Database • Creating FILESTREAM Columns • Retrieving FILESTREAM Data • Full-Text Indexes

Section B: SQL Server Upgrade Planning

• Planning the Upgrade • Planning and Preparation • Mirrored Databases • Replicated Database • Post Upgrade

Section C: Understanding Database Consolidation • Benefits of Consolidation • Risks • Types of Consolidation • Consolidation Steps • Guidelines

Section D: Business Factors

• Analyze Business Requirements • Principle of Least Privilege • Security Areas

• Common Criteria • New to SQL Server • Costs and Benefits

Section E: SQL Server Instance Authentication • Authentication Type • SQL Server Authentication • Setting SQL Server Authentication • Logon Triggers

• Creating Login Trigger • C2 Audit Mode • Login Auditing • SQL Server Audit

• Creating SQL Server Audits • Server Audit Specifications

Section F: Windows Service

Accounts

•

SQL Server Service

Accounts

•

Special Considerations

•

FILESTREAM Security

•

SQL Server Agent Service

•

Credentials Defined

•

Creating Credentials

•

Assigning Credentials to Jobs

Section G: Key Management

•

Instance Level Permission

•

Understanding Certificates

•

Using Keys and Certificates

•

Encryption Methods

Overview

•

Key Management

Considerations

•

Important Key Information

•

Back Up Service Master Key

•

Back Up Certificates

Section H: Enterprise Key

Management

Understanding EKM

Benefits of EKM

Implementing EKM

Securing Ports

Utilizing Firewalls

•

TCP/IP Port Configuration

•

Connecting Using Ports

Section I: Securing Endpoints

•

Endpoints

•

Public Role Permissions

•

Working with Endpoints

•

Secure HTTP Endpoints

•

SSL Encryption Policy

•

SSL Certificate Requirements

•

Implementing SSL

Session 3

Section A: Database Permissions

• Principles • Securables • Permissions

• Basic Security Steps • Adding Database Users • Special Users

• Understanding Roles • Creating a Role • Role Selection • Role Creation Steps • Permission Accumulation

Section B: Schemas and Application Roles

• Understanding Schemas • Object Name Resolution • Creating a User

• Creating a Login • Creating the Schema • Select Using Unqualified

Schema

• Application Roles • Using Application Roles • Creating Application Roles

Using T-SQL

Section C: Ownership Chaining

• Ownership Chaining Defined • Chain of Ownership

• Ownership Scenarios

• Security Problems with CDOC • Extending Impersonation

Section D: CLR and Service Broker

• CLR Security • Permission Sets • Shared State • Other Requirements • Service Broker • Dialog Security • Transport Security • Certificates and SB

Section E: Policy-Based Management

• PBM Defined • Create Condition

• Condition Applies to Target • Evaluation Modes

• SQL Server Management Studio • Mail Policy

• Table Name Policy • Dev Tables

• Facets

Section F: Gathering Security Information

• Performing Security Related Functions

• Permissions Function • Verify Permissions • Built-in Permissions • sp_helprotect

Section G: Extended Events

• Understanding Packages • Targets Defined • Extended Event DMVs • Package Object DMVs • system_health Event • lock_acquired Event

Section H: Event Notification

• Event Notification Defined • Enable Service Broker • Event Notification Processor • Create Event Notification • Causing the Event

Section I: Encryption Strategy

• Transparent Data Encryption • Clear Text Backups

Session 4

Section A: Clustering and Log Shipping

• Clustering

• Clustering Resource Group • Setup Considerations • Clustering Setup • MSCS Service Accounts • Log Shipping • Interoperation Requirements • Switching Roles • Managing Instance-Level Objects • Reinitializing

• Log Ship for Reporting • Consistency Check • Monitoring

• Monitor Server Tables

Section B: Design Database Mirroring

• Mirroring

• When to Use Mirroring • How Mirroring Works • The Mirror • Mode Benefits • Setup Requirements • Mirroring Steps • Failover Types • Suspend vs. Stop • Automatic Page Repair • Snapshots for Reporting • Database Mirroring Failover

Section C: High Availability with Replication

• Replication

• Replication Roles and Language • Publications and Articles • Article Definition • Replication Scenarios • Remote Distribution • Distribution Agent • Types of Replication • Snapshot Replication • Transactional • Peer-to-Peer Replication • Merge • Updating Subscribers

Section D: Replication Planning

• New Replication Features • Planning Considerations • Workload

• Choosing a Solution • Recover from Failure • Peer-to-Peer Recovery • Synchronization • Monitoring Replication

Section E: Selecting a High-Availability Solution

Session 5

Section A: Planning Database Recovery

• Current Recovery Models • Simple Recovery • Full Recovery • Bulk-Logged Recovery • SQL Server 2008 Backup Methods • Backing Up to Disk • Backing Up to Tape • Backup Compression • Compression Benefits • How Often to Back Up • Backup Demonstration • Backup Demo w/Compression

Section B: Full Backups and Restores

• Full Backup Schedule • Restore to a Point-in-Time • The Backup Process • Backup Options • Mirror To

• Performing Backups Using T-SQL • Restore vs. Recovery • Automatic Recovery • Restore Types • Restore Process • Restore Command • Restore Options

Section C: Transaction Log Backups and Restores

• Why Transaction Log backups • Transaction Log Backup • Bulk-Logged Recovery Model • Backup Log Options

• Log Backup Storage Options • Restoring the Log

• Log Backup Theory • Backup NO_TRUNCATE

Example

• Point-In-Time Recovery • STOPAT and RECOVERY

Commands • Log Marks

Section D: Differential Backups and Restores • Differential Backups • Standard Scenario • Back Up File/Filegroup • Restore File/Filegroup • Online Restore • Orphaned Users • Page Restores

Section E: Recovery Test Plans

• Log Ship Considerations • Replication Recovery Test Plan • Restore Publisher

• Hardware Considerations • Hardware Cluster Failures • OS Cluster Failures • Instance Rebuild • Test Plans

Section F: OS Level Monitoring Solutions

• Monitoring Tools • System Monitor • Using System Monitor • Data Collector Sets

• SQL Server Activity Monitor • Standard Reports

• Monitoring Memory • Monitoring Processor • Monitoring Hard Disk I/O • Baseline Monitoring • Log Viewer

• Event Viewer • WMI

Session 6

Section A: Data Collection

• System Monitor • Monitoring Memory • Monitoring Processor • Monitoring Hard Disk I/O • Using System Monitor • Data Collector Sets • SQL Server Profiler

• Running SQL Server Profiler • Creating a Trace

• Selecting Events to Capture

Section B: Index Tuning and DMVs

• Database Tuning Advisor • Running Database Tuning

Advisor

• Tuning Options

• Advisor Recommendations • Advisor Reports

• Performance Monitoring Using DMVs

Section C: The Performance Studio

• Management Data Warehouse • Collection Process

• Data Collector and MDW Security

• MDW Security Steps • Set Up Data Collection • Review SQL Agent Collection

Jobs

Section D: Dedicated Administrator Connections

• DAC Defined

• Establishing DAC Connection • Locking

• Shared Locks

• Update and Intent Locks • Monitoring Locks • Deadlocks

Section E: Page-Level Data Compresssion • Data Compression • Details • Heap Compression • Row Compression • Page Compression • Creating Tables with

Compression

• Checking Compression Statistics

Section F: Heap and Index Maintenance

• Index Fragmentation • Fixing Fragmentation • Page Fullness Concerns • External Data Fragmentation • Internal Data Fragmentation • Additional External

Fragmentation Issues

• Average Fragmentation Percent • Reorganize Index

• Rebuild Index

Session 7

Section A: Table Partitions

• Overview

• Partition Management • Creating a Partitioned Table • Loading the Table

• Executing a Query Plan • Partitioning Column Selects • Switched Partitioning • Merging Partitions • Splitting Partitions • Index Statistics • Auto Update Statistics • Manually Creating Statistics

Section B: Implementing Resource Governor • Introduction • Resource Governor Components • Resource Governor Demonstration

• Creating a Resource Pool • Creating the Classifier Function • Creating a Workload

• Resource Governor Homework

Section C: Understanding Resource Governor

• How Resource Governor Works • Workload Group Settings • System Workload Groups • Queuing Demonstration • Dropping an Active Workload

Section D: Resource Governor Monitoring

• Monitoring Considerations • Resource Governor DMVs • Resource Governor Tips • Testing and Debugging Tips • Using Importance

Section E: Management and Compression

• Design Considerations • Scaling PBM

• Import Policies

• Page vs. Row Compression • Partitions and Indexes

Section F: Management Automation Strategy • DDL Triggers • Comparison • WMI • SQL Server Agent • SQL Server PowerShell • Cmdlets • SQLPS

Section G: Administering SSIS

• Understanding SSIS Packages • Data Protection • Deployment • Direct vs. Indirect Configurations • Troubleshooting Packages • Configuring Checkpoints

Section H: Linked Servers

• Linked Server Security • Delegation

• SQL Provider Properties • Creating a Linked Server • LS Security and Options

Section I: Data Distribution via Replication • Snapshot • Transactional • Peer-to-Peer • Merge • Bulk Inserts • Row Filtering • Column Filtering • Transactional Conflict Detection

• Peer-to-Peer Conflict Detection • Merge Resolvers

• Health Monitoring