Advanced MS SQL Administration and Programming equips database administrators and developers with advanced skills in SQL Server management, T-SQL programming, performance optimization, security, backup and recovery, and enterprise database administration.
Duration 5 days – 35 hrs
Overview
This course provides advanced-level training on Microsoft SQL Server, focusing on both database administration and T-SQL programming. It covers high-level topics such as performance tuning, high availability, automation, advanced security, and sophisticated T-SQL programming techniques. Through a combination of theoretical instruction and hands-on exercises, participants will gain the skills needed to manage complex SQL Server environments and develop optimized, secure database solutions.
Objectives
- Configure, manage, and troubleshoot advanced SQL Server database environments
- Optimize database performance and query efficiency
- Implement and manage high availability and disaster recovery solutions
- Automate administrative tasks using SQL Server Agent and scripts
- Secure databases using advanced permission models and encryption
- Write complex T-SQL scripts, stored procedures, and user-defined functions
- Apply best practices in database design, indexing, and performance monitoring
Audience
- Experienced Database Administrators (DBAs)
- SQL Developers and Programmer
- Data Analysts and Engineers handling production SQL environments
- IT Professionals seeking in-depth knowledge of SQL Server management and programming
Pre- requisites
- Basic to intermediate experience in SQL Server database administration
- Working knowledge of T-SQL and relational database concepts
- Familiarity with SQL Server Management Studio (SSMS) and SQL Server tools
- Completed a foundational SQL Server course or equivalent hands-on experience
Course Content
Module 1: SQL Server Advanced Architecture and Configuration
- SQL Server engine architecture
- Memory and storage internals
- Instance and database configuration best practices
- Working with multiple SQL Server instances
Module 2: Backup, Restore, and Disaster Recovery Planning
- Advanced backup strategies (file/filegroup, striped backups)
- Restore scenarios (page-level, point-in-time, tail-log)
- Disaster Recovery planning and documentation
Module 3: High Availability and Replication
- Always On Availability Groups setup and monitoring
- Log Shipping and Database Mirroring
- Peer-to-peer and transactional replication
- Quorum and failover management in clusters
Module 4: Performance Monitoring and Query Optimization
- Query execution plans and indexing strategy
- DMVs and performance monitoring tools
- SQL Profiler and Extended Events
- Optimizing TempDB and I/O subsystem
Module 5: Automation and Job Scheduling
- SQL Server Agent deep dive
- Creating and troubleshooting jobs, alerts, and operators
- PowerShell integration for automation
- Custom maintenance scripts and plans
Module 6: Security and Compliance
- Managing permissions and roles
- Advanced security: TDE, auditing, RLS, dynamic data masking
- Authentication methods: Windows, SQL logins, contained users
- Auditing tools and compliance tracking
Module 7: Advanced T-SQL Programming
- Using advanced functions and windowing techniques
- Writing efficient stored procedures and functions
- Cursors, transactions, and error handling with TRY…CATCH
- Dynamic SQL and execution plans
- Working with JSON and XML data in SQL Server
Module 8: Troubleshooting and Real-World Scenarios
- Troubleshooting locking and blocking
- Deadlock detection and resolution
- Resource Governor configuration
- Labs on performance tuning and query optimization
- Final capstone lab: Diagnosing and solving real-world SQL Server issues
