70-764 Administering a SQL Database Infrastructure Question and Answers

You are creating a database that will store username and password for an application.You need to recommend a solution to store the passwords in database.What should you recommend?

Options are :

  • Reversible Encryption
  • Encrypting File System (EFS)
  • Transparent Data Encryption (TDE) (Correct)
  • One-Way Encryption

Answer : Transparent Data Encryption (TDE)

Explanation Transparent Data Encryption (TDE) is a special case of encryption using a symmetric key. TDE encrypts an entire database using that symmetric key called the database encryption key. The database encryption key is protected by other keys or certificates which are protected by the database master key stored in an EKM module.

You have a database named DB1 that is 650GB and is updated frequently.You enabled log shipping for DB1 and configure backup and restore to occur every 30 minutes.You discover that the disks on the data server are almost full.You need to reduce the amount of disk space used by the log shipping process.Solution: You enable compression for the transaction log backups.Does this solution meet the goal?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation You must have created at least one full backup before you can create any log backup. Enabling database backups to use compression can be done in one of two ways. You can either configure all backups for the instance to be compressed (it is disabled by default), or you can issue the WITH COMPRESSION command for a specific database backup.

70-764 Administering a SQL Database Infrastructure Mock Exam

True or False: In SQL Server 2016 the Backup compression is compatible with Transparent Database Encryption (TDE).

Options are :

  • True (Correct)
  • False

Answer : True

Explanation Yes, the Backup Compression is Compatible with Transparent Database Encryption (TDE) for SQL Server 2016. For earlier versions of SQL Server, the backup compression is not compatible with Transparent Database Encryption (TDE).

You are administrator at a Microsoft SQL Server 2016 instance.After a routine shutdown, the drive that contains tempdb fails.You need to be able to start the SQL Server.What should you do?

Options are :

  • Start SQL Server in single-user mode.
  • Modify tempdb location in startup parameters
  • Configure SQL Server to bypass Windows application logging.
  • Start SQL Server in minimal configuration mode. (Correct)

Answer : Start SQL Server in minimal configuration mode.

Explanation Start SQL Server in minimal configuration mode if it shut down because of a hardware or software failure. Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode. Tempdb is configured at the smallest possible size.

You are using Azure SQL Database.

In which type of authentication a combination of username and password is required?

Options are :

  • SQL Authentication (Correct)
  • Azure Active Directory(AD) Authentication

Answer : SQL Authentication

Explanation You should use the SQL Authentication for authentication of Azure SQL Database because it requires a combination of username and password.

70-764 Administering a SQL Database Infrastructure Tests

Which Editions of SQL Server support Transparent Database Encryption (TDE)?

Options are :

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

Answer : Enterprise Edition

Explanation The Enterprise Edition of SQL Server support the Transparent Database Encryption (TDE). It is the only edition of SQL Server which support TDE.

You have a server that runs SQL Server 2016 standard Edition.

You need to examine the missing indexes.

What should you use?

Options are :

  • SQL Server Data collector (Correct)
  • Activity Monitor
  • SQL Server Data Tools (SSDT)
  • Query Store

Answer : SQL Server Data collector

Explanation You should use SQL Server Data Collector to get information about missing indexes. The Data Collector by default collect information about disk usage, query statistics, and server activity, Utility Information. The Query statistics collect information about activity, execution plans and text on the SQL Server instance. The information about missing indexes can be found with the execution plans.

Which of the following Backup operation should you use if your database is very large and it is made up of different files?

Options are :

  • File Backup operation (Correct)
  • Partial Backup operation
  • Full Backup operation
  • Differential Backup operation

Answer : File Backup operation

Explanation You should use the File Backup operation when your database is very large and it is made up of different files.

70-764 Administering a SQL Database Infrastructure

You support a SQL Server 2016 cluster on a windows server failover cluster instance(FCI).

Now you need to add some additional disk to the FCI for SQL Server cluster.

Which of the following tool should you use?

