70-764 Administering a SQL Database Infrastructure Tests

Which of the following DMV (Dynamic Management View) should you use for determining the degree of index fragmentation?

Options are :

  • sys.dm_server_audit_status
  • sys.dm_server_audit_class_type_map
  • sys.dm_db_index_physical_stats (Correct)
  • sys.dm_audit_actions

Answer : sys.dm_db_index_physical_stats

Explanation You should use the sys.dm_db_index_physical_stats DMV (Dynamic Management View) for determining the degree of index fragmentation.

Which recovery model is the default recovery model when SQL Server is installed?

Options are :

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

Answer : Full Recovery Model

Explanation Full Recovery Model: It is the default recovery model when SQL Server is installed.

You are the administer of Microsoft SQL Server 2016 database named Contract on a server named Server1.

You need to track all SELECT statements issued in the Contract database only by users in a role named Sales.

What should you create?

Options are :

  • A Server Audit specification
  • A SQL server Audit specification
  • A Database Audit specification (Correct)
  • A Data Collector Set

Answer : A Database Audit specification

Explanation You should create a Database Audit Specification. It collects database-level audit actions raised by extended events. You create only one server audit specification per SQL Server audit.

TDE uses the hierarchy of keys to encrypt and decrypt data.

Which of the following key is used to encrypt the entire database?

Options are :

  • Server certificate
  • Database master key
  • Service master key
  • Database encryption key (Correct)

Answer : Database encryption key

Explanation The database encryption key in the user database. It is used to encrypt the entire database. The service master key is created at the time of the installation of the SQL Server instance by setup. The database master key for the master database. It is used to generate a certificate in the master database. The server certificate is generated in the master database. It is used to encrypt an encryption key in each TDE enabled database.

True or False: Instant File Initialization can be used with transaction log files.

Options are :

  • True
  • False (Correct)

Answer : False

Explanation The given statement is false because instant file initialization cannot used with transaction log files. It means that transactions can be blocked while log file growth occurs.

Does dynamic data masking encrypt data at rest?

Options are :

  • No (Correct)
  • Yes

Answer : No

Explanation The dynamic data masking does not encrypt data at rest.

You are the administer of Microsoft SQL Server 2016 database named Contract on a server named Server1.

You want to collect data for a long period of time to troubleshoot wait statistics when querying Contract.

You need to ensure that minimum impact to the server.

What should you create?

Options are :

  • An Extended Event session (Correct)
  • A Resource Pool
  • A SQL Profiler Trace
  • A Database Audit Specification

Answer : An Extended Event session

Explanation You should create an Extended Event session when there is requirement to troubleshoot a specific incident or problem. Extended Events use an asynchronous model so as to minimize the impact on SQL Server Performance.

Users reports about poor query performance for queries that query the [Products_History] table. The table is 300GB in size. It is not partitioned.

After the analysis you determine that the poor query performance is due to outdated statistics.

What statistic update should you use for updating the statistics in the least possible time for the table?

Options are :

  • FULLSCAN (Correct)
  • RESAMPLE
  • NORECOMPUTE
  • INCREMENTAL

Answer : FULLSCAN

Explanation You should use the FULLSCAN option because it will take too long on a 300GB table. You should not use NORECOMPUTE option because it disables the automatic statistics update. You should not use the INCREMETAL option does not apply partitioned table. You should not use the RESAMPLE option update statistics based on the most recent sample rate.

The Query Store uses a DATA_FLUSH_INTERVAL_SECONDS option for controlling the memory buffers is flushed to the database.

What is the Default interval for DATA_FLUSH_INTERVAL_SECONDS?

Options are :

  • 100 second
  • 900 second (Correct)
  • 400 second
  • 700 second

Answer : 900 second

Explanation The default value for DATA_FLUSH_INTERVAL_SECONDS is 900 second (15 minutes).

Which of the following option is used to write a new media header on the backup devices used in the backup?

Options are :

  • WITH NOFORMAT
  • WITH NOINIT
  • WITH INIT
  • WITH FORMAT (Correct)

Answer : WITH FORMAT

Explanation You should use WITH FORMAT option. This option is used to write a new media header on the backup devices used in the backup.

Which of the following DMV (Dynamic Management view) returns information about I/O, latching, locking, compression, and access method metrics on the index?

Options are :

  • sys.dm_db_index_operational_stats (Correct)
  • sys.dm_db_missing_index_details
  • sys.dm_db_missing_index_columns
  • sys.dm_db_index_usage_stats

Answer : sys.dm_db_index_operational_stats

Explanation The sys.dm_db_index_operational_stats DMV (Dynamic Management View) returns information about I/O, latching, locking, compression and access method metrics on the index. The sys.dm_db_missing_index_columns returns information table columns that are missing an index. The sys.dm_db_missing_index_details returns information about potential missing indexes. The sys.dm_db_index_usage_stats returns information about how many times queries have used an index.

Which of the following database is read only?

Options are :

  • msdb
  • resource (Correct)
  • model
  • tempdb

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.

Which of the following Backup Strategy allows the database to be fully restored in the case of data file loss?

Options are :

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

Answer : Transaction Log Backup

Explanation The Transaction Log Backup strategy allows the database to be fully restored in the case of data file loss.

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)

WITH FULLSCAN, NORECOMPUTE

GO

Does this solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation FULLSCAN: Compute statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE 100 PERCENT have the same results. 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.

Which of the following triggers are associated with table and run when data in the table is modified?

Options are :

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

Answer : DML triggers

Explanation DML triggers: They are associated with table and run when data in the table is modified.

True or False: The sys.dm_db_index_usage_stats DMV (Dynamic Management View) returns information about how many times queries have used an index.

Options are :

  • True (Correct)
  • False

Answer : True

Explanation The sys.dm_db_index_usage_stats returns information about how many times queries have used an index.

You are using Microsoft SQL Server 2016.

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

You are the member dbcreator Fixed Server Role.

Are you able to perform the backup operation?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation No, if you are the member of dbcreator Fixed Server Role then you are not able to perform the backup operation.

Which System Database is used by SQL Server Agent for scheduling alerts and jobs?

Options are :

  • tempdb
  • msdb (Correct)
  • model
  • master

Answer : msdb

Explanation The SQL Server Agent uses the msdb System Database for scheduling alerts and jobs, and for recording details of operators.

True or False: Compressed SQL Server backups can share media with other types of backup.

Options are :

  • True
  • False (Correct)

Answer : False

Explanation The given statement is false because compressed SQL Server backups cannot share media with other types of backup.

You have to add and remove members from a role.

Solution: You use ALTER ROLE statement.

Does this solution meet the goal?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation Yes, this solution meets the goal. You can add and remove members from a role using the ALTER ROLE statement. For Example: ALTER ROLE db_backupoperator ADD MEMBER a1; ALTER ROLE db_backupoperator DROP MEMBER a1;

True or False: The Backups created by SQL Server encoded in Microsoft Tape Format (MTF).

Options are :

  • False
  • True (Correct)

Answer : True

Explanation The Backups created by SQL Server are encoded in a Microsoft Tape Format (MTF). MTF is a common format used by other Microsoft products in addition to SQL Server.

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 deterministic encryption.

Does this solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation Yes, the solution meets the goal. Deterministic encryption: It allows equality joins, grouping and indexing on encrypted columns. It uses a method that always generates the same ciphertext for any given plaintext value.

Which of the following statements should you use for changing the data compression used by Columnstore index from COLUMNSTORE to COLUMNSTORE_ARCHIVE or vice versa? (Choose all possible option.)

Options are :

  • ALTER INDEX REBUILD (Correct)
  • ALTER TABLE REBUILD (Correct)

Answer : ALTER INDEX REBUILD ALTER TABLE REBUILD

Explanation For changing the data compression used by Columnstore index from COLUMNSTORE to COLUMNSTORE_ARCHIVE or vice versa You should use the - ALTER INDEX REBUILD - ALTER TABLE REBUILD

TDE uses the hierarchy of keys to encrypt and decrypt data.

Which of the following key is created at the time of the installation of the SQL Server instance by setup?

Options are :

  • Service master key (Correct)
  • Database encryption key
  • Server certificate
  • Database master key

Answer : Service master key

Explanation The service master key is created at the time of the installation of the SQL Server instance by setup. The database master key for the master database. It is used to generate a certificate in the master database. The server certificate is generated in the master database. It is used to encrypt an encryption key in each TDE enabled database. The database encryption key in the user database. It is used to encrypt the entire database.

You are using a Microsoft SQL Server 2016.

You created a database named Database1 and it has two filegroups named FileG1 and FileG2.

Database1 experiences a failure.

You need to ensure that the database remains in the RECOVERING state until the restoration of FileG2 completes.

After the restoration of FileG2 completes, the database must be online.

Which options should you use with BACKUP LOG statement when you run the recovery command?

Options are :

  • Use WITH RECOVERY option for FileG1 and the WITH NORECOVERY option for FileG2.
  • Use WITH NORECOVERY option for FileG1 and the WITH RECOVERY option for FileG2. (Correct)
  • Use WITH RECOVERY option for both FileG1 and FileG2.
  • Use WITH NORECOVERY option for both FileG1 and FileG2.

Answer : Use WITH NORECOVERY option for FileG1 and the WITH RECOVERY option for FileG2.

Explanation The WITH NORECOVERY option backs up the tail-log and leaves the database in the RESTORING state. This option is useful when you need to fail over to a secondary database in an Availability Group.

Which of the following option should you use with the Backup to ensure that all backup sets are overwritten?

Options are :

  • WITH EXPIREDATE
  • WITH FORMAT
  • WITH INIT (Correct)
  • WITH CHECKSUM

Answer : WITH INIT

Explanation You should use the WITH INIT option with the Backup operation to ensure that all backup sets are overwritten. The existing backup sets are not overwritten if the EXPIREDATE have not expired.

In SQL Server you created a role userXYZ.

By default, userXYZ belongs to which Fixed Server Role?

Options are :

  • diskadmin
  • serveradmin
  • sysadmin
  • public (Correct)

Answer : public

Explanation By default, userXYZ belongs to public fixed server role. This role cannot be changed. This role does not initially grant any administrative permissions. The public role has CONNECT and VIEW ANY DATABASE permissions by default.

Which of the following Fixed Server-level role should you use to grant permissions for managing logins?

Options are :

  • serveradmin
  • securityadmin (Correct)
  • processadmin
  • dbcreator

Answer : securityadmin

Explanation You should use securityadmin fixed server-level role to grant permissions for managing logins. It includes the ability to create and drop logins and the ability to assign permissions to logins. Securityadmin role is equivalent to sysadmin. processadmin grant permissions to terminate sessions running on the SQL Server instance. dbcreator grants permissions to manage databases. serveradmin grant permission to configure server-wide settings and to shut down the server.

You work for company XYZ. The management wants to track data changes made to a number of tables within the database.

The solution must be able to show the data before and after any modification.

Solution: You should use DDL triggers.

Does the solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation You should not use DDL triggers because it will only track schema changes to objects made with a database.

Which System Database store information about indexes, system tables, table variables, temporary objects in SQL Server?

Options are :

  • model
  • msdb
  • master
  • tempdb (Correct)

Answer : tempdb

Explanation The tempdb system database store information about indexes, system tables, table variables and temporary objects in SQL Server.

Which of the following Encryption key is the root of the database engine’s encryption hierarchy?

Options are :

  • Extensible Key Management
  • Symmetric Key
  • Service Master Key (Correct)
  • Database Master Key

Answer : Service Master Key

Explanation The Service Master Key is the root of the database engine’s encryption hierarchy and is generated automatically the first time it is needed to encrypt another key.

Azure SQL Database service is only available through ___________.

Options are :

  • TCP port 1443
  • TCP port 1343
  • TCP port 1334
  • TCP port 1433 (Correct)

Answer : TCP port 1433

Explanation Azure SQL Database service is only available through TCP Port 1433.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions