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
|
