Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
I am going to take the Microsoft exam 70-764, Administering a SQL Database Infrastructure. I would like to know what materials I can use to study and help pass this exam.
In this article, we will talk about materials that can be used to help study for this exam.
What is the 70-764 certification exam?
This is oriented for DBAs and covers security, backups, monitoring and high availability solutions.
Is it an exam for database developers?
This exam is for DBAs, but database developers can find some of the topics useful.
Is it an exam oriented to SQL Server 2016?
Yes, this is an exam is oriented to SQL Server 2016 and Azure SQL.
Which SQL Server 2012/2014 exam is similar to this one?
The 70-462 is similar to this new exam, but some topics were changed. This exam contains more Azure SQL related topics and a lot of topics were removed in this exam like installation, maintenance, replication and import/export data.
Is this exam difficult?
We believe that these new exams are less extensive than the older ones and it should take less time to study.
Are there some virtual labs?
Yes, there are some virtual labs. They are very useful to save time and contain some practice labs:
Some of the labs like the AlwaysOn labs are in SQL 2014, but they are still recommended for this exam.
Which books would you recommend for this exam?
There is no Training Kit book yet, but older books for old exams may help for this one. Here are some:
- Training Kit (Exam 70-462) Administering Microsoft SQL Server 2012 Databases (MCSA) (Microsoft Press Training Kit)
- Professional Microsoft SQL Server 2014 Administration
- Microsoft Azure SQL Database Step by Step (Step by Step Developer)
- Pro SQL Server on Microsoft Azure (specially chapter 8-10)
Which courses would you recommend for this exam?
The official Microsoft course is the 20764B, Administering a SQL Database Infrastructure.
There will be a specific MVA course in the future, but not for now. Other courses provided by are:
- Pluralsight has several SQL Server courses with topics related to this exam
- SolidQ, check high availability and tuning courses
- The MVA course, Developing Microsoft SQL Server Databases
- SQL Server DBA Tutorial
What links do you recommend reading for this exam?
Here are some useful links:
Configure data access and auditing
- Configure encryption
- SQL Server 2016 Always Encrypted
- Always Encrypted (Database Engine)
- SQL Server 2014 Backup Encryption
- SQL Server 2008 Transparent Data Encryption getting started
- Implementing Transparent Data Encryption in SQL Server 2008
- Transparent Data Encryption (TDE)
- How to configure SSL encryption in SQL Server
- Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)
- How to troubleshoot SSL encryption issues in SQL Server
- Blitz Result: Transparent Data Encryption (TDE) and Certificates
- Configure data access and permissions
- GRANT (Transact-SQL)
- DENY (Transact-SQL)
- REVOKE (Transact-SQL)
- SQL Server Security Tips
- Create a Database User
- Create a Server Role
- Controlling and granting database access
- Row-Level Security
- Dynamic Data Masking
- Understand the Limitations of SQL Server Dynamic Data Masking
- Configure auditing
Manage backup and restore of databases
- Develop a backup strategy
- SQL Server Backup Tips
- Introduction to Backup and Restore Strategies in SQL Server
- How to backup SQL Server databases to Microsoft Azure
- Simple script to backup all SQL Server databases
- Backup SQL Server Databases with a Windows PowerShell Script
- Back Up a Transaction Log (SQL Server)
- SQL Server Transaction Log Backups
- Recovery Models (SQL Server)
- SQL Server Recovery Models
- Restore databases
- How to restore only a specific filegroup - A piecemeal restore
- Piecemeal Restores (SQL Server)
- SQL Server point in time restore
- Restore a SQL Server Database to a Point in Time (Full Recovery Model)
- Restore Files and Filegroups (SQL Server)
- Checking to make sure a SQL Server backup is useable
- Backup and recovery basics: Testing your backups
- Manage database integrity
Manage and monitor SQL Server instances
- Monitor database activity
- sp_who (Transact-SQL)
- Using sp_who2
- Open Activity Monitor (SQL Server Management Studio)
- SQL Server Profiler
- How to identify blocking in SQL Server
- Tempdb Configuration Best Practices in SQL Server
- Troubleshooting Insufficient Disk Space in tempdb
- Data Collection
- Introduction to Utility Control Points in SQL Server 2008 R2
- Create a SQL Server Utility Control Point (SQL Server Utility)
- Monitor queries
- Monitoring Performance By Using the Query Store
- SQL Server Extended Events Tips
- Troubleshooting slow running query using Extended Events Wait info event
- SQL Server Execution Plans
- Manage indexes
- Manage statistics
- Monitor SQL Server instances
- SQL Server Agent
- Configure Database Mail
- Administer Servers by Using Policy-Based Management
- SQL Server Policy Based Management Tips
- SQL Server Agent Tips
- Performance Monitoring and Tuning Tools
- SQL SERVER - Disk Space Monitoring - Detecting Low Disk Space on Server
- Manage high availability and disaster recovery
- SQL Server Log Shipping Tips
- About Log Shipping (SQL Server)
- SQL Server AlwaysOn Availability Tips
- Overview of Always On Availability Groups (SQL Server)
- Implement failover cluster instances
Here are more links that will be useful:
Last Update: 2017-02-17
About the author
View all my tips