Topic 1: Starting with SQL Server 2014/2016/2017/2019

• History of SQL Server - versions
• What's New in SQL Server 2005& 2008/ R2, 2012, 2014, 2016, 2017, 2019 for Administrators?
• SQL Server 2017& 2019 Service Packs
• Editions of SQL Server
• Tools of SQL Server
• Differences between Enterprise and Standard editions
• Requirements
  • Hardware
  • Software
• Instances
  • Advantages of Instances
  • Types
    • Default Instance
    • Named Instances
  • SQL Server Services
  • Installing SQL Server 2014/2016/2017/2019
    • Pre installation steps
    • Installations 2014/2016/2017/2019. New changes in 2016/2017/2019
    • Viewing installation process with LOG
    • Adding or removing
    • Installing service
  • Exercises

Topic 2: Installing and configuring

Configuration

Case Study

• Configuring various
• Startup
• Configuring data file and log file
• Configuring default backup folder and
• Configuring services
• Remote connections
• Exercise
• Configuring network protocols,
• Memory configuration

• Troubleshooting SQL Server installation common issues
• Roll backing Service
• Best Practices
• Exercise

Topic 3: Working with Databases

Working with databases.

• System Defined databases
• Moving system databases
• Handling TempDB

Database Architecture.

• Data Files
• Log Files
• Filegroups
• Extents
• Pagestypes
• Page architecture
• Tracking free space
• Creating
• Adding files,

Case Study

• Recovering suspect database
• Moving system databases
• Troubleshooting TempDB issues
• Log file full how to solve the

Topic 4: Implementing Security

• Security in SQL Server 2008 R2/ 2012/2014/2016/2017 & 2019
• Security Enhancements

Types of Authentications.

• Windows Authentication
  • Creating logins from windows users and groups
  • Orphan
• SQL Server Authentication
  • Creating SQL logins and testing logins
• Setting authentication Mode
• Security
• Understanding server
• Working with
• Resolving orphan
• Understanding database roles, custom and application
• Understanding
• Encryption and
• Working with certificates and

·        Case Study

• Connecting to instance without login credentials Resolving orphan users

Topic 5: Backup and Restoration

• Understanding Transaction Log
• Understanding checkpoints& Lazy
• Truncating log

Recovery Models

• Full
• Bulk Logged
• Simple
• Setting recovery

Database Backups

Backup Types.

• Why we need to backups
• Full
• Differential
• Transaction Log
• File or Filegroup
• Copy-only, Mirrored and tail log

• Backup
• Performing Restoration
• Backup system
• Viewing complete details of backup

·        Case Study

• Recovering a crashed database
• Point-in-time recovery

Topic 6: Automating Administrative Tasks

Working with Database Mail.

• Mail
• Configuring Profiles and Accounts
• Sending Mail
  • Configuring linked

Implementing Automation

• Configuring SQL Server
• Creating Operators, Alerts, Jobs
  • Managing jobs and resolving
  • Monitoring
  • Auto alert when jobs are enabled, disabled or
• Database
• Reverting from
• Maintenance Plans

LEVEL - 2

HA & DR

Working with Log Shipping.

• Features
• Jobs
• Requirements to implement Log
• Configuring Log
• Monitoring Log
• Manually performing Fail
• Transferring
• Log shipping tables and stored Handling out of sync issues

  Working with Database Mirroring.

• Advantages
• Architecture
• Operating Modes
• Servers involved in Mirroring
• Requirements for Mirroring
• Configuring

Using System views and SPs. Mirroring Fail Over

Mirroring system tables and stored procedures.

Case Study

• Handling suspect issues
• How to patch mirrored instances
• How to move mirrored database files
• Threads created for mirroring

 Replication

• Replication and advantages
• New features 2008 R2/ 2012
• Replication Entities
• Replication
• Replication
• Types of Replications

Configuring Replication

• Snapshot Replication
• Transactional Replication
• Merge Replication
• Peer to peer

• Configuring Oracle publication
• Replication Topologies
• Managing
• Monitoring and Tuning

Case Study

• Troubleshooting Primary Key violation error in Transactional Replication
• Troubleshooting Log file growth issues.
• Troubleshooting out of sync/ latency issues

Clustering (WFC) on Azure VM

• Introduction to cluster environment &
  • Overview of Windows
  • How SQL Server supports
  • Requirements
    • Installing and configuring SQL Server clustering
  • Applying service packs and hot
  • Moving
  • Adding node on a SQL Server Failover
  • Troubleshooting cluster
  • Patching clustered instances
• Configure Quorum
• Configure MSDTC Service

Always-ON SQL Server on AZURE VM

• Introduction to AlwaysON environment &
  • Overview of
  • How SQL Server supports
  • Requirements
    • Installing and configuring SQL Server AlwaysON
  • Applying service packs and hot
  • Moving
  • Adding Replicas on Availability
  • Troubleshooting AlwaysON issues.
  • Patching AlwaysON instances

• Enhancement AlwaysON on 2012 and
• Configure Listener

Case Study

• Quorum issue
• Nodes not in SYNC
• Availability groups are in resolving status
• Replicas out of SYNC

Advanced Administration Concepts

• Maintenance plans

Monitoring and Tuning SQL Server

• Performance counters setup
• Measuring performance of
• Tuning
• Tuning
• Tuning physical architecture of
• Using

Monitoring Tools

Troubleshooting

• System Monitor
• SQL Server Profiler
• Database Engine Tuning
• Dynamic Management
• SQL Server and Windows Error

• Physical server
• Connectivity to SQL Server
• Database

Managing Concurrency

• Locks
• Deadlocks
• Transaction Isolation
• Understanding
• Terminating

Managing Databases •    Using the DAC.

• Case Study
  • Troubleshooting slowly running queries
  • Using DTA

• Manage Index Fragmentation
• Manage Statistics
• Shrink Files
• Performing database integrity checks by using DBCC CHECKDB
• Index Rebuilding and

• SQL Server Up
• Using DBCC
• Resource Governor

Topic 1: Cloud Computing Introduction

• How IT Infrastructure worked before Cloud Computing
• History and Evolution of Data Centers
• Impact of Virtualization
• Types of CloudComputing
  • Private
  • Public
  • Hybrid
• Types of Public Clouds: IaaS, PaaS & SaaS Models
• Services offered by Microsoft Azure (as Introduction)
• Azure Free trail account creation & subscription
• Resource Group and Resource intro

Topic 2: Microsoft Azure SQL Database

• Introduction
• The Azure SQL Database Architecture
• Provisioning an Azure SQL Database
• Connecting to and querying the SQL Database from Server Management Studio
• Creating Resources
• Differences between Azure SQL Database and SQL Server
• Selecting a Service Tier
• Selecting a Migration Tool
• DTU and vCore Pricing Models
• Scaling up the Azure SQL Database Service Tier
• Choosing Between a vCore Pricing Model and DTU based

Topic 3: Backing Up an Azure SQL Database

• Introduction
• Automatic Backups
• Backup Storage
• Backup Retention Period
• Configuring Long-Term Backup Retention for Azure SQL Database

Topic 4: Restoring an Azure SQL Database

• Introduction
• Restore Types
• Point-In-Time Restore
• Long-Term Database Restore
• Restoring DeletedDatabases
• Importing a Database

Topic 5: Securing an Azure SQL Database

• Introduction
• Firewall Rules
• Managing Server-Level Firewall Rules Using the Azure Portal Authentication
• SQL Authentication Azure Active Directory Authentication
• Server-Level and DB level Roles
• Azure Key Vault
• TDE
• Dynamic Data Masking
• Azure Sentinel

Topic 6: Azure Elastic Pools, Elastic Queries & Elastic Jobs

• Intro Elastic Pool
• When Should You Consider Elastic Pools?
• EDTU’s
• Elastic Jobs & Deployment

Topic 7: High Availability and Disaster Recovery

• Geo-Replication
• Failover Groups
• Command line failover and configuration

 Topic 8: Migration from ON-PREM to Cloud

Migration using Lift/Shift Methods

• Import & Export
• DACPAC & BACPAC
• Fixing Orphan Users
• MDB database Migration
• Database Migration Assistant (DMA)
• Database Migration Service (DMS)

Topic 9: Monitoring and Tuning

• Monitoring an Azure SQL Database Using the Azure Portal
• Monitoring an Azure SQL Database Using DMVs
• Monitoring Database Metrics
• Intelligent query processing
• Automatic Tuning
• Clear procedure cache
• Set the query optimizer cardinality estimation model
• Enable or disable query optimization hotfixes
• Enable or disable parameter sniffing
• Enable or disable the identity cache
• Enable or disable a compiled plan stub to be stored in cache when a batch is compiled for the first time.
• Enable or disable collection of execution statistics for natively compiled T-SQL
• Enable or disable the auto-drop functionality of global temporary tables
• Changing cost threshold for parallelism

• Changing instance-level max degree of parallelism

Troubleshooting and Common Issues

CPU Issues

• SQLOS
• Scheduler
• Pre-emptive Scheduler & Cooperative Scheduler
• Quantum
• Scheduling of threads and scheduler
• Scheduling Algorithm
• Thread Life Cycle

Memory Issues

• 32-bit vs 64-bit difference
• NUMA configuration
• Buffer Pool Usage
• SQL Reservation Clerk
• PLE, Memory Grands Pending

IO Issues

• IO stall
• Disk latency
• SQL IO latency

Connection Timeout

• 18456 error
• SSL vs TLS
• NTLM vs Kerberos

Query Performance Impact

• SET STATISTICS IO
• SET STATISTICS TIME
• Parameter sniffing
• Implicit & Explicit conversion
• Lookup’s
• Search Arguments Ability

Experienced:

• SQL Azure (PAAS & IAAS)
• Performance Tuning and implementing best practices / standards
• Microsoft Business Intelligence, Data Warehousing and Data Migrations
• Migrate on premise databases to Azure/Cloud location using(DMS).
• Database Administration and
• Database design, model, development and code
• Database requirement gathering and architect
• Process Automation, Documentation and Knowledge Sharing