70-764 Administering a SQL Database Infrastructure Mock Exam

Which of the following recovery model is default for the [master], [msdb] and [tempdb] system databases?

Options are :

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

Answer : Simple Recovery Model

Explanation The simple recovery model is the default model for [msdb], [master] and [tempdb] system databases.

Which of the following option should you use with the Backup to know that your backup is successfully restore without any errors?

Options are :

  • WITH CONTINUE_AFTER_ERROR
  • WITH COPY_ONLY
  • WITH CHECKSUM (Correct)
  • WITH COMPRESSION

Answer : WITH CHECKSUM

Explanation You should use the WITH CHECKSUM option with the Backup so you can ensure that your backup is successfully restored without any errors.

70-764 Administering a SQL Database Infrastructure Exam

You are using Microsoft SQL Server 2016.

How many number of System Databases are there in SQL Server?

Options are :

  • Three
  • Four (Correct)
  • Two
  • Five

Answer : Four

Explanation There are four system databases there in any version of SQL Server. These are master, model, msdb, tempdb.

Which of the following system database use the Full Recovery Model by default?

Options are :

  • msdb
  • master
  • model (Correct)
  • tempdb

Answer : model

Explanation The [model] system database use the Full Recovery Model by default. The simple recovery model is the default model for [msdb], [master] and [tempdb] system databases. You can also change the [msdb] system database recovery model to full if you want to use the benefits of transaction log backups.

RLS supports filter and block security predicates. Which of the following security predicates explicitly blocks all write operations that violate the predicate?

Options are :

  • Block predicate (Correct)
  • Filter predicate

Answer : Block predicate

Explanation Block predicate: It explicitly blocks all write operations that violate the predicate.

Developing SQL Databases - Exam 70-762 Certification Exam

Which of the following key is used to protect the private keys of certificates and asymmetric keys that are present in the database?

Options are :

  • Symmetric Key
  • Service Master Key
  • Database Master Key (Correct)
  • Asymmetric Key

Answer : Database Master Key

Explanation Database Master Key: It is used to protect the private keys of certificates and asymmetric keys that are present in the database.

SQL Server supports the custom roles.

Which of the following roles allow you to create a custom role at the server scope with a customized set or permissions?

Options are :

  • User-defined database roles
  • Application roles
  • User-defined server roles (Correct)

Answer : User-defined server roles

Explanation The SQL Server supports the following custom roles: User-defined server roles: Introduced in the SQL Server 2012. It allows you to create a custom role at the server scope with a customized set or permissions. Application roles: It is introduced in SQL Server 7.0. It allows you to restrict user access to data based on the application that the user is using. User-defined database roles: Available with SQL Server since it was released on the Windows NT platform.

A company named XYZ has an on premises Azure SQL Database instances and SQL Server environment. An environment hosts some customer databases. A customer that uses an on-premises instance reports that queries take long time to complete.You have to reconfigure table statistics so that the query optimizer can use the optimal query execution plans available.Which T-SQL segment should you use?Solution: You use SET AUTO_UPDATE_STATISTICS ONDoes this solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation Yes, the solution meets the goal. AUTO_UPDATE_STATISTICS ON specifies that the query optimizer updates statistics when they are used by a query and when they might be out of date.

70-764 Administering a SQL Database Infrastructure Tests

You manage a SQL Server database named SalesOrders.You need to verify the integrity of the database and attempt to repair any errors that are found.Repair must not cause any data loss in the database.Which DBCC command should you use?

Options are :

  • DBCC CHECKDB (‘SalesOrders’, REPAIR_REBUILD) (Correct)
  • DBCC REPAIR_FAST (‘SalesOrders’, REPAIR_REBUILD)
  • DBCC PHYSICAL_ONLY (‘SalesOrders’, REPAIR_FAST)
  • DBCC REPAIR_REBUILD (‘SalesOrders’, REPAIR_ALLOW_DATA_LOSS)

Answer : DBCC CHECKDB (‘SalesOrders’, REPAIR_REBUILD)

Explanation You should use DBCC CHECKDB (‘SalesOrders’, REPAIR_REBUILD) command. CHECKDB checks the logical and physical integrity of all the objects in the specified database. REPAIR_REBUILD performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

True or False: The DBCC CHECKDB command improves query performance.

Options are :

  • True
  • False (Correct)

Answer : False

Explanation The given statement is false. The DBCC CHECKDB command does not improve query performance. It only checks the physical and logical integrity of all the objects in the specified database.

You are managing SQL Server 2016 database.You provide temporary security access to UserA to the database server.You need to know if UserA adds or remove logins to security admin.Which server-level audit group should you use?

Options are :

  • SUCCESSFUL_LOGIN_GROUP
  • SERVER_ROLE_MEMBER_CHANGE_GROUP (Correct)
  • SERVER_PRINCIPAL_IMPERSONATION_GROUP
  • SERVER_STATE_CHANGE_GROUP

Answer : SERVER_ROLE_MEMBER_CHANGE_GROUP

Explanation You should use the SERVER_ROLE_MEMBER_CHANGE_GROUP server-level audit group. This event is raised whenever a login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures. It is equivalent to the Audit add login to server role event class.

70-764 Administering a SQL Database Infrastructure Exam

You are implementing a SQL server 2016 seven-node failover cluster.You need to choose a quorum configuration.Which configuration should you use?

Options are :

  • Cluster Shared Volume (CSV)
  • Node and Disk Majority
  • Distributed File System(DFS)
  • Node Majority (Correct)

Answer : Node Majority

Explanation You should configure a Node Majority for seven-node failover cluster. Node Configuration is used when there is an odd number of nodes in the cluster.

Which of the following command checks the allocation and structural integrity of all tables and indexed views in a filegroup?

Options are :

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

Answer : DBCC CHECKFILEGROUP

Explanation DBCC CHECKFILEGROUP: It checks the allocation and structural integrity of all tables and indexed views in a filegroup. DBCC CHECKTABLE: It checks the allocation and structural integrity of a table. DBCC CHECKDB: It checks the physical and logical integrity of all the objects in a database. DBCC CHECKCATALOG: It checks the consistency of the system tables with a database.

You are implementing a SQL server 2016 four-node failover cluster.You need to choose a quorum configuration.Which configuration should you use?

Options are :

  • Distributed File System(DFS)
  • Node and Disk Majority (Correct)
  • Node Majority
  • Cluster Shared Volume (CSV)

Answer : Node and Disk Majority

Explanation You should configure a Node and Disk Majority for four-node failover cluster. Node and Disk Configuration is used when there is an even number of nodes in the cluster.

70-764 Administering a SQL Database Infrastructure Exam

True or False: The DBCC CHECKDB command update any indexes.

Options are :

  • True
  • False (Correct)

Answer : False

Explanation The given statement is false. The DBCC CHECKDB command does not update any indexes. It only checks the physical and logical integrity of all the objects in the specified database.

You need to create a new custom User-defined server role for performing some actions.

Which of the following system database should you use for creating role?

Options are :

  • msdb
  • model
  • master (Correct)
  • tempdb

Answer : master

Explanation You should use master system database for creating custom User-defined server role. You create it using the statements like: Use master; CREATE SERVER ROLE role_name;

Which of the following statement is true?

Options are :

  • No transaction log backups are possible in simple recovery model. (Correct)
  • The full recovery model is typically used in non-production environments.
  • The simple recovery model is typically used in production environments.
  • Transaction log backups are possible in simple recovery model.
  • The bulk logged recovery model is typically used in production environments.

Answer : No transaction log backups are possible in simple recovery model.

Explanation Simple recovery model: It is typically used in non-production environments. Transaction log backups are not possible. Full recovery model: It is typically used in production environments. Bulk logged recovery model: It is not typically used in production environments.

Developing SQL Databases - Exam 70-762 Certification Exam

True or False: SQL Server 2016 Enterprise Edition support enhanced Server-level and Database-level auditing.

Options are :

  • False
  • True (Correct)

Answer : True

Explanation SQL Server 2016 Enterprise Edition support enhanced Server-level and Database-level auditing and Standard Edition only support basic server-level auditing.

True or False: Dynamic data masking does not encrypt the data.

Options are :

  • True (Correct)
  • False

Answer : True

Explanation The given statement is true. Dynamic data masking does not encrypt the data. It only masks the result set.

