Microsoft MCSA: SQL 2016 Database Administration (70-764 & 70-765)

Unprotected data, untested restores, and unmonitored SQL Server instances create audit gaps and avoidable outages. This course gives database teams a consistent approach to securing, recovering, and tuning SQL Server 2016 and SQL Server workloads in Azure.

Risk and compliance fit: Your team practices encryption, including Always Encrypted and transparent data encryption, permissions, auditing, backup strategy, and restores. These are the areas most often examined in data-protection reviews and recovery-time planning.

Role fit: The course is built for Database Administrators and IT Practitioners with at least a year of SQL Server experience. It also suits experienced DBAs who need to validate their skills. It is not an entry point: staff new to databases should start with fundamentals first.

Time investment: Plan for about 17.7 hours per learner across the SQL Server 2016 Database Administration path, including 70-764 administration topics and the Azure-heavy 70-765 material. Team Leads can assign the modules in sequence over several weeks.

Change-management note: The content targets SQL Server 2016. Team Leads should map the material to the versions your team runs in production. For current Azure-focused credentials, consider pairing this course with Microsoft Certified: Azure Database Administrator Associate (DP-300).

How CBT Nuggets supports the outcome: Modules with Nugget-level Virtual Labs give learners a hands-on environment for practicing tasks like encryption, auditing, permissions, and logins without touching production systems.

IT teams complete this training to keep production SQL Server and Azure SQL environments secure, recoverable, and performing under real pressure. The scenarios come straight from the course. A hardware failure corrupts a database while the Recovery Time Objective (RTO) clock is ticking. A developer with too much privilege runs an UPDATE with the wrong WHERE clause and trashes data. Blocking sessions caused by a poorly designed data access layer bring an application to a crawl.

• Faster, more predictable recovery: Your team builds backup strategies around full, differential, and transaction log backups. They also learn how recovery models fit in, so restores after corruption or user error happen without guesswork.
• Stronger data protection and audit readiness: DBAs practice Always Encrypted, transparent data encryption, custom database roles, and SQL Server auditing. They troubleshoot encryption errors by working down the key hierarchy from the service master key.
• Fewer performance escalations: Team members practice managing and monitoring SQL Server instances and databases, then use what they observe to investigate slowdowns, blocking, and other conditions that can put applications under pressure.
• Higher availability with less downtime: Your team gets hands-on with log shipping, Always On Availability Groups, including manual failover, and Failover Cluster Instances. The 70-765 portion extends these skills to Azure SQL Database and SQL Server VMs.

Each module ends in a challenge-based Virtual Lab, so practitioners rehearse these tasks in a sandbox instead of a production environment.

Knowledge

• How the SQL Server encryption hierarchy works, from the service master key through the database master key to encryption keys, and how Always Encrypted, transparent data encryption, cell-level encryption, and dynamic data masking protect data
• How authentication modes, logins, database users, custom roles, schemas, and permissions control data access, including row-level security
• How full, differential, and transaction log backups and recovery models shape a backup strategy built around recovery time objectives
• Which tools reveal database and query activity: Activity Monitor, DMVs, Query Store, Extended Events, and execution plans
• How SQL Server availability and recovery planning supports outage response, maintenance, and continuity requirements
• How Azure SQL Database and SQL Server on Azure VMs are deployed, secured with firewall rules, and managed alongside on-premises instances

Ability

• Configure SQL Server auditing and troubleshoot encryption errors by tracing the key hierarchy to the root cause
• Restore databases after hardware failure, corruption, or accidental data changes, and provide copies for development and testing
• Identify sessions that cause blocking, and improve problem execution plans by replacing scans with seeks
• Find and fix index fragmentation, missing indexes, and underused indexes, and keep statistics current
• Configure operators and alerts, manage common performance settings, and build performance baselines to pinpoint degradation
• Manage SQL Server databases and instances across on-premises and Azure environments using PowerShell, the Azure CLI, the Azure portal, and SQL Server Management Studio (SSMS)