Sql server 2008R2 dba training@SQLSERVER MASTERS - PowerPoint PPT Presentation

About This Presentation
Title:

Sql server 2008R2 dba training@SQLSERVER MASTERS

Description:

please find the course contents in brief. Module: 1 Architecture & Internals of SQL Server Engine Module: 2 Installing, Upgrading, Configuration, Managing Services and Migration Module: 3 Security, Automation & Monitoring Module: 4 Backup & Restore, High Availability/Disaster Recovery techniques & Replication Module: 5 Windows & SQL Server Clustering & AlwaysOn Availability Groups Module: 6 Locking & Concurrency,Performance Tuning, Indexing & Optimizing SQL Server For full course details please visit our website www.sqlservermasters.com Duration for course is 30 days or 45 hours and special care will be taken. It is a one to one training with hands on experience. * Resume preparation and Interview assistance will be provided. For any further details please contact +91-9052666559 or visit www.sqlservermasters.com please mail us all queries to training@sqlservermasters.com – PowerPoint PPT presentation

Number of Views:349
Slides: 33
Provided by: sssql
Category:

less

Transcript and Presenter's Notes

Title: Sql server 2008R2 dba training@SQLSERVER MASTERS


1
Project oriented online realtime training on sql
  • (www.sqlservermasters.com)

2
Project oriented online realtime training on sql
  • Module 1 Architecture Internals of SQL Server
    Engine
  • Module  2 Installing, Upgrading, Configuration,
    Managing Services and Migration
  •  
  • Module 3 Security, Automation Monitoring
  • Module 4 Backup Restore, High Availability
    Replication
  •  
  • Module 5 SQL Server Clustering AlwaysOn
    Availability Groups
  •  
  • Module 6 Performance Tuning, Indexing
    Optimizing SQL Server

3
Project oriented online realtime training on sql
  • 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

4
Project oriented online realtime training on sql
  • 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 User-defined

5
Project oriented online realtime training on sql
  • 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
  • Checkpoints

6
Project oriented online realtime training on sql
  • 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

7
Project oriented online realtime training on sql
  • 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

8
Project oriented online realtime training on sql
  • M o d u l e 2
  • I ns t al l ing, U p gra d ing, C on f ig u
    ration, M anaging ser v ic es and M
    igration
  • S Q L ser ver 2 0 1 2 Ins t al l
    at ion
  • Planning t h e System/Pre-Re q uisites
  • Installing SQL server 2008 R2 /201 2
  • Installing Analysis Services
  • Installing Configuring Reporting Services
  • Best Practices on Installat ion
  • U ninst alling SQL server
  • Common Installat ion Issues

9
Project oriented online realtime training on sql
  • U p gra d ing to S Q L ser v er 2 0 0
    8 R 2 / 2 0 1 2
  • U pgrading th e server by applying service
    packs
  • U pgrading t h e server by applying H ot fi xes
  • In-Place Vs. Side-by-Side upgradations
  • Pre U pgrade C hecks / pre re q uisites
  • U pgrade advisor
  • In-Place U pgradation from SQL server 200 8 R2
    to 2012
  • Best Practices to follow w h ile upgrading

10
Project oriented online realtime training on sql
  • M anaging ser v ic es
  •  
  • Server/Engine Connect ivity issues
  • Security / F irewall access issues
  • Starting and Stopping Services t h roug h
  • Configuration manager
  • Net Command
  • M anagement Studio
  • Start U p parameters
  • Start ing SQL server in single user mode

11
Project oriented online realtime training on sql
  • C onf ig u ring S Q L S er v er 2 0 1 2
  • Configuring Network Protocols from SQL Server
    configuration manager
  • Configuring Client Protocols from SQL Server
    configuration manager
  • Dedicated Administrat or Connection
  • Enabling advanced features by using facets
  • Connecting t o DAC
  • Configuring Database M ail
  • Configuring Registered servers
  • Configuring Central M anagement Servers C M
    S
  • Querying data from different servers by using C M
    S
  • Configuring ot h er set t ings t h roug h
    SP_Configure
  • Configuring Server memory set t ings
  • Configuring Database Set t ings
  • Tempdb configuration
  • Best Pract ices on configuration t empdb
    Database set t ings

