SQL Server DBA Online Training

 >>  SQL Server DBA Online Training

SQL Server DBA Online Training


 (5) | 1500 Ratings


Introduction


SQL Server DBA Online Training Details
Track Regular Track Weekend Track Fast Track
Course Duration 35 Hrs 8 Weekends 5 Days
Hours 1hr/day 2 Hours a day 6 Hours a day
Training Mode Online Classroom Online Classroom Online Classroom
Delivery Instructor Led-Live Instructor Led-Live Instructor Led-Live


Course Curriculum

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

Exam & Certification

0

Course Review

(5)
5 stars
4 stars
3 stars
2 stars
1 stars

Course Curriculum

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

    Click here for Help and Support: info@sacrostectservices.com     For Inquiry Call Us:   +91 996-629-7972(IND)

  +91 996-629-7972(IND)
X

Quick Enquiry

X

Business Enquiry