Call :+91 8055223360 

MS SQL SERVER DBA TRAINING IN THRISSUR

2678 Reviews
4.8/5
2765 Learners

Overview

A database administrator (DBA) directs or performs all activities related to maintaining a successful database environment. Responsibilities include designing, implementing, and maintaining the database system; establishing policies and procedures pertaining to the management, security, maintenance, and use of the database management system; and training employees in database management and use. DBA is also responsible for DBA Activities.

Course Duration: 50 hrs

Watch
INTRO VIDEO

Why Radical Technologies

Check Batch Schedulings

Course Curriculum

Course description

 SQL Server Training by  Certified Instructors

Trainers : 12 years Exp in  Database Administration

Duration of Training: 50 hrs

Weekend & weekday Batches Available

Classroom | Online | Corporate Trainings

Pre-requisites

There are no formal prerequisites for this course; however, previous operating system administration experience will be very beneficial.

Course Content

SQL Server & T-SQL installation, Architecture, DB

Job Roles, Installation

Introduction to Databases, DBMS
Microsoft SQL Server : Advantages, Use
Versions and Editions of SQL Server
SQL DBA Job Roles, Responsibilities
Routine Maintenance DBA Activities
Emergency SQL DBA Activities
SQL Server Prerequisites : S/W, H/W
SQL Server 2019/2017/2016 Installation
Default Instance, Named Instances
Port Numbers, Instance Differences
Service and Service Account Use
Authentication Modes and Logins
Firewall Configuration in Real-time
SQLServr.exe and SQLBrowser.exe

SSMS Tool, SQL Basics

SSMS Tool Installation, Connections
SQL Server Management Studio
Single Server, Multi Server Connections
System Databases: Master and Model
MSDB, TempDB, Resource Databases
SQL and T-SQL : Basic Differences
DDL, DML, SELECT Statements
Using GUI and SQL Scripts in SSMS
Creating Databases : Files [MDF, LDF]
Creating Tables in SQL Server
Data Storage in Tables : Inserts
SELECT : Data Retrieval Statement
Table Scans. LIVE QUERY STATISTICS
Limitations with SSMS GUI Options

SQL for DBAs

Creating Databases in SQL Server
Database Connections and Usage
Creating Tables and Data Storage
Data Inserts and SELECT Statement
WHERE Conditions and Keywords
IN Operator and NOT IN Operator
Between, Not Between Operators
LIKE and NOT LIKE Operators
UPDATE Statement & Conditions
DELETE & TRUNCATE Statements
Logged and Non-Logged Operations
Table Structure Modifications
ADD, ALTER and DROP Columns
Aliases and Batch Statements

Schemas, Temporary Tables

Schemas : Group Tables in Database
Schemas : Security Management Object
Creating Schemas & Batch Concept
Using Schemas for Table Creation
Data Storage in Tables with Schemas
Data Retrieval and Usage with Schemas
Table Migrations across Schemas
Import and Export Wizard in SSMS
Data Imports with Excel File Data
Performing Bulk Operations in SSMS
Temporary Tables : Real-time Use
Local and Global Temporary Tables
# and ## Prefix, Scope of Usage
Session Level, Connection Level Use

Constraints, Indexes

Constraints and Keys – Data Integrity
NULL, NOT NULL Property on Tables
UNIQUE KEY Constraints: Importance
PRIMARY KEY Constraint: Importance
FOREIGN KEY Constraint: Importance
REFERENCES, CHECK and DEFAULT
Candidate Keys and Identity Property
Database Diagrams and ER Models
Relationships Verification and Links
Indexes : Basic Types and Creation
Index Sort Options, Search Advantages
Clustered and Non Clustered Indexes
Primary Key and Unique Key Indexes
Need for Indexes – working with Keys

JOINS & AUDITS

