Microsoft 70-764 Study Guides 2021

NEW QUESTION 1
You manage a Microsoft SQL Server instance that has a database named DB1. The instance has a server audit named Audit1. DB1 uses the following schemas:
You need to implement a database audit specification for database DB1 that meets the following requirements:
Audit only delete statements performed by users in the db_datawriter role.
Audit insert statements for all tables in Schema1 for all users.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

NEW QUESTION 2
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 has several Microsoft SOI Server instances. Each instance hosts many databases. You observe I/O corruption on some of the instances.
You need to perform the following actions:
• Identify databases where the PAGE VERIFY option is not set.
• Configure full page protection for the identified databases. Solution: You run the following Transact-SQL statement:

For each database that you identify, you run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. NO

Answer: B

NEW QUESTION 3
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.
SQL Server backups should be stored as Microsoft Azure blob pages. The connection process from the SQL Server instances to Azure should be encrypted.
You need to store backups as Azure blob pages. Which option should you use?

• A. backup compression
• B. backup encryption
• C. file snapshot backup
• D. mirrored backup media sets
• E. SQL Server backup to URL
• F. SQL Server Managed Backup to Azure
• G. tail-log backup
• H. back up and truncate the transaction log

Answer: F

Explanation: SQL Server Managed Backup to Microsoft Azure manages and automates SQL Server backups to Microsoft Azure Blob storage. You can choose to allow SQL Server to determine the backup schedule based on the transaction workload of your database. Or you can use advanced options to define a schedule. The retention settings determine how long the backups are stored in Azure Blob storage.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-managed-backup-to-microso

NEW QUESTION 4
You have a server named SQL1 that has SQL Server 2012 installed. SQL1 hosts a database named Database1.
Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the Date field. The schema of Table1 is configured as shown in the following table.

Table1 contains the indexes shown in the following table.

You need to recommend an index strategy to maximize performance for the queries that consume the indexes available to Table1.
Which type of index storage should you recommend? To answer, drag the appropriate index storage type to the correct index in the answer area.

Answer:

Explanation: Index Storage Type
Designing a partitioned index independently (unaligned) of the base table can be useful in the following cases:
- The base table has not been partitioned.
- The index key is unique and it does not contain the partitioning column of the table.
- You want the base table to participate in collocated joins with more tables using different join columns.

NEW QUESTION 5
Your company has a SQL Azure subscription.
You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2. A user named User1 informs you that he is unable to run sp1.
You verify that User1 has the SELECT permission on Table1 and Table2. You need to ensure that User1 can run sp1.
The solution must minimize the number of permissions assigned to User1. What should you do?

• A. Grant User1 the INSERT permission on Table2.
• B. Add User1 to the db_datawriter role.
• C. Change sp1 to run as the sa user.
• D. Grant User1 the EXECUTE permission on sp1.

Answer: D

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms191291.aspx

NEW QUESTION 6
Contoso. Ltd. has Microsoft SQL Server databases that support a custom application. The current SQL Server environment consists of two servers: ContosoSQL1 and ComosoSQL2. These two servers participate in an Always On Availability Group named ContosoAG1 that is configured to use synchronous-commit with automatic failover. The secondary replica is not configured for read-only access.
The application performs both transactional processing and historical data retrieval in a database named ContosoDB. The application includes an inventory management module. The inventory management module and database have experienced performance issues.
Users report mat a Query named inventoryQuery1 takes a long time to complete. The query is shown as follows:
SELECT ProductNumber, Name, ProductLine FROM Production.Product
WHERE ProductNumber = N’ <specific product>’
The query plan used by SQL Server for this query is shown in the exhibit.

Various Performance issues, including frequent long-term blocking episodes, prevent business users from completing their daily tasks. You suspect the tempdb database resources could be responsible. You must create blocking reports for the contosoDB database to identify issues.
You plan to use Extended Events to review all Transact SQL statements that are run against the ContosoSQL1 instance. The output from the Extended Events session must contain both start and stop events and must be written to a file. You must configure the Extended Events session to minimize possible data loss and reduce the effect on server performance.
You plan to deploy an additional secondary replica named contosoSQL3 to ContosoAG1. Ready-only traffic must be load-balanced between the two secondary replicas, regardless of which instance is the primary replica. Contoso plans to add an additional dedicated reporting system that will reply on real-time data from the transactional database.
The company plans to improve their high availability/disaster recovery (HA/DR) solution. As part of the planned improvements, you will back up all databases from ContosoSQL1 directory to an off-site location.
You need to create the blocking reports.
Which three actions should you perform in sequence?

Answer:

Explanation:

NEW QUESTION 7
You administer a Microsoft SQL Server 2021 database instance.
You create a new user named UserA. You need to ensure that UserA is able to create SQL Server Agent jobs and to execute SQL Server Agent jobs.
To which role should you add UserA?

• A. Securityadmin
• B. RSExecRole
• C. SQLAgentUserRole
• D. DatabaseMailUserRole

Answer: C

NEW QUESTION 8
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

Answer: C

Explanation: Scenario:
A stored procedure named USP_2 is used to generate a product list.
USP_2 takes several minutes to run due to locks on the tables the procedure accesses.

NEW QUESTION 9
You have a SQL Server instance on a server named Server1. You need to recommend a solution to perform the following tasks every week:
Rebuild the indexes by using a new fill factor.
Run a custom T-SQL command.
Back up the databases.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

• A. A trigger
• B. An alert
• C. A maintenance plan
• D. Windows PowerShell
• E. A system policy

Answer: C

Explanation: Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.

NEW QUESTION 10
You define a table by using the following statement.

The Teachers table contains one million records. A sample record of the XML data output is as follows:

You plan to create queries against the table based on grade.
You need to recommend which indexes should be applied to the XML fields. The solution must meet the following requirements:
Reduce the amount of time required to query the table based on grade.
Minimize the size of the index.
What should you recommend? To answer, drag the appropriate index types to the correct fields. Each index type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content

Answer:

Explanation: Grade – Secondary XML index; Id – Primary XML index; Subject – No index

NEW QUESTION 11
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 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 meets the following requirements: Modifications to the application to support the new architecture are not permitted. What should you implement?

• A. a Microsoft Azure Stretch Database
• B. log shipping
• C. an Always On Availability Group with all replicas in synchronous-commit mode
• D. a file share witness
• E. a SQL Server failover cluster instance (FCI)
• F. a Windows Cluster with a shared-nothing architecture
• G. an Always On Availability group with secondary replicas in asynchronous-commit mode

Answer: C

Explanation: Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on

NEW QUESTION 12
You work as a Database Administrator (DBA) for a company named ABC.com. The company uses a Microsoft SQL Server 2012 infrastructure.
The sales and marketing departments contain a total of 60 users. Each user uses a custom application that stores data in a SQL Server database.
Each user has a separate database.
A server named ABC-SQL1 hosts the 60 databases.
You need to configure a backup solution for all the databases.
The solution must ensure that any new databases configured on ABC-SQL1 are automatically added to the backup schedule. The backup schedule consists of a full backup every night, a differential backup every hour and a transaction log backup every 15 minutes.
How should you configure the backup solution?

• A. You should configure SQL Server Agent jobs.
• B. You should configure Change Data Capture.
• C. You should configure Policy-Based Management.
• D. You should configure a Maintenance Plan.

Answer: D

NEW QUESTION 13
You administer a Windows Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory.
Users need to be able to SELECT from all tables in the database and execute the stored procedure. You need to grant only the necessary permissions.
What should you do?

• A. Grant EXECUTE permission on p_AddInventory to all user
• B. Grant VIEW DEFINITION to all users.
• C. Grant EXECUTE permission on p_AddInventory to all user
• D. Add all users to the db_datawriter role.
• E. Add all users to the db_owner role.
• F. Grant EXECUTE permission on p_Add!nventory to all user
• G. Add all users to the db_datareader role.

Answer: D

NEW QUESTION 14
You need to provide a group of users from the IT and Manufacturing departments the minimum administrative rights to view database information and server state for the Manufacturing database on MainDB1.
What should you do?

• A. You should configure a Database Role.
• B. You should configure a Server Role.
• C. You should configure a Shared SQL Server Login.
• D. You should configure a Local Security Group.

Answer: B

NEW QUESTION 15
You are deploying a Microsoft Azure SQL Database environment:
You need to configure the environment to meet the following requirements:
Access to databases must be logged.
Email alerts must be sent to the database administrator if anomalous activity is detected.
What should you do? To answer, drag the appropriate solutions to the correct requirements. Each solution may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

Explanation: Box 1: Enable database auditing Box 2: Enable threat detection
SQL Database Threat Detection runs multiple sets of algorithms which detect potential vulnerabilities and SQL injection attacks, as well as anomalous database access patterns (such as access from an unusual location or by an unfamiliar principal). Security officers or other designated administrators get email notification once a threat is detected on the database. Each notification provides details of the suspicious activity and recommends how to further investigate and mitigate the threat.
References:

NEW QUESTION 16
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 security requirement. What should you recommend?

• A. Revoke user permissions on the table
• B. Create stored procedures that manipulate dat
• C. Grant the users the EXECUTE permission on the stored procedures.
• D. Grant the users the SELECT permission on the table
• E. Create views that retrieve data from the tables.Grant the users the SELECT permission on the views.
• F. Deny the users SELECT permission on the table
• G. Create views that retrieve data from the table
• H. Grant the users the SELECT permission on the views.
• I. Deny the users the SELECT permission on the table
• J. Create stored procedures that manipulate data.Grant the users the EXECUTE permission on the stored procedures.

Answer: C

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