Skip to main content
Our Tech Ideas

Complete SQL Server DBA Tutorials

Complete SQL Server DBA Tutorial

MS SQL Server DBA Training

Welcome to Complete SQL Server DBA Tutorials ! This course is designed to help beginners and professionals learn the fundamentals of SQL Server DBA (Database Administrator). We will cover everything from basic concepts to advanced features, using real-life examples to enhance your understanding of database administration. Join us as we explore the practical applications of SQL Server DBA.

Overview

Database Administrators (DBAs) are responsible for the design, implementation, support and maintenance of computerized databases in today’s organizations. The role also includes architecting, building and scaling databases for future data growth and capacity. They are also responsible for security, performance and availability of data to users and customers. .

All the above tasks are performed with the help of a Database Management System (DBMS) and among the most widely used DBMS across the world today is the Microsoft SQL Server Data Platform.

Topics discussed

SQL Server Database Administration

  1. Database Administration
  2. DBA Responsibilities
  3. DBA Roles / Daily Activities

SQL Server Architecture

  1. Components of SQL Server
  2. SQL Server Network Interface (SNI)
  3. Command Parser
  4. Optimizer
  5. Query Executor
  6. Access Methods
  7. Buffer Manager
  8. Plan Cache
  9. Data Cache
  10. SQL Server Architecture Diagram
  11. Steps in executing a query
  12. Protocols available in SQL Server

SQL Server Storage Architecture

  1. Storage Architecture
  2. Page Architecture
  3. The page consists of 3 sections
  4. Types of Pages
  5. SQL Server Extents
  6. SQL Server File
  7. Database
  8. File Groups
  9. Storage Structure

SQL Server Database Architecture

  1. Database architecture
  2. Transactions
  3. ACID Properties in SQL Server
  4. Database Architecture
  5. SQL Server Buffer
  6. How Buffer Works
  7. SQL Server Checkpoint Process
  8. Advantages of the checkpoint in SQL Server,
  9. SQL Server Recovery Process
  10. Lazy Writer in SQL Server
  11. Dirty Pages

SQL Server Transaction Log Architecture

  1. Transaction Log Architecture
  2. WAL (Write Ahead Logging)
  3. Inactive Log
  4. SQL Server Log Architecture
  5. Advantages of T-Log
  6. Enhancement in SQL Server (Indirect checkpoint)

SQL Server installation

  1. Installation
  2. SQL Server Versions & Editions
  3. SQL Server Core Production Editions
  4. Windows Operating System Editions
  5. Binary File Location
  6. Collation Settings
  7. SQL Server Authentication
  8. Windows Authentication
  9. Mixed mode Authentication
  10. SQL Server Service Accounts
  11. SQL Server Service
  12. SQL Agent Service
  13. SQL Browser Service
  14. Full Text Service
  15. MSDTC
  16. Installing SQL SERVER 2012
  17. SQL Server 2012 Installation Process
  18. SQL Server 2012 Setup
  19. SQL Server Post Installation Steps

SQL Server Databases

  1. Types of Databases in SQL Server:
  2. System Databases:
  3. SQL Server Master Database:
  4. SQL Server Model Database
  5. MSDB
  6. SQL Server Temp DB
  7. SQL Server Resource DB
  8. Creating Databases in SQL Server
  9. SQL Server Database Properties
  10. To create database using T-Sql

SQL Server Backups

  1. SQL Server Backups
  2. Backup Objectives:
  3. SQL Server Recovery Models
  4. Simple Recovery Model
  5. Full Recovery Model
  6. Bulk-Logged
  7. SQL Server Backup Types
  8. SQL Server Full Backup
  9. SQL Server Differential Backup
  10. SQL Server T-Log Backup
  11. SQL Server Partial Backup
  12. Advanced Types of Backups in SQL Server
  13. SQL Server Copy Only Backup, Compressed Backup, Split Backup &Tail-Log Backup
  14. Situations of Tail-Log Backup
  15. SQL Server Backup Options
  16. SQL Server Backup History
  17. SQL Server Backup Permissions
  18. Backup History

SQL Server Restoration Tutorial

  1. Situations for Restoration
  2. Pre Restoration in SQL Server
  3. Restore File List Only
  4. Actual Restoration in SQL Server
  5. Post Restoration in SQL Server
  6. Restoring Backups using SSMS (GUI) in SQL Server
  7. SQL Server Restoration Options
  8. 1. With Replace – SQL Server Restoration Option
  9. 2. With Move – SQL Server Restoration Option
  10. 3. With Stop-at – SQL Server Restoration Option
  11. SQL Server Restoration Situations
  12. States and Steps of Restoration
  13. SQL Server Roll Forward, Roll Back & SQL Server No-recovery
  14. Restoring Enhancements in SQL Server 2012
  15. SQL Server Page Restore

SQL Server Security

  1. Levels of SQL Server Security
  2. SQL Server Security Processes
  3. Authentication & Authorization
  4. Types of Authorization Roles
  5. Database roles, Object level
  6. Encryption & Transparent data encryption(TDE)

SQL Server AUTOMATION Tutorial

  1. JOBS & How to create a job
  2. Post Job Steps
  3. Troubleshooting the Job Failures

SQL Server Migration

  1. What is SQL Server Migration?
  2. SQL Server Migration Scenarios
  3. Steps Involved in Migration Process
  4. Moving System Databases, Migrate Tables & Migrate Stored Procedures

SQL Server Upgradation

  1. What is SQL Server Upgradation?
  2. Microsoft sql server 2012 upgrade advisor
  3. Steps in installing upgrade advisor
  4. Types of upgradation

SQL Server Log shipping

  1. What is Log Shipping in SQL Server DBA?
  2. Log shipping Architecture
  3. Components of log shipping
  4. Configuring log shipping
  5. Failover steps in log shipping:
  6. Advantages and Disadvantages
  7. TUF file in Log shipping

to be continued..