01. You support a SQL Server 2016 cluster on a Windows Server failover cluster instance (FCI). You need to add an additional disk to the FCI for the SQL Server cluster.
Which tool should you use?
a) SQL Server Management Studio
b) Failover Cluster Manager
c) Disk Manager
d) Server Manager
02. Which feature should you enable and configure so session requests addressed to a specific instance can be allocated different processor resources based on session request properties?
a) Resource Governor
b) Windows System Resource Manager
c) Processor affinity
d) I/O affinity
03. You have a distributed availability group with two local servers in Singapore and a read-only replica in London. Failover only occurs between servers in Singapore. All replicas apart from the primary replica should be available for read-only access. You need to configure any required read-only routing list entries.
For which two replicas should you configure a read-only routing list?
a) All secondary replicas
b) Secondary replica in Singapore
c) Primary replica
d) Secondary replica in London
04. DB1 is a database that is hosted on an instance of SQL Server 2016. You are asked to run full and transaction log backups from the database to Microsoft Azure.
You configure the Azure and SQL Server prerequisites for SQL Server Managed Backup to Microsoft Azure. You enable Database Mail on the server instance. You need to enable email notifications of errors and warnings generated during backups. Emails should be sent to your email address.
Which function or procedure should you run?
a) managed_backup.sp_backup_config_advanced
b) managed_backup.sp_set_parameter
c) managed_backup.fn_backup_db_config
d) managed_backup.sp_ backup_master_switch
05. You are responsible for a SQL Server 2016 database instance deployed in your local office. You are responsible for server and database security and monitoring.
You create and enable a SQL Server audit configured to save audit entries to \\SecServ\DB1Audit\Tracking.sqlaudit. You create and enable a server audit specification and a database audit specification for the server audit. You need to retrieve the audit file contents in a tabular format.
Which function or view should you use?
a) sys.dm_audit_actions
b) sys.server_audits
c) sys.server_file_audits
d) sys.fn_get_audit_file
06. A Microsoft SQL Server database named DB1 has two filegroups named FG1 and FG2. You implement a backup strategy that creates backups for the filegroups. DB1 experiences a failure.
You must restore FG1 and then FG2. You need to ensure that the database remains in the RECOVERING state until the restoration of FG2 completes. After the restoration of FG2 completes, the database must be online.
What should you specify when you run the recovery command?
a) the WITH NORECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
b) the WITH RECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
c) the WITH RECOVERY clause for both FG1 and FG2
d) the WITH NORECOVERY clause for both FG1 and FG2
07. You want to simulate read, write, checkpoint, backup, sort, and read-ahead activities for your organization's SQL Server 2016 deployment.
Which of the following tools would you use to accomplish this goal?
a) SQLIO
b) SQLIOSim
c) SQLIOStress
d) chkdsk
08. DB1 supports online transaction processing and data analysis. DB1 was recently upgraded from SQL Server 2014 to SQL Server 2016.
You created the clustered columnstore index cciinventory without first creating an underlying rowstore table. You recently did a great deal of data reorganization that included deleting over 10,000 rows from cciinventory and bulk loading over 100,000 new records.
You are asked to ensure that all deleted rows are completely removed, all new data is integrated into the index, and the index is fully defragmented and rebuilt. You need to identify the appropriate procedure to accomplish this. The procedure can be completed offline.
Which two Transact-SQL (T-SQL) commands could you run first to meet the goal?
a) ALTER TABLE
b) DROP INDEX
c) CREATE TABLE
d) ALTER INDEX
e) CREATE COLUMNSTORE INDEX
09. You administer a Microsoft SQL Server 2016 database instance. You create a new user named UserA. You need to ensure that UserA is able to create SQL Server Agent jobs and to execute SQL Server Agent jobs.
To which role should you add UserA?
a) Securityadmin
b) RSExecRole
c) SQLAgentUserRole
d) DatabaseMailUserRole
10. You are using dynamic management views to monitor an SQL Server server named SQL1. A database administrator named Dba1 must monitor the health of SQL1. You need to ensure that Dba1 can access dynamic management views for SQL1. The solution must use the principle of least privilege.
Which permissions should you assign to Dba1?
a) VIEW SERVER STATE
b) VIEW ANY DEFINITION
c) VIEW DEFINITION
d) CONTROL SERVER
The purpose of this Sample Question Set is to provide you with information about the Administering a Microsoft SQL Database Infrastructure exam. These sample questions will make you very familiar with both the type and the difficulty level of the questions on the 70-764 certification test. To get familiar with real exam environment, we suggest you try our Sample Microsoft Administering a SQL Database Infrastructure Certification Practice Exam. This sample practice exam gives you the feeling of reality and is a clue to the questions asked in the actual Microsoft Certified Solutions Associate (MCSA) - Dynamics 365 for Operations certification exam.