Options are :

  • SSMS (SQL Server Management Studio)
  • Failover Cluster Manager (Correct)
  • Disk Manager
  • Server Manager

Answer : Failover Cluster Manager

Explanation You should use the Failover Cluster Manager tool for adding additional disk to the FCI for SQL Server Cluster. Use Server Manager for installing WSFC on the first node of your failover cluster. You should use Disk Manager to verify that the disk has been added and to help troubleshoot problems that encounter at the time of adding disk. You should not use SSMS because this entire procedure must be completed outside of the SQL Server instance.

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 DML triggers.

Does the solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation You should use DML triggers to track data before and after changes made to the tables within the database.

Which of the following Maintenance plan should you use for reducing the disk space consumed by the database?

Options are :

  • Shrink Database (Correct)
  • Check Database Integrity
  • Update Statistics
  • Notify Operator Task

Answer : Shrink Database

Explanation You should use the Shrink Database Maintenance plan if you want to reduce the disk space consumed by the database. The size will be reduce removing empty data, index, and log pages.

70-764 Administering a SQL Database Infrastructure Exam

In which of the following situations you can use Full Recovery Model? (Choose all possible options)

Options are :

  • To recover data up to the point in time when the database crashed. (Correct)
  • To recover data to a specific point in time. (Correct)
  • To recover to a marked transaction. (Correct)
  • If you want to perform a bulk operation with the best performance.

Answer : To recover data up to the point in time when the database crashed. To recover data to a specific point in time. To recover to a marked transaction.

Explanation You should use the Full Recovery Model when - To recover data up to the point in time when the database crashed. - To recover data to a specific point in time. - To recover to a marked transaction.

Which of the following System Database is default in SQL Server?

Options are :

  • msdb
  • master (Correct)
  • tempdb
  • model

Answer : master

Explanation SQL Server contains four system databases which are master, model, msdb, tempdb. master database is default database for SQL Server.

You need to analyze information about processor utilization, database disk I/O, batch requests, and the number of waiting tasks on a particular database in Microsoft Azure.

What should you use?

Options are :

  • SQL Server Data Tools (SSDT)
  • SQL Server Configuration Manager
  • Activity Monitor in SQL Server Management Studio (Correct)
  • SQL Server Data Collector

Answer : Activity Monitor in SQL Server Management Studio

Explanation You should use Activity Monitor in SQL Server Management Studio to analyze information about processor utilization, database disk I/O, batch requests, and the number of waiting tasks. Activity Monitor is a tabbed document window and contains Overview, Active User Tasks, Data file I/O, and Recent Expensive Queries.

70-764 Administering a SQL Database Infrastructure Mock Exam

Which of the following Backup option should you use with Backup statement if you want to ignore ‚Äúsafety check‚Ä??

Options are :

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

Answer : WITH SKIP

Explanation You should use the WITH SKIP option with the Backup operation to ensure that ‚Äúsafety check‚Ä? is ignore. The ‚Äúsafety check‚Ä? normally checks the backup set‚Äôs expiration date and name before overwriting it.

How many SQL Server Failover Cluster Instances (FCI) are supported when you are using shared cluster disks?

Options are :

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

Answer : 25 FCIs

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

Which of the following option should you use with the Backup to ensure that your backup will be encrypted?

Options are :

  • WITH ENCRYPTION (Correct)
  • WITH DESCRIPTION
  • WITH CHECKSUM
  • WITH SERVER CERTIFICATE

Answer : WITH ENCRYPTION

Explanation You should use the WITH ENCRYPTION option with the Backup to ensure that your backup is encrypted. The default behavior for this option is NO_ENCRYPTION.

70-764 Administering a SQL Database Infrastructure Tests

True or False: The implementation of Residual Information Protection (RIP) decrease security?

Options are :

  • True
  • False (Correct)

Answer : False

Explanation The implementation of Residual Information Protection (RIP) increases security, but can negatively impact the performance of the system.

