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
Students Enquiry Form
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
- Benefits of the multitenant architecture
- Differences between the root container and pluggable database containers
- Structure of the root
- Structure of Pluggable Database (PDB)
- CDB_xxx and DBA_xxx views
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)