Microsoft 70-764 Exam Dumps 2021

NEW QUESTION 1
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 disk monitoring solution that meets the business requirements. What should you include in the recommendation?

• A. a SQL Server Agent alert
• B. a dynamic management view
• C. a maintenance plan
• D. an audit

Answer: B

Explanation: Dynamic Management Views and Functions (Transact-SQL)

NEW QUESTION 2
You need to grant access to an OLTP database regardless of the user operating the application. Which strategy should you use?

• A. Application role
• B. Database user
• C. Server login
• D. Server role

Answer: A

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:
Classifications (Customers)

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 disaster recovery strategy for the Inventory database. What should you include in the recommendation?

• A. Log shipping
• B. SQL Server Failover Clustering
• C. AlwaysOn availability groups
• D. Peer-to-peer replication

Answer: A

Explanation: Scenario:
- You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Point Objective (RPO) of one hour.
- A. Datum Corporation has offices in Miami and Montreal.
- SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.

NEW QUESTION 4
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 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 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: The line WITH FULLSCAN should be replaced with WITH FULLSCAN, NORECOMPUTE. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql

NEW QUESTION 6
You administer a Microsoft SQL Server 2021 database.
The database is currently configured to log ship to a secondary server.
You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online.
You want to perform a tail-log backup. You need to leave the primary database in a restoring state. Which option of the BACKUP LOG command should you use?

• A. NO_TRUNCATE
• B. NORECOVERY
• C. STANDBY
• D. FORMAT

Answer: B

NEW QUESTION 7
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login.
You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages. What should you do?

• A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.
• B. Disable the guest user in the msdb database.
• C. Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile.
• D. In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile.

Answer: A

Explanation: You enable and configure Database Mail using the Database Mail Configuration Wizard. Profiles are either public or private. A private profile is accessible only to specific users or roles.
References: https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-database-mail

NEW QUESTION 8
You are the administrator of a Microsoft SQL Server 2021 server. Some applications consume significant resources.
You need to manage the server workload by restricting resource-intensive applications. You need to dynamically limit resource consumption.
What should you do?

• A. Set up Service Broker to ensure that applications are not allowed to consume more than the specified amount of resources.
• B. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor.
• C. Configure Extended Events to monitor and restrict resource limits allowed by each application type.
• D. Create a new Plan Guide with a Scope Type of sql and define the resource limits for each application.

Answer: B

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 plan to delegate encryption operations to a user.
You need to grant the user permission to implement cell-level encryption while following the principle of least privilege.
Which permission should you grant?

• A. DDLAdmin
• B. db_datawriter
• C. dbcreator
• D. dbo
• E. View Database State
• F. View ServerState
• G. View Definition
• H. sysadmin

Answer: G

Explanation: The following permissions are necessary to perform column-level encryption, or cell-level encryption.
CONTROL permission on the database.
CREATE CERTIFICATE permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.
ALTER permission on the table.
Some permission on the key and must not have been denied VIEW DEFINITION permission. References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data

NEW QUESTION 10
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 maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.

The backup strategies for each database are described in the following table.

Each full or differential backup operation writes into a new file and uses a different sequence number. You observe the following database corruption issues.

SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages:
database name
impacted file id
impacted file physical name
impacted page id
event type that identifies the error type
error count
Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet the following requirements:
Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).
Capture queries based on resource consumption.
Use a stale query threshold value of 60 days.
The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were automatically created and have not been updated for 30 days. The update should be based on all data in the table.
You need to monitor query statistics and execution plans for SalesDb2.
Which options should you set for the Query Store configuration? To answer, select the appropriate options in the answer area.

Answer:

Explanation: Operation Mode: Read-Write Query Store Capture Mode: Auto Size Based Cleanup Mode: AUTO Operation Mode: Read-Write
It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.
Query Store Capture Mode: Auto
Auto – Infrequent queries and queries with insignificant compile and execution duration are ignored. Thresholds for execution count, compile and runtime duration are internally determined.
Size Based Cleanup Mode: AUTO
It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.
OFF – size based cleanup won’t be automatically activated.
AUTO - size based cleanup will be automatically activated when size on disk reaches 90% of max_storage_size_mb. This is the default configuration value.
Size based cleanup removes the least expensive and oldest queries first. It stops at approximately 80% of max_storage_size_mb.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-query-store-option https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store#set-the

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 maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.

The backup strategies for each database are described in the following table.

Each full or differential backup operation writes into a new file and uses a different sequence number. You
observe the following database corruption issues.

SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages:
database name
impacted file id
impacted file physical name
impacted page id
event type that identifies the error type
error count
Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet the following requirements:
Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).
Capture queries based on resource consumption.
Use a stale query threshold value of 60 days.
The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were automatically created and have not been updated for 30 days. The update should be based on all data in the table.
You need to write the query the maintenance plan will use to update the statistics.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Answer:

Explanation: Step 1: Use SalesDB2…
We specify the specific database SalesDB2, not the master. Step 2:
Step 3:
From scenario: The plan should only update statistics that were automatically created and have not been updated for 30 days.
Step 4:
Declare the curser using WITH FULLSCAN. References:
https://solutioncenter.apexsql.com/how-to-automate-and-schedule-sql-server-index-defragmentation/

NEW QUESTION 12
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 is developing a new business intelligence application that will access data in a Microsoft Azure SQL Database instance. All objects in the instance have the same owner.
A new security principal named BI_User requires permission to run stored procedures in the database. The stored procedures read from and write to tables in the database. None of the stored procedures perform IDENTIFY_INSERT operations or dynamic SQL commands.
The scope of permissions and authentication of BI_User should be limited to the database. When granting permissions, you should use the principle of least privilege.
You need to create the required security principals and grant the appropriate permissions. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: One method of creating multiple lines of defense around your database is to implement all data access using stored procedures or user-defined functions. You revoke or deny all permissions to underlying objects, such as tables, and grant EXECUTE permissions on stored procedures. This effectively creates a security perimeter around your data and database objects.
Best Practices
Simply writing stored procedures isn't enough to adequately secure your application. You should also consider the following potential security holes.
Grant EXECUTE permissions on the stored procedures for database roles you want to be able to access the data.
Revoke or deny all permissions to the underlying tables for all roles and users in the database, including the public role. All users inherit permissions from public. Therefore denying permissions to public means that only owners and sysadmin members have access; all other users will be unable to inherit permissions from membership in other roles.
Do not add users or roles to the sysadmin or db_owner roles. System administrators and database owners can access all database objects.
References:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedur

NEW QUESTION 13
You administer a Microsoft SQL Server 2012 instance.
You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

• A. Disable FILESTREAM on the Database.
• B. Enable FILESTREAM on the Server Instance.
• C. Configure the Database for Partial Containment.
• D. Create a non-empty FILESTREAM file group.
• E. Enable Contained Databases on the Server Instance.
• F. Set the FILESTREAM directory name on the Database.

Answer: BDF

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

NEW QUESTION 14
You run the sp_who system stored procedure. A process is blocked by a long-running operation.
You need to identify details of the processes involved in the deadlock including the resources that are being accessed. You must also be able to view the deadlock chain.
What should you use?

• A. DBCC OPENTRAN()
• B. SQL Profiler
• C. sys.dm_exec_sessions
• D. Query Store

Answer: A

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 Microsoft SQL Server 2021 database named Orders.
Users report that during peak usage periods, certain operations are taking more time than expected. Your initial analysis suggests that blocking is the cause.
You need to gather more data to be able to determine which processes are being blocked and to identify the root cause.
What should you do?

• A. Start a trace using SQL Server Profiler to catch the Lock: Deadlock event.
• B. Use sp_configure to set the blocked process threshol
• C. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.
• D. Schedule a SQL Agent job to run every 60 seconds and insert the results of executing the sys.dm_os_wait_stats DMV into a table.
• E. Use System Monitor to catch the Lock Waits/sec event.

Answer: B