• Instructor: Mike Hussy
  • Students: 2359
  • Duration: 5 weeks
Tags:

Microsoft SQL Server 20019 DBA online training Course content

Getting familiar with ITIL Concept

  • Type and Shifts  DBAs
  • Roles and Responsibilities of DBA
  •  History of SQL Server & Code name
  •  New features of SQL Server 2005 & 2008
  •  Different Editions of SQL Server
  •  Tools of SQL Server
  • Hardware and Software Requirements
  • Instances
  • Default Instance
  • Named Instances
  • SQL Server Services
  • Instance  Aware Service  Instance Unaware Services

 

Installing SQL Server 2005 & 2008.

  • Pre-installation steps
  • Installations
  •   Viewing installation process with LOG files
  • Common issues.
  • Adding /Removing components
  •  Installing service packs.
  •  Best Practices after Installing SQL Server

Configuration

  •  Configuring various Services with Local and Domain Account
  •  Startup Parameter
  •  Configuring data file and log file paths
  •   Memory configuration
  •  Remote connections
  •   Configuring Network protocols, Ports

Working with databases.

  •   System Defined databases
  • Steps to move  System databases
  • Handling TempDb issues.

Database Architecture.

  •   Data File  & Log File
  • Filegroups
  • Extents
  •  Pages – types
  •  Page architecture
  •   Tracking free space
  • Log file full – How to solve the problem

Creating Databases

Database Snapshots

Adding files, file groups, and Schema

 Security in SQL Server 2008

  • Types of Authentications.

Windows Authentication

  • Creating logins from windows users and groups
  •  Orphan logins.
  • SQL Server Authentication
  • Creating SQL logins and testing logins
  • Setting authentication Mode
  • Security Auditing
  • Understanding server roles
  • Working with users
  • Resolving orphan users issues
  • Understanding database roles, custom and application roles
  • Understanding permissions
  • Encryption and decryption
  • Major issues
  • Understanding Transaction process
  • Understanding Transaction Log file
  • Checkpoint& Lazy writer process
  • Truncating log file.
  • Database Recovery Models
  •   Full
  • Bulk Logged
  •   Simple
  • Setting recovery model
  • Database Backups
  • Backup Types.
  •   Full
  • Differential
  • Transaction Log
  • File or Filegroup
  • Copy-only, Mirrored and tail log backup
  • Performing Restoration operations

Practical Scenario :

  •   Steps to Restore Master database when Server crashes.
  •   How to Recover Database from Suspect mode.
  •   How to Recover Database when Log file is Corrupted.

 

  • Introduction to High Availability
  • Working with Log Shipping

  Log Shipping Requirement

  Configuring Log Shipping

  Monitoring Log Shipping Status

 Manually performing Fail Over

  Transferring logins

  Log shipping  tables and stored procedures

  Fixing log shipping issues

  • Working with Database Mirroring

  Difference bet Logshipping and Mirroring

 Operating Modes in Mirroring

  Server  Roles  in Mirroring

  Requirements for Mirroring

  Configuring Mirroring

 Performing failover in Mirroring

  Different Ways to monitor Mirroring Status

 Mirroring system tables and stored procedures.

 Major issues with mirroring.

 

  • Replication and advantages
  • Replication Entities
  • Replication Architecture
  • Replication Agents
  • Types of Replications
  • Configuring Replication

 Snapshot Replication

 Transactional Replication

 Merge Replication

  • Peer to peer replication
  • Replication Topologies

Working with Database Mail.

Mail architecture.

Configuring Profiles and Accounts

Creating  Operators

  Sending Mail

  • Configuring linked servers
  • Implementing Automation

  Configuring SQL Server Agent

 Creating  Jobs

  Managing jobs and resolving errors.

 Monitoring jobs.

 Auto alert when jobs are enabled, disabled or failed.

  • Managing replication.
  • Monitoring and Tuning Replication

 Maintenance plans

  • Monitoring and Tuning SQL Server

  Performance counters setup

  Measuring performance of server.

  Tuning queries.

  Tuning databases.

  Tuning physical architecture of databases.

  Using DTA.

  • Monitoring Tools

 Performance  Monitor

  SQL Server Profiler

  Database Engine Tuning Advisor.

  Dynamic Management Views.

  SQL Server and Windows Event Logs.

  • Troubleshooting

Physical server performance.

 Connectivity to SQL Server

  Database Performance.

  Using the DAC.

 Shrink Files

 Using DBCC commands.

   SQL Server Architecture

  Relational Engine

  Storage Engine

  Buffer pool

  Managing execution plans.

  • Locking and Blocking

 Locks

  •   Deadlocks
  •   Understanding Blocking.
  •  Terminating Processes
  •   Transaction Isolation Levels.

 Why Indexes

  • Type of Indexes
  •   Cluster Index
  •   Noncluster Index
  •    Full Text Index
  • Creating Indexes
  • Manage Index Fragmentation

  DBCC Showcontig

  •   DM.DB_Index_Physical_Stats
  •   Index Rebuilding and Reorganizing.
  • Manage Statistics
  • Performing database integrity checks

  SSUA Tool

  Upgrade Type

o   Inplace Upgrade

o   Side By Side  Upgrade

  •   Compatibility level
  •  Transferring logins
  •   Transferring Jobs
  •   DTS to SSIS Migration
  • Why Clustering?
  •   Overview of Windows Clustering.
  •   Types of Cluster
  •   Pre- Requisites to implement Cluster
  •   Installing  SQL Server Failover Cluster
  •   Applying service packs and hot fixes
  •   Performing failover
  •   Adding node on a SQL Server Failover cluster.
  •   Troubleshooting cluster issues
  •   Central Management Server
  •  Data Collector
  •   Auditing
  •   Resource Governor
  •  Backup Compression

Curriculum is empty

Price

Free
Call Now ButtonCall NowRequest Form