JOINS – Table Comparisons Queries
INNER JOINS For Matching Data
OUTER JOINS For (non) Match Data
Left Outer Joins with Example Queries
Right Outer Joins with Example Queries
FULL Outer Joins – Real Time Scenarios
Join Queries with “ON” Conditions
Join Unrelated Tables in SQL Server
NULL, IS NULL Operators in Joins
CROSS JOIN and CROSS APPLY
CROSS JOIN Versus CROSS APPLY
One-way & Two Way Data Comparisons
Important Join Queries in T-SQL
Join Options: Merge, Loop, Hash

View, Procedures, UDF Basics

Views : Types, Usage in Real-time
System Predefined Views and Audits
Listing Databases, Tables, Schemas
Functions : Types, Usage in Real-time
Scalar, Inline and Multi-Line Functions
System Predefined Functions, Audits
DBId, DBName, ObjectID, ObjectName
Date and Time Functions : Getdate()
Variables & Parameters in SQL Server
Procedures : Types, Usage in Real-time
User & System Predefined Procedures
Parameters and Dynamic SQL Queries
Sp_help, Sp_helpdb and sp_helptext
sp_pkeys, sp_rename and sp_help
sp_recompile, Performance Benefits
System Objects for Metadata Access

Transactions, Linked Servers

Linked Servers and Real-time Usage
Creating Linked Servers in SQL Server
Security Options and Access Options
Data Access, RPC and RPC Out Settings
Automations with Triggers: DML,DDL
INSERTED, DELETED Memory Tables
Table Data Replication using Triggers
Transactions : Types, ACID Properties
Transaction Types and AutoCommit
EXPLICIT & IMPLICIT Transactions
COMMIT and ROLLBACK Statements
SQL Server Configuration Manager Tool
Services : Start and Stop Options
Network Configurations and Protocols
SQL Server Instance: Ports, Aliases

Server, DB Architecture

Server Architecture and Protocols
Database Engine and Query Processor
Parser, Optimizer, SQL & DB Manager
Storage Engine Components, SQL OS
File Manager and Database Files
Transaction Services, Buffer Manager
Lock Manager, IO Manager, MDAC
CLR, WAL, Lazy Writer, Checkpoint
Database Architecture – Data Files
Database Architecture – Log Files
Primary (mdf), Secondary Files (ndf)
Filegroups Usage, ReadOnly Filegroups
Database Files : Size and Location
Pages, Extents. Uniform, Mixed Extents
Transaction Log File [LDF], LSN, VLF

SQL DBA – Backup-Restores, Jobs, Performance Tuning, Security

BACKUPS – DB, Filegroup, File

Database Backups, Filegroup Backups
Log File Backups and Log Truncations
COPY_ONLY Backups and Real-time Use
Mirror Backups and Split Backups
Partial Backups – ReadOnly Filegroups
Format, Compression and Checksum
Backup Verification, RetainDays, Stats
ContinueOnError and Backup Scripts
GUI and Script Backups: Differences
Backup History Tables in MSDB – Joins
Backup Audits. HOT and COLD Backups
Backup Devices – Creation and Usage
Using Backup Devices – Advantages
Common Errors and Solutions

RESTORES & DB RECOVERY

Restore Phases – COPY, REDO, UNDO
RECOVERY, NORECOVERY Options
STANDBY and REPLACE in Restores
File, File Group & Metadata Restores
Backup Verifications using GUI, Scripts
VERIFYONLY : Backup Verification
STATS, UNLOAD, STOPAT and INIT
PARTIAL / PIECEMEAL Restores – Use
Tail Log Backup Usage in Real-time
Restores using GUI and T-SQL Scripts
MOVE Options for File Level Restores
Point-In-Time Restore, Checkpoint LSN
Standby Restores and Read-Only State
Common Errors and Solutions

JOBS, MAINTENANCE PLANS

SQL Server Agent Service & Agent XPs
SQL Agent Jobs – GUI, Script Creations
Job Steps – Creation, Edits and Parse
Job Executions, Disable/Enable Options
Job History Purge. Job Activity Monitor
Database Maintenance – Backup Jobs
Scheduling Database Maintenance Plans
Automated Job Creations using DMPs
Backup Cleanup & History Cleanup Jobs
Backup Strategies For Minimal Data Loss
Backup Options: Block Size, Transfer Size
DB Mail Configurations and Alert System
DB Mail Profiles, SMTP Email Accounts
Operators : Creation, Job Notifications

SECURITY MANAGEMENT

Authentication Types & Modifications
Windows Logins & SQL Server Logins
Logins – Users Mapping, DB Access
Server Roles & Database Roles – Usage
Object, Column and Schema Security
GRANT, WITH GRANT, DENY, REVOKE
CONTROL, OWNERSHIP, Authorization
Data Encryption: Keys and Certificates
Data Encryption with Stored Procedures
Job Security : Credentials and Proxies
Using Proxies for SSIS Jobs, Repl Jobs
Security Scripts and Documentation
DMVs for Security Audits, Orphan Users
Login, User, Server Principal Audits

MIGRATIONS & SLA-OLA

CDW : Copy Database Wizard @ SSMS
Database Detach and Attach Options
SMO Method and Database Scripting
CDW SSIS Packages, SSIS Proxies Use
Scheduling Database Migration Jobs
Detecting and Resolving Orphan Users
Containment Databases Authentication
SLA and OLA Process, Ticketing Tools
Immediate, High, Normal Priorities
Impact, Urgency and SLA Metrics
Licensing and Pricing Options
Core Based Licenses. Device CALs
User CALs and Multiplexing Concept
Versions, Editions Comparisons

Tuning 1 – Audits, Indexes

Audit Long Running Queries : DMV, DMF
Activity Monitor Tool, Server Dashboards
Logical I/O, Physical I/O, Database I/O
Recent Expensive Queries, Wait Time
Active Expensive Queries, Statistics
Plan Handle, Execution Time – Audits
CPU, IO, Memory Consumption Reports
Indexes: Architecture and Index Types
B Tree Structure, IAM Page [Root]
Clustered & NonClustered Indexes
Included, Columnstore, Online
Filtered, Covering, Indexed Views
Fill Factor and Pad Index Options
Query Store – Settings and Advantages

Tuning 2 – INDEX MANAGEMENT

PARTITIONS : Advantages, Performance
Partition Functions & Partition Schemes
Partitioning Un-partitioned Tables: GUI
Partition Compression : ROW and PAGE
Auditing Table Partitioned Structures
Statistics : Purpose, Auto Creation
Statistics : Audits and Updates
Working with Indexes and Partitions
Internal and External Fragmentation
Index Rebuilding Process and Audits
Database Maintenance Plans Jobs
Last Used, Page Count, Fragmentation
Index Page Count and Index Condition
Degree Of Parallelism [DOP] Settings
Resumable Indexes: ONLINE, RESUME
PAUSE & RESUME in Index Rebuilds

Tuning 3 – Tuning Tools, Locks

Tuning Tools : Workload Files, Trace Files
Profiler Tuning Template, SP Events
DTA, Profiler Trace : Recommendations
Perfmon Tool Counters, Real-time Tracking
Execution Plan Analysis and Internals
Query Costs : IO Cost and CPU Cost
Query Costs: SubTree & Operator Cost
NUMA Nodes, Processor, IO Affinity
Thread Count, Degree of Parallelism
LOCKS : Types and Isolation Levels
S, X, IX,U, MD, Sch-M and Sch-S
Lock Audits : SP_WHO2 and SP_LOCK
sysprocesses and Lock Waits : Audits
Deadlock Audits and Deadlock Graphs
XDL Files and Deadlocks Prevention

Health Checks, Issues, Solutions

