Preparing for the Microsoft Administering a SQL Database Infrastructure exam while holding down a job leaves little room for wasted effort. 2Pass4sure condenses 452 carefully verified practice questions into a focused 070-764 study package, so every hour you invest actually moves you toward a passing score.

Microsoft 070-764 Exam Overview:

Microsoft 070-764 Exam Syllabus Topics:

Answers to the Most Common Questions About Microsoft Administering a SQL Database Infrastructure

Microsoft Administering a SQL Database Infrastructure Sample Questions:

Question 1

You deploy a database by using SQL Server 2014.
The database contains a table named Table1.
You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required.
What should you recommend?

A. Master Data Services
B. Change data capture
C. A trigger
D. Statistics

Question 2

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a server named Server1 that has Microsoft SQL Server installed.
Server1 has SQL Server Adult configured to send audit even records to a file.
You need to ensure that a database user named User1 can review the audit data.
Solution: You assign the db_datareader role to User1.
Does this meet the goal?

A. Yes
B. No

Question 3

You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login.
You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages.
What should you do?

A. Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile.
B. Disable the guest user in the msdb database.
C. In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile.
D. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.

Question 4

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are examining information about users, sessions, and processes in an on-premises Microsoft SQL Server
2016 Standard Edition server.
You need to identify waits for resources and return only the following information:
* a list of all databases on the SQL Server instance, along with information about the database files, their paths, and names
* a list of the queries recently executed that use most of memory, disk, and network resources What should you use?

A. Activity Monitor
B. SQL Server Data Tools (SSDT)
C. Sp_who3
D. SQL Server Management Studio (SSMS) Object Explorer
E. SQL Server Configuration Manager
F. SQL Server Data Collector

Question 5

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are migrating a set of databases from an existing Microsoft SQL Server instance to a new instance.
You need to complete the migration while minimizing administrative effort and downtime.
Which should you implement?

A. an Always On Availability Group with all replicas in synchronous-commit mode
B. an Always On Availability Group with secondary replicas in asynchronous-commit mode
C. a Windows Cluster with a shared-nothing architecture
D. a SQL Server failover cluster instance (FCI)
E. a file share witness
F. log shipping

Solutions: