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.

  • PRIVATE
  • 10 Days
  • 0 Units
  • 0 Hrs

Select Your Currency

WOOCS 1.1.8
Drop Us A Query
[contact-form-7 id="5639" title="Drop Us A Query"]
© 2016, ALL RIGHTS RESERVED.
Create an Account