Alerts : Creation and Notifications
DB Suspect Event Alerts (023)
Important Perfmon Counters, Alerts
Log Space, Memory, Tempdb Alerts
Scheduling Alerts & Notifications
DBCC CHECKDB : DB Health Checks
Allocation Errors, Consistency Errors
DBCC ShowContig, Extent Fragmentation
Trace Flags and EstimateOnly
DBCC Page: GAM, SGAM and PFS
Consistency Errors : Cause & Solutions
Allocation Errors : Cause and Solutions
Log Space Issues and Log Rebuilds
Memory & TempDB Issues, Solutions
DBCC ShrinkDB and Page Restores

SQL DBA PROJECT – Level 1

Audit Login Failures : Server Logs
Monitoring Connectivity Issues
Database Refresh and MSDTC
Adhoc Memory Dump Files
PLE (Page Life Expectancy) Issues
Object Refresh and Recompilations
Server Registrations and Operations
Lock Monitoring Operations
Index Management Options
Open Transactions, Blocking
Metadata Sync-up Issues
Stored Procedure Recompilations
Backup and HA-DR Strategies
Db Restores and DB Repairs
Health Checks, Issues, Solutions

Training Options

Live Online Training

  • Highly practical oriented training
  • Installation of Software On your System
  • 24/7 Email and Phone Support
  • 100% Placement Assistance until you get placed
  • Global Certification Preparation
  • Trainer Student Interactive Portal
  • Assignments and Projects Guided by Mentors
  • And Many More Features

Course completion certificate and Global Certifications are part of our all Master Program

Live Classroom Training

  • Weekend / Weekdays / Morning / Evening Batches
  • 80:20 Practical and Theory Ratio
  • Real-life Case Studies
  • Easy Coverup if you missed any sessions
  • PSI | Kryterion | Redhat Test Centers
  • Life Time Video Classroom Access ( coming soon )
  • Resume Preparations and Mock Interviews
  • And Many More Features

Course completion certificate and Global Certifications are part of our all Master Program

Exam & Certification

Course Reviews

I had a wonderful experience in Radical technologies where i did training in Hadoop development under the guidance of Shanit Sir. He started from the very basic and covered and shared everything he knew in this field. He was brilliant and had a lot of experience in this field. We did hands on for every topic we covered, and that’s the most important thing because honestly theoretical knowledge cannot land you a job.
Rohit Agrawal Hadoop
I have recently completed Linux course under Anand Sir and can assuredly say that it is definitely the best Linux course in Pune. Since most of the Linux courses from other sources are strictly focused on clearing the certification, they will not provide an insight into real-world server administration, but that is not the case with Anand Sir’s course. Anand Sir being an experienced IT infrastructure professional has an excellent understanding of how a data center works and all these information is seamlessly integrated into his classes.
Manu Sunil Linux
I had undergone oracle DBA course under Chetan sir’s Guidance an it was a very good learning experience overall since they not only provide us with theoretical knowledge but also conduct lot of practical sessions which are really fruitful and also the way of teaching is very fine clear and crisp which is easier to understand , overall I had a great time for around 2 months , they really train you well.also make it a point to clear all your doubts and provide you with clear and in-depth concepts hence hope to join sometime again
Reema banerjee Oracle DBA
I have completed Oracle DBA 11g from Radical technology pune. Excellent trainer (chetna gupta ). The trainer kept the energy level up and kept us interested throughout. Very practical, hands on experience. Gave us real-time examples, excellent tips and hints. It was a great experience with Radical technologies.
Mrudul Bhokare Oracle DBA
Linux learning with Anand sir is truly different experience… I don’t have any idea about Linux and system but Anand sir taught with scratch…He has a great knowledge and the best trainer…he can solve all your queries related to Linux in very simple way and giving nice examples… 100 🌟 to Anand Sir.
Harsh Singh Parihar Linux
Prev
Next

Why we are the best Radical Technologies

Radical Technologies is truly progressing and offer best possible services. And recognition towards Radical Technologies is increasing steeply as the demand is growing rapidly.

Creative

0%

Innovative

0%

Student Friendly

0%

Practical Oriented

0%

Valued Certification

0%

Training FAQs

Similar Courses

ENQUIRE NOW











    [recaptcha]