SQL Server DBA Online Training Modules Overview

SQL Server DBA 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 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 its use? blocking the DML/DDL operations, commit & rollback options and much more is learned 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

 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

Practice Test & Interview Questions

SQL Server DBA project

Course Content

Core topics of SQL SERVER PERFORMANCE TUNING Online Course

• SQL Performance Tuning Overview
• The Performance-Tuning Process
• Performance vs. Price
• Performance Baseline
• Where to Focus Efforts
• SQL Server Performance Killers

System Performance Analysis

• Performance Monitor Tool
• Dynamic Management Views
• Hardware Resource Bottlenecks
• Memory Bottleneck Analysis
• Memory Bottleneck Resolutions
• Disk Bottleneck Analysis
• Disk Bottleneck Resolutions
• Processor Bottleneck Analysis
• Processor Bottleneck Resolutions
• Network Bottleneck Analysis
• Network Bottleneck Resolutions
• SQL Server Overall Performance
• Creating a Baseline
• System Behavior Analysis Against Baseline

SQL Query Performance Analysis

• Extended Events Wizard
• Extended Events Automation
• Extended Events Recommendations
• Other Query Performance Metrics Methods
• Costly Queries
• Execution Plans

Index Analysis

• What Is an Index?
• Index Design Recommendations
• Clustered Indexes
• Nonclustered Indexes
• Clustered vs. Nonclustered Indexes
• Advanced Indexing Techniques
• ColumnStore Indexes
• Special Index Types
• Additional Characteristics of Indexes

Database Engine Tuning Advisor

• Database Engine Tuning Advisor Mechanisms
• Database Engine Tuning Advisor Examples
• Database Engine Tuning Advisor Limitations

Bookmark Lookup Analysis

• Purpose of Bookmark Lookups
• Drawbacks of Bookmark Lookups
• Analyzing the Cause of a Bookmark Lookup
• Resolving Bookmark Lookups

Statistics Analysis

• The Role of Statistics in Query Optimization
• Statistics on an Indexed Column
• Statistics on a Nonindexed Column
• Analyzing Statistics
• Statistics Maintenance
• Analyzing the Effectiveness of Statistics for a Query
• Recommendations

Fragmentation Analysis

• Causes of Fragmentation
• Fragmentation Overhead
• Analyzing the Amount of Fragmentation
• Fragmentation Resolutions
• Significance of the Fill Factor
• Automatic Maintenance

Execution Plan Cache Analysis

• Execution Plan Generation
• Execution Plan Caching
• Components of the Execution Plan
• Aging of the Execution Plan
• Analyzing the Execution Plan Cache
• Execution Plan Reuse
• Query Plan Hash and Query Hash
• Execution Plan Cache Recommendations

Query Recompilation

• Benefits and Drawbacks of Recompilation
• Identifying the Statement Causing Recompilation
• Analyzing Causes of Recompilation
• Avoiding Recompilations

Query Design Analysis

• Query Design Recommendations
• Operating on Small Result Sets
• Using Indexes Effectively
• Avoiding Optimizer Hints
• Using Domain and Referential Integrity
• Avoiding Resource-Intensive Queries
• Reducing the Number of Network Round-Trips
• Reducing the Transaction Cost

Blocking Analysis

• Blocking Fundamentals
• Understanding Blocking
• Locks
• Isolation Levels
• Effect of Indexes on Locking
• Capturing Blocking Information
• Blocking Resolutions
• Recommendations to Reduce Blocking
• Automation to Detect and Collect Blocking Information

Deadlock Analysis

• Deadlock Fundamentals
• Using Error Handling to Catch a Deadlock
• Deadlock Analysis
• Avoiding Deadlocks

Cursor Cost Analysis

• Cursor Fundamentals
• Cursor Cost Comparison
• Default Result Set
• Analyzing SQL Server Overhead with Cursors
• Cursor Recommendations

Database Performance Testing

Performance Testing Overview

• Capturing Data with the Server Side Trace
• Distributed Replay for Database Testing
• Summary and SQL Server Optimization Checklist
• Conclusion

Job Opportunities in SQL Server DBA

Who wouldn’t prefer a job that assures a fast paced global career, higher than average perks? The job opportunities in the domain of SQL Server DBA are quite plenty. And with the increase in the colossal demand for the qualified experts across the top industries, more & more number of aspirants are planning towards securing their career in this domain. And also in response to the whooping salary packages for the certified professionals in this domain most of the professionals who are working in other prominent technologies are working towards making a career transition into this domain. SQL Server DBA Online Training by SacrosTek Systems will set you on the right career path of achieving success in this domain.

SacrosTek Systems offer certification programs for SQL Server DBA. Certificates are issues on successful completion of the course and the assessment examination. Students are requested to participate in the real-time project program to get first-hand experience on the usage and application of the SQL Server DBA. The real-time projects are designed by our team of industry experts to help students get best possible exposure to the SQL Server DBA and its applications.