SAINORA

HomeBlogs SQL Server 2005/2008 Database Administration

SQL Server 2005/2008 Database Administration

Pre-Requisites

  • Knowledge of Microsoft Windows operating system.
  • Working knowledge on Transact-SQL
  • Database design experience (Table/View/Function/SP/Trigger Design)

Exit Profile

  • Ability to take exams 70-432 and 70-450

Prerequisites Refreshment

  • RDBMS Concepts and CODD's Rules
  • Normalization
  • Query Writing Skills
  • Query Writing Skills
  • Transact SQL (User and System Procedures)

Editions and Features

  • Editions and Licensing
  • Features

Administration Basics

  • Installation on Standalone Server
  • Installing Service Packs and Applying Hot Fixes

Disaster Recovery

  • Backing Up User Databases
  • Performing Full, Differential & Log Backup
  • Backing Up Individual Files or Filegroups
  • Viewing Backup Metadata
  • Restoring User Databases
  • Restoring Database from Full/Diff/Log backups
  • Restoring a File or Filegroup
  • Performing a Piecemeal (PARTIAL) Restore
  • About RTO, RPO and Designing Backup Strategy
  • System Databases and Disaster Recovery
  • Online Restore

Managing Security

  • Security Architecture
  • Managing Securables , Principals and Permissions
  • Keys and Certificates
  • Usage of Blockbox Trace
  • C2 Level Auditing

Automating Administrative Tasks

  • Configuring the SQL Server Agent
  • Creating Jobs and Operators
  • Create Alerts
  • Managing Multiple Servers (using Central Management Server)
  • Managing SQL Server Agent Security
  • Policy Management
  • Resource Governance

Monitoring and Tuning Server / Database Performance

  • Viewing Current Activity
  • Using System Monitor
  • Using DDL Triggers
  • Using Event Notifications
  • Server Settings and Database Options

Troubleshooting and Tuning Query Performance

  • Using SQL Server Profiler (including Background execution approach)
  • Query Processing and Execution
  • Importance of Statistics
  • Reading and Analyzing Query Plans
  • Best Practices for Query Improvements
  • Index Architecture and Tuning Indexes
  • Reindexing and Index Defragmentation

Troubleshooting Concurrency Problems

  • Understanding Isolation Levels
  • Snapshot isolation
  • Understanding Transaction Processing
  • Locking architecture
  • Row Versioning
  • Controlling Locking
  • Troubleshooting Locking / Blocking / Deadlocking