Tested 70-764 Braindumps 2021

NEW QUESTION 1
You deploy a database by using SQL Server 2014. The database contains a table named Table1.
You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required.
What should you recommend?

• A. Change data capture
• B. Statistics
• C. A trigger
• D. Master Data Services

Answer: A

Explanation: Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

NEW QUESTION 2
You administer a Microsoft SQL Server 2021 database named Contoso on a server named Server01. You need to be notified immediately when fatal errors occur on Server01.
What should you create?

• A. an Alert
• B. a Server Audit Specification
• C. an Extended Event session
• D. a Resource Pool
• E. a Policy
• F. a SQL Profiler Trace
• G. a Database Audit Specification

Answer: A

Explanation: References:
http://www.sqlskills.com/blogs/glenn/creating-sql-server-agent-alerts-for-critical-errors/

NEW QUESTION 3
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 meet the security requirements of the junior database administrators. What should you include in the recommendation?

• A. A server role
• B. A database role
• C. A credential
• D. A shared login

Answer: C

Explanation: - Scenario: A group of junior database administrators must be able to view the server state of the SQL Server instance that hosts the Sales database. The junior database administrators will not have any other administrative rights.
- Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server. Credentials can also be used when a SQL Server Authentication user needs access to a domain resource, such as a file location to store a backup.

NEW QUESTION 4
You work as a Database Administrator (DBA) for a company named ABC.com. The company uses a Microsoft SQL Server 2012 infrastructure.
You have a database named CorpDB. CorpDB contains a table named SalesInfo.
You discover that some table has been deleted from the SalesInfo table. You are unable to find out who deleted the information.
You need to implement a solution to monitor the deletion of any further information from the SalesInfo table. You want to minimize the development effort required for the solution.
What should you configure?

• A. You should configure table permissions.
• B. You should configure a user role.
• C. You should configure change data capture.
• D. You should configure a trigger.

Answer: C

NEW QUESTION 5
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 reorganize 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 6
You are the new database administrator for a SQL Server 2014 instance.
You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off.
You see no evidence that any maintenance has been occurring. You want to set up monitoring to see if query performance is being affected. You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed.
What should you do?

• A. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key columns in the database.
• B. Use the missing_column_statistics extended event.
• C. Query the sys.statistics system view to see all cases where the statistics were last needed.
• D. Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical.

Answer: B

Explanation: The Missing Column Statistics event class indicates that column statistics that could have been useful for the optimizer are not available. By monitoring the Missing Column Statistics event class, you can determine if there are statistics missing for a column used by a query.
This can cause the optimizer to choose a less efficient query plan than expected.

NEW QUESTION 7
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 8
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 identify the issue with tempdb. What should you do?

• A. Monitor for high PADELATCH-related waits on the entire instance.
• B. Monitor for low values of the Av
• C. Disk Sec/Read perfmon counter on the disk where tempdb is stored.
• D. Monitor for excessive PAGELATCH_UP and PAGELATCH_10 waits in tempdb.
• E. Use Query Store to observe query performance within tempdb.

Answer: A

NEW QUESTION 9
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 the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.
You have a user database named HRDB that contains sensitive human resources data. The HRDB backup files must be encrypted.
You need to grant the correct permission to the service account that backs up the HRDB database. 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: Restoring the encrypted backup: SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption

NEW QUESTION 10
You need to recommend a backup process for an Online Transaction Processing (OLTP) database. The process must meet the following requirements:
Ensure that if a hardware failure occurs, you can bring the database online with a minimum amount of data loss.
Minimize the amount of administrative effort required to restore any lost data.
What should you include in the recommendation? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation:

NEW QUESTION 11
You administer a Microsoft SQL Server 2021 failover cluster.
You need to ensure that a failover occurs when the server diagnostics returns query_processing error. Which server configuration property should you set?

• A. SqlOumperDumpFlags
• B. FailureConditionLevel
• C. HealthCheckTimeout
• D. SqlDumperDumpPath

Answer: B

Explanation: The SQL Server Database Engine resource DLL determines whether the detected health status is a condition for failure using the FailureConditionLevel property. The FailureConditionLevel property defines which detected health statuses cause restarts or failovers. Multiple levels of options are available, ranging from no automatic restart or failover to all possible failure conditions resulting in an automatic restart or failover.
References:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/failover-policy-for-failover-cluster-ins

NEW QUESTION 12
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 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 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: B

Explanation: The fn_get_audit_file, not dm_db_audit_file, the 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 14
You plan to install Microsoft SQL Server 2021 for a web hosting company.
The company plans to host multiple web sites, each supported by a SQL Server database.
You need to select an edition of SQL Server that features backup compression of databases, basic data integration features, and low total cost of ownership.
Which edition should you choose?

• A. Express Edition with Tools
• B. Standard Edition
• C. Web Edition
• D. Express Edition with Advanced Services

Answer: B

Explanation: Backup compression is supported on SQL Server 2021 editions: Enterprise, Standard, and Developer. References: https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2021

NEW QUESTION 15
You need to address the requirements for disc usage monitoring for the SQL Servers. What should you do?

• A. You should configure disc quotas.
• B. You should configure a Dynamic Management View.
• C. You should configure alerts sent by the SQL Server Agent.
• D. You should configure a SQL Server Maintenance Plan.

Answer: B

NEW QUESTION 16
You administer a Microsoft SQL Server 2021 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do?

• A. Execute sp_configure 'max log size', 2G.
• B. use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter.
• C. In SQL Server Management Studio, right-click the instance and select Database Setting
• D. Set the maximum size of the file for the transaction log.
• E. in SQL Server Management Studio, right-click the database, select Properties, and then click Files.Open the Transaction log Autogrowth window and set the maximum size of the file.

Answer: D