Course Name: Microsoft SQL Server Database Management and Administration
Introduction
The Microsoft SQL Server Database Management and Administration (MSSQL-DMA) course offered from Information Access Center (IAC) is an essential database course for database professionals who want to have a strong foundation and expertise in the industry's most advanced database management system.
Objectives
- To develop skills for efficiently managing and administering an MSSQL Server database.
- To manage the industry's most advanced information systems which will help to gain access to better career opportunities
Outcome of the Learning
Students completing this course successfully will be able to -
- Gain a conceptual understanding of the MSSQL Server database architecture and how its components work and interact with one another.
- Install and maintain a MSSQL Server database.
- Create an operational database and properly manage the various structures in an effective and efficient manner.
- Effectively learn performance monitoring, database security, user management, and backup/recovery techniques.
- Earn skills required for working with basic SQL.
- Use advanced features of SQL in order to query and manipulate data within the database.
- Control privileges at the object and system level, and use advanced querying and reporting techniques.
- Earn skills required for Backup, User Management, Replication.
Class Schedule
The course length will be 8 weeks with two classes in each week and 3 hours in each class.
Class Contents
Section 1: Introduction
- Introduction to Database
- RDBMS
- What is SQL?
- Introduction to 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 2: 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 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 3: 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 4: 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 Stored Procedure?
- Function, Trigger, and INDEX
- Clustered, Non-clustered Indexes and Index Design Considerations
- Index Fragmentation and Lab for Index
Section 5: 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 6: 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 7: 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 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.
Section 8: MSSQL User Management
- MSSQL Security Model
- MSSQL Server Authentication Modes
- Server Logins
- Server Roles
- Database Users
- Database Roles
- Permissions (GRANT, DENY, and REVOKE)
Section 9: MSSQL Server Agent Management
- SQL Server Agent Introduction
- SQL Server Agent Jobs and Schedules
- Agent Alerts
- Operators
- Database Mail
- Activity Monitor
Section 10: Advanced SQL Server Administration Topics
- High Availability and its Types
- What Is Replication and Transactional Replication
- Transactional Replication Setup
Learning and Evaluation Method
- The classroom will be equipped with a multimedia projector.
- One faculty member from the Department of Computer Science and Engineering (CSE), BUET will take each class.
- Each student will have access to a PC for proper practice, and the class size will not exceed 35 students.
- A final exam will be held at the end of the course.
- We will provide a certificate upon successfully passing this course.
Further Query
Email: iac@cse.buet.ac.bd
Phone: 9665650-80 Ext-6438, mobile : 01741 686742