Register Now!
One of our team members will reach out to you

Our Courses

Apply Now

1.1 Overview of Microsoft SQL Server

  • Business intelligence
  • Database Engine
  • T-SQL programming interface
  • Security subsystem
  • Replication
  • SQL Server Agent
  • High Availability and Disaster Recovery Tools
  • SQL Server Integration Services
  • SQL Server Management Tools

1.2 Introduction to Virtual Machines (VMs)

  • Introduction to Virtualization
  • Enabling Hypervisor (Hyper V) Manager
  • Creating Virtual Network
  • Introduction to Active Directory
  • Introduction to Domain Name Server (DNS)
  • Configuration of VMs for Domain Controller and 2 NODES
  • Creating a File Share
  • Copying files across servers

1.3 Installing, Configuring, and Upgrading Microsoft SQL Server

  • Before Installation
  • Hardware requirements for SQL Server
  • Software Prerequisites
  • Edition and Version of SQL Server
  • Provision storage for OS and SQL Server

Install and Configure SQL Server

  • Creating Service Accounts and Grant permissions
  • Installation types
  • Types of SQL Server instance
  • Types of Authentication modes
  • Collation sequences
  • Selecting SQL Server components
  • Types of ways to Install SQL Server
  • Installing SQL Server from the Setup Wizard
  • Installation of SQL Server Management Studio

Post installation

  • Ensure the TCP/IP Protocol is enabled
  • Assigning a TCP/IP port number to the SQL Server Database Engine
  • Opening a SQL Server instance port using Windows Firewall
  • Configure TempDB according to best practice
  • Configure SQL Server Max degree of Parallelism
  • Configure SQL Server Cost Threshold for Parallelism
  • Configure SQL Server Max Memory
  • How to Login to SQL Server

Using SQL Server administration and development tools

  • Using SQL Server Management Studio
  • Using SQL Server Configuration Manager

Apply Now

Apply Now

2.1 DATABASE DESIGN [PART 1]

Designing SQL Server database

  • Understanding System databases
  • Understanding SQL server database structure
  • Creating a database
  • Understanding arguments
  • Adding files and filegroups
  • Detaching and attaching SQL Server databases
  • Understanding Database recovery models

Creating your first table

  • Developing a naming Standard
  • Understanding Schemas
  • Understanding SQL Server data types
  • Understanding column properties
  • Understanding computed columns
  • Adding constraints to a table

Views

What are views?

Creating indexed views

  • Referenced table requirements.
  • Indexed view requirements

2.2 DATABASE DESIGN [PART 2]

Table compression

  • Understanding row compression
  • Understanding page compression
  • Estimating effect of compression
  • Compression considerations

Table partitioning

  • Creating a partition function
  • Creating a partition scheme
  • Partitioning tables and indexes

Database snapshots

  • Understanding database snapshot prerequisite and limitations
  • Creating and viewing database snapshots
  • Reverting to a database snapshot

Apply Now

Dropping database snapshots

Apply Now

3.1 INTRODUCTION TO TRANSACT-SQL (T-SQL)

Writing a SELECT statement

Sorting results

Filtering data with the WHERE clause

  • Using comparison operators
  • Using the BETWEEN operators
  • Using the WHERE clause with multiple conditions
  • Searching for a list of values
  • Using a wildcard search

Creating aliases

Using the JOIN operator to return data from multiple tables.

  • Using INNER JOIN
  • Using OUTER JOINs

Limiting the data returned in your result set

  • Using TOP
  • Using DISTRICT and NULL

Using UNION to combine result sets

Pivoting and unpivoting data

  • Using he PIVOT operator
  • Using the UNPIVOT operator

Paging data

  • Writing expressions
  • Using variables

3.2 Modifying data

Inserting data into DQL Server tables

  • Using the INSERT INTO statement

Updating data in tables

  • Updating rows while referencing multiple tables

Deleting data from tables

Merging data

Returning output data

3.3 Built-in scalar functions

Using date and time functions

  • Deriving dates from parts
  • Differencing modifying and validating date values

Using conversation functions

  • New SQL Server 2012 conversion functions

Using string functions

Using logical function

Apply Now

Apply Now

4.1 INTRODUCTION TO ADVANCED T-SQL

Aggregating, windowing and grouping

  • Performing aggregations
  • Performing aggregation with groupings

Windowing

  • New T-SQL windowing features
  • Using the HAVING clause
  • Using SQL Server temporary objects
  • Common table expressions
  • Table variables
  • Temporary tables

Handling T-SQL errors

Controlling flow keywords

  • BEGIN…END
  • .ELSE
  • WHILE

User-defined functions

Understanding user-defined scalar functions

  • Parameterizing functions
  • Executing scalar functions

Understanding table-valued functions

  • Using table-valued functions

