Administering and implementations of Microsoft SQL

Microsoft SQL Server continues to be one of the world's most popular and top databases. The database is the heart of IT which is needed to store all critical data and information.

 

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

 

This training course will provide you with all the necessary skills you need to be a successful database administrator.

  • Explain MS SQL storage engines
  • Explain database transactions
  • Obtain database metadata
  • Describe MS SQL GUI tools
  • Monitor database performance
  • Perform database backup and recovery
  • Export and import database data
  • Describe the features and benefits of MS SQL
  • Explain the basics of relational databases
  • Design an effective database
  • Build a database and tables by using SQL Modify or delete database entities
  • Join data from multiple tables
  • Use built-in MS SQL functions

 

  • Team leaders seeking professional development in MS SQL
  • IT Team Leader.
  • MS SQL developer
  • IT manager
  • IT department managers.
  • Project managers.
  • Systems designers.

 

  • RDBMS
  • What is SQL?
  • Top 5 RDBMS
  • Introduction to MS SQL Server
  • Different editions of MS SQL

 

  • 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

 

  • 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

 

  • What are Data and Database?
  • How is data stored?
  • What are 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 the table with data…

 

  • Welcome to Basic SQL Commands
  • What are SQL Statements and what types of SQL statements
  • DML Statement with examples
  • DDL Statements with examples
  • DCL Statement with examples
  • TCL Statement with examples

 

  • 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

 

  • 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 are master, etmpdb, msdb, and other system databases?

 

  • 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 a user Database

  • Backup of a Database, Importance for DBA, and Different Media used for Backups
  • Recovery Models in MSSQL and how that impact backups and 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.

 

  • MSSQL Security Model
  • MSSQL Server Authentication Modes
  • Server Logins
  • Server Roles
  • Database Users
  • Database Roles
  • Permissions (GRANT, DENY, and REVOKE)

 

  • SQL Server Agent Introduction
  • SQL Server Agent Jobs and Schedules
  • Agent Alerts
  • Operators
  • Database Mail
  • Activity Monitor

 

  • 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

 

Related Courses