You have a database named Demo.Users report that database application that updates the data in Demo is unresponsive.You have to identify which process prevents the application from responding.What should you do?Solution: You run sp_who statement.Does the solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation No, the solution does not meet the goal. sp_who: It provides information about current sessions, users and processes in an instance of the SQL Server database engine. You should query sys.dm_exec_session_wait_stats because it returns information about all the waits encountered by threads that executed for each session.

70-764 Administering a SQL Database Infrastructure

You manage a Microsoft SQL Server environment.

You have a database named Sales_Info that includes a table named Tbl1.

You repair the table because it is corrupted.

You need to ensure that all data in Tbl1 complies with the schema.

Solution:

Use Sales_Info;

DBCC CHECKCONSTRAINTS (‘Tbl1’) WITH ALL_CONSTRAINTS;

Does the solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation DBCC CHECKCONSTRAINTS checks the integrity of a specified constraint or all constraints on a specified table in the current database. ALL_CONSTRAINTS option checks all enabled and disabled constraints on the table if the table name is specified or if all tables are checked. Otherwise checks only the enabled constraint. It has no effect when a constraint name is specified.

How many Custom Roles are supported by SQL Server?

Options are :

  • Two
  • One
  • Three (Correct)

Answer : Three

Explanation SQL Server supports three custom roles: Application roles, User-defined Database roles, User-defined Server Roles.

You plan to install Microsoft SQL Server 2016 for a web hosting company. Company plan to host multiple web sites, each supported by a SQL Server database.You need to select an edition of SQL Server that support:- Backup Compression of databases- Basic data integration features- low total cost of ownershipWhich edition should you use?

Options are :

  • Express Edition with tools
  • Web Edition
  • Standard Edition (Correct)
  • Express Edition with Advanced Services

Answer : Standard Edition

Explanation Backup compression is supported on SQL Server 2016 editions: Enterprise, Standard, Developer

70-764 Administering a SQL Database Infrastructure Mock Exam

Which of the following DBCC Command should you use for checking the logical and physical integrity of all the objects of a database?

Options are :

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

Answer : DBCC CHECKDB

Explanation You should use the DBCC CHECKDB command for checking the logical and physical integrity of all objects of a database. DBCC CHECLTABLE command check the allocation and structural integrity of a table. DBCC CHECKALLOC command checks the consistency of disk space allocation structures with a database.

You need to ensure that session requests are addressed to a specific instance can be allocated different processor resources based on session request properties?

Options are :

  • Processor Affinity
  • Windows System Resource Manager
  • Resource Governor (Correct)
  • I/O Affinity

Answer : Resource Governor

Explanation Resource Governor enables you to allocate session requests to different resources based on the characteristics of the session request properties.

Which permission grants the right to create new databases?

Options are :

  • CONTROL SERVER
  • ADMINISTER BULK OPERATIONS
  • ALTER ANY DATABASE
  • CREATE ANY DATABASE (Correct)

Answer : CREATE ANY DATABASE

Explanation CREATE ANY DATABASE: It grants the right to create new databases. ADMINISTER BULK OPERATIONS: It grants access to bulk insert operations. CONTROL SERVER: It implicitly grants all other server level permissions. ALTER ANY DATABASE: It implicitly grants the CREATE ANY DATABASE permission.

70-764 Administering a SQL Database Infrastructure Mock Exam

Which of the following Fixed Database role should you grant to a database user so that it will be able to perform actions like it can perform all configuration maintenance activities on the database and also drop the database?

Options are :

  • db_datareader
  • db_securityadmin
  • db_owner (Correct)
  • public

Answer : db_owner

Explanation You should grant a db_owner fixed database role to a database user so that it will perform actions like can perform all configuration maintenance activities on the database and also drop the database.

Which of the following predicates block delete operations?

Options are :

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

Answer : BEFORE DELETE

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

True or False: A database backup and page restore can be run at the same time.

Options are :

  • True
  • False (Correct)

Answer : False

Explanation The given statement is false because a database backup and page restore cannot be run at the same time.

70-764 Administering a SQL Database Infrastructure Exam

True or False: SQL Server supports auditing at the server scope and database scope.

Options are :

  • True (Correct)
  • False

Answer : True

Explanation The given statement is true. The SQL Server supports auditing at the server scope and database scope.

Which of the following server action group includes actions that alter server settings?

