• Post category:Udemy (Sept 2021)
  • Reading time:19 mins read

Become SQL database certified (MCSA and MTA certification)

What you will learn

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

Description

Microsoft SQL Server continues to be one of the world’s most popular and top databases.  Database is a heart of IT which is needed to stored all critical data and information. 

If you want to become SQL database administrator then this is the course for YOU.

This training courses will provide you all the necessary skills you need to be a successful database administrator and take you to the next level.  Plus, this course will prep you for the SQL Server MCSA certification.

Here is the course outline:

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)
  • MSSQL Data Encryption and type of encryption
  • Transparent Data Encryption (TDE) and configuration
  • Backup and restore of encrypted Databases
  • MS SQL Server Reporting Services (SSRS)
  • MS SQL Server Integration Services (SSIS)

Section 13 – Course Re-cap

  • Commands we have learned

English

Language

Content

Introduction

Introduction

Course overview

Introduction to Database

What is RDBMS ?

What is SQL ?

Top 5 RDBMS using SQL

Introduction to Microsoft SQL Server

Different Editions of Microsoft SQL

Quiz

Download, Install and Configure Windows

Welcome to Download, Install and Configure Windows

MS SQL Server Pre-requisite

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

Download, Install and Configure MS SQL Server

Welcome to Download, Install and Configure Microsoft SQL

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

Database Fundamentals and Design

Welcome to Database Fundamentals and Design

What is Data?

What is Database?

How Data is Stored ?

What is a Table, COLUMN and ROW ?

What is a Key ?

What is Primary Key , Foreign Key, Unique Key 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…

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

Query and Manipulation of Data using SQL

Welcome to Query and Manipulation of Data using SQL Commands


Subscribe to latest coupons on our Telegram channel.

Create TABLE(s) and Temp Table(s)

What Is a View?

SELECT Statement in detail

Operators, Expressions and Conditions

WHERE Clause, ORDER BY Clause , HAVING By Clause, GROUP BY Clause

Select from two tables – JOINS

Different Types of JOINS

What is a Sub Query?

INSERT Statement in detail

UPDATE Statement in detail

DELETE Statement in detail

Difference between DELETE and Truncate Statement

What is a Store Procedure

What is a Function

What is a Trigger

What is an INDEX

Clustered ,NonClustered Indexes and Index Design considerations

Index Fragmentation and Lab for Index

Microsoft SQL Database Administration and System databases

Welcome to Microsoft SQL Database Administration and System Databases

Overview of MSSQL Management Tools

Exploring SQL Server Management Studio (SSMS) (part -1)

Exploring SQL Server Management Studio (SSMS) (part -2)

Exploring SQL Server Configuration Manager

MSSQL System Databases, DO’s and DONT’s of System databases

What is master Database?

What is tempdb Database ?

What is msdb Database ?

Other system Databases (model,distribution)

Deep Dive into MSSQL Working

Welcome to Deep Dive into MSSQL working

What are Pages , Extents, Page Architecture and PFS ?

MSSQL Architecture

MSSQL Database Architecture

Operation and working of transaction log

MSSQL Backup and Restore

welcome to MSSQL Backup and Restore

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

Recovery Models in MSSQL and how that impact backups an restore

Full Backups

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.

MSSQL User Management

Welcome to MS SQL User Management

MSSQL Security Model

MSSQL Server Authentication Modes

Principals (Part-1) : Server Logins, Server Roles

Principals (Part-2) : Database Users, Database Roles

Permissions (GRANT , DENY , REVOKE)

MSSQL Server Agent Management

Welcome to Overview of MSSQL Server Agent

SQL Server Agent Introduction

SQL Server Agent Jobs and Schedules

Agent Alerts ,Operators , Database Mail and Activity Monitor

Advanced SQL Server Administration Topics

Introduction to Advanced Topics in MSSQL Server Administration

High Availability and its Types

What Is Replication and Transactional Replication Setup

Transactional Replication Setup (LAB)

Log Shipping and Configuring Log Shipping

SQL Server Encryption and Encryption Types

Transparent Data Encryption (TDE) , Configuration and Restore of TDE Databases

Course Recap

SQL Queries We Have Learned

Don’t Give Up!

Congratulations