12
Project oriented online realtime training on sql
  • M igrating S Q L s er v er

  • Side-By- Side M igrat ion Tec h ni q ues
  • Difference between in-place Side by Side M
    igration / U pgrada tion
  • Advantages/Disadvantages of In-Place t o
    Side-by-Side
  • M igrating Databases
  • migration by using At tac h and Detac h M et
    h od
  • iM igration by using Back and restore met h od
  • mira t ion by using Copy Database W iz ard
  • migrating Logins F I x ing Orp h aned U
    sers
  • Creating and migrating linked servers
  • Imgrating J obs
  • Data movement by using Import E x port wi z
    ard
  • migrating jobs logins by using SSIS

13
Project oriented online realtime training on sql
  • M o d u l e 3 S ec u rit y , A u t o
    m at ion M onit oring  
  • A u t o m at ing A d m inis t rat I v e
    Ta s k s
  • About SQL server Agent
  • Creating Jobs, Alerts and Operators
  • Sc h eduling t h e Jobs
  • W orking wi t h Job activ ity M
    onitor
  • Resolving failure Jobs
  • Configuring Alert system in SQL server agent
  • Best practices on job maintenance

14
Project oriented online realtime training on sql
  • M onitoring S Q L S er v er
  • The Goal of M onitoring
  • Choosing t h e Appropriate M o nitoring
    Tools
  • M onitoring h ealt h status by using server
    Dash boardD M Vs
  • M o nit oring Job activi t ies by job activi
    t y monitor
  • M onitoring SQL Server process by server
    activi t y monitor
  • M o nitoring SQL Server Error Logs / W
    indows by log file viewer
  • Best Practices on M onitoring

15
Project oriented online realtime training on sql
  • Se c u rit y
  • Security Principles Aut h entica t ions
  • Server and Database Roles
  • U ser-defined server roles
  • Server and Database Principles
  • Server Database Securable
  • Creating Logins and mapping Users to databases
  • Creating Sc h emas credentials
  • Default Sc h ema for Groups
  • Enabling contained databases
  • Creating users for contained databases
  • Connecting t o contained databases from SS M S
  • Role permissions for C M S and SQL Server Agent
  • Granting t o Object level Permissions
  • Best Practices on security

16
Project oriented online realtime training on sql
  • M o d u l e 4 B ac k u p R e
    s t ore, H ig h A v aila b ili t y
    R e p li c at ion  
  • B ac k u p Re s t ore
  • Recovery M odels Simple, Bulk-Logged Full
  • H ow Backup W orks
  • Types of backups
  • F ull backup
  • Diff backup
  • T-log backup
  • Copy Only
  • M irror
  • Tail-Log
  • Compressed backups

17
Project oriented online realtime training on sql
  • Restoring M odes W it h 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 ex ecuting a
    Backup Plan
  • Creating M aintenance Plans
  • Resolving Backup failures in Real time scenarios
  • Best Practices on Backup Recovery

18
Project oriented online realtime training on sql
  • L og S h i p p ing
  • Log-S h ipping Arc h itecture
  • Building DRS for log-shipping
  • Pre-req uisites/Log-S h ipping Process
  • Deploying Log S h ipping
  • W orking with Log S h ipping M onitor
  • Logs hipping Role c h anging F ail-Over
  • Removing Log Sh ipping
  • F re q uently Raised Errors In Log-Shipping
  • Case study H ow to add files to a log- s hipped
    database
  • Best Practices on Log-Sh ipping

19
Project oriented online realtime training on sql
  • D at a b as e M irroring
  • Overview of Database M irroring
  • Operating M odes in Database M irroring
  • Pre-Re q uisites for Database M irroring
  • Deploying Database M irroring
  • F ail-Over from Principle to M irror
  • W o rking with Database mirroring monitor
  • Advantages Disadvantages of database mirroring
  • Database Snaps h ots
  • U s ing Database Snaps h ots for reporting
    purposes.
  • Case study on moving mirrored files
  • Best practices on M irroring

20
Project oriented online realtime training on sql
  • R e p l icat ion
  • Replication Overview
  • Replication M o dels (snaps h o
    t/Transactional / M erge/Peer to Peer)
  • Replication agents
  • Configuring Distributor
  • Deploying Transactional Replication for H i g h
    Availability
  • Deploying M erge Replication for Bi-directional
  • Creating Subscriptions H omogeneous / h
    eterogeneous
  • M onitoring Replication by using replication
    monitor
  • Scripting Removing Replication
  • Best Practices on Replication
  • Configuring peer to peer replication
  • Fre q uently asked q uestions in replication

21
Project oriented online realtime training on sql
  • M o d ule 5 s q l s er v er cl u s t
    erI ng grou p a v aila b I l iIt y
    widows server 201 2 clustering
  • w h at is a cluster and Overview of windows
    cluster
  • Server cluster tec h nologies
  • Server clusters
  • NLB clusters Network load balancing
  • Basic architecture of server clusters
  • Networks in clustering Public Private
  • H ow cluster works
  • H ealth Detection Looks alive, Is alive

22
Project oriented online realtime training on sql
  • Introduction to fail-over cluster manger
  • Validating t he cluster configuration
  • Creating cluster t h rough fail-over cluster
    manager
  • Adding Nodes to t he cluster 2/ 3 Node
    cluster
  • Configuring MSDTC as a cluster aware application
  • Active - passive Vs Active - Active Clustering
  • Adding/Evicting Nodes to/from t he cluster
  • Adding volumes to t he roles/services in the
    cluster
  • Simulating t he failover for the resources
  • F ailing over t h e core cluster resources to t
    he anot her node

23
Project oriented online realtime training on sql
  • I ns t al l I ng S q l S er v er r 2 F
    ailo v er clu s t er
  • Pre-SQL Server Installation Tasks.
  • Configure SQL ServerRelated Service Accounts and
    Service Account Security
  • Stop Unnecessary Processes or Services
  • C h eck for Pending Reboots
  • Install SQL Server Setup Support Files
  • SQL Server 201 2 Setup
  • Install t h e F irst Node
  • Perform Post installation Tasks
  • Verify the Configuration
  • Set t h e Preferred Node Order for Failover
  • Configure a Static TCP/IP Port for the SQL Server
    Instance

24
Project oriented online realtime training on sql
  • S q l S er v er r 2 F ailo v er clu s
    t er
  • Install SQL Server Service Packs, Patc h es, and
    H ot fix es
  • Introducing Failover Cluster Management
  • Monitoring t h e Cluster Nodes
  • Adding volumes to cluster roles
  • Clustered SQL Server Administration
  • F ail 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

25
Project oriented online realtime training on sql
  • A l w a y s an a v aila b il I t y G
    rou p s
  • 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

26
Project oriented online realtime training on sql
  • H ig h A v ail a b Ii l Ii t y I nt ero
    p era b ilian d C oe x I s t ence
  • Database Mirroring and Log Shipping
  • Database Mirroring and Database Snapshots
  • Database Mirroring and Failover Clustering
  • Replication and Log Shipping
  • Replication and Database Mirroring
  • F ailover Clustering and AlwaysOn Availability
    groups

27
Project oriented online realtime training on sql
  • M o d ul e 6 p erf orm ance Tuning, I n d
    e x ing s q l s er ver
  •  
  • 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

28
Project oriented online realtime training on sql
  • 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

29
Project oriented online realtime training on sql
  • I n d ex ing
  • 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

30
Project oriented online realtime training on sql
  • Locking C oncurrency  
  • 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

31
Project oriented online realtime training on sql
  • 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.

32
Project oriented online realtime training on sql
  • P erformance T uning
  • 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 (DMVs)
  • 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 hint in SQL
    Server
Write a Comment
User Comments (0)
About PowerShow.com