SQL Server 2012 / 2014 /

2016 DBA TRAINING

Trainer – Rajkumar Pomaji (Senior SQL DBA)

PRODUCTION / APPLICATION SUPPORT DBA TRAINING CONTENTS

SQL Server 2016 / 2014 DBA TRAINING (Copy right year -2018)
DAY 1:
Training Time: 3 HOURS

Chapter 1:
Planning and Installing SQL Server 2016 Chapter 2:
Configuring and Managing SQL Server Instances
Lesson 1:
 Planning Your Installation Lesson 1:
 Evaluating Installation Requirements  Configuring SQL Server Instances
 Designing the Installation  Instance-Level Settings
 Planning Scale Up versus Scale out Basics  Database Configuration and Standardization
 Shrinking and Growing Databases  Distributed Transaction Coordinator
 Designing the Storage for New Databases  Configuring Database Mail
 Remembering Capacity Constraints  Revision on chapter 1 and chapter 2
 Identifying a Standby Database for Reporting  Q and A session
 Identifying Windows-Level Security and
Service-Level Security
 Performing a Core Mode Installation
 Benchmarking a Server
SQL Server 2016 / 2014 DBA TRAINING
DAY 1:
Training Time: 3 HOURS
Chapter 3: Configuring SQL Server 2016 Components

Lesson 1: Configuring Additional SQL Server Lesson 2: Managing and Configuring Databases
Components  Designing and Managing File groups
 Deploying and Configuring Analysis Services  Configuring and Standardizing Databases
 Deploying and Configuring Reporting Services  Understanding Contained Databases
 Deploying and Configuring SharePoint Integration  Using Data Compression
 Configuring SQL Server Integration Services  Encrypting Databases with Transparent Data
Security Encryption
 Managing Full-Text Indexing  Partitioning Indexes and Tables
 Configuring FILESTREAM  Managing Log Files
 Configuring File Tables  Using Database Console Commands

SQL Server 2016 / 2014 DBA TRAINING

DAY 1:
Training Time: 3 HOURS
Chapter 4: Migrating, Importing, and Exporting

Lesson 1: Migrating to SQL Lesson 2: Managing and Configuring Databases

Server 2016
 Designing and Managing File groups
 Upgrading an Instance to  Lesson porting Data
SQL Server 2016
 Copying and Exporting Data
 Migrating a Database to a
 Using the SQL Server Import and Export Wizard
SQL Server 2016 Instance
 Using BCP to Import and Export Data
 Copying Databases to
Other Servers  Importing Data by Using BULK INSERT

 Migrating SQL Logins  Importing Data by Using OPENROWSET (BULK)

 Using Format Files

 Preparing Data for Bulk Operations

 SELECT INTO

SQL Server 2016 / 2014 DBA TRAINING

DAY 2:
Training Time: 2 HOURS
Chapter 5: SQL Server Logins, Roles, and Users

Lesson 1: Managing Logins and Server Roles Lesson 2: Managing Users and Database Roles
 SQL Logins  Database Users
 Server Roles  Database Roles
 User-Defined Server Roles  Contained Users
 Credentials  Least Privilege
 Application Roles

SQL Server 2016 / 2014 DBA TRAINING

DAY 2:
Training Time: 3 HOURS
Chapter 6: SQL Server Agent, Backup, and Restore

Lesson 1: Managing SQL Server Agent Lesson 2: Configuring and Maintaining a Backup
Strategy.
 Understanding Backup Types
 Executing Jobs by Using SQL Server
Agent  Backing up System Databases

 Managing Alerts  Backing up Replicated Databases

 Managing Jobs  Backing up Mirrored Databases

 Monitoring Multi-Server Environments  Backing up AlwaysOn Replicas

 Using Database Checkpoints
 Using Backup Devices
 Backing Up Media Sets
 Performing Backups
 Viewing Backup History

SQL Server 2016 / 2014 DBA TRAINING

DAY 2:
Training Time: 2 HOURS
Chapter 6: SQL Server Agent, Backup, and Restore

Lesson 3: Restoring SQL Server Databases

 Restoring Databases
 Performing File Restores
 Performing Page Restores
 Restoring a Database Protected with Transparent
Data
 Encryption
 Restoring System Databases
 Restoring Replicated Databases
 Checking Database Status

SQL Server 2016 / 2014 DBA TRAINING

DAY 2:
Training Time: 2 HOURS
Chapter 7: Securing SQL Server 2016

Lesson 1: Managing Database Permissions Lesson 2: Troubleshooting SQL Server Security

 Understanding Securable  Troubleshooting Authentication
 Assigning Permissions on Objects  Troubleshooting Certificates and Keys
 Managing Permissions by Using Database  Troubleshooting Endpoints
Roles
 Using Security Catalog Views
 Protecting Objects from Modification
 Using Schemas Lesson 3: Auditing SQL Server Instances
 Determining Effective Permissions  Using SQL Server Audit
 Configuring Login Auditing
 Using c2 Audit Mode
 Common Criteria Compliance
 Policy-Based Management

SQL Server 2016 / 2014 DBA TRAINING

DAY 3:
Training Time: 4 hours
Chapter 8:a) Mirroring, Replication

