Microsoft 70-764 Study Guides 2021

NEW QUESTION 1
Which feature should you enable and configure so session requests addressed to a specific instance can be allocated to different processor resources based on session request properties?

• A. Resource Governor
• B. Windows System Resource Manager
• C. Processor affinity
• D. I/O affinity

Answer: C

NEW QUESTION 2
You are planning the deployment of two new Always On Failover Cluster Instances (FCIs) of Microsoft SQL Server to a single Windows Server Cluster with three nodes. The planned configuration for the cluster is shown in the Server Layout exhibit. (Click the Exhibit button.)

The SAN team has configured storage for the cluster and sent the configuration to you in the email shown in the SAN Team Email exhibit. (Click the Exhibit button.)

Each node of the cluster has identical local storage available as shown in the Local Storage exhibit. (Click the Exhibit button.)

All local storage is on SSD.
You need to plan specific configurations for the new cluster.
For each of the following statement, select Yes if the statement is true. Otherwise, select No.

Answer:

Explanation: Box 1: Yes
tempdb on local storage. FCIs now support placement of tempdb on local non-shared storage, such as a local solid-state-drive, potentially offloading a significant amount of I/O from a shared SAN.
Prior to SQL Server 2012, FCIs required tempdb to be located on a symmetrical shared storage volume that failed over with other system databases.
Box 2: No
The VNN is set on the group level, not on the instance level.
Database client applications can connect directly to a SQL Server instance network name, or they may connect to a virtual network name (VNN) that is bound to an availability group listener. The VNN abstracts the WSFC cluster and availability group topology, logically redirecting connection requests to the appropriate SQL Server instance and database replica.
The logical topology of a representative AlwaysOn solution is illustrated in this diagram:

References:
http://download.microsoft.com/download/d/2/0/d20e1c5f-72ea-4505-9f26-fef9550efd44/microsoft%20sql%20se

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 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 have a Microsoft Azure SQL database that has Blob Auditing configured. You need to review the audit logs.
Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.

Does this meet the goal?

• A. YES
• B. NO

Answer: A

Explanation: fn_get_audit_file returns information from an audit file created by a server audit in SQL Server.
This example reads from a file that is named ShiraServer/MayaDB/SqlDbAuditing_Audit/2021-07-14/10_45_22_173_1.xel:
SELECT * FROM sys.fn_get_audit_file ('https://mystorage.blob.core.windows.net/sqldbauditlogs/ShiraServer/MayaDB/SqlDbAuditing_Audit/2021-07-
Note: Blob auditing logs are saved as a collection of blob files within a container named sqldbauditlogs. References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

NEW QUESTION 4
You have a table that has grown in the past six months.
A user reports that queries against the table take a long time to complete.
You need to update the statistics for the table in the least amount of time without disabling automatic statistics updates.
Which Transact-SQL statement should you run?

• A. UPDATE STATISTICS WITH RESAMPLE
• B. UPDATE STATISTICS WITH FULLSCAN
• C. UPDATE STATISTICS WITH SAMPLE 10 PERCENT
• D. UPDATE STATISTICS WITH NORECOMPUTE

Answer: C

Explanation: SAMPLE number { PERCENT | ROWS } specifies the approximate percentage or number of rows in the table or indexed view for the query optimizer to use when it updates statistics.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2021

NEW QUESTION 5
You administer a Microsoft SQL Server 2021 database.
Users report that an application that accesses the database displays an error, but the error does not provide meaningful information.
No entries are found in the SQL Server log or Windows event logs related to the error. You need to identify the root cause of the issue by retrieving the error message.
What should you do?

• A. Create an Extended Events session by using the sqlserver.error_reported event.
• B. Create a SQL Profiler session to capture all ErrorLog and EventLog events.
• C. Flag all stored procedures for recompilation by using sp_recompile.
• D. Execute sp_who.

Answer: A

Explanation: Trapping SQL Server Errors with Extended Events
One very useful usage of Extended Events is the ability to trap SQL Server error without the need to have a server trace running (which, btw, is deprecated), with the additional feature of being able to query the data as soon as it comes in. This means that we a solution to monitor and trap errors as soon as they happen can be easily created, in order to help developers to fix problems as soon as they are detected. This is really, really, really helpful especially in very big applications, where the code base is quite old and there is no-one really knowing everything of the solution.
To start a Extended Events sessions in order to trap SQL Server errors with severity greater than 10, just run the following script:
CREATE EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported Etc.
References:
http://sqlblog.com/blogs/davide_mauri/archive/2013/03/17/trapping-sql-server-errors-with-extended-events.aspx

NEW QUESTION 6
You have a database named DB1.
DB1 contains a table named Sales.SalesPerson that has an index named AK_SalesPerson_rowguid. Queries that do not use the index take approximately 10 times longer to complete than queries that use the
index. You discover that AK_SalesPerson_rowguid has severe fragmentation. You need to recommend a solution to defragment the index.
The solution must ensure that the index can be used by queries during the defragmentation.
What statement should you use? To answer, drag the appropriate elements to the correct locations. Each element 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: Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.
Use: ALTER INDEX REBUILD WITH (ONLINE = ON)
References: Reorganize and Rebuild Indexes https://msdn.microsoft.com/en-us/library/ms189858.aspx

NEW QUESTION 7
You administer a Microsoft SQL Server 2021 instance that has several SQL Server Agent jobs configured. SQL Server Agent jobs fail, the error messages returned by the job steps are truncated.
The following error message is an example of the truncated error message:
"Executed as user CONTOSOServiceAccount. ...0.4035.00 for 64-bit Copyright (C) Microsoft Corp
1984-2011. All rights reserved. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code 0x00000000 Source Log Import Activity Descript... The package execution fa... The step failed."
You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs. What should you do?

• A. Expand agent logging to include information from all events.
• B. Disable the Limit size of job history log feature.
• C. Configure event forwarding.
• D. Configure output files.

Answer: D

Explanation: When you have a multiple-step job, then log all steps against a single file. Check the 'Append output to existing file' checkbox for all steps in the job that execute after the initial step. This results in a log file with all of the job steps from the last job execution. Each time the first step executes (each time the job is kicked-off) the file will be overwritten, so we have a record of the last set of output.
References: https://www.mssqltips.com/sqlservertip/1411/verbose-sql-server-agent-logging/

NEW QUESTION 8
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 have a database that includes a table named Candidate.
You need to update the statistics for a column named Skills in the table and turn off automatic statistics updates for the column.
Solution: You run the following query:

Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: RESAMPLE: Update each statistic using its most recent sample rate.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql

NEW QUESTION 9
You administer two instances of Microsoft SQL Server 2021.
You deploy an application that uses a database on the named instance.
The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?

• A. Use the Data Quality Client to configure the application.
• B. Start the SQL Server Browser Service.
• C. Use the Master Data Services Configuration Manager to configure the application.
• D. Start the SQL Server Integration Services Service.

Answer: B

Explanation: The SQL ServerBrowser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:
Browsing a list of available servers Connecting to the correct server instance Etc.
References: https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service

NEW QUESTION 10
You manage a Microsoft SQL Server that has a database named salesOrders. Users connect to the database by using a client application.
Users report that the application cannot connect to the database. You observe that the database storage has experienced a failure.
You need to repair the database and ensure that applications can connect to the database.
Which three action 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.

Answer:

Explanation: The specified database must be in single-user mode to use one of the following repair options. REPAIR_REBUILD
Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

NEW QUESTION 11
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
A customer that uses an on-premises instance reports that queries take a long time to complete.
You need to reconfigure table statistics so that the query optimizer can use the optimal query execution plans available.
Which Transact-SQL segment should you use?

• A. sys.index_columns
• B. UPDATE STATISTICS
• C. CREATE STATISTICS
• D. SET AUTO_CREATE_STATISTICS ON

Answer: D

Explanation: AUTO_UPDATE_STATISTICS { ON | OFF }
ON specifies that the query optimizer updates statistics when they are used by a query and when they might be out-of-date. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-20

NEW QUESTION 12
You plan to install two SQL Server 2014 environments named Environment1 and Environment2. Your company identifies the following availability requirements for each environment:
Environment1 must have mirroring with automatic failover implemented.
Environment2 must have Always On with automatic failover implemented.
You need to identify the minimum number of SQL Server 2014 servers that must be deployed to each environment to ensure that all data remains available if a physical server fails.
How many servers should you identify? To answer, drag the appropriate number to the correct environment in the answer area.

Answer:

Explanation: Environment1 – 3; Environment2 - 2

NEW QUESTION 13
You manage Microsoft SQL Server databases for an organization.
You need to configure the databases to meet the following requirements:
Encrypt the data at rest.
Ensure that unencrypted values for specific columns can only be viewed by using a decryption key.
Ensure that decrypted columns are only accessible by using database views.
Which actions should you perform? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: Box 1: Configure TDE…in the master database
To protect all databases use a certificate in the master database
Note: TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. The database encryption key can also be protected using a certificate, which is protected by the database master key of the master database.
The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module.
Box 2: Configure DDM
Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. DDM can be configured on the database to hide sensitive data in the result sets of queries over designated database fields, while the data in the database is not changed.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/enable-tde-on-sql-server-using-ekm https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

NEW QUESTION 14
You administer a Microsoft SQL Server 2021 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day.
The reporting database is only updated from the transactional database.
The recovery model and backup schedule are configured as shown in the following table:

At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database.
You need to ensure that the databases are restored.
You also need to ensure that data loss is minimal. What should you do?

• A. Perform a partial restore.
• B. Restore the latest full backup, and restore the latest differential backu
• C. Then, restore each log backup taken before the time of failure from the most recent differential backup.
• D. Restore the latest full backup.
• E. Restore the latest full backup, and restore the latest differential backu
• F. Then, restore the latest log backup.
• G. Perform a page restore.
• H. Restore the latest full backu
• I. Then, restore each differential backup taken before the time of failure from the most recent full backup.
• J. Perform a point-in-time restore.
• K. Restore the latest full backu
• L. Then, restore the latest differential backup.

Answer: H

Explanation: At restore time, before you restore a differential backup, you must restore its base. Then, restore only the most recent differential backup to bring the database forward to the time when that differential backup was created. Typically, you would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.
References: https://technet.microsoft.com/en-us/library/ms345448(v=sql.105).aspx

NEW QUESTION 15
You plan to install a Microsoft SQL Server 2021 instance.
The instance will support a database that has the following requirements:
Store Excel workbooks on the file system.
Access the workbooks through Transact-SQL.
Include the workbooks in database backups.
During installation, you need to ensure that the requirements will be met.
Which feature should you use?

• A. Excel Services
• B. FILESTREAM
• C. SQL Server Integration Services (SSIS)
• D. OpenXML

Answer: B

NEW QUESTION 16
You are designing a database for a university.
The database will contain two tables named Classes and Enrollment that have the following specifications:
Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths.
Enrollment will store information about students and their classes. Performance must be enhanced for queries of the current enrollments.
You need to identify which SQL Server technology meets the specifications of each table. Which technologies should you identify? To answer, drag the appropriate technology to the correct table in the answer area.

Answer:

Explanation: The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services including full-text search and semantic search-over unstructured data and metadata. In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.