Options are :

  • SUCCESSFUL_LOGIN_GROUP
  • SERVER_OPERATION_GROUP (Correct)
  • BACKUP_RESTORE_GROUP
  • DATABASE_CHANGE_GROUP

Answer : SERVER_OPERATION_GROUP

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

True or False: The bulk logged recovery model is typically used in production environments.

Options are :

  • True
  • False (Correct)

Answer : False

Explanation The given statement is false because the bulk logged recovery model is not typically used in production environments. The full recovery model is typically used in production environments.

70-764 Administering a SQL Database Infrastructure Question and Answers

You implement Always Encrypted (AE) technique for encryption of your data.AE supports the two types of encryption: Deterministic and Randomized. Which of the following encryption uses a method that always generates the same ciphertext for any given plaintext value?

Options are :

  • Deterministic (Correct)
  • Randomized

Answer : Deterministic

Explanation Deterministic encryption: It uses a method that always generates the same ciphertext for any given plaintext value.

Which of the following RESTORE Option should you use to checks whether the backup set it complete and the entire backup is readable?

Options are :

  • LABLEONLY
  • VERIFYONLY (Correct)
  • HEADERONLY
  • FILELISTONLY

Answer : VERIFYONLY

Explanation The VERIFYONLY restore option is used to check whether the backup set is complete and the entire backup is readable. This option also performs the checksum is correct if present, checks there is a sufficient space on the destination disks also checks some header fields of database pages such as pageID.

Which of the following system catalog view returns each cryptographic property associated with a securable?

Options are :

  • sys.key_encryption
  • sys.openkeys
  • sys.crypt_properties (Correct)
  • sys.cryptographic_providers

Answer : sys.crypt_properties

Explanation sys.crypt_properties: It is a system catalog view that returns each cryptographic property associated with a securable.

You are managing a Microsoft SQL Server Environment.You implement Transparent Data Encryption (TDE).A user will assist in managing TDE.You need to ensure that the user can view the TDE metadata.Which permission should you grant to user?

Options are :

  • View Database State
  • dbo
  • DDLAdmin
  • sysadmin
  • View Definition (Correct)

Answer : View Definition

Explanation For allowing a user to view the TDE metadata grant View Definition permission.

Which of the following server action group includes actions that alter server settings?

Options are :

  • SERVER_OPERATION_GROUP (Correct)
  • BACKUP_RESTORE_GROUP
  • DATABASE_CHANGE_GROUP
  • SUCCESSFUL_LOGIN_GROUP

Answer : SERVER_OPERATION_GROUP

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

SQL Server supports the custom roles.

Which of the following custom roles allow you to restrict user access to data based on the application that the user is using?

Options are :

  • User-defined server roles
  • Application roles (Correct)
  • User-defined database roles

Answer : Application roles

Explanation The SQL Server supports the following custom roles: i) Application roles: It is introduced in SQL Server 7.0. It allows you to restrict user access to data based on the application that the user is using. ii) User-defined database roles: Available with SQL Server since it was released on the Windows NT platform. iii) User-defined server roles: Introduced in the SQL Server 2012. It allows you to create a custom role at the server scope with a customized set or permissions.

You are using Always Encrypted (AE) feature in SQL Server 2016.

Which of the following key is used to encrypt sensitive data stored in table’s columns?

Options are :

  • Column Master Key (CMK)
  • Column Encryption Key (CEK) (Correct)

Answer : Column Encryption Key (CEK)

Explanation Column Encryption Key: It is used to encrypt sensitive data stored in table’s columns. Column Master Key: It is used to protect the keys used to encrypt the column encryption keys.

Can we use an asymmetric key to encrypt a symmetric key within database?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation Yes, We can use an asymmentric key to encrypt a symmetric key within a database.

SQL Server Agent alerts the 832 error message.

When the 832 error message is generated?

Options are :

  • It is generated when a logical consistency check fails after reading or writing a database page.
  • It is generated when a page in memory has been corrupted. (Correct)
  • 832 error message is not generated by SQL Server Agent alert.
  • It is generated whenever the Windows environment issues a cyclic redundancy check

Answer : It is generated when a page in memory has been corrupted.

Explanation SQL Server Agent alert generates the 832 error message when a page in memory has been corrupted. It is a rare error.

True or False: Log Shipping is a high availability technology.

Options are :

  • True
  • False (Correct)

Answer : False

Explanation The given statement is false. Log Shipping: It is not a high availability technology. It supports multiple secondary servers.

You have two databases name DB1 and DB2 that are located on the same server.You create a stored procedure named Stored1 in DB1.Stored1 will query a table named Table2 in DB2.You need a solution to ensure that Stored1 can access Table2 without granting users direct access to Table2.What Should you include in the recommendation?

Options are :

  • Cross-database ownership chaining
  • Application roles (Correct)
  • Digital Certificate
  • Contained Databases

Answer : Application roles

Explanation You should recommend an application role. Application role is a database principle that enables an application to run with its own, user-like permissions.

You are troubleshooting an application that runs a query.The application frequently causes deadlocks. You need to identify the isolation level used by the query when a deadlock occurs.What should you do?

Options are :

  • Query the sys.dm_exec_seesions dynamic management view (Correct)
  • Query the sys.dm_exec_requests dynamic management view.
  • Create a trace in SQL Server Profiler that contains the deadlock graph event.
  • Enable trace flag 1222, and then view the SQL Server error log.

Answer : Query the sys.dm_exec_seesions dynamic management view

Explanation The sys.dm_exec_sessions DMV returns one row per authenticated session on SQL Server. It include the column transaction_isolation_level smallint. If it is 0 then unspecified, 1 if Read UnCommitted, 2 if Read Committed, 3 if Repeatable, 4 if Serializable, 5 if Snapshot. This column is not nullable.

You are a database administrator for a Microsoft SQL Server 2016.Merge replication has been configured for an application that is distributed across offices the XML and varchar(max) data types.Occasionally, merge replication fails due to timeout errors.You need to reduce the occurrence of these timeout errors.Solution: Set the merge agent on the problem subscribers to use the slow link agent profile.Does the solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation You might have different profile for different instances of an agent. When replication is configured, a set of agent profiles is installed on the distributor. An agent profile contains a set of parameters that are used each time an agent runs.

True or False: TDE does not encrypt data in the database engine’s buffer pool.

Options are :

  • True (Correct)
  • False

Answer : True

Explanation The given statement is true. TDE: It does not encrypt data in the database engine’s buffer pool. Any user can potentially read the data if they have sufficient permissions.

You are deploying a Microsoft SQL Server architecture to support a new mission-critical application. The application includes a dedicated reporting component that performs read-only operations against the application’s databases.You need to implement a solution that meet the below requirements:- Include automatic failover in the event of a hardware problem on the primary server.- Provide maximum uptime for the databases.- Separate the reporting workload from the read/write transactional processing workload and contain real-time data.- Modifications to the application to support the new architecture are not permitted.What should you implement?

Options are :

  • A Windows Cluster with a shared-nothing architecture
  • A file share witness
  • Log Shipping
  • An Always On Availability Group with all replicas in synchronous-commit mode (Correct)

Answer : An Always On Availability Group with all replicas in synchronous-commit mode

Explanation Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.

Sometimes DBCC command can fail during its operation and generates an error message.

What should the state value 3 of the error message represents?

Options are :

  • It represents an unknown error.
  • It represents a corruption in metadata that terminated the DBCC command. (Correct)
  • It represents an internal DBCC error.
  • It represents an assert or access violation was detected.

Answer : It represents a corruption in metadata that terminated the DBCC command.

Explanation The state value 3 of the error message generated by DBCC command represents a corruption in metadata that terminates the DBCC command.

Which of the following are not applicable for Dynamic Data Masking (DDM)? (choose all possible options)

Options are :

  • FILESTREAM data (Correct)
  • COLUMN_SET or a Sparse Column that is a part of a column set (Correct)
  • Computed column (Correct)
  • Always Encrypted Columns (Correct)

Answer : FILESTREAM data COLUMN_SET or a Sparse Column that is a part of a column set Computed column Always Encrypted Columns

Explanation Dynamic Data Masking (DDM) is not applicable to all of the following: - Always Encrypted Columns - COLUMN_SET or a Sparse Column that is a part of a column set - Computed column (if computed column depends on a masked column the computed column will return masked data) - FILESTREAM data

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions