Microsoft SQL Server Administration

• By the end of this course you will be a professional SQL database administrator and be able to apply for SQL jobs
• Administration of SQL database through Microsoft Windows
• You will learn 150+ SQL queries
• You will be able to troubleshoot everyday SQL related issues
• You will manage SQL servers in a corporate environment
• You will write basic to advance SQL queries
• Troubleshoot everyday SQL database related issues
• Know how to answer specific business questions by using SQL’s aggregate functions
• Students will be able to run reports for management so they can make financial decisions

Section 1 - Introduction

• Introduction to Database

• RDBMS

• What is SQL?

• Top 5 RDBMS

• Introduction to MS SQL Server

• Different editions of MS SQL

Section 2 - Download, Install and Configure Windows

• MS SQL Server prerequisite

• Lab setup

• What is virtualization?

• Download and Install VMWare Workstation Player

• Optional - Download and Install Oracle Virtualbox

• Create a Virtual Machine

• Download and Install Windows Server

• Virtual Machine Management

Section 3 - Download, Install and Configure MS SQL Server

• Prerequisites for MSSQL Installation

• Download MSSQL and SQL Server Management Studio

• Install and Configure MSSQL

• Install SQL Server Management Studio

• Download and Install AdventureWorks Database

Section 4 - Database Fundamentals and Design

• What is Data and Database?

• How is data stored?

• What is a Table, COLUMN and ROW ?

• What is a key? primary, foreign, unique keys etc.

• What is Relational Database and Relational Database Management System (RDBMS)?

• What is a Transaction and ACID properties?

• Database Normalization AND Different forms of Database Normalization

• Create your first Database, Table and Populate table with data…

Section 5 - Introduction to SQL Commands

• Welcome to Basic SQL Commands

• What is a SQL Statement and types of SQL statements

• DML Statement with examples

• DDL Statements with examples

• DCL Statement with examples

• TCL Statement with examples

Section 6 - Query and Manipulation of Data using SQL

• Create TABLE(s) and Temp Table(s)

• What Is a View?

• SELECT Statement in detail

• Operators, Expressions and Conditions

• WHERE Clause, ORDER BY, HAVING BY, GROUP BY Clause

• Select from two tables – JOINS

• Different Types of JOINS

• What is a Sub Query?

• INSERT, UPDATE, DELETE and Truncate Statements

• What is a Store Procedure?

• Function, Trigger and INDEX

• Clustered ,Non-clustered Indexes and Index Design considerations

• Index Fragmentation and Lab for Index

Section 7 - Microsoft SQL Database Administration

• Overview of MSSQL Management Tools

• Exploring SQL Server Management Studio (SSMS)

• Exploring SQL Server Configuration Manager

• MSSQL System Databases, DO's and DONT's of System databases

• What is master, etmpdb, msdb and other system databases?

Section 8 - Deep Dive into MSSQL Working

• What are Pages , Extents, Page Architecture and PFS?

• MSSQL Architecture

• MSSQL Database Architecture

• Operation and working of transaction log

• Configuring user Database

• Best practices while creating user Database

Section 9 - MSSQL Backup and Restore

• Backup of a Database, Importance for DBA and Different Media used for Backups

• Recovery Models in MSSQL and how that impact backups an restore

• Full and differential backups

• Transaction Log Backups, Log Backup Chain and Tail-log Backup

• Lab for Full, Differential and Transaction Log Backups

• Restore and Recovery Overview

• Full, Differential and Transaction Log database restore

• Point in time restore of a database

• Complete Backup , Restore and Restore in Time Lab

• MS SQL Server DBCC CHECKDB command

• Page Level Restore / Recovery using Full Backups

• Creating Maintenance Plan Backups/Re-indexing etc.

Section 10 - MSSQL User Management

• MSSQL Security Model

• MSSQL Server Authentication Modes

• Server Logins

• Server Roles

• Database Users

• Database Roles

• Permissions (GRANT , DENY, and REVOKE)

Section 11 - MSSQL Server Agent Management

• SQL Server Agent Introduction

• SQL Server Agent Jobs and Schedules

• Agent Alerts

• Operators

• Database Mail

• Activity Monitor

Section 12 - Advanced SQL Server Administration Topics

• High Availability and its Types

• What Is Replication and Transactional Replication

• Transactional Replication Setup (LAB)

• Log Shipping and Configuring Log Shipping

• SQL Server Encryption and Encryption Types

• Transparent Data Encryption (TDE) and configuration