Sql Server DBA Training in Bangalore

MS SQL Server 2016

Database Administration

With AlwaysOn & Clustering

Techniques

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

Pages & Extents

 Pages

 Extents [Uniform & Mixed]

 Managing Extent Allocations

 Tracking Free Space

Files and File groups

 Database Files

o Primary data files

o Secondary data files

o Log files

 Database File groups [Primary & Userdefined]

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

 Installing Analysis Services

 Installing & Configuring Reporting Services

 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 Database Mail

 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

1. Migration by using Attach and

Detach Method

2. Migration by using Back and

restore method

3. 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,

High Availability &

Replication

Backup & Restore:

 Recovery Models [Simple, Bulk-Logged &

Full]

 How Backup Works

 Types of backups

1. Full backup

2. Diff backup

3. T-log backup

4. Copy Only

5. Mirror

6. Tail-Log

7. 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

High Availability

 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

Server Clustering

Windows Server 2016 Clustering

 What is a cluster and Overview of Windows

cluster

 Server cluster technologies

1. Server clusters

2. 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

 Resolving concurrency effects 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