SQL DBA Training

Course details

Introduction to database management systems and databases

• 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

SQL Server 2012 Installation

• Editions of SQL Server 2012
• Hardware and Software pre-requisites 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.

SQL Server 2012 Administration and Development Tools

• Using SQL Server Management Studio (SSMS)
• Using SQL Server Command Line interface
• Using SQL Server Configuration Manager

SQL Server 2012 Architecture

• Understanding SQL Server system databases.
• Understanding the SQL Server database structure.
• SQL Server files and filegroups.
• SQL Server Transactional logs, Recovery Models.
• What is the difference between SQL Server instance and database.
• Client-Server Communication.
• Attaching and detaching SQL Server databases.

SQL Server Database objects

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

Understanding Locks & their Usage

• SP_WHO2 and SP_LOCK Outputs
• Lock Types, Lock Hints and Lock Escalations
• Avoiding WAITS on PAGE and TABLE
• Isolation Levels - Types and Usage
• READ COMMITED and SERIALIZABLE
• READ UNCOMMITED and REAPEATABLE
• SNAPSHOT & READCOMITTED SNAPSHOT
• Choosing Isolation Levels For OLTP
• Statement Locks & Transaction Locks
• TempDB Issues with Isolation Options
• Locking Hints to Avoid Blockings
• A DEADLOCK Scenario and DML Audits
• Deadlock Detection and Avoidance

SQL Server Backup and Recovery - Part 1

• 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.

SQL Server Backup and Recovery - Part 2

• Introduction to Database Restore and Recovery
• Full database restore
• Full database restore and recovery of transactional logs
• Restoring system databases

SQL Server Data Export and Import (Data Movement)

• Partial Data export and import using GUI tools- Export and Import Wizard
• Move or copy a full database.

SQL Server security

• SQL Server authentication vs. Windows authentication
• Auditing database logins using SQL Server Error Logging
• SQL Server server-wide logins and database specific users
• Creating user-defined server roles
• Configuring contained databases.

Performance Tuning- Part 1

• Understanding database indexes
• Checking index fragmentation and maintaining them
• Database statistics
• Understanding SQL Server Profiler
• Creating Traces
• Running server-side traces

Performance Tuning - Part 2

• Understanding the Extended Events architecture
• Creating and configuring an Extended Events session
• Using an extended events session to monitor system performance

SQL Server High Availability (Advance Topics)

• Database Mirroring
• Log Shipping
• AlwaysOn
• Failover cluster instance improvements
• Creating AlwaysOn availability groups
• Administering AlwaysOn Availability groups
• Reading secondary database copies

Data Exports (SSIS) and DB Mail

• Import & Export Operations
• OLE-DB, SQLNCLI and Jet Drivers
• Choosing correct Data Providers
• Scheduling SSIS Packages using Jobs
• Proxies and Reading Job History
• Database Scripting and DB Encryption
• Database Cloning Process and Options
• Configuring Linked Servers - Custom
• SQL Server Agent - Alert Properties
• Database (DB) Mail Configurations
• Securing DB Profiles : SMTP Accounts
• DB Mail Profiles and Public Tokens
• Alert System Configurations in SQL Agent
• Creating Operators and Emails
• Warnings and Emails with DB Mail
• Testing Job Failures and Notifications
• Job Activity Monitor and History Delete