Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 301:

  You administer a Microsoft SQL Server 2016 database named Contoso on a server named Server01.

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

  You also need to ensure minimum impact to the server.

  What should you create?

  A. An Alert
  B. A Resource Pool
  C. An Extended Event session
  D. A Server Audit Specification
  E. A SQL Profiler Trace
  F. A Database Audit Specification G. A Policy
  C. An Extended Event session

• Question 302:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  Your company is developing a new business intelligence application that will access data in a Microsoft Azure SQL Database instance. All objects in the instance have the same owner.

  A new security principal named BI_User requires permission to run stored procedures in the database. The stored procedures read from and write to tables in the database. None of the stored procedures perform IDENTIFY_INSERT

  operations or dynamic SQL commands.

  The scope of permissions and authentication of BI_User should be limited to the database. When granting permissions, you should use the principle of least privilege.

  You need to create the required security principals and grant the appropriate permissions.

  Solution: You run the following Transact-SQL statement in the master database:

  CREATE LOGIN BI_User WITH PASSWORD = 'Pa$$w鴕d'

  You run the following Transact-SQL statement in the business intelligence database:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 303:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  A company has a server that runs Microsoft SQL Server 2016 Web edition. The server has a default instance that hosts a database named DB1.

  You need to ensure that you can perform auditing at the database level for DB1.

  Solution: You migrate DB1 to the default instance on a server that runs Microsoft SQL Server 2016 Standard edition.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 304:

  You configure log shipping on two servers named SQL1 and SQL2 is in Standby mode.

  You configure backup and restore to occur every hour.

  A user generates a report from SQL2 and discovers that the report data is out-of-date.

  You verify that the report data is a day old and discover that the primary backup files are available only on SQL1.

  You need to identify what prevents the primary backup files from being available on SQL2.

  Which two actions should you perform? Each correct answer presents a complete solution.

  NOTE: Each correct selection is worth one point.

  A. From SQL2, execute the SP_log_shipping_monitor_secondary system stored procedure.
  B. From Microsoft SQL Server Management Studio on SQL1, run the Transaction Log Shipping Status report.
  C. From Microsoft SQL Server Management Studio on SQL2, run the Transaction Log Shipping Status report.
  D. From SQL1, review the SQL Server Agent job history log.
  E. From SQL1, execute the SP_log_shipping_monitor_secondary system stored procedure.
  A. From SQL2, execute the SP_log_shipping_monitor_secondary system stored procedure.
  B. From Microsoft SQL Server Management Studio on SQL1, run the Transaction Log Shipping Status report.

• Question 305:

  You work as a Database Administrator (DBA) for a company named ABC.com.

  The company uses a Microsoft SQL Server 2012 infrastructure.

  A server named ABC-SQL1 hosts multiple databases that are accessed using OLTP (Online Transaction Processing) applications.

  You are concerned about the lack of redundancy provided by this single server solution.

  You want to configure a solution that ensures high-availability for the databases while minimizing costs.

  You need to ensure that the databases remain online in the event of a hardware failure of ABCSQL1.

  You configure a second server named ABC-SQL2 and install SQL Server 2012.

  Which of the following solutions would provide the high-availability and enable users to run reports on both copies of the databases?

  A. AlwaysOn availability groups
  B. A failover cluster
  C. Database mirroring
  D. Transaction log shipping
  A. AlwaysOn availability groups

• Question 306:

  DRAG DROP

  You are maintaining a Microsoft SQL Server database named DB1. The database uses the dbo schema. Tables in the database were created by running the following Transact-SQL statements:

  

  The Sales table has a column named Managerid. This column is used to assign the manager for a sales transaction. You have read access to the Employees table. Other employees have read and write access to the Sales table but no

  access to the Employees table.

  You need to implement row-level security (RLS) for the Sales table. The solution must meet the following requirements:

  Managers must only read and modify sales records that are assigned to them.

  Managers cannot assign sales data to another manager.

  Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-security-policy-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/functions/database-principal-id-transact-sql?view=sql-server-2017

• Question 307:

  You need to grant access to an OLTP database regardless of the user operating the application. Which strategy should you use?

  A. Application role
  B. Database user
  C. Server login
  D. Server role
  A. Application role

• Question 308:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series.

  Information and details provided in a question apply only to that question.

  You are the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments. A service account has the required permissions to backup all databases.

  You have a user database named HRDB that contains sensitive human resources data. The HRDB backup files must be encrypted.

  You need to grant the service account that backs up the HRDB database the permission necessary to access the encryption key. Your solution must use 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 ServerState
  G. View Definition
  H. sysadmin
  G. View Definition

• Question 309:

  HOTSPOT

  You manage Microsoft SQL Server databases for an organization.

  You need to configure the databases to meet the following requirements:

  Encrypt the data at rest.

  Ensure that unencrypted values for specific columns can only be viewed by using a decryption key.

  Ensure that decrypted columns are only accessible by using database views.

  Which actions should you perform? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  To protect all databases use a certificate in the master database Note: TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. The database encryption key can also be protected using a certificate, which is protected by the database master key of the master database. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. Box 2: Configure DDM Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. DDM can be configured on the database to hide sensitive data in the result sets of queries over designated database fields, while the data in the database is not changed.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/enable-tde-on-sql-server-using-ekm?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

• Question 310:

  You work as a Database Administrator (DBA) for a company named ABC.com.

  The company uses a Microsoft SQL Server 2012 infrastructure.

  You have a database named CorpDB. CorpDB contains 2TB of data.

  You plan to import a large amount of data into tables in CorpDB.

  You want to minimize the size of the transaction log while the data is imported.

  What should you do?

  A. You should configure the recovery model of the database to Full.
  B. You should configure the recovery model of the database to Bulk-Logged.
  C. You should start a new transaction log file.
  D. You should configure a new filegroup for the existing log file.
  B. You should configure the recovery model of the database to Bulk-Logged.