OTHER DATABASES

SQL Server Database Administration


RDBMS-Overview
  • Database systems Overview
  • Relational Database Systems
  • Normalization
  • CODD Rules
  • ACID Properties
  • Database Design
SQL Server- Overview
  • What is SQL SERVER?
  • Usage of SQL Server
  • SQL Server Version
  • SQL Server Editions
  • SQL Server Instance
Architecture
  • SQL Server Engine Architecture
  • Storage Engine: Buffer Manager, Lock Manager, File Manager, Transaction Services
  • Query Processor: Parser, Optimizer, SQL Manager, Database Manager, Query Executor
  • SQLOS API, Protocols, External Components,
  • Transaction Log Architecture
  • VLFs, checkpoint, LSN, Active – In active Logs
  • Database Architecture
  • Pages, Different Types of pages, Data Files, Log files
SQL Server Installation
  • Installation checklist
  • Step by step Installation & Configuration
  • System DB’s , User DB’s
  • Post Configuration (Memory Settings, Tempdb Settings, Port, DB configuration, Maintenance jobs
  • Automatic Installation
  • SQL Server Clients –SSMS, SQL CMD
  • SSMS Overview
  • SQL Server Configuration Manager
Database and Tables
  • Create Database, Schemas
  • Drop Database, Schemas
  • File Groups, File growth, File Shrink
  • Partition
  • Capacity planning
  • User Tables, system Tables, Temporary Tables
  • DML, DDL, DCL
  • Constraints, Data Types, Different types of Keys
  • System objects
SQL Queries
  • Understanding of TSQL Language
  • Select, where, Order by , union
  • Different Types of joins
  • Operators: Logical, Compound, Comparison, Bitwise and Arithmetic
  • Tsql Query and Sub Queries
Views, Stored Procedures , Triggers, Cursors ( Respective to Core DBA activity)
  • Create, update and Drop View
  • Different Types Of view
  • Create user Stored Procedures, system SPs , Xps
  • When to compile and recompile – Effects of recompiling SP
  • What is Cursors and Triggers
  • How and when it could be used
  • Place Views, Cursors, Triggers, queries, sub-queries in Stored Procedure
Database Recovery Model & Database Isolation level
  • Different Types of Recovery Model
  • How choose Recovery Model
  • What is isolation level?
  • Different types of isolation level
  • How to choose isolation level
  • Impact of Isolation level
Security
  • Authentication Modes
  • Fixed Server Roles, Database Roles
  • Grant and Revoke Permissions
  • Logins, Users, Credentials, Schemas
  • Orphan Users
  • Security Audit
Backups
  • LSN – Related to Backups
  • Different Types of Backups
  • Backup – Internals on how backup actually runs
  • Perform Backup using TSQL, SSMS
  • Compressed Backups
  • Schedule Backups using Jobs
  • Verifying Backups
  • Overview on external backups Tools
Restore and Recovery
  • Automatic Recovery
  • Recovery / No Recovery/Standby
  • Restore using Tsql , SSMS
  • Point in time Restore
  • Restoring System Databases
  • Restore Database, Restore Logs
SQL Server Jobs, Alert , Mail setup
  • Create SQL Server jobs
  • Automate tasks using SQL Server jobs
  • Common Automation tasks using SQL jobs
  • Setting and Notify using Alert
  • Setting and Configuring SQL Mail
  • Sending Automatic Mail , reports from SQL Server
Database migration /Upgrades
  • Backup & Restore
  • Detach & Attach
  • Export & Import
  • Compatibility level
Server Upgrade / Instance Upgrade
  • Pre-requisites for Upgrade
  • Upgrade Advisor
  • Different Types of Upgrade – In place & Side-by-Side
  • Service Pack Upgrades and Apply hot fixes
  • Checklist for Upgrade
  • Post check
Indexes and Statistics
  • What are indexes
  • B –Tree Structure
  • Clustered , Non-Clustered and Heap Index
  • Column Store Indexes
  • Index Internals –How index works
  • Fragmentation in indexes
  • Fill factor in indexes
  • How Index increases Performance
  • Page Split, Key Lookup, RID Lookup
  • What is Statistics
  • Update Statistics
Performance trouble shooting
  • Locks, Latches, Blocks, Dead locks
  • Memory and CPU Bottle Necks
  • Disk Latency
  • Different Wait types
  • Identify Long Running Queries
  • How to read Execution Plans
  • Different Operators in Execution Plans
  • Table Scan, Index Scan, Index Seek
  • Estimated Vs Actual Execution Plans
  • How identify Bad Execution Plan
  • DMVs to work on Performance Troubleshooting
  • Trouble Shooting Methodology
Tools for Performance Trouble shooting
  • Activity Monitor
  • SQL Server Profiler
  • Extended Events
SQL Server Optimization
  • Server Level Optimizations
  • Database Level Optimizations
  • Tempdb Optimization
  • Data Partition
  • Resource Governor
  • Query Optimization
  • Resource Optimization
  • OS level Optimization
  • Maintenance Plan (Rebuild, Reorg, update Stats, Check DB, Checkalloc)
  • Optimize for Unknown
  • Plan Caching and Recompilation
New features in SQL Server 2014 and 2016
  • Stretch Database
  • Query Store
  • In-Memory Tables
  • Buffer Pool Extension
  • Always On Enhancements
  • Updatable Coulmnstore Indexes
  • Backup Encryption
SQL Server Mirroring
  • Types of mirroring
  • How to configure Mirroring
  • How Mirroring Works
  • Mirroring Monitor
  • Trouble Shooting Mirroring Issues
SQL Server Always On
  • Always on Architecture
  • Prerequisites for Always on
  • Quorum in always on
  • Configuring Always on
  • Availability Replica and Readable Secondary Replica
  • Manual /Automatic Replica Role Change
  • Trouble Shoot Always on Issues
SQL Server Clustering
  • What is Clustering?
  • How to Setup Clustering
  • Quorum Settings
  • Active and Passive Nodes
  • Heart beat in Clusters
  • Failover in clustering
Replication
  • What is replication?
  • Different types of Replication
  • Configure Replication
  • Trouble Shoot Replication
Other SQL Services
  • Overview of SQL Server Integration Services
  • Overview of SQL Server Reporting Services
  • Overview on Basic Power shell related to Database Administration
  • Tsql Queries , DMVs used in DBA day to day Operations

Next Course

RELATED VIDEOS

Example of Embedding YouTube Video inside Bootstrap Modal

Enrolled Students

8565

Education Awards

120

Certified Teachers

193

Library and Labs

150