Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 321:

  You are a database administrator at Contoso, Ltd. You are preparing to move a global sales application from a development environment to a production environment. You have a database named Contoso that has a schema named Sales. All objects in the database have the same owner. The schema has a large number of views and stored procedures. None of the stored procedures perform IDENTITY_INSERT operations or dynamic SQL commands. You create all views by using the WITH SCHEMABINDING option.

  All employees in the sales division are members of an Active Directory Domain Services (AD DS) security group named Contoso\Sales.

  The following database objects are the only objects queried when a user from Contoso\Sales is using the application.

  Views SalesReport SalesInvoice Stored procedures InvoiceExecute -performs read/write operations InvoiceSearch -performs read-only operations

  When granting permissions to the database, you should use the principle of least privilege.

  You create a new user-defined database role named SalesRole and add Contoso\Sales as a member of SalesRole.

  You need to grant all employees in the Sales division permission to use the views and stored procedures.

  Which two solutions will meet the requirements? Each correct answer presents a complete solution.

  A. Grant the SELECT permission on the Sales schema to SalesRole. Grant the EXECUTE permission on the Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.
  B. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to Contoro\Sales. Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to Contoso\Sales.
  C. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to SalesRole. Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.
  D. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to SalesRole. Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole. Grant the SELECT permission on all tables referenced by Sales.SalesReport, Sales.SalesInvoice, and Sales.InvoiceSearch to SalesRole. Grant the SELECT, INSERT, UPDATE, and DELETE permissions on all tables referenced by Sales.InvoiceExecute to SalesRole.
  B. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to Contoro\Sales. Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to Contoso\Sales.
  C. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to SalesRole. Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.

• Question 322:

  You administer a Microsoft SQL Server 2016 instance.

  You discover that the SQL Agent Error Log file is rapidly growing in size.

  You need to ensure that the SQL Agent Error Log file does not grow rapidly when SQL Server agent jobs execute.

  What should you do?

  A. Execute the sp_cycle_agent_errorlog stored procedure.
  B. Configure event forwarding.
  C. Enable the Auto Shrink option on the master database.
  D. Enable the Auto Shrink option on the msdb database.
  E. Disable the Include execution trace messages feature.
  E. Disable the Include execution trace messages feature.

• Question 323:

  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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You attempt to restore a database on a new SQL Server instance and receive the following error message:

  "Msg 33111, Level 16, State 3, Line 2

  Cannot find server certificate with thumbprint `0x7315277C70764B1F252DC7A5101F6F66EFB1069D'."

  You need to ensure that you can restore the database successfully.

  Solution: You disable BitLocker Drive Encryption (BitLocker) on the drive that contains the database backup.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 324:

  Overview

  Application Overview

  Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.

  Contoso is designing a new version of the ERP application. The previous version of the ERP application used SQL Server 2008 R2.

  The new version will use SQL Server 2014.

  The ERP application relies on an import process to load supplier data. The import process updates thousands of rows simultaneously, requires exclusive access to the database, and runs daily. You receive several support calls reporting

  unexpected behavior in the ERP application. After analyzing the calls, you conclude that users made changes directly to the tables in the database.

  Tables

  The current database schema contains a table named OrderDetails.

  The OrderDetails table contains information about the items sold for each purchase order. OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase order. The product price is stored in a table named

  Products. The Products table was defined by using the SQL_Latin1_General_CP1_CI_AS collation.

  A column named ProductName was created by using the varchar data type. The database contains a table named Orders.

  Orders contains all of the purchase orders from the last 12 months. Purchase orders that are older than 12 months are stored in a table named OrdersOld.

  The previous version of the ERP application relied on table-level security.

  Stored Procedures

  The current version of the database contains stored procedures that change two tables. The following shows the relevant portions of the two stored procedures:

  

  Customer Problems

  Installation Issues

  The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most customers set up the ERP application in multiple locations and must create logins multiple times.

  Index Fragmentation Issues

  Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the

  clustered index key:

  

  Backup Issues

  Customers who have large amounts of historical purchase order data report that backup time is unacceptable.

  Search Issues

  Users report that when they search product names, the search results exclude product names that contain accents, unless the search string includes the accent.

  Missing Data Issues

  Customers report that when they make a price change in the Products table, they cannot retrieve the price that the item was sold for in previous orders.

  Query Performance Issues

  Customers report that query performance degrades very quickly. Additionally, the customers report that users cannot run queries when SQL Server runs maintenance tasks. Import Issues During the monthly import process, database

  administrators receive many supports call from users who report that they cannot access the supplier data. The database administrators want to reduce the amount of time required to import the data.

  Design Requirements

  File Storage Requirements

  The ERP database stores scanned documents that are larger than 2 MB. These files must only be accessed through the ERP application. File access must have the best possible read and write performance.

  Data Recovery Requirements

  If the import process fails, the database must be returned to its prior state immediately.

  Security Requirements

  You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.

  Concurrency Requirements

  You must reduce the likelihood of deadlocks occurring when Sales.Prod and Sales.Proc2 execute.

  You need to recommend a solution that addresses the file storage requirements.

  What should you include in the recommendation?

  A. FileStream
  B. FileTable
  C. The varbinary data type
  D. The image data type
  B. FileTable

• Question 325:

  You administer a Microsoft SQL Server 2016 database.

  The database contains a Product table created by using the following definition:

  

  You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

  A. Convert all indexes to Column Store indexes.
  B. Implement Unicode Compression.
  C. Implement row-level compression.
  D. Implement page-level compression.
  D. Implement page-level compression.

• Question 326:

  You need to recommend a solution to meet the recovery requirements for the manufacturing database.

  Your solution must minimize costs.

  What should you recommend?

  A. Database snapshots
  B. Transaction log backups
  C. Differential backups
  D. SQL Server Failover Clustering
  E. Peer-to-peer replication
  A. Database snapshots

• Question 327:

  DRAG DROP

  You are the senior database administrator at Contoso, Ltd. You manage a SQL Server 2016 Instance, with multiple databases used for reporting.

  You have recently hired a junior database administrator. You want this person to be able to view the database structures on the server, but you do not want him or her to be able to make changes or see the data in the tables.

  The new hire's login credentials are as follows:

  Login name: JFree

  Password: Jx672$qse

  You want the new hire to be required to change his password on his next login.

  The code that is produced should execute no matter the initial database context in which it is started.

  You need to write the code required to give the new hire only the desired access, using the smallest number of steps. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the

  code blocks.

  Select and Place:

  

  

• Question 328:

  You plan to migrate the db to azure.

  You verify that all objects are valid for azure sql database. You need to ensure that users and logins are migrated to azure.

  What should you do?

  A. Use the Copy Database wizard
  B. Use the Database Transfer wizard
  C. Use the SQL Management Studio to deploy the db to azure
  D. Back up the databasae from the local server and restore it to azure
  C. Use the SQL Management Studio to deploy the db to azure
  D. Back up the databasae from the local server and restore it to azure

• Question 329:

  Overview

  You are a database administrator for a company named Litware, Inc.

  Litware is a book publishing house. Litware has a main office and a branch office.

  You are designing the database infrastructure to support a new web-based application that is being developed.

  The web application will be accessed at www.litwareinc.com. Both internal employees and external partners will use the application.

  You have an existing desktop application that uses a SQL Server 2008 database named App1_DB.

  App1_DB will remain in production.

  Requirements

  Planned Changes

  You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.

  All database files will be stored in a highly available SAN.

  Database1 will contain two tables named Orders and OrderDetails.

  Database1 will also contain a stored procedure named usp_UpdateOrderDetails.

  The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes. The rows returned from the first query must be returned on the second query unchanged

  along with any rows added to the table between the two read operations.

  Database1 will contain several queries that access data in the Database2 tables.

  Database2 will contain a table named Inventory.

  Inventory will contain over 100 GB of data.

  The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index.

  The column that is used as the primary key will use the identity property.

  Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does

  not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes

  occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.

  Business Requirements

  You have the following requirements:

  Costs for new licenses must be minimized.

  Private information that is accessed by Application must be stored in a secure format.

  Development effort must be minimized whenever possible.

  The storage requirements for databases must be minimized.

  System administrators must be able to run real-time reports on disk usage.

  The databases must be available if the SQL Server service fails.

  Database administrators must receive a detailed report that contains allocation errors and data corruption.

  Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.

  You must encrypt the backup files to meet regulatory compliance requirements.

  The encryption strategy must minimize changes to the databases and to the applications.

  You need to recommend a solution for the deployment of SQL Server 2014. The solution must meet the business requirements. What should you include in the recommendation?

  A. Create a new instance of SQL Server 2014 on the server that hosts the SQL Server 2008 instance.
  B. Upgrade the existing SQL Server 2008 instance to SQL Server 2014.
  C. Deploy two servers that have SQL Server 2014 installed and implement Failover Clustering.
  D. Deploy two servers that have SQL Server 2014 installed and implement database mirroring.
  C. Deploy two servers that have SQL Server 2014 installed and implement Failover Clustering.

• Question 330:

  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.

  A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts a customer database named DB1.

  Customers connect to hosted database instances by using line-of-business applications. 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 Definition
  H. sysadmin
  A. DDLAdmin