SQL Server DBA Training
SQL Server DBA Training is designed to enlighten all that is required to perform SQL DBA, SQL Server instances, SAN storage and security. Our professional training gets you started to a career as a SQL Server DBA.
SQL Server DBA Training Curriculum
SQL Server Fundamentals & T-SQL
SQL Server Introduction & Installation
Get started with the introduction, installation and components of sql server, configuring tools, providing services and authentication and many more are learnt in this module.
Topics- SQL Server Installation
Services & Authentication
SQL Server Components & Firewall
SSMS
Configuration Tools & SMO
Protocols & Server Aliases
Linked Servers & Types
SQL Server 2012 Database Design
Know the creation and modification of sql database, recommendations of data and log files and the offline/online options of db.
SQL Database Creation
Files & File Group Options
Data File Recommendations
Log File Recommendations
DB Offline & Online Options
Database Modifications
SQL Server Tables & Data
Here, you’ll learn about the aliasing, schemas, data types and table design of T-SQL and also the comparison between delete & truncate.
Table Design and T-SQL Data Types
INSERT & SELECT Options
Aliasing
Schemas
Tables & File Group Usage
DELETE versus TRUNCATE
Data Validations and Constraints
This chapter describes about the constraints and it’s properties & objects and also the different keys and it’s uses.
Constraints – Properties & Objects
UNIQUE KEY and NOT NULL
PRIMARY KEYS & Usage
Composite Keys
CHECK and DEFAULTS
Basic Queries and JOIN Queries
Learn the different types of queries and bulk operations in the lesson.
EXISTS
APPLY
WHERE
ORDER BY
GROUPING
HAVING
Sub Queries & Nested Queries
Bulk Operations – Local & Remote
Views and Row Data Security
This chapter describes the views as physical & virtual objects, types of views and the issues with alter table.
Views as Virtual Objects
Views as Physical Objects, SCHEMA BINDING
ENCRYPTION
Issues with ALTER TABLE
System Views & Dynamic Management Views
Views with Joins and Views
Indexes and Query Tuning
You’ll know the types, architecture, working, performance and uses of indexes.
Architecture of Table Indexes
Clustered & NonClustered Indexes
SORT_IN_TEMPDB
ONLINE
FILLFACTOR and PAD_INDEX
Execution Plans and Performance
Unique Indexes and Uses
Working with Indexed Views
Stored Procedures and Benefits
About Stored Procedure and it’s use, types of parameters, dynamic sql queries and also procedure cache & recompilation are included in this module.
Stored Procedures & Use
Variables and Parameters
SCHEMABINDING
ENCRYPTION
INPUT & OUTPUT Parameters
Dynamic SQL Queries
Procedure Cache & Recompilation
User Defined Functions and Usage
Different types of functions and the uses of it are discussed in this chapter.
Functions Use & Types
Scalar & Table Valued Functions
SCHEMABINDING
ENCRYPTION
System Functions and Events
Date
Time
String and Metadata Functions
Triggers and Cursors – Memory Limitations
Understanding the DML triggers & performance, types of cursors and it’s usage and also the inserted & deleted tables.
DML Triggers & Performance
INSERTED and DELETED tables
STATIC and DYNAMIC cursors
SCROLL & FORWARD_ONLY
LOCAL and GLOBAL cursors
KEYSET Cursors and Usage
Embedding Cursors in SPs
Transaction Concepts in Realtime
What are transaction types and it’s use?, blocking the DML/DDL operations, commit & rollback options and many more are learnt in this module.
Transaction Types & Use
Blocking – DML Operations
Blocking – DDL Operations
COMMIT & ROLLBACK Options
SAVEPOINT & CHECKPOINT
Deciding Transaction Types
Querying SQL_MODULES View
Basic SQL DBA
Transaction Locks and Deadlocks
What is the significance, types, hints, usage of locks?, types of isolation levels & models and the issues of real-time data deployment are discussed in this chapter.
Significance of LOCKS and Usage
Types of Locks and Lock Hints?
Isolation Levels – Types & Usage
Choosing best Isolation Level
TempDB Issues with Isolation Levels
Recovery Models and Options
Choosing Best Recovery Model
Real-time Data Deployment Issues
Database Backups Strategies
The focus in this course is to know the types, validations, restore paths and audits of backup, need for backup strategies, different options and MSDB history tables.
Need for Backups Strategies
Database Backups and Partial Backups
FORMAT
INIT
SKIP
CHECKSUM Options
Backup using T-SQL Scripts & UI
Backup validations and Restore Paths
MSDB History Tables
Backup Audits
Compressed Backups and Expiry
Log backups and Truncation options
Backup Audit Tables in MSDB
Media Sets and Media Truncate Options
Database Restores and Recovery Paths
Learn How to restore the database, types of restore and how can we provide backup or recovery path using T-SQL & UI..
Need for Restores and Types
Backup Verification using T-SQL & UI
Restore Phases and Recovery Path
Database
File Group
File Restores
PARTIAL and Piecemeal Restores
Point-In-Time Restores and Options
Compressed Tail Log Restores
Recovery
MOVE and FILELISTONLY Options
SQL Server Jobs & Alerts
Understand how to Create jobs using UI and T-SQL Scripts, Schedules the Job Steps, Monitoring the Job Activity, How do you configure DB Mail and many more.
Creating Jobs using UI
Creating Jobs using T-SQL Scripts
Understanding Job Steps and Schedules
Backup Schedules & Jobs using Scripts
Job Failures and History View
Enabling and Disabling Jobs
Job Activity Monitor and Agent Status
Alert System and DB Mail Configuration
Data Replications and DR Process
Get a brief understanding on How to Install Replication and Configure it, Types and Alerts of replication, How to monitor Replication and also about DB Recovery Process.
Replication Architecture and Configuration
Snapshot and Transactional Replications
Merge Replication
DB Recovery process
Peer-Peer with Backup Initialization
Replication Types – PUSH & PULL
Replication Alerts
Warnings and Emails
Replication Monitors and Event Watch
Replication for Load Balancing & DR
Agent Operators, Notifications / Emails
Data Access Modes and Agent Properties
Log Shipping and DB Mirroring
Here you’ll learn about the Log shipping and DB Mirroring configuration and its modes respectively and how can regain the disaster recovery in SQL Server.
Disaster Recovery in SQL Server
Log Shipping Configuration and Modes
Manual DB Failover using Log Shipping
DB Mirroring Configuration and Modes
Automatic and Manual Failover Options
Mirror Monitors
Network Considerations
Comparing Log Shipping and Mirroring
Security and Data Encryption
Levels of Security Implementation
Server level Logins and DB level Users
Server Roles and User Mappings
Schema Level Security and DB Roles
Table and Column permissions
DB roles
Data Encryption Keys and Certificates
Login Failures
Authentication Audits
Server Audits and DB Monitors
Types of Audits and SQL Tools
Server level Audits and Error Logs
Activity Monitors and IO Usage Reports
SQL Profiler, Deadlock Graphs and Filters
DMVs for Query Audits
Tempdb Audits
DMFs for Index
Session and Log Audits
DMFs
DMVs for Queries
Tempdb Audits
Important Queries for DBCC
Important DMVs and DMFs
Security Audits and Profiling
Advanced SQL DBA
Database Maintenance Plans (SSIS)
Database Maintenance Plan Strategies
Wizard Based DMPs and Schedules
Control Flow Tasks
Precedence Usage
Index Rebuilds and Reorganization
Deciding Fill Factor and PAD_INDEX Options
Managing MSDB and TEMPDB
Using NOTIFY OPERATOR task in DMP
Using UPDATE STATISTICS and Agent
Maintenance Issues in Real-time
Partitions and Full-Text Indexes
Able Partitions and Compressions
Aligned Partitions and FillFactor
Managing Partitions for Query Tuning
Statistics with Indexes and Partitions
Full Text Search Usage and Catalogs
Full Text Indexes for Query Tuning
CHANGE_TRACKING options
Use Tuning Implementations with Cache
Compressed Indexes with Partitions
Database Engine Tuning Advisor (DTA)
Creating Workload Files using Profiler
Workload Tables with SQL Profiler
TUNING Templates and Column Filters
Index Selectivity and DTA Usage
Filtered Indexes and Index Sizing
Role of Statistics in Performance Tuning
Index Fragmentation and REBUILDS
Alerts and Troubleshooting
LOG SPACE Issues – Alerts and Solutions
TEMPDB Issues – Alerts and Solutions
MEMORY Issues – Alerts and Solutions
DB STATE Issues – Alerts and Solutions
Network Issues – Alerts and Optimizations
Using PERFMON Counters and Filters
Replication and Mirroring Thresholds
Activity Monitor and Deadlock Issues
DB Engine Configurations and PBM
Working with SQL Engine Properties
DTC and Remote Connection Properties
Query Governor and Timeout Options
Database State Properties and Options
Policy Based Management (PBM) Usage
Database Facets and Conditions
Scheduling and Reporting Policies
Resource Governor & Browser Tools
Server Properties and Configurations
Server Updates and Upgrades
Establish Downtime
Sample Notifications
PRIOR Maintenance activities
Precautions
Applying Patch/hot fix and Service Packs
Verifying Updates and SmokeTest Options
Understanding Upgrade Advisor Issues
Server Upgrades and Smoke Tests
Rollback Service Packs and Precautions
System Database Rebuilds
Windows and SQL Cluster Configurations
Windows and SQL Licensing Options
Domain Controller and Active Directory
Windows and Network Configuration
PING tests and DTC Configurations
QUORUM settings and SAN Settings
SQL Server Cluster Installation
SQL Group and RAID Configurations
Virtual SAN Setup and Verification
SQL Cluster Issues and Troubleshooting
SQL Cluster Node Installation
QUORUM Check and DTC Settings
Connection Issues and Drains
SQL Cluster Configurations and Heartbeat
Security Issues with Solutions
Storage Issues with Solutions
Always-On (AAG) Groups and HA
Practical Considerations For HA.