Microsoft SQL Server 2017 DBA

Course Duration: 50 Hours

Required Prerequisites

  • Basic Knowledge of Windows O.S.
  • Basic Knowledge of T-SQL

Target Audience

  • SQL Developer who wants to learn Microsoft server 2017 DBA
  • IT Professional who wants to make career in Microsoft SQL Server DBA

Course Highlights

  • Installation of Microsoft SQL Server 2017
  • SQL Server Basic
  • Backups , Restore and Recovery
  • SQL Server Maintenance Activity
  • SQL Server Agent
  • DBCC Commands
  • SQL Server Security
  • SQL Logins
  • SQL Server Tuning
  • Clustered and Non-Cluster indexes
  • SQL Profiler
  • Database Engine Tuning Advisor
  • Activity Monitor
  • Blocking and Deadlock
  • Partition
  • SQL Server Data import export
  • BCP
  • Linked Server
  • SQL Server Agent Logs
  • Upgrade and Migration

Course Content

Section 1: Software Required

Oracle Virtual-Box

Windows Server 2016

SQL Server 2017

SSMS 17.9

AdventureWorks2017 database

Section 2: Installation of Microsoft SQL Server 2017

Install Virtual-Box
Install Windows Server 2016 on Virtual-Box
Install Virtual-Box Guest Additional on Window Server 2016
Install SQL Server 2017 on Windows Server 2016 Virtual-Box
Install SSMS ( SQL Server Management Studio )
Restore AdentureWorks2017 database

Section 3: SQL Server Basic

What is Database ?
What is RDBMS ?
About Microsoft SQL Server Database
Microsoft SQL Server Database Versions
Introduction to System Databases
SQL Server Data and Log Files
Details of Transaction Log
Auto Growth and sizing of transaction Log
Types of Recovery Model – IMP
Virtual Log File
Create a SQL Database
TempDB and its importance
Attach and Detach a database

Section 4: Backups , Restore and Recovery

Introduction to Backups
Full Database Backup
Transactional Log Backups
Differential Backups
Introduction to Restore
Restore database with GUI
Recovery Mode with tail Log backup
Backup using Maintenance Plan
Backup Database using Maintenance Plan

Section 5: SQL Server Maintenance Activity

Maintenance Plan Tasks
Don’t shrink a database
The importance of using SQL Server Agent
Create a simple backup job using SQL Server Agent
The importance of DBCC CheckDB
Using SQL Server Agent with multiple steps
Setting up SQL Database Mail
Setting up alerts Severity Errors from 17 to 25
Central Management with SQL Server Agent

Section 6: SQL Server Security

Introduction to SQL Server Security
Demonstration of Users Logins Roles
Managing security with T-SQL
Selecting Authentication or Mixed Mode security
Accessing SQL Server using SQL Login
Understanding SQL Server Roles
Managing SQL Server Roles via TSQL
SQL Server Roles Demonstration – Done
Managing SQL Server Permissions
Best SQL Server Practice Security

Section 7:SQL Server Tuning Part-1

Clustered Index
Create Non-Cluster Index
Composite Index
Use or Not use Indexes
Deciding factors in creating indexes
using SQL profiler for indexes
Re-organize and Rebuild Indexes
Introduction to SQL Profiler
SQL Profiler longest running Query
Audit Logins
SQL Profile with Index Tuning
SQL Profiler Best Practices
SQL Server Side Trace
Database Engine Tuning Advisor
Statistics
Activity Monitor

Section 8: SQL Server Tuning Part-2

SQL Performance Monitor
SQL Performance hardware issues
SQL Performance Monitoring Hardware CPU
SQL Performance Monitoring Hardware Memory
SQL Performance Monitoring Hardware Disks
SQL Perfmon Schedule
DMV
Block and Locks
BLOCKING
Deadlock
Extended Events
Extended Events via T-SQL
Partition

Section 9: SQL Server Data import export

Database Snapshots
Import Export Data
BCP
Contained Database
Policy Based Management
Linked Server
SQL Server Agent Logs

Section 10: Upgrade and Migration

Backup and Restore
Attach and Detach
Differential Restore for larger databases
In-place upgrade
Side by side upgrade (without High Availability)
Rolling upgrade (with High Availability)