Welcome to Section 1
Introduction
By: Imran Afzal
[Link]
Course Overview
Section 1 thru 13
By: Imran Afzal
[Link]
Section 1 - Introduction
• Introduction to Database
• RDBMS
• What is SQL?
• Top 5 RDBMS
• Introduction to MS SQL Server
• Different editions of MS SQL
By: Imran Afzal
[Link]
Section 2
Download, Install and Configure Windows
• MS SQL Server pre-requisite
• 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
By: Imran Afzal
[Link]
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
By: Imran Afzal
[Link]
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…
By: Imran Afzal
[Link]
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
By: Imran Afzal
[Link]
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
By: Imran Afzal
[Link]
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?
By: Imran Afzal
[Link]
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
By: Imran Afzal
[Link]
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.
By: Imran Afzal
[Link]
Section 10
MSSQL User Management
• MSSQL Security Model
• MSSQL Server Authentication Modes
• Server Logins
• Server RolesDatabase Users
• Database Roles
• Permissions (GRANT , DENY, and REVOKE)
By: Imran Afzal
[Link]
Section 11
MSSQL Server Agent Management
• SQL Server Agent Introduction
• SQL Server Agent Jobs and Schedules
• Agent Alerts
• Operators
• Database Mail
• Activity Monitor
By: Imran Afzal
[Link]
Section 12
Advanced SQL Server Administration Topics
• High Availability and its Types
• What Is Replication and Transactional Replication
• Transactional Replication Setup (LAB)
• MSSQL Data Encryption and type of encryptions
• Transparent Data Encryption (TDE) and configuration
• Backup and restore of encrypted Databases
• MS SQL Server Reporting Services (SSRS)
• MS SQL Server Integration Services (SSIS)
By: Imran Afzal
[Link]
Section 13
Course Recap
• Commands we have learned
By: Imran Afzal
[Link]
Database
• Another component of IT or computers
Database
• Data is a collection of facts, such as numbers, words, • System
measurements, observations or just descriptions of • Foundation
things. E.g. your name, age, height, weight, etc. are • Hub
some data related to you. A picture, image, file, pdf, • Central location
etc. can also be considered data.
Database
A database is an organized and systematic collection of data
generally stored and accessed electronically from a computer
system. Databases make data management easy
By: Imran Afzal
[Link]
Database
• Question
Data can be stored in a spreadsheet or a text
document as well so why we need a database?
• Databases store information more efficiently, databases can
handle volumes of information that would be unmanageable in a
spreadsheet.
• Spreadsheets have record limitations whereas databases do not
• Compared to databases, spreadsheets can require a large amount
of hard-drive space for data storage
• Joining records is a lot easier in DB than compared to text or
spreadsheets.
By: Imran Afzal
[Link]
RDBMS
• RDBMS stands for relational database management system
• A relational database is a type of database. It uses a structure
that allows us to identify and access data in relation to
another piece of data in the database. Often, data in a
relational database is organized into tables
Database
1 10001 Imran Afzal IT 10001 3 some place 917.000.1000
2 10002 Jerry Seinfeld Finance 10002 10 Highrise ave 347.000.9000
3 10003 George Costanza Sales 10003 2020 Nosuch pl 212.000.0001
By: Imran Afzal
[Link]
RDBMS
RDBMS
1 10001 Imran Afzal IT 10001 3 some place 917.000.1000
2 10002 Jerry Seinfeld Finance 10002 10 Highrise ave 347.000.9000
3 10003 George Costanza Sales 10003 2020 Nosuch pl 212.000.0001
By: Imran Afzal
[Link]
What is SQL?
• SQL (Structured Query Language) is a programming language used to communicate with
data stored in a relational database management system. SQL syntax is similar to the
English language, which makes it relatively easy to read, write, update or translate data
• SQL is often pronounced in one of two ways. You can pronounce it by speaking each
letter individually like “S Q L”, or pronounce it using the word “sequel”.)
• It was invented in 1970s by IBM and the first version was called SEQUEL (Structured
English QUEry Language). Later it was changed to SQL because SEQUEL was already
trademarked by an airline company
• SQL is NOT a database; it is a command line language that many RDBMSs use to access
the data from tables
• Example statement: SELECT * FROM tablename;
By: Imran Afzal
[Link]
Top 5 RDBMS using SQL
• Oracle DB
• It is a database product by Oracle and its enterprise version was released in 1979
• It is a database commonly used for running online transaction processing, data
warehousing and mixed database workloads
• Oracle is one of the biggest vendor in the IT market
• Microsoft SQL Server
• MS SQL server is a relational database server by Microsoft
• Its first version was released in 1988
• It is among the most stable, secure and reliable database solutions
• It supports wide variety of transaction processing, analytics, and business
intelligence applications in corporate IT environments
• MS SQL server competes primarily against Oracle DB and IBM DB2
By: Imran Afzal
[Link]
Top 5 RDBMS using SQL
• MySQL
• A Swedish company called MySQL AB originally developed MySQL in 1994
• It is community developed open source and its first version was release in 1995
• The US tech company Sun Microsystems then took full ownership when they bought MySQL AB in
2008
• US tech giant Oracle in 2009 acquired Sun Microsystems itself, and MySQL has been practically
owned by Oracle since
• Even though MySQL is open-source software, you can buy a commercial license version from
Oracle to get premium support services
• The main advantages of MySQL are that it is easy to use, inexpensive, reliable (has been around
since 1995), and has a large community of developers who can help answer questions.
• PostgreSQL
• Community developed open source and its first version was released in 1987
• It was originally named POSTGRES, referring to its origins as a successor to the Ingres database
developed at the University of California, Berkeley.
• In 1996, the project was renamed to PostgreSQL to reflect its support for SQL
• After a review in 2007, the development team decided to keep the name PostgreSQL.
By: Imran Afzal
[Link]
Top 5 RDBMS using SQL
• MariaDB
• Community developed open source and its first version was released in 2009
• Development is led by some of the original developers of MySQL, who forked it due to
concerns over its acquisition by Oracle Corporation in 2009
• MariaDB is named after Monty's younger daughter, Maria
In this course our focus will be on:
By: Imran Afzal
[Link]
Introduction to MS SQL Server
• MSSQL is a suite of database software published by Microsoft and used extensively in
enterprise world
• It includes:
• A relational database engine, which stores data in tables, columns and rows
• Integration Services (SSIS), which is a data movement tool for importing, exporting
and transforming data
• Reporting Services (SSRS), which is used to create reports and serve reports to end
users
• Analysis Services (SSAS), which is a multidimensional database used to query data
from the main database engine.
By: Imran Afzal
[Link]
Introduction to MS SQL Server
• In this course we will download, install and configure MS SQL 2016/2019 full enterprise
evaluation version on Windows server 2016/2019 (180 days)
• Approximately 85% of corporate companies around the world uses MS SQL version
2016/2019 or older
• SQL Server management studio is an additional software that will be used to manage and
maintain the databases, run SQL queries, perform backups and analyze performance charts
• Each version is named by the year but they are not exactly released in those specific years
• Most recent MS SQL versions are:
• SQL Server 2014
• SQL Server 2016
• SQL Server 2017
• SQL Server 2019.
By: Imran Afzal
[Link]
Introduction to MS SQL Server
Pros Cons
Various supported editions Expensive enterprise edition
Online product documentation Difficult licensing process that’s always
changing
Microsoft Premier support
On-premises and cloud database support
Plenty of tools and applications
Support for use on Linux
Please note:
Regardless which RDBMS we choose our end goal is that you understand
how to read, write and manipulate data using SQL statements.
By: Imran Afzal
[Link]
Editions of MS SQL Server
SQL Server Editions Description
Enterprise The premium offering, SQL Server Enterprise edition delivers comprehensive high-end data center
capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence—
enabling high service levels for mission-critical workloads and end-user access to data insights
Standard SQL Server Standard edition delivers basic data management and business intelligence database for
departments and small organizations to run their applications and supports common development tools for
on-premise and cloud—enabling effective database management with minimal IT resources.
Web SQL Server Web edition is a low total-cost-of-ownership option for web hosting companies
Developer SQL Server Developer edition lets developers build any kind of application on top of SQL Server. It includes
all the functionality of Enterprise edition but is licensed for use as a development and test system, not
as a production server. SQL Server Developer is an ideal choice for people who build and test applications
Express Express edition is the entry-level, free database and is ideal for learning and building desktop and small
server data-driven applications. It is the best choice for independent software vendors, developers, and
hobbyists building client applications. If you need more advanced database features, SQL Server Express can
be seamlessly upgraded to other higher end versions of SQL Server. SQL Server Express LocalDB is a
lightweight version of Express that has all of its programming features. It runs in user mode and has a fast,
zero-configuration installation and a short list of prerequisites
By: Imran Afzal
[Link]