Lesson 1: Mirroring Databases Lesson 2: Database Replication

 Database Mirroring  Replication Architecture
 Mirroring Prerequisites  Replication Types
 Configuring Mirroring with Windows  Snapshot Replication
Authentication
 Transactional Replication
 Configuring Mirroring with Certificate
Authentication  Peer-to-Peer Transactional Replication

 Changing Operating Modes  Merge Replication

 Role Switching and Failover  Replication Monitor

 Monitoring Mirrored Databases  Controlling Replication of Constraints, Columns,

and Triggers
 Upgrading Mirrored Databases
 Heterogeneous Data

SQL Server 2016 / 2014 DBA TRAINING

DAY 3:
Training Time: 2 hours

Chapter 8: b) Log shipping

Lesson 1: Transaction Log shipping

 Log shipping Prerequisites

 Benefits of Log shipping
 Configuring Log shipping between two servers
 Troubleshooting Log shipping issues
 Monitoring log shipping from centralized servers or using
powershell.

SQL Server 2016 / 2014 DBA TRAINING

DAY 3:
Training Time: 3 hours
Chapter 9: Clustering and Always On

Lesson 1: Clustering SQL Server 2016 Lesson 2: AlwaysOn Availability Groups

 Fulfilling Edition Prerequisites  What Are AlwaysOn Availability Groups?

 Creating a Windows Server 2012 R2  Meeting Availability Group Prerequisites
Failover Cluster
 Configuring Availability Modes
 Installing a SQL Server Failover Cluster
 Selecting Failover Modes
 Multi-Subnet Failover Clustering
 Configuring Readable Secondary Replicas
 Performing Manual Failover
 Deploying AlwaysOn Availability Groups
 Troubleshooting Failover Clusters
 Using Availability Groups on Failover Cluster
Instances

SQL Server 2016 / 2014 DBA TRAINING

DAY 4:
Training Time: 3 hours
Chapter 10: Troubleshooting SQL Server 2016

Lesson 1: Working with Performance Monitor Lesson 3: Monitoring SQL Server.

 Getting Started with Performance Monitor  Monitoring Activity
 Capturing Performance Monitor Data  Working with Activity Monitor
 Creating Data Collector Sets
Lesson 4: Using the Data Collector Tool.
Lesson 2: Working with SQL Server Profiler  Capturing and Managing Performance Data
 Capturing Activity with SQL Server Profiler  Analyzing Collected Performance Data
 Understanding SQL Trace
 Reviewing Trace Output Lesson 5: Identifying Bottlenecks
 Capturing Activity with Extended Events  Monitoring Disk Usage
Profiler
 Monitoring Memory Usage
 Monitoring CPU Usage
SQL Server 2016 / 2014 DBA TRAINING
DAY 4:
Training Time: 2 hours
Chapter 11: Indexes, Statistics, Maintenance and Concurrency

Lesson 1: Implementing and Maintaining Indexes Lesson 2: Identifying and Resolving Concurrency
Problems
 Understanding the Anatomy of a Balanced Tree
(B-Tree)  Defining Transactions and Transaction Scope

 Understanding Index Types and Structures  Understanding SQL Server Lock Management

 Designing Indexes for Efficient Retrieval  Using AlwaysOn Replicas to Improve Concurrency

 Understanding Statistics  Detecting and Correcting Deadlocks

 Creating and Modifying Indexes  Using Activity Monitor

 Tracking Missing Indexes  Diagnosing Bottlenecks

 Reviewing Unused Indexes  Using Reports for Performance Analysis

 Index Rebuild/ Reorg for removing fragmentation

 Maintenance of Indexes and statistics

SQL Server 2016 / 2014 DBA TRAINING

DAY 4:
Training Time: 2 hours
Chapter 12: Query Processor Internals and Query Tuning

Lesson 1: Query Processor Internals Lesson 2: Query Tuning and Optimization

 Query Processor Internals  Execution Plan Representation ,

 The Plan Cache  Using Graphical , XML , Text, Profiler, Extended

Events
 Compilation-Execution Sequence
Recompilations  Logical and Physical Operators

 Query Logical Simplification  Scan vs. Seek Operations

 Parameterization , Simple vs. Forced ,Skewed  Join Physical Operations

Data Distribution Parameters vs. Local  Index Scan Vs Table Scan
Variables, Changing Parameter Values
 Best practices of DBA and queries optimization
for better performance.

SQL Server 2016 / 2014 DBA TRAINING

DAY 4:
Training Time: 2 hours
Chapter 13: New Features in SQL server 2014 and 2016

Lesson 3: New Features in SQL server 2014 Lesson 3: New Features in SQL server 2016
 New In-Memory OLTP Engine.  Stretch Database
 In-Memory OLTP examples.  Basic Availability Groups
 Enhancements to AlwaysOn Availability Groups.  Distributed Availability Groups
 Enhancements to Backups.  Query Store
 Updateable Columnstore Indexes and its  Live Query Statistics
examples.
 In-Memory OLTP
 Best practices in SQL Server 2014 and SQL
server 2012  Changes in SQL Server Tools
 Temporal Data
 Always Encrypted, Row level security

SQL Server 2016 / 2014 DBA TRAINING

SQL Server 2016 / 2014 DBA TRAINING