Microsoft 70-764 Study Guides 2021

NEW QUESTION 1
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 2021 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 a named instance on a server that runs Microsoft SQL Server 2021 Enterprise edition.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2021 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

NEW QUESTION 2
You are troubleshooting an application that runs a query. The application frequently causes deadlocks. You need to identify which transaction causes the deadlock.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

• A. Query the sys.dm_exec_requests dynamic management view.
• B. Create a trace in SQL Server Profiler that contains the Deadlock graph event.
• C. Query the sys.dm_exec_sessions dynamic management view.
• D. Create an extended events session to capture deadlock information.

Answer: D

Explanation: Troubleshooting deadlocks
You have been receiving reports from users indicating that certain applications are returning deadlock errors. To maximize the effectiveness of troubleshooting these problems, you decide to focus on the deadlocks that are hit most frequently. You create an Extended Events session that:
Configures deadlock event tracking for the session.
Specifies a target that aggregates based on an identifier for the deadlock.
You run the Extended Events session, and after additional deadlocks are reported you are able to obtain aggregated deadlock information, along with the complete XML deadlock graph for each source. Using this information, you are able to pin point the most common deadlocks and start working on a solution.

NEW QUESTION 3
You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database.
You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?

• A. User mapped to a certificate
• B. SQL user without login
• C. Domain user
• D. SQL user with login

Answer: C

NEW QUESTION 4
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 feature to support your backup solution. What should you include in the recommendation?

• A. Transparent Data Encryption (TDE)
• B. Column-level encryption
• C. An NTFS file permission
• D. A Secure Sockets Layer (SSL)

Answer: A

Explanation: - Scenario: You must encrypt the backup files to meet regulatory compliance requirements. The encryption strategy must minimize changes to the databases and to the applications.
- Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery.
Transparent Data Encryption (TDE)

NEW QUESTION 5
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 changes to the ERP application to resolve the search issue.
The solution must minimize the impact on other queries generated from the ERP application. What should you recommend changing?

• A. The collation of the Products table
• B. The index on the ProductName column
• C. The collation of the ProductName column
• D. The data type of the ProductName column

Answer: C

Explanation: References:
http://technet.microsoft.com/en-us/library/aa214408(v=sql.80).aspx

NEW QUESTION 6
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 collect performance metrics on multiple Microsoft SQL Server instances and store the data in a single repository.
You need to examine disk usage, query statistics, and server activity without building custom counters.
What should you use?

• A. Activity Monitor
• B. Sp_who3 stored procedure
• C. Object Explorer in the Microsoft SQL Server Management Studio (SSMS)
• D. SQL Server Data Collector
• E. SQL Server Data Tools (SSDT)
• F. SQL Server Configuration Manager

Answer: D

Explanation: The data collector is a core component of the data collection platform for SQL Server 2021 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data

NEW QUESTION 7
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.
Start of repeated scenario
You are a database administrator for a company that has an on-premises Microsoft SQL Server environment There are two domains in separate forests. There are no trust relationships between the domains. The environment hosts several customer databases, and each customer uses a dedicated instance running SQL Server 2021 Standard edition. The customer environments are shown in the following table.

End of repeated scenario
You need to configure the Contoso instance.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point

Answer:

Explanation:

NEW QUESTION 8
You have configured Resource Governor with three resource pools.
You have assigned the first resource pool a minimum CPU and memory value of 20%. You have assigned the second resource pool a minimum CPU and memory value of 30%. You want to assign maximum CPU and memory values to the third resource pool.
What is the maximum CPU and memory value you can assign to this resource pool?

• A. 30%
• B. 50%
• C. 70%
• D. 100%

Answer: B

Explanation: The maximum resource value assigned to the third pool is 100%; the sum of the minimum resource values assigned to the other pools is 50%.

NEW QUESTION 9
You manage a database that uses the FULL recovery model and has a 60-minute recovery point objective (RPO). Full database backups are performed at 23:00 Monday through Friday.
Each full database backup takes 6 hours to complete. Approximately 5% of the data changes each weekday. Backups must be performed as often as possible to reduce the number of files needed either a full recovery to
any given time. You must minimize the duration of the backup tasks that are performed between 04:00 and
23:00.
You need to reconfigure backups.
Which time slot or schedule should you use for each backup type?

Answer:

Explanation:

NEW QUESTION 10
You maintain three datacenters in different geographical regions. You have a four-node failover cluster that hosts a Microsoft SQL Server Failover Cluster Instance (FCI) in DatacenteM.
You must extend FCI to add four nodes in Datacenter2.
You need to configure a node majority quorum mode for the cluster.
What are the two possible ways to achieve the goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

• A. Use a cloud quorum witness that is hosted in Microsoft Azure.
• B. Use a disk witness that is hosted in Datacenter1.
• C. Use a disk witness that is hosted in Datacenter2.
• D. Use no witness.
• E. Use a file share witness that is hosted in Datacenter3.

Answer: BD

NEW QUESTION 11
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.
You have a database named DB1 that is 640 GB and is updated frequently.
You enable log shipping for DB1 and configure backup and restore to occur every 30 minutes. You discover that the hard disks on the database server are almost full.
You need to reduce the amount of disk space used by the log shipping process. Solution: You reduce the frequency of the transaction log backups to once per hour. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: You should compress the transaction log backups. References:
https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server?view=sql-

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.
You are configuring a highly-available database solution using an AlwaysOn availability group on two servers running SQL Server 2012. The two servers are in separate datacenters.
The two datacenters are connected by a WAN link with a network latency of more than 200ms. Which of the following failover types should you configure for the availability group?

• A. You should configure the asynchronous manual failover failover type.
• B. You should configure the synchronous manual failover failover type.
• C. You should configure the synchronous automatic failover failover type.
• D. You should configure the Asynchronous automatic failover failover type.

Answer: A

NEW QUESTION 13
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.
You have a database named DB1 that contains a table named Table1. You need to audit all updates to TablID.
Solution: You create a trace on a local disk that captures all the update statements. Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 14
You use SQL Server 2014.
You need to create a single object that inserts a provided value into Table1, and then returns a count of the records in Table1.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.

Answer:

Explanation:

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

Answer: B

Explanation: - Scenario: 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.
- FileTables remove a significant barrier to the use of SQL Server for the storage and management of unstructured data that is currently residing as files on file servers.
Enterprises can move this data from file servers into FileTables to take advantage of integrated administration and services provided by SQL Server. At the same time, they can maintain Windows application compatibility for their existing Windows applications that see this data as files in the file system.

NEW QUESTION 16
You have the following Microsoft SQL Server instances. The instances are members of a cluster:

Server1 has 42 gigabytes (GB) of memory and server 2 has 4g of memory. The operating system and other processes on each.......... server required 4 GB memory.
Instance does not failover successfully to Server1. You need to resolve the issue.
What should you do?

• A. Set the value of the Maximum server memory option for Instance to 24576.
• B. Set the value of the Maximum server memory option for Instance2 to 32760.
• C. Set the value of the Minimum server memory option for instance 2 to 0.
• D. Minimum server memory server memory option for Instances 1 to 16384

Answer: B