SQL Server DBA

  • What is a database and a database management system (DBMS)
  • Introduction to Microsoft SQL Server and its overall SQL Server Data Platform
  • History and Current trends of DBMS and Database Administration
  • Tasks of a Database Administrator

  • Editions of SQL Server 2012
  • Hardware and Software pre-requisities for a successful SQL Server installation.
  • Understanding SQL Server components.
  • Perform a successful SQL Server 2012 installation
  • Slipstreaming SQL Server installation with service packs.
  • Understanding SQL Server version identifiers, installed directories and conventions.

  • Understanding SQL Server system databases.
  • Understanding the SQL Server database structure.
  • SQL Server files and filegroups.
  • SQL Server Transactional logs, Recovery Models.

  • Understanding Schemas
  • Understanding SQL Server data types
  • Understanding column properties
  • Creating tables, altering tables and adding constraints, relationships.

  • Introduction to Backup and Recovery strategy for databases.
  • Full Database backups using SIMPLE Recovery Model
  • Full Database and Transactional log backups with FULL Recovery Model
  • Backup history and maintenance
  • Designing Maintenance plans for Backup Tasks
  • SQL Server Agent for scheduling backups
  • Database Mail for email notifications to DBAs.

  • Introduction to Database Restore and Recovery
  • Full database restore
  • Full database restore and recovery of transactional logs
  • Restoring system databases
  • Attaching and detaching SQL Server databases.

  • Partial Data export and import using the command line tools – BCP command
  • Partial Data export and import using GUI tools – Export and Import Wizard
  • Move or copy a full database.

  • SQL Server authentication vs. Windows authentication
  • Auditing database logins using SQL Server Error Logging
  • SQL Server server-wide logins and database specific users

  • Understanding database indexes
  • Checking index fragmentation and maintaining them
  • Database statistics
  • Understanding SQL Server Profiler
  • Creating Traces
  • Blocking/Deadlocks

  • Database Mirroring
  • Log Shipping
  • Snapshot/Transactional Replication
  • Reading secondary database copies