Microsoft 70-764 Microsoft Certified Professional

Quesons & Answers PDF

Page 1

Microsoft 70-764 Exam Microsoft Administering a SQL Database Infrastructure Exam

Thank you for Downloading 70-764 exam PDF Demo You can also try our 70-764 pracce exam soware Download Free Demo: hps://www.cercaongenie.com/7 hps://www.cercaongenie.com/70-764.htm 0-764.htmll

hps://www.cercaongenie.com

Titles you can't find anywhere else

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.














Page 2

Version: 14.0 Queson: 1 Note: This queson queson is part of a series of quesons quesons that present present the same scenario. scenario. Each queson in the series contains a unique soluon that might meet the stated goals. Some queson sets might have more than one correct soluon, while others might not have a correct soluon. Aer you answer a queson in this secon, you will NOT be able to return to it. As a result, these quesons will not appear in the review screen. You need to congure a Microso SQL Server instance to ensure that a user named Mail1 can send mail by using Database Mail. Soluon: You add the DatabaseMailUserRole to Mail1 in the tempdb database. Does the soluon meet the goal? A. Yes B. No

Answer: B Explanaon: Database Mail is guarded by the database role DatabaseMailUserRole in the msdb database, not the tempdb database, in order to prevent anyone from sending arbitrary emails. Database users or roles must be created in the msdb database and must also be a member of DatabaseMailUserRole in order to send emails with the excepon of sysadmin who has all privileges. Note: Database Mail was rst introduced as a new feature in SQLServer 2005 and replaces the SQL Mail feature found in previous versions. References: hp://www.idevelopment.info/data/SQLServer/DBA_ps/Database_Administraon/DBA_20.shtml

Queson: 2 Note: This queson queson is part of a series of quesons quesons that present present the same scenario. scenario. Each queson in the series contains a unique soluon that might meet the stated goals. Some queson sets might have more than one correct soluon, while others might not have a correct soluon. Aer you answer a queson in this secon, you will NOT be able to return to it. As a result, these quesons will not appear in the review screen. You need to congure a Microso SQL Server instance to ensure that a user named Mail1 can send mail by using Database Mail. Soluon: You add the DatabaseMailUserRole to Mail1 in the msdb database. Does the soluon meet the goal? A. Yes














Page 3

Explanaon: Database Mail is guarded by the database role DatabaseMailUserRole in the msdb database in order to prevent anyone from sending arbitrary emails. Database users or roles must be created in the msdb database and must also be a member of DatabaseMailUserRole in order to send emails with the excepon of sysadmin who has all privileges. Note: Database Mail was rst introduced as a new feature in SQL Server 2005 and replaces the SQL Mail feature found in previous versions. References: hp://www.idevelopment.info/data/SQLServer/DBA_ps/Database_Administraon/DBA_20.shtml

Queson: 3 Note: This queson queson is part of a series of quesons quesons that present present the same scenario. scenario. Each queson in the series contains a unique soluon that might meet the stated goals. Some queson sets might have more than one correct soluon, while others might not have a correct soluon. Aer you answer a queson in this secon, you will NOT be able to return to it. As a result, these quesons will not appear in the review screen. You need to congure a Microso SQL Server instance to ensure that a user named Mail1 can send mail by using Database Mail. Soluon: You add the DatabaseMailUserRole to Mail1 in the master database. Does the soluon meet the goal? A. Yes B. No

Answer: B Explanaon: Database Mail is guarded by the database role DatabaseMailUserRole in the msdb database, not the master database, in order to prevent anyone from sending arbitrary emails. Database users or roles must be created in the msdb database and must also be a member of DatabaseMailUserRole in order to send emails with the excepon of sysadmin who has all privileges. Note: Database Mail was rst introduced as a new feature in SQL Server 2005 and replaces the SQL Mail feature found in previous versions. References: hp://www.idevelopment.info/data/SQLServer/DBA_ps/Database_Administraon/DBA_20.shtml

Queson: 4 Note: This queson queson is part of a series of quesons quesons that present present the same scenario. scenario. Each queson in the series contains a unique soluon that might meet the stated goals. Some queson sets might have more than one correct soluon, while others might not have a correct soluon. Aer you answer a queson in this secon, you will NOT be able to return to it. As a result, these quesons will not appear in the review screen.














Page 4

You need to ensure that you can perform auding at the database level for DB1. Soluon: You migrate DB1 to the default instance on a server that runs Microso SQL Server 2016 Standard edion. Does the soluon meet the goal? A. Yes B. No

Answer: B Explanaon: All edions of SQL Server support server level audits. All edions support database level audits beginning beginning with SQL Server 2016 SP1. Prior to that, database database level auding was limited to Enterprise, Enterprise, Developer, and Evaluaon edions. References: hps://docs.microso.com/en-us/sql/relaonal-databases/security/auding/sql-server-auditdatabase-engine

Queson: 5 Note: This queson queson is part of a series of quesons quesons that present present the same scenario. scenario. Each queson in the series contains a unique soluon that might meet the stated goals. Some queson sets might have more than one correct soluon, while others might not have a correct soluon. Aer you answer a queson in this secon, you will NOT be able to return to it. As a result, these quesons will not appear in the review screen. A company has a server that runs Microso SQL Server 2016 Web edion. The server has a default instance that hosts a database named DB1. You need to ensure that you can perform auding at the database level for DB1. Soluon: You migrate DB1 to a named instance on a server that runs Microso SQL Server 2016 Enterprise edion. Does the soluon meet the goal? A. Yes B. No

Answer: A Explanaon: All edions of SQL Server support server level audits. All edions support database level audits beginning beginning with SQL Server 2016 SP1. Prior to that, database database level auding was limited to Enterprise, Enterprise, Developer, and Evaluaon edions. References: hps://docs.microso.com/en-us/sql/relaonal-databases/security/auding/sql-server-auditdatabase-engine














Page 5

Note: This queson queson is part of a series of quesons quesons that present present the same scenario. scenario. Each queson in the series contains a unique soluon that might meet the stated goals. Some queson sets might have more than one correct soluon, while others might not have a correct soluon. Aer you answer a queson in this secon, you will NOT be able to return to it. As a result, these quesons will not appear in the review screen. A company has a server that runs Microso SQL Server 2016 Web edion. The server has a default instance that hosts a database named DB1. You need to ensure that you can perform auding at the database level for DB1. Soluon: You migrate DB1 to a named instance on a server than runs Microso SQL Server 2016 Standard edion. Does the soluon meet the goal? A. Yes B. No

Answer: B Explanaon: All edions of SQL Server support server level audits. All edions support database level audits beginning beginning with SQL Server 2016 SP1. Prior to that, database database level auding was limited to Enterprise, Enterprise, Developer, and Evaluaon edions. References: hps://docs.microso.com/en-us/sql/relaonal-databases/security/auding/sql-server-auditdatabase-engine

Queson: 7 Note: This queson queson is part of a series of quesons quesons that present present the same scenario. scenario. Each queson in the series contains a unique soluon that might meet the stated goals. Some queson sets might have more than one correct soluon, while others might not have a correct soluon. Aer you answer a queson in this secon, you will NOT be able to return to it. As a result, these quesons will not appear in the review screen. A company has an on-premises Microso SQL Server environment and Microso Azure SQL Database instances. The environment hosts several customer databases. One One cus customer tomer repo report rtss that that their their dat databas abase e is not not resp respon ondi ding ng as quick quickly ly as the the servi service ce level level agreements dictate. You observe that the database is fragmented. You need to opmize query performance. Soluon: You reorganize all indexes. i ndexes. Does the soluon meet the goal? A. Yes B. No

Answer: A














Page 6

References: hps://msdn.microso.com/en-us/library/ms189858(v=sql.105).aspx

Queson: 8 Note: This queson queson is part of a series of quesons quesons that present present the same scenario. scenario. Each queson in the series contains a unique soluon that might meet the stated goals. Some queson sets might have more than one correct soluon, while others might not have a correct soluon. Aer you answer a queson in this secon, you will NOT be able to return to it. As a result, these quesons will not appear in the review screen. A company has an on-premises Microso SQL Server environment and Microso Azure SQL Database instances. The environment hosts several customer databases. One One cus customer tomer repo report rtss that that their their dat databas abase e is not not resp respon ondi ding ng as quick quickly ly as the the servi service ce level level agreements dictate. You observe that the database is fragmented. You need to opmize query performance. Soluon: You rebuild all indexes. Does the soluon meet the goal? A. Yes B. No

Answer: A Explanaon: You can remedy index fragmentaon by either reorganizing an index or by rebuilding an index. References: hps://msdn.microso.com/en-us/library/ms189858(v=sql.105).aspx

Queson: 9 Note: This queson queson is part of a series of quesons quesons that present present the same scenario. scenario. Each queson in the series contains a unique soluon that might meet the stated goals. Some queson sets might have more than one correct soluon, while others might not have a correct soluon. Aer you answer a queson in this secon, you will NOT be able to return to it. As a result, these quesons will not appear in the review screen. A company has an on-premises Microso SQL Server environment and Microso Azure SQL Database instances. The environment hosts several customer databases. One One cus customer tomer repo report rtss that that their their dat databas abase e is not not resp respon ondi ding ng as quick quickly ly as the the servi service ce level level agreements dictate. You observe that the database is fragmented. You need to opmize query performance. Soluon: You run the DBCC CHECKDB command. Does the soluon meet the goal? A. Yes B. No
















Page 7

Explanaon: DBCC CHECKDB CHECKDB only only checks checks the logical logical and physic physical al integri integrity ty of all the the object objectss in the specied specied database. It does not update any indexes, and does not improve query performance. References: hps://docs.microso.com/en-us/sql/t-sql/database-console-command hps://docs.microso.com/en-us/ sql/t-sql/database-console-commands/dbcc-checkdbs/dbcc-checkdb-transact-sql transact-sql

Queson: 10 Note: This queson is part of a series of quesons that use the same or similar answer choices. An answer choice may be correct for more than one queson in the series. Each queson is independent of the other quesons in this series. Informaon and details provided in a queson apply only to that queson. You are the database administrator for a company that hosts Microso SQL Server. You manage both on-premises and Microso Azure SQL Database environments. One instance hosts a user database named HRDB. The database contains sensive human resources data. You need to grant an auditor permission to view the SQL Server audit logs while following the principle of least privilege. Which permission should you grant? A. DDLAdmin B. db_datawriter C. dbcreator D. dbo E. View Database State F. View Server State G. View Denion H. sysadmin

Answer: F Explanaon: Unless otherwise specied, viewing catalog views requires a principal to have one of the following: Membership in the sysadmin xed server role. The CONTROL SERVER permission. The VIEW SERVER STATE permission.














Page 8

queson. You manage a Microso SQL Server environment. You implement Transparent Data Encrypon (TDE). A user will assist in managing TDE. You need to ensure that the user can view the TDE metadata while following the principle of lease privilege. Which permission should you grant? A. DDLAdmin B. db_datawriter C. dbcreator D. dbo E. View Database State F. View Server State G. View Denion H. sysadmin

Answer: G Explanaon: Viewing the metadata involved with TDE requires the VIEW DEFINITION permissionon the cercate. References: hps://docs.microso.com/en-us/sql/relaonal-databases/sec hps://docs.microso.com/en-us/ sql/relaonal-databases/security/encrypon/transpar urity/encrypon/transparent-dataent-dataencrypon-tde

Queson: 12 Note: This queson is part of a series of quesons that use the same or similar answer choices. An answer choice may be correct for more than one queson in the series. Each queson is independent of the other quesons in this series. Informaon and details provided in a queson apply only to that queson. You are the database administrator for a company that hosts Microso SQL Server. You manage both on-premises and Microso Azure SQL Database environments. You have a user database named HRDB that contains sensive human resources data. The HRDB backup les must be encrypted. You need to grant the correct permission to the service account that backs up the HRDB database. Which permission should you grant?














Page 9

Explanaon: Restoring the encrypted backup: SQL Server restore does not require any encrypon parameters to be speci specied ed during during rest restor ores. es. It does does requi require re that that the cercate cercate or the asymmet asymmetric ric key key used used to encrypt the backup le be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the cercate or key. References: hps://docs.microso.com/en-us/sql/relaonal-databases/backu hps://docs.microso.com/en-us/ sql/relaonal-databases/backup-resto p-restore/backup-encrypo re/backup-encrypon n

Queson: 13 Note: This queson is part of a series of quesons that use the same or similar answer choices. An answer choice may be correct for more than one queson in the series. Each queson is independent of the other quesons in this series. Informaon and details provided in a queson apply only to that queson. You are the database administrator for a company that hosts Microso SQL Server. You manage both on-premises and Microso Azure SQL Database environments. You plan to delegate encrypon operaons to a user. You need need to grant grant the user user permiss permission ion to impleme implement nt cell-le cell-level vel encryp encrypon on while while follow following ing the principle of least privilege. Which permission should you grant? A. DDLAdmin B. db_datawriter C. dbcreator D. dbo E. View Database State F. View Vi ew ServerState G. View Denion H. sysadmin

Answer: G Explanaon: The The follo followin wing g permi permiss ssion ionss are are nece necess ssar ary y to perf perfor orm m colum columnn-le leve vell encr encryp ypo on, n, or cellcell-lev level el encrypon. CONTROL permission on the database.














Page 10

answer choice may be correct for more than one queson in the series. Each queson is independent of the other quesons in this series. Informaon and details provided in a queson apply only to that queson. A company has an on-premises Microso SQL Server environment and Microso Azure SQL Database instances. The environment hosts a customer database named DB1. Customers connect to hosted database instances by using line-of-business applicaons. Developers connect by using SQL Server Management Studio (SSMS). You need to grant the developers permission to alter views for DB1 while following the principle of least privilege. Which permission should you grant? A. DDLAdmin B. db_datawriter C. dbcreator D. dbo E. View Database State F. View Server State G. View Denion H. sysadmin

Answer: A Explanaon: To execute ALTER VIEW, at a minimum, ALTER permission on OBJECT is required. Member Memberss of the db_ddladm db_ddladmin in xed xed databa database se role role can run any Data Data Deni Denion on Languag Language e (DDL) (DDL) command in a database. References: hps://technet.microso.com/en-us/library/ms190667(v=sql.90).aspx

Queson: 15 Note: This queson is part of a series of quesons that use the same or similar answer choices. An answer choice may be correct for more than one queson in the series. Each queson is independent of the other quesons in this series. Informaon and details provided in a queson apply only to that queson. You are the database administrator for a company that hosts Microso SQL Server. You manage both














Page 11

F. View Server State G. View Denion H. sysadmin

Answer: C Explanaon: Members of the dbcreator xed server role can create, alter, drop, and restore any database. References: hps://docs.microso.com/en-us/sql/relaonal-databases/sec hps://docs.microso.com/en-us/ sql/relaonal-databases/security/authencaon-access/ urity/authencaon-access/serverserverlevel-roles

Queson: 16 Note: This queson is part of a series of quesons that use the same or similar answer choices. An answer choice may be correct for more than one queson in the series. Each queson is independent of the other quesons in this series. Informaon and details provided in a queson apply only to that queson. You are examining informaon about users, sessions, and processed in an on-premises Microso SQL Server Database Engine instance. You need to return informaon about processes that are not idle, that belong to a specic user, or that belong to a specic session. What should you use? A. Acvity Monitor B. sp_who3 C. SQL Server Management Studio (SSMS) Object Explorer D. SQL Server Data Collector E. SQL Server Data Tools (SSDT) F. SQL Server Conguraon Manager

Answer: B Explanaon: Use sp_who3 to rst view the current system load and to idenfy a session of interest. You should
















Page 12

Note: This queson is part of a series of quesons that use the same or similar answer choices. An answer choice may be correct for more than one queson in the series. Each queson is independent of the other quesons in this series. Informaon and details provided in a queson apply only to that queson. You observe that several indexes are fragmented. You need to rebuild the indexes. What should you use? A. Acvity Monitor B. Sp_who3 stored procedure C. Object Explorer in the SQL Server Management Studio (SSMS) D. SQL Server Data Collector E. SQL Server Data Tools (SSDT) F. SQL Server Conguraon Manager

Answer: C Explanaon: How to: Rebuild an Index (SQL Server Management Studio) To rebuild an index In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases, expand the database that contains the table with the specied index, and then expand Tables. Expand the table in which the index belongs and then expand Indexes. Right-click the index to rebuild and then click Rebuild. To start the rebuild operaon, click OK.














Page 13

Answer: A Explanaon: Acvity Monitor displays informaon about SQL Server processes and how these processes aect the current instance of SQL Server. Server. Acvity Monitor is a tabbed document window with the following expandable and collapsible panes: Overview, Acve User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries. The Acvity User Tasks Pane shows informaon for acve user connecons to the instance, including the following column: * Login: The SQL Server login name under which the session is currently execung. The Recent Expensive Queries Pane shows informaon about the most expensive queries that have been run on the instance over the last 30 seconds, including the following column: * CPU (ms/sec): The rate of CPU use by the query References: hps://technet.microso.com/en-us/library/cc879320(v=sql.105).aspx

Queson: 19 Note: This queson is part of a series of quesons that use the same or similar answer choices. An answer choice may be correct for more than one queson in the series. Each queson is independent of the other quesons in this series. Informaon and details provided in a queson apply only to that queson. You collect performance metrics on mulple Microso SQL Server instances and store the data in a single repository. repository. You need need to examin examine e disk disk usage, usage, query query stas stascs cs,, and server server acvity acvity withou withoutt buildin building g custom custom counters.














Page 14

of the other quesons in this series. Informaon and details provided in a queson apply only to that queson. You are examining informaon about users, sessions, and processes in an on-premises Microso SQL Server 2016 Standard Edion server. server. You need to idenfy waits for resources and return only the following informaon: a list of all databases on the SQL Server instance, along with informaon about the database les, their paths, and names a list of the queries recently executed that use most of memory, disk, and network resources What should you use? A. Acvity Monitor B. Sp_who3 C. SQL Server Management Studio (SSMS) Object Explorer D. SQL Server Data Collector E. SQL Server Data Tools (SSDT) F. SQL Server Conguraon Manager

Answer: E Explanaon: SQL Server Server Data Data Tools ools (SSDT) (SSDT) is a Micro Microso so Visual Visual Studio Studio envir environm onment ent for for crea creang ng busine business ss intelligence soluons. SSDT features the Report Designer authoring environment, where you can open, modify, preview, save, and deploy Reporng Services paginated report denions, shared data sources, shared datasets, and report parts. References: hps://msdn.microso.com/en-us/library/hh272686(v=vs.103).aspx














Page 15

Data Collector can gather performance informaon from mulple SQL Server instances and store it in a single repository. It has three built-in data collecng specicaons (data collectors) designed to collect the most important performance metrics. The informaon collected by default is about disk usage, query stascs, and server acvity. The Query Stascs data collecon set collects informaon about query stascs, acvity, execuon plans and text on the SQL Server instance. Missing indexes can be found with the execuon plans. References: hps://www.sqlshack.com/sql-server-performance-monitoring-data-collector/

Queson: 22 HOTSPOT Note: This queson is part of a series of quesons that use the same scenario. For your convenience, the scenario scenario is repea repeated ted in each each ques queson. on. Each Each ques queson on prese presents nts a dier dierent ent goal and answer answer choices, but the text of the scenario is exactly the same in each queson in this series. You are a database administrator for a company that has an on-premises Microso SQL Server environment and Microso Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are shown in the following table.














Page 16
















Page 17

Answer:














Page 18














Page 19














Page 20

For For databa databases ses that that use full and bulk-lo bulk-logged gged recov recovery ery,, databa database se backu backups ps are are necess necessary ary but not sucient. Transacon log backups are also required. Note: Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any me. References: hps://docs.microso.com/en-us/sql/relaonal-databases/backu hps://docs.microso.com/en-us/ sql/relaonal-databases/backup-resto p-restore/recovery-models-sqlre/recovery-models-sqlserver

Queson: 24 HOTSPOT Note: This queson is part of a series of quesons that use the same scenario. For your convenience, the scenario scenario is repea repeated ted in each each ques queson. on. Each Each ques queson on prese presents nts a dier dierent ent goal and answer answer choices, but the text of the scenario is exactly the same in each queson in this series. You are a database administrator for a company that has an on-premises Microso SQL Server environment and Microso Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are














Page 21
















Page 22














Page 23














Page 24

hps://docs.microso.com/en-us/sql/relaonal-databases/security/auding/create-a-server-auditand-database-audit-specicaon

Queson: 25 HOTSPOT Note: This queson is part of a series of quesons that use the same scenario. For your convenience, the scenario scenario is repea repeated ted in each each ques queson. on. Each Each ques queson on prese presents nts a dier dierent ent goal and answer answer














Page 25














Page 26
















Page 27














Page 28

Microsoft 70-764 Microsoft Certified Professional

Recommend Documents