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

Description

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.

English

Language

Content

Microsoft SQL Server Setup

Introduction

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

Schema

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

Introduction

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

Views

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

Introduction

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

Introduction

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

Introduction

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

Introduction

Why Table Join

Full Outer Join

INNER Join

LEFT Outer Join

Right Outer Join