Subcribe and Access : 5200+ FREE Videos and 21+ Subjects Like CRT, SoftSkills, JAVA, Hadoop, Microsoft .NET, Testing Tools etc..

Batch Date: Oct 14th @9:00PM

Faculty: Mr. Praveen Madupu (15+ Yrs Of Real Time Exp,..)

Duration: 45 Days

Venue :
DURGA SOFTWARE SOLUTIONS,
Flat No : 202, 2nd Floor,
HUDA Maitrivanam,
Ameerpet, Hyderabad - 500038

Ph.No: +91 - 8885252627, 9246212143, 80 96 96 96 96

Syllabus:

SQL SERVER DBA

Module 1: SQL Server Fundamentals and Installation (Days 1–7)

Day 1: Introduction to SQL Server DBA

• Introduction to SQL Server and RDBMS concepts.
• Roles and responsibilities of a SQL Server DBA.
• Development, test, staging, and production environments.
• Daily activities and responsibilities of a DBA.
• Overview of database administration career opportunities.

Day 2: SQL Server Architecture

• SQL Server instances and database engine.
• Relational Engine and Storage Engine fundamentals.
• SQL Server services and client connectivity.
• SQL Server memory and transaction processing basics.
• SQL Server editions and versions.

Day 3: SQL Server Installation

• Installation prerequisites and hardware requirements.
• SQL Server edition selection and installation options.
• Default and named instances.
• Authentication modes and service accounts.
• SQL Server installation and configuration basics.

Day 4: SQL Server Management Studio (SSMS)

• SSMS installation and connectivity.
• Object Explorer and Query Editor.
• Activity Monitor and SQL Server error logs.
• SQL Server Configuration Manager.
• Basic T-SQL administration commands.
  

Day 5: System Databases and Database Files

• master, model, msdb, and tempdb.
• MDF, NDF, and LDF files.
• Database pages and extents.
• Data storage and transaction log fundamentals.
• Physical and logical database file names.

Day 6: SQL Server Services and Connectivity

• SQL Server Database Engine service.
• SQL Server Agent service.
• Windows services and service accounts.
• TCP/IP, ports, and named-instance connectivity.
• Basic connection failure troubleshooting.

Day 7: Architecture Revision and Practical Assessment

• Revision of installation and architecture concepts.
• Instance and database identification.
• System database exploration.
• SQL Server environment inventory.
• Basic DBA troubleshooting exercises.

Module 2: Database Administration and Storage (Days 8–14)

Day 8: Database Creation and Management

• Creating databases using SSMS and T-SQL.
• CREATE DATABASE, ALTER DATABASE, and DROP DATABASE.
• Database properties and configuration.
• Database naming conventions.
• Database creation troubleshooting.

Day 9: Data Files, Log Files, and Filegroups

• Primary and secondary data files.
• Transaction log files.
• Creating and managing filegroups.
• File placement and storage planning.
• Database file management.

Day 10: Database Sizing and Autogrowth

• Initial database file size.
• File growth increments and maximum size.
• Percentage-based vs. fixed-size autogrowth.
• Database capacity planning.
• Disk space and database growth monitoring.

Day 11: Database Recovery Models

• SIMPLE recovery model.
• FULL recovery model.
• BULK_LOGGED recovery model.
• Transaction log behavior and truncation.
• Recovery model selection and operational impact.

Day 12: Database States and Options

• ONLINE, OFFLINE, RESTORING, SUSPECT, and RECOVERY_PENDING states.
• SINGLE_USER and MULTI_USER modes.
• READ_ONLY and READ_WRITE databases.
• Database compatibility level.
  Database state troubleshooting.

Day 13: Database Metadata and System Views

• sys.databases.
• sys.database_files.
• sys.master_files.
• Database inventory and configuration queries.
• Retrieving database status, size, and recovery model.

Day 14: Database Administration Practical

• Creating and configuring a database.
• Managing data and log files.
• Configuring recovery models.
• Reviewing database properties.
• Practical database administration assessment.

Module 3: Security, Backup, and Recovery (Days 15–21)

Day 15: SQL Server Security Fundamentals

