MS SQL SERVER DBA TRAINING IN PUNE | ONLINE

Duration of Training  :  50 hrs

Batch type  :  Weekdays/Weekends

Mode of Training  :  Classroom/Online/Corporate Training

MS SQL Server DBA Training & Certification in Pune

Highly Experienced Certified Trainer with 10+ yrs Exp. in Industry

Realtime Projects, Scenarios & Assignments

Why Radical Technologies

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

Learn MS SQL Server DBA – Course in Pune with Training, Certification & Guaranteed Job Placement Assistance!

Online Batches Available for the Areas-

Ambegaon Budruk | Aundh | Baner | Bavdhan Khurd | Bavdhan Budruk | Balewadi | Shivajinagar | Bibvewadi | Bhugaon | Bhukum | Dhankawadi | Dhanori | Dhayari | Erandwane | Fursungi | Ghorpadi | Hadapsar | Hingne Khurd | Karve Nagar | Kalas | Katraj | Khadki | Kharadi | Kondhwa | Koregaon Park | Kothrud | Lohagaon | Manjri | Markal | Mohammed Wadi | Mundhwa | Nanded | Parvati (Parvati Hill) | Panmala | Pashan | Pirangut | Shivane | Sus | Undri | Vishrantwadi | Vitthalwadi | Vadgaon Khurd | Vadgaon Budruk | Vadgaon Sheri | Wagholi | Wanwadi | Warje | Yerwada | Akurdi | Bhosari | Chakan | Charholi Budruk | Chikhli | Chimbali | Chinchwad | Dapodi | Dehu Road | Dighi | Dudulgaon | Hinjawadi | Kalewadi | Kasarwadi | Maan | Moshi | Phugewadi | Pimple Gurav | Pimple Nilakh | Pimple Saudagar | Pimpri | Ravet | Rahatani | Sangvi | Talawade | Tathawade | Thergaon | Wakad