Home / Courses / ICT and Security Management
ICT and Security Management · MSD3013

Microsoft SQL Server Administration

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 course will provide you all the necessary skills you need to be a successful database administrator.  Plus, this course will prepare you for the SQL Server MCSA certification.

Register Online Face to Face Registration Download Course Details
Microsoft SQL Server Administration
Online fee from
$200 USD
Standard: $300 · Save $100
Course Outcomes

Expected learning outcomes

  • 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
Curriculum

Course modules and 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)

  • Log Shipping and Configuring Log Shipping

  • SQL Server Encryption and Encryption Types

  • Transparent Data Encryption (TDE) and configuration

Why Attend

Key course benefits

Practical capacity building approach
Designed for government, NGOs and public institutions
Real-world African case studies
Certificate of completion
Applicable tools and templates
Interactive facilitation and exercises
Course Enquiry

Need more information?

Ask Magna Skills about this course

Use the PHPMaker enquiry form to request a quotation, proposal letter, invoice, group training package, online access, or face-to-face training arrangement.

Make Course Enquiry WhatsApp Us

Debug

sql: SELECT COUNT(*) FROM `userlevels` WHERE `userlevelid` = -2, executionMS: 0.00044703483581543

sql: SELECT `userlevelid`, `userlevelname` FROM `userlevels`, executionMS: 0.00026488304138184

sql: SELECT COUNT(*) FROM `userlevelpermissions` WHERE `userlevelid` = -2, executionMS: 0.0005640983581543

sql: SELECT `tablename`, `userlevelid`, `permission` FROM `userlevelpermissions` WHERE `userlevelid` IN (-2), executionMS: 0.0018389225006104

sql: SELECT c.*, cc.Category_Name, cc.Summary AS CategorySummary, cc.Category_Description FROM courses c LEFT JOIN course_categories cc ON cc.id = c.Category WHERE c.id = 3013 LIMIT 1, executionMS: 0.00080704689025879

sql: UPDATE courses SET Views = IFNULL(Views,0) + 1 WHERE id='3013', executionMS: 0.0016000270843506

sql: SELECT * FROM course_modules WHERE CourseID = 3013 AND Status IN ('Active','1') ORDER BY SortOrder, id LIMIT 20, executionMS: 0.00043678283691406

sql: SELECT * FROM course_highlights WHERE CourseID = 3013 AND Status IN ('Active','1') ORDER BY SortOrder, id LIMIT 8, executionMS: 0.00034499168395996

sql: SELECT * FROM course_learning_outcomes WHERE CourseID = 3013 AND Status IN ('Active','1') ORDER BY SortOrder, id LIMIT 10, executionMS: 0.00032305717468262

sql: SELECT * FROM course_target_audience WHERE CourseID = 3013 AND Status IN ('Active','1') ORDER BY SortOrder, id LIMIT 8, executionMS: 0.00027012825012207

sql: SELECT * FROM resources WHERE CourseID = 3013 AND Status IN ('Published','Active','1') ORDER BY id DESC LIMIT 5, executionMS: 0.00035715103149414

sql: SELECT * FROM reviews WHERE CourseID = 3013 AND CanPublish = 1 AND Status IN ('Approved','Published','1') ORDER BY id DESC LIMIT 4, executionMS: 0.0004119873046875

sql: SELECT * FROM gallery_albums WHERE CourseID = 3013 AND Status IN ('Published','Active','1') ORDER BY IsFeatured DESC, AlbumDate DESC, id DESC LIMIT 3, executionMS: 0.00038599967956543

sql: SHOW COLUMNS FROM `venues` LIKE 'id', executionMS: 0.0012588500976562

sql: SELECT v.*, v.`id` AS VenueKey, (SELECT COUNT(*) FROM apply a WHERE a.Venue = v.`id` AND a.Course_Title = 3013) AS CourseApplications, (SELECT COUNT(*) FROM apply a WHERE a.Venue = v.`id`) AS TotalApplications FROM venues v ORDER BY CourseApplications DESC, TotalApplications DESC, v.Venue ASC, executionMS: 1.6337029933929

sql: SHOW COLUMNS FROM `courses` LIKE 'Category', executionMS: 0.0011279582977295

sql: SELECT id, Course_Title, Course_Code, Short_Description FROM courses WHERE Category = 809 AND id <> 3013 ORDER BY id DESC LIMIT 6, executionMS: 0.00080108642578125

sql: SELECT COUNT(*) FROM apply WHERE Course_Title = 3013, executionMS: 0.0714111328125

sql: SELECT COUNT(*) FROM enroll WHERE Course = 3013, executionMS: 0.00058412551879883

sql: SELECT ROUND(AVG(Rating),1) FROM reviews WHERE CourseID = 3013 AND CanPublish = 1, executionMS: 0.00033688545227051

sql: SELECT COUNT(*) FROM course_modules WHERE CourseID = 3013, executionMS: 0.00023603439331055

sql: SELECT COUNT(*) FROM course_lessons cl INNER JOIN course_modules cm ON cm.id = cl.ModuleID WHERE cm.CourseID = 3013, executionMS: 0.00030398368835449

sql: SELECT COUNT(*) FROM course_highlights WHERE CourseID = 3013, executionMS: 0.0011169910430908

sql: SELECT COUNT(*) FROM course_learning_outcomes WHERE CourseID = 3013, executionMS: 0.00027108192443848

sql: SELECT COUNT(*) FROM course_target_audience WHERE CourseID = 3013, executionMS: 0.0002598762512207

sql: SELECT COUNT(*) FROM resources WHERE CourseID = 3013, executionMS: 0.00022602081298828

sql: SELECT COUNT(*) FROM reviews WHERE CourseID = 3013, executionMS: 0.00023078918457031

sql: SELECT COUNT(*) FROM gallery_albums WHERE CourseID = 3013, executionMS: 0.00027084350585938

sql: SELECT COUNT(*) FROM course_enquiries WHERE CourseID = 3013, executionMS: 0.0002448558807373