70-764 Administering a SQL Database Infrastructure Exam

Which of the following server scoped permissions grants SELECT permissions on server level dynamic management objects?

Options are :

  • ALTER SERVER STATE
  • ALTER ANY LOGIN
  • VIEW SERVER STATE (Correct)
  • ALTER TRACE

Answer : VIEW SERVER STATE

Explanation VIEW SERVER STATE: It grants SELECT permissions on server level dynamic management objects. ALTER SERVER STATE: It grants permission to execute DBCC commands that affect the contents of the buffer pool. ALTER TRACE: It grants administrative control over SQL Trace.

Which of the following triggers runs in response to a login event?

Options are :

  • DML triggers
  • Logon triggers (Correct)
  • DDL triggers

Answer : Logon triggers

Explanation Logon trigger: It runs in response to a login event.

Which of the following triggers are not supported in Azure SQL database?

Options are :

  • DML triggers
  • Logon triggers (Correct)
  • DDL triggers

Answer : Logon triggers

Explanation Logon triggers: They are not supported in Azure SQL database. Azure SQL database: It includes support for DDL triggers and DML triggers.

Which of the following Dynamic Management Views (DMVs) should you use, if you want the list of all audits defined on an instance of SQL Server and their current status?

Options are :

  • sys.dm_audit_class_type_map
  • sys.dm_server_audit_status (Correct)
  • sys.dm_audit_actions
  • sys.server_audits

Answer : sys.dm_server_audit_status

Explanation sys.dm_server_audit_status gives the list of all the audits defined on an instance of SQL Server and their current status. sys.dm_audit_actions give the complete list of available audit actions and audit action groups. sys.dm_audit_class_type_map gives the reference data list mapping audit class codes to descriptions.

Which of the following system views should you use, if you want the list of all audits that write data to a file target?

Options are :

  • sys.server_file_audits (Correct)
  • sys.server_audits
  • sys.server_audit_specifications
  • sys.server_audit_specifications_details

Answer : sys.server_file_audits

Explanation sys.server_file_audits: It returns the list of all audits that write data to a file target. sys.server_audits: It returns list of all the audits defined on an instance of SQL Server. sys.server_audit_specifications: It returns list of high level information about server audit specifications on an instance. sys.server_audit_specifications_details: It returns list of the action groups associated with each server audit specification.

True or False: The USER_DEFINED_AUDIT_GROUP action group can be included in a database audit specification or a server audit specification.

Options are :

  • True (Correct)
  • False

Answer : True

Explanation The given statement is true. The USER_DEFINED_AUDIT_GROUP action group can be included in a database audit specification or a server audit specification.

Which of the following system views should you use to return list of the actions and action groups associated with each database audit specification?

Options are :

  • sys.audit_database_specifications_details (Correct)
  • sys.server_audits
  • sys.server_audit_specifications
  • sys.server_audit_specifications_details

Answer : sys.audit_database_specifications_details

Explanation sys.audit_database_specifications_details: It returns the list of the actions and action groups associated with each database audit specification. sys.server_audits: It returns list of all the audits defined on an instance of SQL Server. sys.server_audit_specifications: It returns list of high level information about server audit specifications on an instance. sys.server_audit_specifications_details: It returns list of the action groups associated with each server audit specification.

You have a SQL Server instance on a server named Server1.

You need to recommend a solution to perform the following task every week:

- Backup the databases.

- Rebuild the indexes by using a new fill factor.

- Run a custom T-SQL command.

What should you recommend?

Options are :

  • A system policy
  • A Maintenance plan (Correct)
  • An Alert
  • A Trigger

Answer : A Maintenance plan

Explanation Maintenance plans create a workflow of the tasks required to make sure that your database is optimizes, regularly backup, and free of inconsistencies.

True or False: Page restore is available only under the simple recovery model.

Options are :

  • True
  • False (Correct)

Answer : False

Explanation The given statement is false because page restore is available only under the full and bulk-logged recovery models.

How many SQL Server Failover Cluster Instances (FCIs) are supported when you are using SMB file shares?

Options are :

  • 21 FCIs
  • 50 FCIs (Correct)
  • 25 FCIs
  • 125 FCIs

Answer : 50 FCIs

Explanation 50 FCIs are supported when you are using Server Message Block (SMB) file shares and 25 FCIs when you are using shared cluster disks.

You are the database administrator for a company. You have to enable a user to view the code definition of the stored procedure. Which permission should you use?   

Options are :

  • VIEW DEFINITION (Correct)
  • ALTER

Answer : VIEW DEFINITION

Explanation You should use VIEW DEFINITION permission. VIEW DEFINITION permission: It enables a user to view the code definition of the stored procedure. ALTER permission: It enables a user to change the definition of a stored procedure.

Which permission set strictly limits the actions that the assembly can perform and inhibits it from accessing external system resources?   

Options are :

  • SAFE (Correct)
  • EXTERNAL_ACCESS
  • UNSAFE

Answer : SAFE

Explanation SAFE permission set: It strictly limits the actions that the assembly can perform and inhibits it from accessing external system resources. EXTERNAL_ACCESS permission set: It allows the code to access local and network resources, registry and environment variables. UNSAFE permission set: It relaxes many standard controls over code.

Which of the following recovery model can reduce the transaction logging requirements for bulk operations?

Options are :

  • Full Recovery Model
  • Simple Recovery Model
  • Bulk-logged Recovery Model (Correct)

Answer : Bulk-logged Recovery Model

Explanation Bulk-logged Recovery Model: It can reduce the transaction logging requirements for bulk operations.

You need to analyze which queries are consuming the most memory by their query plans.

Which of the following DMV (Dynamic Management View) should you query?

Options are :

  • sys.dm_exec_cached_plans (Correct)
  • sys.dm_db_task_space_usage
  • sys.dm_db_index_usage_stats
  • sys.dm_db_index_operational_stats

Answer : sys.dm_exec_cached_plans

Explanation You should use the sys.dm_exec_cached_plans DMV (Dynamic Management View) trach query plan’s memory usage.

Which of the following database contains copies of all system objects that ship with SQL Server?

Options are :

  • msdb
  • model
  • tempdb
  • resource (Correct)

Answer : resource

Explanation resource database: It is read only. It contains copies of all system objects that ship with SQL Server. model database: It is the template for all database that are created on the instance of SQL Server. msdb database: It contains history tables such as those that record details of backup and restore operations. tempdb database: It is a workspace for holding temporary or intermediate result sets.

You are using Microsoft SQL Server 2016.

You have mydb database. You required to perform a backup of the database.

You are the member sysadmin Fixed Server Role.

Are you able to perform the backup operation?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation For performing Backup operation, you must be a member of the sysadmin Fixed Server Role or the db_owner or db_backupoperator Fixed Database roles.

The management needs you to configure a monitoring solution that will track query execution plans and server level wait stats. So that your junior DBA can troubleshoot performance and identify query execution times for a year. What should you use?

Options are :

  • Data Collector and Management Data Warehouse (Correct)
  • Query Store

Answer : Data Collector and Management Data Warehouse

Explanation Data Collector will capture both query execution metrics and wait stats. The Query Store will not collect wait stats.

Which of the following statement identifies and authorizes user tokens?

Options are :

  • SELECT * FROM sys.user_token; (Correct)
  • SELECT * FROM sys.loginuser_token;
  • SELECT * FROM sys.userlogin_token;
  • SELECT * FROM sys.login_token;

Answer : SELECT * FROM sys.user_token;

Explanation SELECT * FROM sys.user_token; statement identifies and authorizes user tokens.

A company named XYZ has a SQL Server environment in Azure. The databases are stored directly in Azure blob storage.

You have to ensure that you can restore database to specific point in time between backups while minimizing the number of Azure storage containers required.

Which of the following option should you use?

Options are :

  • backup encryption
  • SQL Server backup to URL
  • SQL Server managed backup to Azure (Correct)
  • backup compression

Answer : SQL Server managed backup to Azure

Explanation You should use SQL Server managed backup to Azure because it supports point in time restore for the retention time period specified.

Which of the following command checks the consistency of the system tables with a database?

Options are :

  • DBCC CHECKTABLE
  • DBCC CHECKALLOC
  • DBCC CHECKCATALOG (Correct)
  • DBCC CHECKDB

Answer : DBCC CHECKCATALOG

Explanation DBCC CHECKCATALOG: It checks the consistency of the system tables with a database. DBCC CHECKALLOC: It checks the consistency of disk space allocation structures with a database. DBCC CHECKDB: It checks the logical and physical integrity of all the objects of a database. DBCC CHECKTABLE: It checks the allocation and structural integrity a table.

Can you use the stop at mark functionally in SSMS?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation No, you cannot use the stop at mark functionally in SSMS. It is only available by using the T-SQL statement.

True or False: You cannot start the instance of SQL Server if model database is corrupt?

Options are :

  • False
  • True (Correct)

Answer : True

Explanation Yes, you cannot start the instance of SQL Server if model database is corrupt. The model database is the template for all databases that are created on the instance of SQL Server.

You have a database that includes a table named Applicant.

You need to update the statistics for a column named Skills in the table and turn off automatic statistics updates for the column.

Solution: you the following query:

Use CustomerDb

GO

Update Statistics Person.Applicant(Skills)

RESAMPLE, NORECOMPUTE

GO

Does this solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation NORECOMPUTE: Disable the automatic statistics update option, AUTO_UPDATE_STATISTICS, for the specified statistics. If this option is specified, the query optimizer completes this statistics update and disables future updates. To re-enable the AUTO_UPDATE_STATISTICS option behavior, run UPDATE STATISTICS again without the NORECOMPUTE option or run sp_autostats.

Which of the following statement is true?

Options are :

  • Extensible Key Management(EKM) is not available for production use in SQL Server Enterprise edition.
  • Extensible Key Management(EKM) is only available for production use in SQL Server Enterprise edition. (Correct)
  • TDE is not available in Azure SQL database.

Answer : Extensible Key Management(EKM) is only available for production use in SQL Server Enterprise edition.

Explanation Extensible Key Management(EKM) is only available for production use in SQL Server Enterprise edition. TDE is available in the Azure SQL database.

You are the administer of Microsoft SQL Server 2016 database.

You want to make a full backup of the database to a file on disk.

In doing backup, you need display a message indicating the progress of the backup operation.

Which of the following backup option should you use?

Options are :

  • SKIP
  • SERVER CERTIFICATE
  • STATS (Correct)
  • STOPAT

Answer : STATS

Explanation You should use the STATS backup option; it controls at what percentage intervals the database engine should display a message indicating the progress of the backup operation.

Which of the following System Database is recreated every time when an instance of SQL Server starts?

Options are :

  • master
  • tempdb (Correct)
  • model
  • msdb

Answer : tempdb

Explanation The tempdb System Database is recreated every time when an instance of SQL server starts. There is no need to back up or restore the tempdb database.

You have a database named Demo that contains two tables.

You have to encrypt one column in each table using the Always Encrypted feature.

The solution must support groupings on the encrypted columns.

Which action should you perform?

Solution: You encrypt both columns by using randomized encryption.

Does this solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation No, the solution does not meet the goal. Randomized encryption: It prevents searching, joining, grouping and indexing on encrypted columns. It generates different ciphertext for the same plaintext.

You are using Microsoft SQL Server.

You have to save the data that has been changed since the last full backup.

Which of the following backup should you use?

Options are :

  • Differential Backups (Correct)
  • Partial Backups
  • Transaction Log Backups
  • Full Backups

Answer : Differential Backups

Explanation Differential Backup: It is used to save the data that has been changed since the last full backup.

True or False: SQL Server supports the creation of copy only backups.

Options are :

  • False
  • True (Correct)

Answer : True

Explanation The given statement is true. SQL Server supports the creation of copy only backups. Copy-only backups: It can be used to create a copy of the backup to take offsite to a safe location.

Which of the following server action group includes actions that create, drop or alter a database?

Options are :

  • BACKUP_RESTORE_GROUP
  • SUCCESSFUL_LOGIN_GROUP
  • DATABASE_CHANGE_GROUP (Correct)
  • SERVER_OPERATION_GROUP

Answer : DATABASE_CHANGE_GROUP

Explanation DATABASE_CHANGE_GROUP: It includes actions that create, drop or alter a database. BACKUP_RESTORE_GROUP: It includes all backup and restore actions. SERVER_OPERATION_GROUP: It includes actions that alter server settings. SUCCESSFUL_LOGIN_GROUP: It includes details of successful logins.

In Mirrored Backup, the backup data is written to each backup device concurrently.

Which of the following Edition of SQL Server support Mirrored Backup?

Options are :

  • Express Edition
  • Developer Edition
  • Enterprise Edition (Correct)
  • Standard Edition

Answer : Enterprise Edition

Explanation Only the Enterprise Edition of SQL Server support Mirrored Backup.

In which of the following System Database you cannot perform Backup?

Options are :

  • msdb
  • model
  • tempdb (Correct)
  • master

Answer : tempdb

Explanation Yes, you cannot perform backup of tempdb System Database. tempdb database is created during instance startup.

You use STOPAT option with RESTORE statement for point-in-time recovery.

TRUE or False: The STOPAT option specify a recovery point that is based on a datetime value.

Options are :

  • False
  • True (Correct)

Answer : True

Explanation The STOPAT option specify a recovery point that is based on a datetime value.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions