SQL Server Administration Advanced

Strengthen your SQL Server administration skills with advanced training in security, data integration, and version upgrades. This hands-on course tackles the real-world challenges of managing complex database environments, covering robust access control, SSIS package design and automation, and upgrade planning strategies so you can keep enterprise SQL Server systems secure, efficient, and current.

What You'll Learn

By the end of this course, you will be able to:

• Implement role-based access control and apply the principle of least privilege across SQL Server environments
• Audit login events and troubleshoot permission and access issues
• Design, deploy, and schedule SSIS packages for reliable data integration workflows
• Add logging, checkpoints, and error handling to SSIS packages for production readiness
• Optimize SSIS performance through parallelism and partitioning strategies
• Assess upgrade readiness and plan in-place or side-by-side upgrade strategies
• Execute test and production upgrades with proper backups, validation, and post-upgrade optimization

Requirements

• Strong understanding of SQL Server management, backup, and restore processes
• Familiarity with SQL Server Agent, security models, and database maintenance tasks

Audience

• Database Administrators and Engineers
• System Administrators
• DevOps and Infrastructure Engineers
• Senior DBAs

Topics

SQL Server Security and Access Control

• SQL Server Authentication Modes
• Managing Logins, Users, and Roles
• Implementing Server-Level and Database-Level Roles
• Configuring Permissions Using the Principle of Least Privilege
• Designing Role-Based Access Control (RBAC)
• Managing Access in Multi-Tenant Databases
• Auditing and Tracking Login Events
• Troubleshooting Permission and Access Issues
• Applying Security and Compliance Best Practices

SQL Server Integration Services (SSIS)

• Understanding Control Flow and Data Flow
• Designing SSIS Packages for Data Integration
• Implementing Variables, Parameters, and Expressions
• Adding Logging, Checkpoints, and Error Handling
• Deploying Packages to the SSIS Catalog
• Scheduling and Automating Packages with SQL Server Agent and PowerShell
• Optimizing SSIS Performance Through Parallelism and Partitioning
• Monitoring SSIS Executions Using SSMS Reports
• Managing and Versioning SSIS Packages
• Troubleshooting Failed Executions and Environment Issues

SQL Server Upgrade and Migration

• Comparing SQL Server 2016 and 2019 Features
• Assessing Upgrade Readiness and Compatibility
• Planning In-Place and Side-by-Side Upgrade Strategies
• Preparing for Upgrades with Backups and Pre-Checks
• Executing Test and Production Upgrades
• Upgrading SSIS, SSRS, and Full-Text Search Components
• Validating and Optimizing Post-Upgrade Performance
• Updating Statistics and Indexes
• Developing Post-Upgrade Maintenance and Monitoring Plans