Microsoft SQL Server DBA ONLINE TRAINING
Microsoft SQL Server
DBA ONLINE TRAINING
Course
Duration: 35 Hours
FOR FREE DEMO contact us at:
EMail: info@apex-online-it-training.com
ABOUT THE TRAINER
- 10+ YEARS OF INDUSTRY EXPERIENCE
- 6 YEARS OF EXPERIENCE IN ONLINE AND CLASSROOM TRAINING
Microsoft SQL Server DBA online training Course content
Module 1: Introduction to SQL Server
· 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 Services
Ø Instance Unaware Services
Module
2: Installation and configuring SQL Serve
• 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
Module
3: Working with Databases
• 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
Module 4: Implementing Security
• 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
Module 5: Backup and Restoration
• 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.
Module
6: High Availability
• 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.
Module
7: Replication
• 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
Module
8: Automating Administrative Tasks
• 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
Module
9: Advanced Administration Concepts
• 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.
Module
09: SQL Server Architecture
· SQL Server Architecture
Ø Relational Engine
Ø Storage Engine
Ø Buffer pool
Ø Managing execution plans.
· Locking and Blocking
Ø Locks
Ø Deadlocks
Ø Understanding Blocking.
Ø Terminating Processes
Ø Transaction Isolation Levels.
Module
10: Index Management
· 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
Module 11: SQL Server 2000/2005 to
2008 Upgrade Process
Ø SSUA Tool
Ø Upgrade Type
o Inplace Upgrade
o Side By Side Upgrade
Ø Compatibility level
Ø Transferring logins
Ø Transferring Jobs
Ø DTS to SSIS Migration
Module 12: Clustering
Ø 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
Module 13: SQL Server 2008 New
Featured
Ø Central Management Server
Ø Data Collector
Ø Auditing
Ø Resource Governor
Ø Backup Compression