• Authentication vs. authorization.
• Windows Authentication and SQL Server Authentication.
• Server logins and database users.
• Security principals.
• Principle of least privilege.

Day 16: Roles and Permissions

• Fixed server roles.
• Database roles.
• GRANT, DENY, and REVOKE.
• Schema-level and object-level permissions.
• User access validation and troubleshooting.

Day 17: Backup Fundamentals and Strategy

• Importance of database backups.
• Full, differential, transaction log, and copy-only backups.
• Recovery Point Objective (RPO).
• Recovery Time Objective (RTO).
• Backup retention and scheduling strategy.

Day 18: Full and Differential Backups

• Backup destinations and permissions.
• Performing full database backups.
• Performing differential backups.
• Backup compression and checksums.
• Backup history and verification.

Day 19: Transaction Log Backups

• Transaction log architecture.
• Transaction log backup chain.
• Log truncation and log reuse waits.
• Active transactions and log growth.
• Transaction log backup troubleshooting.

Day 20: Database Restore and Recovery

• Restore sequence and recovery process.
• Restoring full and differential backups.
• Restoring transaction log backups.
• WITH NORECOVERY and WITH RECOVERY.
• Database relocation using WITH MOVE.

Day 21: Point-in-Time Recovery and Validation

• Point-in-time recovery using STOPAT.
• RESTORE VERIFYONLY.
• Backup checksums and backup metadata.
• Test restores and recovery validation.
• Backup and restore practical assessment.

Module 4: SQL Server Agent and Database Maintenance (Days 22–28)

Day 22: SQL Server Agent Fundamentals

• SQL Server Agent architecture.
• Jobs, job steps, and schedules.
• Job owners and execution context.
• Job history and activity monitoring.
• Creating basic automated DBA jobs.

Day 23: Job Scheduling and Troubleshooting

• One-time and recurring schedules.
• Job failures and retry settings.
• Permissions and job ownership.
• Job history and error analysis.
• Troubleshooting jobs that do not execute.

Day 24: Database Mail and Notifications

• Database Mail architecture.
• SMTP configuration fundamentals.
• Mail profiles and accounts.
• SQL Server Agent operators.
• Job failure email notifications.

Day 25: Database Integrity Checks

• Database consistency and integrity.
• DBCC CHECKDB.
• DBCC CHECKTABLE.
• Understanding consistency-check errors.
• Scheduling database integrity checks.

Day 26: Index Fundamentals and Maintenance

• Clustered and nonclustered indexes.
• Index fragmentation.
• Index rebuild vs. reorganize.
• Index maintenance considerations.
• Monitoring index health.

Day 27: Statistics and Maintenance Automation

• SQL Server statistics fundamentals.
• Automatic statistics updates.
• UPDATE STATISTICS.
• Maintenance windows and job scheduling.
• Automating routine maintenance tasks.

Day 28: TempDB Administration

• TempDB architecture and usage.
• TempDB data and log files.
• File sizing and autogrowth.
• TempDB contention fundamentals.
• TempDB space usage monitoring.

Module 5: Monitoring, Performance, and Troubleshooting (Days 29–35)

Day 29: SQL Server Monitoring and DMVs

• Dynamic Management Views (DMVs).
• Sessions, requests, and connections.
• Database status monitoring.
• Current workload inspection.
• Building a basic DBA health-check report.

Day 30: CPU, Memory, and Disk I/O

• CPU utilization and resource pressure.
• SQL Server memory configuration.
• Buffer pool and Page Life Expectancy.
• Disk I/O latency and storage performance.
• Windows performance monitoring fundamentals.

Day 31: Blocking and Transaction Management

• Locks and blocking fundamentals.
• Identifying blocked sessions and lead blockers.
• Blocking chains and long-running transactions.
• Isolation levels and lock escalation.
• Blocking troubleshooting techniques.

Day 32: Deadlocks and Extended Events

• Deadlock concepts and causes.
• Deadlock victim selection.
• Reading deadlock graphs.
• Introduction to Extended Events.
• Deadlock prevention and troubleshooting.

Day 33: Slow Queries and Execution Plans

• Query execution plan fundamentals.
• Actual vs. estimated execution plans.
• Index scans, index seeks, and key lookups.
• Logical reads and query performance.
• Basic query optimization techniques.

Day 34: Transaction Log and Disk-Space Incidents

• Transaction log full errors.
• Log reuse waits and active transactions.
• Missing transaction log backups.
• Disk space shortage and autogrowth issues.
• Safe troubleshooting and corrective actions.

Day 35: Error Logs and Production Troubleshooting

• SQL Server error logs.
• Login failure investigation.
• Service startup issues.
• Failed backups and SQL Agent jobs.
• Incident documentation and root cause analysis.

Module 6: High Availability, Disaster Recovery, and DBA Operations (Days 36–42)

Day 36: High Availability and Disaster Recovery

• HA vs. DR.
• RPO and RTO planning.
• Failover and recovery concepts.
• Business continuity planning.
• Disaster recovery testing fundamentals.

Day 37: Log Shipping

• Log shipping architecture.
• Primary and secondary databases.
• Backup, copy, and restore jobs.
• Monitoring and alerting.
• Failover procedures and limitations.

Day 38: Always On Availability Groups

• Availability Group architecture.
• Primary and secondary replicas.
• Availability databases.
• Synchronous vs. asynchronous commit.
• Automatic and manual failover.

Day 39: Failover Cluster Instances and HA/DR Comparison

• Windows Server Failover Clustering fundamentals.
• Shared storage and instance-level availability.
• Failover Cluster Instance architecture.
• FCI vs. Availability Groups vs. log shipping.
• HA/DR solution selection.

Day 40: SQL Server Patching and Upgrades

• SQL Server version and build identification.
• Cumulative updates and security updates.
• Compatibility levels and upgrade planning.
• Pre-upgrade checks and rollback planning.
• Post-patching validation.

Day 41: Database Migration Fundamentals

• Backup/restore migration approach.
• Detach/attach considerations.
• Database compatibility and application validation.
• Migration of logins, SQL Agent jobs, and dependencies.
• Migration checklist and post-migration verification.

Day 42: Daily, Weekly, and Monthly DBA Operations

• Daily database health checks.
• Backup and job failure reviews.
• Disk space and database growth monitoring.
• Security and maintenance reviews.
• Capacity planning and operational documentation.

Module 7: Real-Time DBA Scenarios and Final Assessment
(Days 43–45)

Day 43: Production Incident Simulation

• Investigating slow database performance.
• Identifying blocking and long-running queries.
• Diagnosing transaction log growth.
• Troubleshooting failed jobs and backups.
• Root cause analysis and incident documentation.

Day 44: End-to-End DBA Practical Assessment

• Database creation and configuration.
• Security and permission management.
• Backup and restore operations.
• SQL Server Agent automation.
• Database integrity and health checks.
• Monitoring and troubleshooting assessment.

Day 45: Final Revision and Interview Preparation

• SQL Server DBA fundamentals revision.
• Frequently asked DBA interview questions.
• Real-time troubleshooting discussions.
• Review of practical assignments.
• Roadmap to intermediate and advanced DBA skills.

Training Methodology

• Daily 90-minute instructor-led online session.
• Concept explanation, live demonstrations, guided practical labs, and Q&A.;
• Weekly revision and practical assessments.
• Final production-style troubleshooting exercise.

Expected Learning Outcomes

• Install and configure SQL Server and use SSMS for administration.
• Create and configure databases, files, and transaction logs.
• Manage logins, users, roles, and permissions.
• Perform backup, restore, and point-in-time recovery exercises.
• Automate routine tasks with SQL Server Agent.
• Perform database integrity checks and basic maintenance.
• Monitor SQL Server and investigate common performance issues.
• Identify blocking, deadlocks, and transaction log problems.
• Explain HA/DR architectures and database migration fundamentals.
• Follow a structured production incident investigation process.

Lab Requirements

• Windows computer or virtual machine.
• SQL Server Developer Edition for non-production learning.
• SQL Server Management Studio (SSMS).
• Recommended 8 GB RAM minimum; 16 GB is preferable.
• Approximately 50–100 GB free disk space, depending on installation and lab needs.
• Additional Windows Server/infrastructure may be needed for advanced HA/DR labs;
• rchitecture demonstrations can be used if unavailable.