Benefits
• Learn to design a backup and recovery solution
• Understand troubleshooting and disaster planning techniques
• Prepare and pass 70-450 exam from Microsoft.

Module 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
• Creating Database Scripts
• Utilizing Simple Databases
• Alter Database Syntax

Module 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
• SSL Client Scope

Module 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 Accumulati

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
• Backup Using Encryption
• Backing Up Certificates
• Data Encryption

Module 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
• Clustering Review
• Mirroring Review
• Log Shipping Review
• Replication Review

Module 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
• Using WMI Eventing

Module 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
• Saving During Rebuild
• Online Operations
• Heap Maintenance

Module 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
• Reviewing Replication Alerts