Microsoft SQL Server Database Administration step by step

What you will learn

Microsoft SQL Server Setup

Installing Sample Databases

Creating Database Objects

Database Design

Tracking database activities

Creating stored procedures

Database backup and recovery

Monitoring Database Performance

Database security and user accounts

Querying Database with T-SQL

Aggregating Data using aggregate functions

Filtering and sorting records

Extracting data from multiple tables


SQL Server is Microsoft’s relational database management software and uses a common set of tools to deploy and manage databases both on-premises and in the cloud.

Microsoft SQL Server Management Studio is a rich, integrated administrative client designed to meet the SQL Server and Azure SQL Database administrator’s server management requirements. In Management Studio, administrative tasks are accomplished using Object Explorer, which allows you to connect to any server in the SQL Server family and graphically browse its contents. A server can be an instance of the Database Engine, Analysis Services, Reporting Services, Integration Services or Azure SQL Database.

The tool components of Management Studio include Registered Servers, Object Explorer, Solution Explorer, Template Explorer, the Object Explorer Details page, and the document window.

Use Management Studio to:

  • Register servers.
  • Connect to an instance of the Database Engine, SSAS, SSRS, SSIS or Azure SQL Database.
  • Configure server properties.
  • Manage database and SSAS objects such as cubes, dimensions, and assemblies.
  • Create objects, such as databases, tables, cubes, database users, and logins.
  • Manage files and filegroups.
  • Attach or detach databases.
  • Launch scripting tools.
  • Manage security.
  • View system logs.
  • Monitor current activity.
  • Configure replication.
  • Manage full-text indexes.

SQL Server Data Tools (SSDT) is a modern development tool for building SQL Server relational databases, databases in Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.

T-SQL, which stands for Transact-SQL and is sometimes referred to as TSQL, is an extension of the SQL language used primarily within Microsoft SQL Server. This means that it provides all the functionality of SQL but with some added extras.




Microsoft SQL Server Setup


What is SQL Server

What is a DBA

SQL Server Editions

Hardware | Software Requirements

Download SQL server

Install SQL Server

Install SSMS

SQL Server Configuration Manager

SQLCMD Utility


Connecting SSMS to SQL Server

Installing Sample Databases

Database Concepts

Install Adventureworks 2014

Install Adventureworks 2016

Install AdventureworksDW 2016

Install World Wide Importers Database

Install World Wide ImportersDW Database

Creating Database Objects

Create and configure a new database

Create a new table

Edit Table Structure

Manually Inserting Data into database

Import data from csv file

Create a table from a flat file

SQL Server Data Files

Database Table Design Properties

Creating an identity column

Primary keys

Setting default values

Check constraints

Unique Constraints

Foreign Keys

Creating relationships between tables

SQL Server Profiler – DBA Tool

What is SQL Server Profiler

How SQL Server Profiler can help DBA’s

SQL Server Profiler Architecture

SQL Server Profiler Terminology

Permissions required to access SQL Server Profiler

How to start SQL Server Profiler

Stimulating database activity

How to start a trace

How to load a saved trace

Exploring events selection

Creating a custom trace from scratch – part 1

Creating a custom trace from scratch – part 2

Creating a custom trace from scratch – part 3

Creating CRUD Stored Procedures

What is CRUD

What are stored procedures

Basic Application Stucture

Permission required to create stored procedures

Get Instant Notification of New Courses on our Telegram channel.

Ways to create stored procedures

Creating CRUD Stored Procedures

Naming Convention for CRUD Stored Procedures

Stored Procedures for CREATE CRUD Operation

Stored Procedures for READ CRUD Operation

Stored Procedures for UPDATE CRUD Operation

Stored Procedures for DELETE CRUD Operation

Database Backup & Recovery

Enabling Log Shipping

Copy database

Full Database Backup

Differential Database Backup

Transactional Database Backup

Database Recovery

Lab Exercise 1

Lab Exercise 2

Monitoring SQL Server Performance


Activity Monitor

Collecting data

Performance Monitor

Extended Events

Using Indexes

Database Security & user accounts

Creating a new user account

Resetting System Admin Account

Enforcing Authentication

Applying permission level access

Checking database integrity with audits

Querying Data with Transact-SQL

What is SQL

What is Transact-SQL ( T-SQL)

Creating database with T-SQL

Creating table with T-SQL

Inserting data with T-SQL

Querying data


Updating Data

Backing up data with T-SQL

Deleting Data

Truncating Table

Dropping Table

Dropping Database

Restore database with T-SQL

Aggregating Data with T-SQL


Max Aggregate Function

Min Aggregate Function

Sum Aggregate Function

Count Aggregate Function

AVG Aggregate Function

Grouping Data

Using Multiple Aggregate Functions

Filtering Data with T-SQL


Filtering data with basic comparison

Filtering data with logical comparison

Filtering Data with string comparison filters

Filtering data with NULL Values

Filtering Data with string comparison

Sorting Data with T-SQL


Using ORDER BY Clause

Sorting data in ascending order

Sorting data in descending order

Sorting data by multiple columns

Extracting Data with Table Joins using T-SQL


Why Table Join

Full Outer Join


LEFT Outer Join

Right Outer Join