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