Module 1: SQL Server Architecture

Introduction to SQL Server 2016

Overview on RDBMS and Beyond Relational

Big picture of SQL Server 2016

Components & Services of SQL Server 2016

Roles of production DBA

System Databases

Master, Model, MSDB, Tempdb & Resource

SQL Server 2016 Editions & Capacity Planning

Principal Editions [Enterprise, BI,Standard]

Specialized Editions [Web]

Breadth Editions [Developer, Express]

Hardware Requirements

OS and Software requirements

Physical and Logical Architecture of SQL server

Pages & Extents

Pages

Extents [Uniform & Mixed]

Managing Extent Allocations

Tracking Free Space

Files and File groups

Database Files

Primary data files

Secondary data files

Log files

Database File groups [Primary & Userdefined]

Logical Architecture:

Tables, views etc. 

Thread and Task Architecture

Allocating threads to CPU

Affinity Mask

IO and Processor affinity mask

Configuring Affinity masks

Boost SQL Server priority

Hot Add CPU

Memory Architecture

32-bit Vs 64-bit Architecture

Dynamic Memory Management

Effects of min and max server memory

Buffer Management

The Relational Engine

The Command Parser

The Query Optimizer

The Query Executor

Using AWE

Configuring Memory Settings

The Buffer Pool and the Data Cache

Checkpoints

Hot Addition of memory

Difference between Checkpoint & Lazy writer

T-Log Architecture

Transaction Log Logical Architecture

Transaction Log Physical Architecture

Recovery phases [Analysis, Redo & Undo]

Checkpoint Operation

Write-Ahead Transaction Log

Managing T-log

Truncating and shrinking the log file

Managing T-Log issues by using DBCC commands

Module 2: Installing, Upgrading, Configuration, Managing services and Migration SQL server 2016 Installation.

Planning the System/Pre-Requisites Installing SQL server 2008 R2 /2016 Best Practices on Installation, Uninstalling SQL server

Common Installation Issues

Upgrading to SQL server 2008 R2/2016, Upgrading the server by applying service packs

Upgrading the server by applying Hot fixes

In-Place Vs. Side-by-Side upgradations

Pre-Upgrade Checks/pre-requisites

Upgrade advisor

In-Place Upgradation from SQL server 2008 R2 to 2016

Best Practices to follow while upgrading

Managing services

Server/Engine Connectivity issues

Security/Firewall access issues

Starting and Stopping Services through

Configuration manager

Net Command

Management Studio

Start Up parameters

Starting SQL server in single user mode

Starting SQL server with minimal configuration

Case study: Moving System databases from one location to another location.

Case Study: Starting the SQL Server without tempdb

Configuring SQL Server 2016

Configuring Network Protocols from SQL Server configuration manager

Configuring Client Protocols from SQL Server configuration manager

Dedicated Administrator Connection Enabling advanced features by using facets

Connecting to DAC

Configuring Registered servers Configuring Central Management Servers [CMS]

Querying data from different servers by using CMS

Configuring other settings through SP_Configure

Configuring Server memory settings

Configuring Database Settings

Tempdb configuration: Best Practices on configuration tempdb & Database settings

Migrating SQL server

Side-By- Side Migration Techniques

Difference between in-place & Side by Side Migration/Upgradation

Advantages/Disadvantages of In-Place to Side-by-Side

Migrating Databases

Migration by using Attach and

Detach Method

Migration by using Back and

restore method

Migration by using Copy Database Wizard

Migrating Logins [Fixing Orphaned Users]

Creating and migrating linked servers

Migrating Jobs

Data movement by using Import & Export wizard

Migrating jobs & logins by using SSIS

Module 3: Security,

Automation & Monitoring

Automating Administrative Tasks

About SQL server Agent

Creating Jobs, Alerts and Operators

Scheduling the Jobs

Working with Job activity Monitor

Resolving failure Jobs

Configuring Alert system in SQL server agent

Best practices on job maintenance

Monitoring SQL Server

The Goal of Monitoring

Choosing the Appropriate Monitoring Tools

Monitoring health status by using server

Dashboard & DMV’s

Monitoring Job activities by job activity monitor

Monitoring SQL Server process by server activity monitor

Monitoring SQL Server Error Logs/Windows by log file viewer

Best Practices on Monitoring

Security

Security Principles & Authentications

Server and Database Roles

User-defined server roles

Server and Database Principles

Server & Database Securable

Creating Logins and mapping Users to databases

Creating Schemas & credentials

Default Schema for Groups

Enabling contained databases

Creating users for contained databases

Connecting to contained databases from SSMS

Role permissions for CMS and SQL Server Agent

Granting to Object level Permissions

Best Practices on security

Module 4: Backup & Restore,

Backup & Restore:

Recovery Models [Simple, Bulk-Logged &

Full]

How Backup Works

Types of backups

Full backup

Diff backup

T-log backup

Copy Only

Mirror

Tail-Log

Compressed backups

Restoring Modes [With Recovery, No

Recovery, Read only/Standby]

Disaster Recovery Planning

Performing Restore (point-in-time

recovery)

Partial availability of database.

Database Recovery advisor

Backup strategy: Developing and executing

a Backup Plan

Creating Maintenance Plans

Resolving Backup failures in Real time

scenarios

Best Practices on Backup & Recovery

Log Shipping

Log-Shipping Architecture

Building DRS for log-shipping

Pre-requisites/Log-Shipping Process

Deploying Log Shipping

Working with Log Shipping Monitor

Logs hipping Role changing [Fail-Over]

Removing Log Shipping

Frequently Raised Errors In Log-Shipping

Case study: How to add files to a logshipped

database

Best Practices on Log-Shipping Database Mirroring

Overview of Database Mirroring

Operating Modes in Database Mirroring

Pre-Requisites for Database Mirroring

Deploying Database Mirroring

Fail-Over from Principle to Mirror

Working with Database mirroring monitor

Advantages & Disadvantages of database mirroring

Database Snapshots

Using Database Snapshots for reporting purposes.

Case study on moving mirrored files

Best practices on Mirroring

AlwaysOn Availability Groups

AlwaysOn Overview

Understanding Concepts and Terminology

Availability Modes

Types of fail-overs

Pre-requisites for AlwaysOn configuration

Configuring Availability Groups

Monitoring Availability groups

Add/remove database/replica

Suspend/resume an availability database

Backups on Secondary

AlwaysOn Failover Cluster Instances

Online Operations

Replication

Replication Overview

Replication Models

(snapshot/Transactional/Merge/Peer to

Peer)

Replication agents

Configuring Distributor

Deploying Transactional Replication for

Deploying Merge Replication for Bidirectional

Creating Subscriptions [Homogeneous /

heterogeneous]

Monitoring Replication by using replication monitor

Scripting & Removing Replication

Best Practices on Replication

Configuring peer to peer replication

Frequently asked questions in replication

Module 5: Windows & SQL(Clustering and Always-ON)

Server Clustering

Windows Server 2016 Clustering

What is a cluster and Overview of Windows cluster Server cluster technologies

Server clusters

NLB clusters [Network load balancing]

Basic architecture of server clusters

Networks in clustering [Public & Private]

How cluster works

Health Detection [Looks alive, Is alive]

Introduction to Windows Server 2016

Basic elements of a cluster with single

quorum.

Adding Roles and features by using server

manager

Introduction to fail-over cluster manger

Validating the cluster configuration

Creating cluster through fail-over cluster

manager

Adding Nodes to the cluster [2/3 Node

cluster]

Configuring MSDTC as a cluster aware

application

Active – passive Vs Active – Active

Clustering

Adding/Evicting Nodes to/from the cluster

Adding volumes to the roles/services in the

cluster

Simulating the failover for the resources

Failing over the core cluster resources to

the another node

Installing SQL Server 2016 Fail-Over

Clustering

Pre-SQL Server Installation Tasks.

Configure SQL Server–Related Service

Accounts and Service Account Security

Stop Unnecessary Processes or Services

Check for Pending Reboots

Install SQL Server Setup Support Files

SQL Server 2016 Setup

Install the First Node

Add Nodes to the Instance

Perform Post installation Tasks

Verify the Configuration

Set the Preferred Node Order for Failover

Configure a Static TCP/IP Port for the SQL

Server Instance

Administering a SQL Server 2016

Failover Cluster

Install SQL Server Service Packs, Patches,

and Hot fixes

Introducing Failover Cluster Management

Monitoring the Cluster Nodes

Adding volumes to cluster roles

Clustered SQL Server Administration

Fail over resources/roles between the nodes

Automatic failover & Failback

Destroying a Cluster- Using Failover

Cluster Management

Uninstalling a Failover Clustering Instance

Best Practices on Clustering

High Availability: Interoperability and

Coexistence

Database Mirroring and Log Shipping

Database Mirroring and Database

Snapshots

Database Mirroring and Failover Clustering

Replication and Log Shipping

Replication and Database Mirroring

Failover Clustering and AlwaysOn

Availability groups

Module 6: Performance

Tuning, Indexing & Optimizing SQL Server

Optimizing SQL server

Policy based management

Policy based management implementation

Creating Policy & Condition

Evaluating polices

Resource governor

Resource pool & Workloads

Using resource governor from SSMS

Monitoring Resource governor

Change data capture [CDC]

Enabling CDC at Database and table level

Compression techniques

Data & Backup compression

Row compression & Page compression

Monitoring data compression

Partitioning – A big picture

Table and index partitioning

Creating a partition function/schema

Indexing

Index Architecture

How to optimally take advantage of indexes

Clustered & Non-Clustered indexes

Covering Index or index with included

column

Creating covering indexes

Filtered indexes

Creating filtered indexes to minimize the CPU pressure

Column store Index Overview

Column store Index Fundamentals and Architecture

Creating column store index to improve the performance

Index Fragmentation

How to determine fragmentation

Creating maintenance plan for rebuilding/re-organizing indexes

Best Practices on Indexing

Locking & Concurrency

Isolation Levels in SQL Server

Locking in SQL Server

Lock modes – Shared, Update, Exclusive,

Intent, Schema, bulk-update, key-range

Lock escalation in SQL server

Blocking [SP_Who2]

Resolving blocking issues in SQL Server

Working with Activity Monitor Live &

Dead Locks Trace flags to capture dead locks

Capturing dead lock information in error logs

SQL Profiler [How to capture events data by using Profiler]

Capturing deadlock events in profiler

Deadlocks and deadlock chain detection.

Performance Tuning

Factors That Impact Performance

Tools used SQL Profiler, Database Tuning

Advisor, System Monitor

Introduction to  Database Tuning Advisor [DTA]

Analyzing the profiler data by using DTA

Performance Monitor [System Monitor]

Correlate SQL Profiler Data with

Performance Monitor Data

New Dynamic Management Views (DMV’s)

Best Practices on Performance Tuning

Case Study A: Performance Counters

Setup-Collect-Analyze

Case Study B: Performance CountersThresholds

Case study: Effects of MAXDOP query hint

in SQL Server