Limitations of functions

4.2 Stored procedures

  • Working with stored procedures
  • Using the EXECUTE keyword
  • Parameterizing stored procedures
  • Dropping stored procedures

4.3 TRIGGERS

Data Manipulation triggers

  • Types of triggers
  • Creating triggers
  • Altering triggers

Data definition triggers

  • Dropping triggers

Enabling and disabling triggers

Apply Now

Apply Now

MAINTENANCE

5.1 Database Back Up

Understanding different types of Back up

  • Full database backups
  • Differential backup
  • Transaction log database backups
  • Stripped backup
  • Copy only backup
  • Understanding backup strategy
  • Understanding backup devices

5.2 Database Restore

  • Understanding recovery models
  • Restoring databases
  • Database Refresh

5.3 Upgrade and Migration

  • SQL Server Patching
  • In-Place Upgrade
  • Database Migration

5.4 SQL SERVER AUTOMATION

SQL Server Agent

  • SQL Server agent components
  • Viewing SQL Server Agent configuration Manager options
  • Configuring SQL Server Agent properties
  • Creating Operators
  • Configuring alerts
  • Configuring jobs
  • Configuring proxies

Database Mail

  • Database mail components
  • Configuring Database Mail
  • Sending email using Database Mail
  • Monitoring Database Mail

Maintenance plan

  • Performing database consistency checks
  • Automate backups and other jobs

Using the maintenance Plan Wizard

Apply Now

Apply Now

  1. PERFORMANCE TUNNING
  • Building and designing indexes
  • Managing and maintaining indexes and statistics
  • Checking index fragmentation
  • Defragmenting indexes
  • Reorganizing indexes
  • Rebuilding indexes
  • Checking index usage

Creating and updating database statistics

  • Viewing database statistics options
  • Updating database statistics
  • SQL Server Profiler
  • Understanding SQL Server Profiler
  • Typical uses of SQL Server Profiler
  • Creating trace
  • Filtering a trace
  • Creating trace templates
  • Running server-side traces

Extended Events

  • Understanding the extended events architecture
  • Creating and configuring an extended events session
  • Using an extended events session to monitor system performance

Query Store

  • Understanding the component of query store
  • Understanding the importance of query store in performance tuning

Dynamic Management Objects

  • Retrieving SQL Server metadata from DMOs
  • Querying server performance statistics
  • Querying server information
  • dm_server_services
  • dm_os_volume_stats
  • dm_os_sys_memory
  • dm_exec__request and sys.dm_exec_sessions
  • dm_exec_sql_text
  • Querying performance information
  • dm_exec_query_stats
  • Querying indexing statistics
  • dm_db_index_usage_stats
  • dm_db_index_physical_stats
  • Understanding Executions plan
  • Configuring Tempdb

Resource Governor

  • Enabling and disabling Resource Governor
  • Creating resource pools
  • Creating a workload group
  • Using classifier functions
  • Testing classifier functions
  • Modifying Resource Governor configurations

Apply Now

Apply Now

  1. SQL SERVER SECURITY

Understanding principals

Creating server logins

Creating user-defined server roles

Creating database users

Creating built-in database roles

Configuring contained databases

  • Creating a contained user
  • Benefits and limitations of contained databases
  • Types of Authentication
  • Orphan Users
  • Transparent Data Encryption
  • Dynamic Data Masking
  • Always encryption
  • Row Level security

Apply Now

Apply Now

  1. HIGH AVAILABILITY AND DISASTER RECOVERY SOLUTIONS

8.1 SQL SERVER REPLICATION

Types of replication

  • Snapshot
  • Transactional
  • Merge

Replication Agents

  • Snapshot Agent
  • Distribution Agent
  • Log Reader Agent
  • Merge Agent

Configuring replication

Monitoring replication

8.2 TRADITIONAL FAILOVER CLUSTER

  • Introduction to Failover Cluster
  • Understanding Active/Passive and Active/Active environment
  • Building a Cluster
  • Installing SQL Server Failover Cluster Instance
  • Provisioning a Shared storage

8.3 Configuring SQL Server AlwaysOn Availability Groups

  • Understanding AlwaysOn Availability Groups
  • Setting up Windows Server Failover Clustering
  • Enabling AlwaysOn
  • Creating availability groups

8.4 Log shipping

Log shipping components

Log shipping prerequisites

Configuring log shipping

Introduction to Azure

  • Understanding Cloud services
  • Azure PaaS, IaaS, and SaaS
  • Understanding managed instance
  • Understanding pricing tiers
  • Migrating from on premises to azure
  • Understanding Azure storage
  • Designing a database in azure
  • Monitoring performance in azure
  • Managing security in azure
  • Azure PowerShell deployment

Apply Now

Minimum 4 characters