MCSA: SQL 2016 Database Administration


comp name

This Certification validates your skills in database installation, administration, configuring tasks, cloud deployment, upgradation and migrations, backup and restore, security and others.
Earning certification qualifies you for a position of Database administrator or infrastructure specialist.

Prerequired Exam(s) Or Skills :
Its Not mandatory but its recommended to pass an optional exams like MTA Database or MTA Developer, or if you have enough skills in dba you can directly attempt to MCSA SQL 2016 db administration.

To Earn MCSA SQL 2016 Database administration certifications, Candidates should pass 2 exams that are listed below.



Exam 70-764 : Administering a SQL Database Infrastructure


Exam Details

Required Exam(s) 70-764
Durations 120 Minutes (40 - 60 Questions)
Question Types Multiple choice, drag and drop, Fill in the blanks and simulation types.
Exam Fees Each Exam Rs.4800(Approx)
Exam Policy Click here to read the Exam policies.
Recommended Training Administering a SQL Database Infrastructure

Exam Pattern

Below are the exam objectives of Exam 70-764 : Administering a SQL Database Infrastructure
Configure data access and auditing (20–25%)
Configure encryption
  • Implement cell-level encryption, implement Always Encrypted, implement backup encryption, configure transparent data encryption, configure encryption for connections, troubleshoot encryption errors
Configure data access and permissions
  • Manage database object permissions, create and maintain users, create and maintain custom roles, configure user options for Azure SQL Database, configure row-level security, configure dynamic data masking
Configure auditing
  • Configure an audit on SQL Server, query the SQL Server audit log, manage a SQL Server audit, configure an Azure SQL Database audit, analyze audit logs and reports from Azure SQL Database
Manage backup and restore of databases (20–25%)
Develop a backup strategy
  • Back up very large databases, configure alerting for failed backups, back up databases to Azure, manage transaction log backups, configure database recovery models, configure backup automation
Restore databases
  • Perform piecemeal restores, perform page recovery, perform point-in-time recovery, restore file groups, develop a plan to automate and test restores
Manage database integrity
  • Implement database consistency checks, identify database corruption, recover from database corruption
Manage and monitor SQL Server instances (35–40%)
Monitor database activity
  • Monitor current sessions, identify sessions that cause blocking activity, identify sessions that consume tempdb resources, configure the data collector
Monitor queries
  • Manage the Query Store, configure Extended Events and trace events, identify problematic execution plans, troubleshoot server health using Extended Events
Manage indexes
  • Identify and repair index fragmentation, identify and create missing indexes, identify and drop underutilized indexes, manage existing columnstore indexes
Manage statistics
  • Identify and correct outdated statistics, implement Auto Update Statistics, implement statistics for large tables
Monitor SQL Server instances
  • Create and manage operators, create and manage SQL Agent alerts, define custom alert actions, define failure actions, configure database mail, configure Policy-Based Management, identify available space on data volumes, identify the cause of performance degradation
Manage high availability and disaster recovery (20–25%)
Implement log shipping
  • Configure log shipping, monitor log shipping
Implement AlwaysOn Availability Groups
  • Configure Windows clustering, create an availability group, configure read-only routing, manage failover, create distributed availability groups
Implement failover cluster instances
  • Manage shared disks, configure cluster shared volumes

Books to Prepare

Below are the recommended books for preparing Exam 70-764 : Administering a SQL Database Infrastructure .

SQl Cover Image

Book Name : Exam Ref 70-764 Administering a SQL Database Infrastructure

Author Name : Victor Isakov

Publisher :

Topics Covered in this book:

  • Configuring data access and auditing, including encryption, data access, permissions, and more
  • Managing database backup/restore: developing backup strategies, performing backups/restores, and managing database integrity
  • Managing and monitoring SQL Server instances: monitoring database activity and queries; managing indexes and statistics, and monitoring instances
  • Managing high availability and disaster recovery: implementing log shipping, AlwaysOn Availability Groups, and failover cluster instances

Softwares for Practice


Content Will be Updated Soon

Exam 70-765 : Provisioning SQL Databases


Exam Details

Required Exam(s) 70-765
Durations 120 Minutes (40 - 60 Questions)
Question Types Multiple choice, drag and drop, Fill in the blanks and simulation types.
Exam Fees Each Exam Rs.4800(Approx)
Exam Policy Click here to read the Exam policies.
Recommended Training Provisioning SQL Databases

Exam Pattern

Below are the exam objectives of Exam 70-765 : Provisioning SQL Databases
Implement SQL in Azure (30–35%)
Deploy a Microsoft Azure SQL Database
  • Choose a service tier, create servers and databases, create a sysadmin account, configure elastic pools
Plan for SQL Server installation
  • Plan for an IaaS or on-premises deployment, select the appropriate size for a virtual machine, plan storage pools based on performance requirements, evaluate best practices for installation, design a storage layout for a SQL Server virtual machine
Deploy SQL Server instances
  • Deploy a SQL Server instance in IaaS and on-premises, manually install SQL Server on an Azure Virtual Machine, provision an Azure Virtual Machine to host a SQL Server instance, automate the deployment of SQL Server databases, deploy SQL Server by using templates
Manage databases and instances (35–40%)
Configure secure access to Microsoft Azure SQL Databases
  • Configure firewall rules, configure Always Encrypted for Azure SQL Database, configure cell-level encryption, configure dynamic data masking, configure transparent data encryption (TDE)
Configure SQL Server performance settings
  • Configure database performance settings, configure max server memory, configure the database scope, configure operators and alerts
Manage SQL Server instances
  • Create databases, manage files and file groups, manage system database files, configure tempdb
Deploy and migrate applications (30–35%)
Deploy applications to Microsoft Azure SQL Database
  • Manage deployments that support multiple tenants, migrate on-premises SQL Server to Azure SQL Database, migrate data to Azure SQL Database
Deploy applications to SQL Server on Azure Virtual Machines
  • Migrate an on-premises SQL Server database to an Azure Virtual Machine, generate benchmark data for performance needs, perform performance tuning on Azure IaaS, support availability sets in Azure
Migrate client applications
  • Configure application connection strings, manage traffic between on-premises applications and Azure services, develop application retry connection logic, identify application patterns that must be migrated with SQL Server data, evaluate network performance between applications and databases

Books to Prepare

Below are the recommended books for preparing Exam 70-765 : Provisioning SQL Databases

SQl Cover Image

Book Name : Exam Ref 70-765 Provisioning SQL Databases

Author Name : Christopher Ford

Publisher :

Topics Covered in this book:

  • Implementing SQL in Azure: deploying a Microsoft Azure SQL database, planning for SQL Server installation, and deploying SQL Server instances
  • Managing databases and instances: configuring secure access to Microsoft Azure SQL Databases, configuring SQL Server performance settings, and managing SQL Server instances
  • Deploying and migrating applications: deploying applications to Microsoft Azure SQL Database or SQL Server on Azure Virtual Machines, migrating on-premises SQL Server databases to an Azure Virtual Machine, and migrating client applications

Softwares for Practice


Content Will be Updated Soon