Training & Certification

Palium Skills offers the following SQL Server DBA training to individuals and corporates -

Course Outline:

Spring Training Course Overview

Module 1: SQL Server Architecture

Introduction to SQL Server 2012

• Overview on RDBMS and Beyond Relational

• Big picture of SQL Server 2012

• Components & Services of SQL Server 2012

• Roles of production DBA

• System Databases

• Master, Model, MSDB, Tempdb & Resource

SQL Server 2012 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 & User defined]

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 2012 Installation

• Planning the System/Pre Requisites

• Installing SQL server 2008 R2 /2012

• Installing Analysis Services

• Installing & Configuring Reporting Services

• Best Practices on Installation

• Uninstalling SQL server

• Common Installation Issues

Upgrading to SQL server 2008 R2/2012

• 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 server2008 R2 to 2012

• 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 2012

• 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 byusing 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 Always On Availability Groups

• Always On Overview

• Understanding Concepts and Terminology

• Availability Modes

• Types of fail overs

• Pre requisites for Always On 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: 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 forrebuilding/re organizing indexes

• Best Practices on Indexing Locking & Concurrency

• Isolation Levels in SQL Server

• Locking in SQL Server

• Resolving concurrency effects in SQLServer

• 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 errorlogs

• 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 Counters Thresholds

• Case study: Effects of MAXDOP query hinting SQL Server