Course Content

Syllabus:

SQL SERVER DBA

DBMS

RDBMS

Introduction to SQL SERVER

Software And Hardware Requirements

Client-Server Architecture

SQLSERVER Installation

1. Requirements

2. 2019 Installation Steps

Services & Checks after installation

Features of SQLSERVER 2019

Server startup process

Tools in SQLSERVER 2019

1. Management Studio (SSMS)

2. BI Tools (SSDT)

3. Configuration Tools

4. Performance Tools (Profiler)

ARCHITECTURE

1. SQLSERVER ARCHITECTURE

2. PROTOCOLS

3. STORAGE STRUCTURE

4. DATABASE ARCHITECTURE

Configuration Settings

1. Memory Settings

2. Processor

3. Connections

4. Database Settings

Transactions in SQL SERVER

1. Commit

2. Rollback

3. Buffer

4. Checkpoint

Databases

1. System Databases

2. User Databases

3. Creating Database

4. Altering Database

5. Data Files

6. Log Files

7. File Groups

Security

1. Windows Authentications

2. SQL Server Authentications

3. Logins and Users

4. Roles

5. Schema

6. Orphaned Users

Recovery Models

1. Full

2. Bulk Logged

3. Simple

4. Non Logged Operations

Backups and Planning

1. Full

2. Differential

3. Transaction Log

4. Files and File Groups

5. Compressed backups

6. Mirrored Backups

7. Split Backup

8. Jobs and Schedules

9. Back up Device

10. Append and Over write

11. Permissions and Settings

12. Enhancements

Restore

1. Complete restoration

2. Point in Time restoration

3. Restore Modes

4. Recover Process

Migrations

1. Steps in Migration

2. SSIS , Package creation

Database Upgrade

1. Types

2. Planning for Upgrade

Replication

1. Publisher

2. Publication

3. Subscriber

4. Subscription

5. Distributor

6. Agents

7. Snap Shot Publication

8. Transactional Publication

9. Merge Publication

10. Replication monitor

11. Jobs

12. Latency Issues

Log Shipping

1. Configuring Log shipping

2. Monitoring logshipping

3. Failover Process

4. Issues

Mirroring

1. Principle Server

2. Mirror Server

3. Witness Server

4. Quorum

5. High Availability

6. High Protection

7. High performance

8. Synchronization

9. Disaster Recovery

10. Monitoring & Issues

ALWAYS ON

SQL SERVER AGENT

1. Jobs

2. Schedules

3. Alerts

4. Operators

5. Error Logs

Database Mail Configuration

Maintenance Plan

1. Through SSMS

2. Through SSIS

PERFORMANCE TUNING

1. Isolation levels

2. ACID Properties

3. Normalization

4. Blocking

5. Dead Locks

6. Indexes

7. DBCC and DMV

8. Stored procedures issues and Recompilation

9. Performance Monitor

10. Profiler

11. Counters

TROUBLE SHOOTING

1. Tempdb Full Issues

2. Log file Full Issues

3. Suspect Mode Issues

4. Page Corruption Issues

5. Tail Log Backup

6. Moving system databases

7. Moving User databases

8. Rebuild Matser Database

9. Orphaned Users

10. SQLSERVER Connection Issues

11. SQLSERVER Service Issues

12. LONG Running Slow Queries

13. Index Fragmentation Issues

14. Tickets, SLA

Sql Browser

REGULAR, WEEKLY AND MONTHLY ACTIVITIES

INTERVIEW STUFF

AZURE SQL INSTANCE

AZURE SQL DATABASE

DIFFERENCE BETWEEN ON-PREMISES SQLSERVER AND AZURE SQL

AZURE DATALAKE BASICS