Master the fundamentals of SQL Server 2019, the industry-leading database management platform from Microsoft. In this course, beginners will learn the core skills they need to create and manage SQL Server databases.

You don't need any prior knowledge of databases or SQL query language.

This is a 4 days hands-on course. The trainer will explain how to make a database and maintain it using step-by-step practise. You will also be taught tips & tricks for good database management. You will design your own database and also modify existing databases. You will get ample practise so that you can apply them in practical situations in database management.

COURSE OUTLINE

DAY 1 TRAINING

1. Getting Started Core concepts

SQL Server 2019 editions

Install SQL Server Developer Edition

Install Management Studio

Enable features with Configuration Manager

2. Log into the Server

Management Studio first steps 

Authentication and user permissions 

Create a new user account

3. Create Databases

Create the first database

Database configuration options 

Table structures

Create a simple table

Edit a table's structure

Enter data into a table using the GUI Import data from CSV files

Create tables from flat files

Install a sample database

WORKSHOP DAY 1

Getting started with SQL Server tools Exporting Data with the Import/Export Wizard

 Introducing database server components

Installing the Failover Cluster Feature

Designing and implementing an on-premises database infrastructure 

Designing Multiple Filegroups

Installing and configuring SQL Server instances and features 

Installing SQL Server Analysis Services

Installing SQL Server Integration Services

Installing and configuring SQL Server on Linux

DAY 2 TRAINING

4. Table Design for Healthy Databases Understand data types

Table column properties 

Primary key fields 

Establish a default value 

Check constraints

Unique constraints 

Foreign keys

Create a foreign key column

Create relationships between tables

5. Structured Query Language Use the T-SQL editor

Create a table with T-SQL

Add data to a table

Retrieve records with SELECT 

Filter returned results with WHERE Sort records

Delete records from a table Update records in a table

Join related tables

LEFT JOIN and RIGHT JOIN 

Remove a table from the database

6. Query Performance

Create a view of the data

Data table indexes

Create additional indexes on a table 

Columnstore indexes

View execution plans

SQL Server's Query Store

Rebuild indexes

WORKSHOP DAY 2

Configuring SQL Server

Provisioning and configuring SQL Server databases

   Creating a SQL Login

Using the ALTER Command with the ROLLBACK IMMEDIATE Command 

Maintaining and monitoring SQL Server 

Using CHECKDB

Managing Resource Utilization Using Resource Governor

Automating SQL Server administration

Configuring Database Mail

DAY 3 TRAINING

7. Query Programmability Aggregate functions

Use built-in functions

Leverage user-defined scalar functions 

Introduction to stored procedures 

Create stored procedures 

Parameterised stored procedures

8. Backup and Restore

Create a full backup of the database 

Create a differential backup

Restore a backup

Automate backups

Business continuity solutions

WORKSHOP DAY 3

Developing, deploying, and managing data recovery 

Creating a Differential Backup

Creating a Full Database Backup

Administering security and permissions

Granting Permissions to Create Tables

Protecting data through encryption, privacy and auditing

Creating a New Server Audit and Configuring a Server Audit Specification

DAY 4 TRAINING

9. Security

Database schemas

Dynamic data masking Always On data encryption

10. Monitoring and Management

Explore the role of system databases 

Prevent excessive tempdb autogrowth 

Review the SQL Server error log 

Dynamic management views

WORKSHOP DAY 4

Database Console Commands (DBCC) 

Performance tuning SQL Server 

Optimising Queries 

Understanding and designing indexes 

Creating a Non-Clustered Index 

Creating a Columnstore Index 

Configuring Full-Text Indexing

WHO SHOULD ATTEND

You must have some basic skills in HTML and:

• If you are interested to learn about Structured Query Language (SQL) used in Relational Database Management Systems

• You want to work with SQL databases

• Wish to implement Microsoft's SQL Server database in your organisation

RATIONALITY

• Learn an industry strength, backed by Microsoft, database server that is everything you will need in maintaining your data

LEARNING OUTCOME

You will learn:

• The basics, showing how to install SQL Server and log into a local database server

• Then, you'll learn how to create new databases, design tables, define relationships, write queries, and use stored procedures and built-in functions

• Plus, you will practise how to perform crucial database administration tasks, such as backups 

• and restores, security enhancements, and performance monitoring