Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 231:

  You maintain two Microsoft SQL Server instances that are hosted on two servers named SVR1 and SVR2. You configure log shipping using the following information:

  DB1 on SVR1 is configured as the primary database Logs are backed up to a shared folder on SVR1

  DB2 on SVR2 is configured as the secondary database

  The service accounts for SQL Server Agent are set up using different domain accounts.

  After changes are made to SVR1, log shipping is failing with the following error message:

  

  You need to resolve the issue. What should you do?

  A. Create a new proxy account named PRX1 on SVR2. Grant PRX1 read access to the log copy destination folder on SVR2. Then, configure the copy job to run as PRX1.
  B. Configure the log backup shared folder to ensure the SQL Server Agent service account on SVR2 has read access.
  C. Create a new proxy account named PRX1 on SVR1. Grant PRX1 write access to the log backup shared folder. Then, configure the backup job to run as PRX1.
  D. Configure the log backup shared folder to ensure the SQL Server Agent service account in SVR1 has read access.
  C. Create a new proxy account named PRX1 on SVR1. Grant PRX1 write access to the log backup shared folder. Then, configure the backup job to run as PRX1.

• Question 232:

  HOTSPOT

  You manage a Microsoft SQL Server environment. You have a database named salesOrders that includes a table named Table1.

  Table1 becomes corrupt. You repair the table.

  You need to verify that all the data in Table1 complies with the schema.

  How should you complete the Transact-SQL code statement? To answer, select the appropriate Transact-SQL code segments in the dialog box in the answer area.

  Hot Area:

  

  

  ---

  Box 1: CHECKCONSTRAINTS DBCC CHECKCONSTRAINTS checks the integrity of a specified constraint or all constraints on a specified table in the current database. Box 2: ALL_CONSTRAINTS ALL_CONSTRAINTS 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. Note: Syntax: DBCC CHECKCONSTRAINTS [ ( table_name | table_id | constraint_name | constraint_id ) ] [ WITH [ { ALL_CONSTRAINTS | ALL_ERRORMSGS } ] [ , ] [NO_INFOMSGS ] ]

  References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcccheckconstraints-transact-sql

• Question 233:

  You use SQL Azure to store data used by an e-commerce application.

  You develop a stored procedure named sp1.

  Sp1 is used to read and change the price of all the products sold on the e-commerce site.

  You need to ensure that other transactions are blocked from updating product data while sp1 is executing.

  Which transaction isolation level should you use in sp1?

  A. read committed
  B. repeatable read
  C. snapshot
  D. serializable
  D. serializable

• Question 234:

  You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.

  You create a user account in the msdb database for the AgentPortalUser login.

  You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.

  You need to ensure that AgentPortalUser can send email messages.

  What should you do?

  A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.
  B. Disable the guest user in the msdb database.
  C. Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile.
  D. In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile.
  A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.

• Question 235:

  You administer a SQL Server 2016 database instance.

  You need to configure the SQL Server Database Engine service on a failover cluster.

  Which user account should you use?

  A. A domain user
  B. The BUILTIN\SYSTEM account
  C. A local user with Run as Service permissions
  D. The SQLBrowser account
  A. A domain user

• Question 236:

  DRAG DROP

  You store Microsoft SQL Server backups at an off-site location that has no protection from unauthorized access. Your organization has not implemented hardware security modules (HSM).

  You need to implement a solution for encrypting the SQL backups.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Step 1: Create a database master key of the master database.

  Step 2: Create a certificate for the backups.

  Create a Backup Certificate: Create a backup certificate in the master database.

  Step 3:

  Backup the database: Specify the encryption algorithm and certificate to use.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/backuprestore/create-an-encrypted-backup?view=sql-server-2017

• Question 237:

  DRAG DROP

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer

  choices, but the text of the scenario is exactly the same in each question in this series.

  You maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table

  shows the configuration of each database.

  

  The backup strategies for each database are described in the following table.

  

  Each full or differential backup operation writes into a new file and uses a different sequence number. You observe the following database corruption issues.

  

  SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages:

  database name

  impacted file id

  impacted file physical name

  impacted page id

  event type that identifies the error type

  error count

  Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must

  meet the following requirements:

  Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).

  Capture queries based on resource consumption.

  Use a stale query threshold value of 60 days.

  The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan

  should only update statistics that were automatically created and have not been updated for 30 days. The update should be based on all data in the table.

  You need to write the query the maintenance plan will use to update the statistics.

  Which four 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:

  

  

• Question 238:

  General Overview

  You are the Senior Database Administrator (DBA) for a software development company named Leafield Solutions. The company develops software applications custom designed to meet customer requirements.

  Requirements Leafield Solutions has been asked by a customer to develop a web-based Enterprise Resource Planning and Management application. The new application will eventually replace a desktop application that the customer is

  currently using. The current application will remain in use while the users are trained to use the new webbased application.

  You need to design the SQL Server and database infrastructure for the web-based application.

  Databases

  You plan to implement databases named Customers, Sales, Products, Current_Inventory, and TempReporting.

  The Sales database contains a table named OrderTotals and a table named SalesInfo.

  A stored procedure named SPUpdateSalesInfo reads data in the OrderTotals table and modifies data in the SalesInfo table.

  The stored procedure then reads data in the OrderTotals table a second time and makes further changes to the information in the SalesInfo table. The Current_Inventory database contains a large table named Inv_Current. The Inv_Current

  table has a clustered index for the primary key and a nonclustered index. The primary key column uses the identity property. The data in the Inv_Current table is over 120GB in size. The tables in the Current_Inventory database are accessed

  by multiple queries in the Sales database. Another table in the Current_Inventory database contains a self-join with an unlimited number of hierarchies. This table is modified by a stored procedure named SPUpdate2. An external application

  named ExternalApp1 will periodically query the Current_Inventory database to generate statistical information. The TempReporting database contains a single table named GenInfo. A stored procedure named SPUPdateGenInfo combines

  data from multiple databases and generates millions of rows of data in the GenInfo table.

  The GenInfo table is used for reports.

  When the information in GenInfo is generated, a reporting process reads data from the Inv_Current table and queries information in the GenInfo table based on that data. The GenInfo table is deleted after the reporting process completes. The

  Products database contains tables named ProductNames and ProductTypes.

  Current System

  The current desktop application uses data stored in a SQL Server 2005 database named DesABCopAppDB. This database will remain online and data from the Current_Inventory database will be copied to it as soon as data is changed in the

  Current_Inventory database.

  SQL Servers

  A new SQL Server 2012 instance will be deployed to host the databases for the new system. The databases will be hosted on a Storage Area Network (SAN) that provides highly available storage.

  Design Requirements

  Your SQL Server infrastructure and database design must meet the following requirements:

  Confidential information in the Current_ Inventory database that is accessed by ExternalApp1 must be securely stored.

  Direct access to database tables by developers or applications must be denied.

  The account used to generate reports must have restrictions on the hours when it is allowed to make a connection.

  Deadlocks must be analyzed with the use of Deadlock Graphs.

  In the event of a SQL Server failure, the databases must remain available.

  Software licensing and database storage costs must be minimized.

  Development effort must be minimized.

  The Tempdb databases must be monitored for insufficient free space.

  Failed authentication requests must be logged.

  Every time a new row is added to the ProductTypes table in the Products database, a user defined function that validates the row must be called before the row is added to the table.

  When SPUpdateSalesInfo queries data in the OrderTotals table the first time, the same rows must be returned along with any newly added rows when SPUpdateSalesInfo queries data in the OrderTotals table the second time.

  You need to meet the design requirement for the ProductTypes table in the Product database. Which of the following would be the best solution?

  A. A PRIMARY KEY constraint.
  B. A CHECK constraint.
  C. A UNIQUE constraint.
  D. A Data Definitions Language (DDL) trigger.
  E. A FOREIGN KEY constraint.
  B. A CHECK constraint.

• Question 239:

  Overview

  General Overview

  ADatum Corporation has offices in Miami and Montreal.

  The network contains a single Active Directory forest named adatum.com. The offices connect to each other by using a WAN link that has 5-ms latency. A. Datum standardizes its database platform by using SQL Server 2014 Enterprise

  edition.

  Databases

  Each office contains databases named Sales, Inventory, Customers, Products, Personnel, and Dev.

  Servers and databases are managed by a team of database administrators. Currently, all of the database administrators have the same level of permissions on all of the servers and all of the databases.

  The Customers database contains two tables named Customers and Classifications.

  The following graphic shows the relevant portions of the tables:

  

  The following table shows the current data in the Classifications table:

  

  The Inventory database is updated frequently.

  The database is often used for reporting.

  A full backup of the database currently takes three hours to complete.

  Stored Procedures

  A stored procedure named USP_1 generates millions of rows of data for multiple reports. USP_1 combines data from five different tables from the Sales and Customers databases in a table named Table1. After Table1 is created, the

  reporting process reads data from Table1 sequentially several times. After the process is complete, Table1 is deleted. A stored procedure named USP_2 is used to generate a product list. The product list contains the names of products

  grouped by category.

  USP_2 takes several minutes to run due to locks on the tables the procedure accesses. The locks are caused by USP_1 and USP_3.

  A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction. Currently, if one of the UPDATE statements fails, the stored procedure fails. A

  stored procedure named USP_4 calls stored procedures in the Sales, Customers, and Inventory databases.

  The nested stored procedures read tables from the Sales, Customers, and Inventory databases. USP_4 uses an EXECUTE AS clause.

  All nested stored procedures handle errors by using structured exception handling. A stored procedure named USP_5 calls several stored procedures in the same database. Security checks are performed each time USP_5 calls a stored

  procedure.

  You suspect that the security checks are slowing down the performance of USP_5. All stored procedures accessed by user applications call nested stored procedures.

  The nested stored procedures are never called directly.

  Design Requirements

  Data Recovery

  You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Time Objective (RTO) of 5 minutes.

  You must be able to recover data from the Dev database if data is lost accidentally. You have a Recovery Point Objective (RPO) of one day.

  Classification Changes

  You plan to change the way customers are classified. The new classifications will have four levels based on the number of orders. Classifications may be removed or added in the future. Management requests that historical data be

  maintained for the previous classifications. Security A group of junior database administrators must be able to manage security for the Sales database. The junior database administrators will not have any other administrative rights. A. Datum

  wants to track which users run each stored procedure.

  Storage

  ADatum has limited storage. Whenever possible, all storage space should be minimized for all databases and all backups.

  Error Handling

  There is currently no error handling code in any stored procedure.

  You plan to log errors in called stored procedures and nested stored procedures. Nested stored procedures are never called directly.

  You need to recommend a solution to minimize the amount of time it takes to execute USP_2. What should you recommend?

  A. A database snapshot
  B. A table variable
  C. A temporary table
  D. Snapshot isolation
  C. A temporary table

• Question 240:

  You have a database named DB1 that is configured to use the full recovery model. You have a full daily backup job that runs at 02:00. The job backs up data from DB1 to the file B:\DB1.bak.

  You need to restore the DB1 database to the point in time of May 25, 2016 at 02:23 and ensure that the database is functional and starts to accept connections.

  Which Transact-SQL statement should you run?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B