Which of the following database objects are associated with the Data Definition Language (DDL) trigger? (choose all possible options)

Options are :

  • ALTER (Correct)
  • DROP (Correct)
  • INSERT
  • CREATE (Correct)

Answer : ALTER DROP CREATE

Explanation The Data Definition Language (DDL) triggers are associated with CREATE, ALTER, DROP database object. The INSERT, UPDATE, DELETE statements are associated with Data Manipulation Language (DML) triggers.

By default, Data Collector in SQL Server collect which information? (choose all possible options)

Options are :

  • Disk Usage (Correct)
  • Server Activity (Correct)
  • Disk Usage History
  • Utility Information (Correct)
  • Query Statistics (Correct)

Answer : Disk Usage Server Activity Utility Information Query Statistics

Explanation Data collector in SQL Server by default collects information about Disk usage, Query Statistics, Server Activity, Utility Information. Disk Usage History information is collected by Reports component of the Data Collector architecture.

Developing SQL Databases - Exam 70-762 Certification

Which of the following Dynamic Management View (DMV) should you use, if you want the complete list of available audit actions and audit groups?

Options are :

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

Answer : sys.dm_audit_actions

Explanation The DMV for audits are sys.dm_audit_action, sys.dm_audit_class_type_map, sys.dm_server_audit_type_map. The sys.dm_audit_action DMV gives the complete list of audit actions and audit action groups. The sys.dm_server_audit_status DMV gives a list of all the audits defined on an instance of SQL Server. The sys.dm_server_audit_type_map DMV gives the reference data list mapping audit class code to description.

Which Backup operation required to perform before the Log Backup (Incremental Backup) Operation?

Options are :

  • Differential Backup Operation
  • Full Backup Operation (Correct)

Answer : Full Backup Operation

Explanation Before performing the Log Backup operation, you need to perform Full Backup operation. The Full Backup capture everything in the database. Differential and Log backup do not work unless they have a baseline full database backup.

You work for a company XYZ. The management wants to extend the failover cluster to Anand. They plan to add to two more nodes to the cluster in Anand.

Which quorum configuration should you use?

Options are :

  • Use a node majority quorum with a file share witness
  • Use a node majority quorum a cloud witness. (Correct)
  • Use a node majority with a disk witness.
  • Use a node majority quorum with no witness.

Answer : Use a node majority quorum a cloud witness.

Explanation You should use a cloud witness because it is designed for this type scenario where you do not add a third data center.

70-764 Administering a SQL Database Infrastructure Question and Answers

In which of the following situation you can use Log Backup operation? (Choose all possible options)

Options are :

  • When you want to recover the database until the time that it crashed. (Correct)
  • When you need point-in-time recovery. (Correct)
  • When you do not need point-in-time recovery.
  • When you want to ensure that the database‚Äôs transaction log does not grow too large. (Correct)

Answer : When you want to recover the database until the time that it crashed. When you need point-in-time recovery. When you want to ensure that the database’s transaction log does not grow too large.

Explanation You can use the Differential Backup operation in the following situations: - When you want to recover the database until the time that it crashed. - When you need point-in-time recovery. - When you want to ensure that the database’s transaction log does not grow too large. - The database has smaller RPO than the frequency of your full/differential backups.

You have a database named DB1 that is 650GB and is updated frequently.You enabled log shipping for DB1 and configure backup and restore to occur every 30 minutes.You discover that the disks on the data server are almost full.You need to reduce the amount of disk space used by the log shipping process.Solution: You configure log shipping to backup and restore by using shared folder.Does this solution meet the goal?

Options are :

  • No (Correct)
  • Yes

Answer : No

Explanation Log shipping involves copying a database backup and subsequent transaction log backups from the primary server and restoring the database and transaction log backups on one or more secondary servers.

You are the database administrator for a company that uses the Microsoft SQL Server.

You manage both Microsoft Azure SQL Database and on-premises environment.

Clients connect by using line-of-business applications and the Developers are connect by using SQL Server Management Studio (SSMS).

You need to provide permissions to a service account that will be used to provision a new database for a client.

Solution: You grant dbcreator Fixed Server Role.

Does the solution meet the goal?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation The members of the dbcreator Fixed Server Role can create, alter, drop, and restore any database.

70-764 Administering a SQL Database Infrastructure

You implement Always Encrypted (AE) technique for encryption of your data.

AE support two types of encryption: Deterministic and Randomized encryptions.

Which encryption is more secure?

Options are :

  • Randomized Encryption (Correct)
  • Deterministic Encryption

Answer : Randomized Encryption

Explanation Always Encrypted (AE) supports two encryptions: Deterministic and Randomized encryption. The Randomized Encryption is more secure than deterministic encryption. The Deterministic encryption use the method that always generates the same ciphertext for any given plaintext value. And Randomized Encryption generates different ciphertext for the same plaintext. So the randomized encryption is more secure than deterministic encryption.

You work in XYZ company. The management of this company does not want employees to see the sales target and salaries of other employees in the company. This sensitive information is store in a number of columns in different tables.

Which encryption strategy should you use?

Options are :

  • Row-level security (RLS) (Correct)
  • Dynamic Data Masking (DDM)
  • Always Encrypted
  • Configure Auditing

Answer : Row-level security (RLS)

Explanation You should use the Row-level security (RLS). RLS allows you to control access based on from which department users belong to. This is a data access issue, not an encryption issue. Dynamic Data Masking (DDM) cannot hide data based on the user and rows within a table. Always encrypted encrypts the entire column. It cannot selectively give access to different users.

You are using Microsoft SQL Server 2016.

Which authentication mode should you use at the time of installing the SQL Server so that it enables sa login?

Options are :

  • SQL Server Authentication Mode (Mixed Mode) (Correct)
  • Windows Authentication Mode

Answer : SQL Server Authentication Mode (Mixed Mode)

Explanation You should use SQL Server Authentication Mode (Mixed Mode) at the time of installing the SQL Server so it enables sa login. If you install SQL Server using Windows Authentication mode, and later change to mixed mode it does not enable the sa login.

70-764 Administering a SQL Database Infrastructure Exam

Which type of security predicate should you use to explicitly blocks all write operations that violate the predicate in Row-level security (RLS)?

Options are :

  • Filter Predicate
  • Block Predicate (Correct)

Answer : Block Predicate

Explanation You should use Block Predicate to explicitly blocks all write operations that violate the predicate in row-level security(RLS). Row-level security supports two types security predicates: Filter predicate and Block predicate. Filter predicate filter the rows available to read operations.

Which type of Backup also called Log Backup?

Options are :

  • Incremental Backup (Correct)
  • Partial Backup
  • Differential Backup
  • Full Backup

Answer : Incremental Backup

Explanation Incremental Backup also called Log Backup in SQL Server.

In which of the following situation you can use Differential Backup operation? (Choose all possible options)

Options are :

  • To reduce the size of the database set as compared to the full backup (Correct)
  • When you do not need point-in-time recovery. (Correct)
  • To reduce the time taken by the backup operation as compared to full backup (Correct)
  • When you need point-in-time recovery.

Answer : To reduce the size of the database set as compared to the full backup When you do not need point-in-time recovery. To reduce the time taken by the backup operation as compared to full backup

Explanation You can use the Differential Backup operation in the following situations: - To reduce the time taken by the backup operation as compared to full backup - To reduce the size of the database set as compared to the full backup - When you do not need point-in-time recovery.

70-764 Administering a SQL Database Infrastructure Tests

Which of the following predicates prevent users from updating rows that currently violate the predicate?   

Options are :

  • AFTER INSERT
  • BEFORE UPDATE (Correct)
  • BEFORE DELETE

Answer : BEFORE UPDATE

Explanation BEFORE UPDATE: Prevent users from updating rows that currently violate the predicate. BEFORE DELETE: Block delete operations.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions