|
Module 1
Section A: Installation Considerations
• Installation Options
• Books Online
• SQL Server Editions
• CPU Requirements
• File Locations
• File Structure
• Optimizing Tempdb
• SQL Service Accounts
• Special Considerations
Section B: Installing SQL Server
• SQL Server Installation Center
• Installation Features
• Configuring Service Accounts
• Report Service Options
Section C: Configuring SQL Server Instance
• Memory Considerations
• Affinity Mask
• Management Studio
• Instance Configuration
• SP Configuration
• Configuration Parameters
Section D: Configuring Services
• SQL Server Browser
• Configuration Manager
• Service Properties
• Network Configuration
• Client Configuration
Section E: SQL Server Components
• SQL Server Integration Services
• SQL Server Analysis Services
• SQL Server Reporting Services
• Configure Replication
• Replication Security
• Reporting Services Configuration Manager
• E-mail Settings for SSRS
• Configuring Distribution
• Distribution Settings
Section F: Configuring Database Mail
• Database E-mail
• Profiles and Accounts
• Database Mail Setup
• SMTP Server Name
• Manage Profiles
• Database Mail Configuration
Section G: Full-Text Indexing
• Full-Text Overview
• Full-Text Indexes
• Enabling Indexing
• Catalog Properties
• Configuring Full-Text Indexes
• Creating a Full-Text Index
• Full-Text Indexing Homework
|
Module 2
Section A: SQL Agent Operators
• SQL Agent Properties
• Creating an Operator
• Pager Notifications
• Operator Notifications
• Fail-Safe Operator
Section B: SQL Agent Jobs
• Creating New Job
• Job Categories
• Job Steps and Types
• Step Advanced Options
• Job Schedules
• Alerts and Notifications
• Target Servers
• Security Roles
• SQL Agent Job Security
• Creating a Credential
• Creating a Proxy
Section C: SQL Agent Alerts
• Alert Overview
• New Alert
• Alert Response
• Firing the Alert
• Performance Alerts
• WMI Alerts
• Creating WMI Alert
Section D: Declarative Management Framework
• Policy-Based Management
• Facets
• New Condition
• New Policy
• Evaluate Policy
• Naming Condition
• Naming Policy
• Evaluate Naming Policy
• Managing Policy Categories
Section E: Backing Up
• Backup Concepts
• Windows Server Backup
• Backup Schedule
Section F: SQL Security Considerations
• Surface Area Security
• sp_configure Options
• Authentication Modes
• SQL Server Authentication
• Trusted Security
• Password Complexity
• Password Expiration
Section G: Managing SQL Logins
• Managing SQL Security
• Creating Login in T-SQL
• Working with Logins
• System Login Tokens
• Using LOGINPROPERTY
• Fixed Server Roles
• Public Server Roles
• ServerPrincipal
Section H: Managing SQL Instance Permissions
• Login Triggers
• Login Trigger Benefits
• Creating a Trigger
• Testing Trigger
• Ownership Chaining
• Fixed Roles vs. Permissions
|
|
Module 3
Section A: Auditing SQL Instances
• C2 Security
• C2 Considerations
• ICC for IT Security Evaluation
• Common Criteria
• Audit Login Failures
• DDL Triggers
• Using Triggers
• EVENTDATA Function
• Viewing Triggers
• Event Notifications
• Configuring Event Notifications
• Event Notification Processor
• Create Queue and Service
• Using Server Notifications
Section B: Understanding Users and Roles
• Adding Database Users
• Special Users
• Database Roles
• Object Name Resolution
• Roles
• Application Roles
• Using Application Roles
Section C: Managing Users and Roles
• Creating New User
• Create User with T-SQL
• Create Role with T-SQL
Section D: Schema and Object Permissions
• Schemas
• Schemas Defined
• Chain of Ownership
• When Is Security Checked?
• Scenario One
• Scenario Two
• Scenario Three
• Configure Schema Permissions
• Create Schema and Tables
• Impersonation
• Change User Default Schema
• Impersonation Defined
• Cross DB Chaining
• Cross DB Chaining Problems
• Extending Impersonation
• Using Certificates to Impersonate
Section E: Transparent Data Encryption
• Transparent Data Encryption Defined
• Backups
• Backing Up the Table
• Enable Database Encryption
• Backing Up Certificates
• Restoring Encrypted Databases
Section F: Creating Databases
• Understanding Databases
• Transaction Log
• Database Creation Options
• Database Syntax
• Collation
• Sorting Data
• Comparing Data
• Automatic and Explicit Data Collation
Section G: Manage and Configure Databases
• Transaction Log Rules
• Recovery Considerations
• Filegroup Introduction
• Understanding Filegroups
• New Database
• Options and Filegroups
• Recovery Models Defined
• Create Database Using T-SQL
• Adding Multiple Data Files
• Adding Multiple Filegroups
• Data File Location
• Detach/Attach Databases
• Detach Database Steps
• Attach a Database
|
Module 4
Section A: Understanding Backups
• Backup Methods
• Types of Backup
• Backup Permissions
• Backing Up to Disk
• Backing Up to Tape
• Backup - How Often?
• Full Backup
• Full Backup - When?
• Backup Options
• Tape Options
Section B: Performing Full Backups
• Perform Backup
• Change Default Filegroup
• Backing Up to Default Directory
• Other Backup Types
• Striped Set Backups
• Same File Backups
Section C: Restore Concepts
• Restore vs. Recovery
• Automatic Recovery
• Restore Types
• Restore Process
• What Is On the Media?
• RESTORE VERIFYONLY
• RESTORE FILELISTONLY
• RESTORE LABELONLY and HEADERONLY
Section D: Restoring Full Backups
• Restore Steps
• Restore Command
• Restore Options
• Simple Restore
• Manual Recovery
• Restore Mirrors and Striped Sets
• Restore Change Name
• Restore Compressed Backup
Section E: Performing Transaction Log Backups
• Transaction Log Backup
• Normal Log Backup
• Backup Log Options
• Full Log Considerations
• Running Transaction Log Backup
Section F: Restoring Transaction Log Backups
• Log Restore Process
• Understanding Backup NO_TRUNCATE
• Restore Setup
• Restore with Recovery
• Restoring with NORECOVERY
Section G: Performing Differential Backups
• Understanding Differential Backups
• Monday Differential
• Sunday Differential
• Standard Scenario
• Differential Backup
Section H: File and Filegroup Backups
• Overview
• Restoring Files and Filegroups
• Online Restore
• Backing Up Files and Filegroups
• File and Filegroup Backup Options
Section I: Managing Database Snapshots
• Snapshots Defined
• Snapshot Restrictions
• Snapshot Implementation
• Best Practices
• Create Snapshot
• Use Snapshot
• Update Snapshot
• DDL Changes
• Restoring From Snapshot
Section J: Managing Database Integrity
• Integrity
• DBCC CHECKDB
• Suspect Pages
• Event Types
• Managing Integrity
|
|
Module 5
Section A: Maintenance Plans
• Understanding Database Maintenance
• Maintenance Tasks
• Reviewing Maintenance Plans
Section B: Import and Export Data
• Relocating Data
• SELECT INTO
• Import/Export Wizard
• Using Import/Export Wizard
• Bulk Imports
• Bulk Copy
• BCP
• BCP Syntax
• Bulk Copy Demonstration
• BULK INSERT
• BULK INSERT Demonstration
• OPENROWSET
• OPENROWSET Demonstration
• OPENDATASOURCE Demonstration
Section C: Manage Data Partitions
• Partitioned Tables
• Benefits
• Partition Table Candidates
• Partition Steps
• Partition Function
• Partition Scheme
• Partition Table Creation
• Alter Table Commands
• Merge/Split Partition
Section D: Partitioning
• Create/Fill/Partition Database
• Create Partition Function
• Viewing Partitions and Row Counts
• Partition Range Values and Parameters
• Query Results
• Nonpartition Execution Plan
• Row Count Lookup
• Switch Partitions
Section E: Data Compression
• Row and Page Compression
• Data Compression Demonstration
• Compressing the Data
• Enabling Compression
• Sparse Columns
• Using Sparse Columns
• Insert Using Column Set
• Updating Using Special Purpose Columns
Section F: Understanding Indexes
• Indexing Pros and Cons
• What Indexes Affect
• Seek vs. Scan
• Using Table Scan
• Data Access and Usefulness
• Data Access without Index
• Data Access with Index
Section G: Index Structures
• Indexes and Data Storage
• Heap Storage
• Clustered Indexes
• Nonclustered Indexes
• Nonclustered Index Example
• Nonclustered
Section H: Creating Indexes
• How Indexes Are Created
• Create Index Command
• Creating an Index Process
• Creating an Index
• sort_in_tempdb Command
• ignore_dup_key Command
• drop_existing Command
• MAXDOP and Locking Commands
• INCLUDE Command
• Drop Index Command
• Multi-Columned Indexes
• Partitioned Index
• Computed Column Index
|
Module 6
Section A: Index Options and Metadata
• Create Index Demonstration
• Ignore Duplicate Key Values
• Using Covering Index
• Getting Index Information
• XML/Physical Statistics
• Getting Locking Information
• DM DB Index Usage Statistics
Section B: Index Fragmentation
• Understanding Index Fragmentation
• Fixing Fragmentation
• Page Fullness
• Empty Pages
• External Fragmentation
• Pages Too Full
• More External Fragmentation Concerns
• Logical Fragmentation
• Reorganize and Rebuild Index
• Online Fragmentation Operations
• Create Clustered Index with Fillfactor
• Prepare for Index Tuning
• Tuning Recommendations and Reports
• Tuning Options
• Filtered Indexes
Section C: XML Indexes
• XML Index Considerations
• XML Index Types
• Path Secondary Index
• Value Secondary Index
• Property Secondary Index
• Creating Primary XML Index
• Creating Path Secondary Index
• Enable/Disable Indexes
• Tesselation Covering Rule
• Tesselation Cells Per Object
• Tesselation Deepest Cell
• The Bounding Box
• Creating Spatial Index
• Create Geometry Table
• Using New Index GUI
Section D: SQL Server Service Problems
Browser Service
Engine Startup
Tempdb Full
SQL Server Agent
Section E: Error Logs
• Windows Event Logs
• SQL Server Logs
• Job History Log
Section F: Concurrency Problems
• Lost Updates
• Dirty Read
• Non-Repeatable Read
• Phantoms
• Benefits of Locking
• Lock Types
• Intent Locks
• Bulk Update Lock
• Isolation Levels
• Monitoring Locks
• Deadlock Errors
• Deadlock Demonstration
• Lock Escalation Demonstration
• Set Lock Escalation
• Begin Transaction
• Set Lock Escalation Automatic
• Job Execution Problems
• Proxy Accounts
|
|
Module 7
Section A: Understanding High Availability
• Database Mirroring
• When to Mirror
• How Mirroring Works
• The Mirror Process
• Mirror Mode Benefits
• Setup Requirements
• Mirroring Steps
• Failover
• Creating a Mirror Database
• Set Up Mirroring
• Set Up Failover Partner
Section B: Clustered Instances
• Failover Clustering
• Active/Active Considerations
• SQL Editions
• Requirements
• Installation
• When to Use Clustering
Section C: Implementing Log Shipping
• Log Shipping
• Switching Roles
• Log Shipping Setup Primary Server
• Log Shipping Setup Secondary Server
• Monitoring Transaction Log Shipping
Section D: Understanding Replication
• Replication
• Language of Replication
• Publications and Articles
• Article Definition
• Publisher/Distributor/Subscriber
• Replication Remote Distribution
Section E: Planning for Replication
• Replication Types
• Snapshot Replication
• Transactional Replication
• Replication of Stored Procedures
• Transactional Replication Continued
• Peer-to-Peer Replication
• Merge Replication
• New Replication Features
• Replication Planning
• Workload
• Choosing a Solution
Section F: Implementing Replication
• Distributor Setup
• Publisher Setup
• Subscriber Setup
• Configuring Distribution
• Create New Publication
• Create New Subscription
• Replication Monitoring
Section G: Data Collection
• System Monitor
• Monitoring Memory
• Monitoring the Processor
• Monitoring Hard Disk I/O
• Baseline Monitoring
• Using System Monitor
• Data Collector Sets
• Profiler
• Running SQL Server Profiler
• Creating a Trace
• Selecting Events to Capture
• Running a Trace
Section H: Index Tuning and DMVs
• Database Engine Tuning Advisor
• Running Database Tuning Advisor
• Tuning Options
• Advisor Recommendations
• Advisor Reports
• Performance Monitoring Using DMVs
Section I: The Performance Studio
• Management Data Warehouse
• Collection Process
• Data Collector
• MDW Security
• MDW Security Steps
• Data Collection Setup
• Review SQL Agent Collection Jobs
|
|
