- Instructor: Mike Hussy
- Students: 2359
- Duration: 5 weeks
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
- Default Instance
- Named Instances
- SQL Server Services
- Instance Aware Service Instance Unaware Services
Installing SQL Server 2005 & 2008.
- Pre-installation steps
- Viewing installation process with LOG files
- Common issues.
- Adding /Removing components
- Installing service packs.
- Best Practices after Installing SQL Server
- 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.
- Data File & Log File
- Pages – types
- Page architecture
- Tracking free space
- Log file full – How to solve the problem
Adding files, file groups, and Schema
Security in SQL Server 2008
- Types of Authentications.
- 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
- Bulk Logged
- Setting recovery model
- Database Backups
- Backup Types.
- 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
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
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
- Peer to peer replication
- Replication Topologies
Working with Database Mail.
Configuring Profiles and Accounts
- Configuring linked servers
- Implementing Automation
Configuring SQL Server Agent
Managing jobs and resolving errors.
Auto alert when jobs are enabled, disabled or failed.
- Managing replication.
- Monitoring and Tuning Replication
- Monitoring and Tuning SQL Server
Performance counters setup
Measuring performance of server.
Tuning physical architecture of databases.
- Monitoring Tools
SQL Server Profiler
Database Engine Tuning Advisor.
Dynamic Management Views.
SQL Server and Windows Event Logs.
Physical server performance.
Connectivity to SQL Server
Using the DAC.
Using DBCC commands.
SQL Server Architecture
Managing execution plans.
- Locking and Blocking
- Understanding Blocking.
- Terminating Processes
- Transaction Isolation Levels.
- Type of Indexes
- Cluster Index
- Noncluster Index
- Full Text Index
- Creating Indexes
- Manage Index Fragmentation
- Index Rebuilding and Reorganizing.
- Manage Statistics
- Performing database integrity checks
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
- Resource Governor
- Backup Compression