Microsoft 70-764 Exam Questions 2021

NEW QUESTION 1
You view the Restore Transaction Log settings for a database named DB1 as shown in the following exhibit.

Use the drop down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: Log shipping secondary database can be kept in two restore modes: Restoring - This mode is also known as NORECOVERY mode. Standby - This mode is also known as Read-Only mode.
When you use the NORECOVERY mode, the database will be in a restoring state and inaccessible to users, so uncommitted database transactions are not an issue.
When you use the STANDBY mode, database will be in Read-Only state and users can access this database for read operations. Transactions in process on primary server or any uncommitted transaction cannot be read in secondary database.
References:
https://www.mssqltips.com/sqlservertip/3600/change-the-restore-mode-of-a-secondary-sql-server-database-in-lo

NEW QUESTION 2
You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica.
You have multiple queries that read data and produce reports from the database.
You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. What should you do?

• A. Set the Availability Mode property of HA/Server02 to Asynchronous commit.
• B. Set the Readable Secondary property of HA/Server02 to Read-intent only.
• C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections.
• D. Set the Availability Mode property of HA/Server01 to Asynchronous commit.

Answer: B

Explanation: References: https://msdn.microsoft.com/en-us/library/jj542414.aspx

NEW QUESTION 3
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 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 enabled log shipping for DB1 and configure backup and restore to occur every 30 minutes.
You discover that the disks on the data server are almost full.
You need to reduce the amount of disk space used by the log shipping process. Solution: You configure log shipping to backup and restore by using shared folder. Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 4
You administer a Microsoft SQL Server 2021 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented.
You need to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

• A. CREATE INDEX NCI_OrderDetail_CustomerIDON OrderDetail.CustomerID WITH DROP EXISTING
• B. ALTER INDEX NCI_OrderDetail_CustomerID ONOrderDetail.CustomerID REORGANIZE
• C. ALTER INDEX ALL ON OrderDetail REBUILD
• D. ALTER INDEX NCI_OrderDetail_CustomerID ONOrderDetail.CustomerID REBUILD

Answer: B

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

NEW QUESTION 5
You have a SQL Server 2012 database named DB1. You have a backup device named Device1.
You discover that the log file for the database is full. You need to ensure that DB1 can complete transactions.
The solution must not affect the chain of log sequence numbers (LSNs). Which code segment should you execute?

• A. BACKUP LCG DB1 TO Device1 WITH COPY_ONLY
• B. BACKUP LOG DB1 TO Device1
• C. BACKUP LOG DB1 TO Device1 WITH NCRECCVERY
• D. BACKUP LOG D31 TO Device1 WITH TRUNCATE ONLY

Answer: B

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms186865.aspx http://msdn.microsoft.com/en-us/library/ms179478.aspx http://msdn.microsoft.com/en-us/library/ms190925.aspx

NEW QUESTION 6
You maintain a Microsoft SQL Server database named DB1. You create a maintenance plan to run the DBCC CHECKDB command regularly to identify possible database corruption.
DB1 has the following table:

Filegroup FG1 is read-only. You set FG1 to read-only and create a full backup.
The maintenance plan does not identify any errors after the backup. However, you observe the following error in the logs.
Msg 8979, Level 16, State 1, Line 1 Table error: Object ID 885578193, index ID 3, partition ID***, alloc unit ID *** (type In-row data). Page (***) is missing a reference from previous page (***). Possible chain linkage problem.
You need to resolve the database corruption issues. What should you do?

• A. Restore FG1 from the most recent backup.
• B. Drop and rebuild index ID 3 in Table1.
• C. Change FG1 from read-only to read/write.
• D. Update the statistics on index ID 3 in Table1.
• E. Backup the tail-logs in DB1.

Answer: A

Explanation: A full restore of the database is the safest bet in case of a chain linkage problem.
References: http://sqlsoldier.net/wp/sqlserver/introductiontointegritychainlinkageproblems

NEW QUESTION 7
You plan to deploy SQL Server 2014. Your company identifies the following monitoring requirements:
Tempdb must be monitored for insufficient free space.
Deadlocks must be analyzed by using Deadlock graphs.
You need to identify which feature meets each monitoring requirement.
Which features should you identify? To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.

Answer:

Explanation: You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space.
Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.
SQL Server Profiler and SQL Server Management Studio use a deadlock wait-for graph to describe a deadlock. The deadlock wait-for graph contains process nodes, resource nodes, and edges representing the relationships between the processes and the resources. References: Troubleshooting Insufficient Disk Space in tempdb
References: Analyze Deadlocks with SQL Server Profiler

NEW QUESTION 8
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 manage a Microsoft SQL Server environment. You implement Transparent Data Encryption (TDE). A user will assist in managing TDE.
You need to ensure that the user can view the TDE metadata while following the principle of lease 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

Answer: G

Explanation: Viewing the metadata involved with TDE requires the VIEW DEFINITION permissionon the certificate. References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-tde

NEW QUESTION 9
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 resolves the missing data issue.
The solution must minimize the amount of development effort. What should you recommend?

• A. Denormalize the Products table.
• B. Denormalize the OrderDetails table.
• C. Normalize the OrderDetails table.
• D. Normalize the Products table.

Answer: D

Explanation: - Scenario:
- 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.
- 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.

NEW QUESTION 10
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 an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
One customer reports that their database is not responding as quickly as the service level agreements dictate. You observe that the database is fragmented.
You need to optimize query performance. Solution: You rebuild all indexes.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: You can remedy index fragmentation by either reorganizing an index or by rebuilding an index. References: https://msdn.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

NEW QUESTION 11
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 have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.

Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \SQLBackup. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.

Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.
Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table.

You need to create the connection strings for the operations and reporting systems.
In the table below, identify the option that must be specified in each connection string. NOTE: Make only one selection in each column.

Answer:

Explanation: Reporting system: Connect to any current read-only replica instance
We configure Read-OnlyAccess on an Availability Replica. We select Read-intent only. Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
From Scenario: Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
Operating system: Connect to the current primary replica SQL instance
By default, both read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas of an Always On availability group.
From scenario: Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-o

NEW QUESTION 12
You administer a Microsoft SQL Server 2021 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.

Answer: D

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 support an application that stores data in a Microsoft SQL Server database. You have a query that returns data for a report that users run frequently.
The query optimizer sometimes generates a poorly-performing plan for the query when certain parameters are used. You observe that this is due to the distribution of data within a specific table that the query uses.
You need to ensure that the query optimizer always uses the query plan that you prefer. Solution: You add the KEEPFIXED PLAN query hint to the query.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: KEEPFIXED PLAN forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.
References: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2021

NEW QUESTION 14
You manage a Microsoft SQL Server environment. You plan to encrypt data when you create backups. You need to configure the encryption options for backups.
What should you configure?

• A. a certificate
• B. an MD5 hash
• C. a DES key
• D. an AES 256-bit key

Answer: D

Explanation: To encrypt during backup, you must specify an encryption algorithm, and an encryptor to secure the encryption key. The following are the supported encryption options:
Encryption Algorithm: The supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES
Encryptor: A certificate or asymmetric Key
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption

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

Answer: B

Explanation: This is a certificate problem. The problem is not related to Bitlocker.
References: https://www.sqlservercentral.com/Forums/Topic1609923-3411-1.aspx

NEW QUESTION 16
You administer a single server that contains a Microsoft SQL Server 2021 default instance on which several production databases have been deployed.
You plan to install a new ticketing application that requires the deployment of a database on the server. The SQL login for this application requires sysadmin permissions. You need to ensure that the login for the
ticketing application cannot access other production databases.
What should you do?

• A. Use the SQL Server default instance and enable Contained Databases.
• B. Use the SQL Server default instance and configure a user-defined server rol
• C. Add the login for the ticketing application to this role.
• D. Install a new named SQL Server instance on the server.
• E. Install a new default SQL Server instance on the server.

Answer: C

Explanation: SQL Server supports multiple instances of SQL Server on a single server or processor, but only one instance can be the default instance. All others must be named instances. A computer can run multiple instances of SQL Server concurrently, and each instance runs independently of other instances.
References: https://msdn.microsoft.com/en-us/library/ms143531(v=SQL.105).aspx