Microsoft 70-764 Study Guides 2021

NEW QUESTION 1
You have 10 Microsoft SQL Server 2021 servers.
You deploy a management data warehouse named DW1. You configure DW1 to gather all the performance data from the servers.
You configure a Data Collector on a SQL server named SV1.
You query the data warehouse on DW1 and discover that data from SV1 is unavailable.
You need to ensure that you can review the performance data from SV1 when you query DW1. What should you do?

• A. Start the SQL Server Agent service on DW1.
• B. Execute the msdb.sp_syscollector_set_warehouse_connection_user stored procedure on SV1.
• C. Execute the msdb.sp_syscollector_enable_collector stored procedure on DW1.
• D. Start the SQL Server Agent service on SV1.

Answer: C

Explanation: sp_syscollector_enable_collector enables the data collector. References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-syscollector-enable-colle

NEW QUESTION 2
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 meets the data recovery requirement. What should you include in the recommendation?

• A. A differential backup
• B. A transaction log backup
• C. Snapshot isolation
• D. A database snapshot

Answer: D

NEW QUESTION 3
You are configuring log shipping for a Microsoft SQL Server database named salesOrders. You run the following Transact-SQL script:

You need to determine the changes that the script has on the environment.
How does the script affect the environment? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: Box 1: is
Note: sp_change_log_shipping_primary_database, Parameter: [ @backup_share = ] 'backup_share'
Is the network path to the backup directory on the primary server. backup_share is nvarchar(500), with no default, and cannot be NULL.
Box 2: 72 hours
4320 minutes is 72 hours.
Note: sp_change_log_shipping_primary_database, Backup_retention_period parameter
@backup_retention_period = ] 'backup_retention_period'
Is the length of time, in minutes, to retain the log backup file in the backup directory on the primary server. backup_retention_period is int, with no default, and cannot be NULL.
Box 2: SP_Add_Retcode Box 3: 24 hours sp_add_schedule
@freq_type (here 4, which is daily)
A value indicating when a job is to be executed. freq_typeis int, with a default of 0, and can be one of these values.
ValueDescription 1Once
4Daily 8Weekly 16Monthly
32Monthly, relative to freq_interval
64Run when SQLServerAgent service starts 128Run when the computer is idle
@freq_interval = ] freq_interval
The days that a job is executed. freq_interval is int, with a default of 1, and depends on the value of freq_type. Value of freq_typeEffect on freq_interval
1 (once)freq_interval is unused.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-schedule-transact-sql

NEW QUESTION 4
You have a test server that contains a database named DB1. Backups of the database are written to a single backup device. The backup device has a full, differential, and transaction log backup.
You discover that the database is damaged. You restore the database to the point at which the differential backup was taken.
You need to rebuild the database with data stored in the latest transaction logs.
How should you complete the Transact-SQL statement? To answer. drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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: Box 1: RESTORE
Box 2: RECOVERY
The RESTORE ... WITH RECOVERY option puts the database into a useable state, so users can access a restored database.
References:
https://www.mssqltips.com/sqlservertutorial/112/recovering-a-database-that-is-in-the-restoring-state/

NEW QUESTION 5
You administer a Microsoft SQL Server 2021 database that contains a table named AccountTransaction. You discover that query performance on the table is poor due to fragmentation on the
IDX_AccountTransaction_AccountCode non-clustered index.
You need to defragment the index.
You also need to ensure that user queries are able to use the index during the defragmenting process. Which Transact-SQL batch should you use?

• A. ALTER INDEX IDX_AccountTransaction_AccountCode ONAccountTransaction.AccountCode REORGANIZE
• B. ALTER INDEX ALL ON AccountTransaction REBUILD
• C. ALTER INDEX IDX_AccountTransaction_AccountCode ONAccountTransaction.AccountCode REBUILD
• D. CREATE INDEX IDXAccountTransactionAccountCode ONAccountTransaction.AccountCode WITH DROP EXISTING

Answer: A

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.
A company has several Microsoft SQL Server database in Microsoft Azure.
One database experiences a storage failure, and pages that store critical database metadata are corrupted. You need to perform an offline restore of the database’s pages.
Which option should you use first?

• A. backup compression
• B. backup encryption
• C. file snapshot backup
• D. mirrored backup media sets
• E. SQL Server backup to URL
• F. SQL Server Managed Backup to Azure
• G. tail-log backup
• H. back up and truncate the transaction log

Answer: G

Explanation: An unbroken chain of log backups must be available, up to the current log file, and they must all be applied to bring the page up to date with the current log file.
A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.
Note: The goal of a page restore is to restore one or more damaged pages without restoring the whole database. Typically, pages that are candidates for restore have been marked as "suspect" because of an error that is encountered when accessing the page.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server?view=sql-s https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server?view=sql-serv

NEW QUESTION 7
General Overview
You are the Senior Database Administrator (DBA) for a software development company named Leafield Solutions. The company develops software applications custom designed to meet customer requirements.
Requirements Leafield Solutions has been asked by a customer to develop a web-based Enterprise Resource Planning and Management application. The new application will eventually replace a desktop application that the customer is currently using. The current application will remain in use while the users are trained to use the new webbased application.
You need to design the SQL Server and database infrastructure for the web-based application. Databases
You plan to implement databases named Customers, Sales, Products, Current_Inventory, and TempReporting. The Sales database contains a table named OrderTotals and a table named SalesInfo.
A stored procedure named SPUpdateSalesInfo reads data in the OrderTotals table and modifies data in the SalesInfo table.
The stored procedure then reads data in the OrderTotals table a second time and makes further changes to the information in the SalesInfo table.
The Current_Inventory database contains a large table named Inv_Current. The Inv_Current table has a clustered index for the primary key and a nonclustered index. The primary key column uses the identity property.
The data in the Inv_Current table is over 120GB in size. The tables in the Current_Inventory database are accessed by multiple queries in the Sales database.
Another table in the Current_Inventory database contains a self-join with an unlimited number of hierarchies. This table is modified by a stored procedure named SPUpdate2.
An external application named ExternalApp1 will periodically query the Current_Inventory database to generate statistical information. The TempReporting database contains a single table named GenInfo.
A stored procedure named SPUPdateGenInfo combines data from multiple databases and generates millions of rows of data in the GenInfo table.
The GenInfo table is used for reports.
When the information in GenInfo is generated, a reporting process reads data from the Inv_Current table and queries information in the GenInfo table based on that data.
The GenInfo table is deleted after the reporting process completes. The Products database contains tables named ProductNames and ProductTypes.
Current System
The current desktop application uses data stored in a SQL Server 2005 database named DesABCopAppDB. This database will remain online and data from the Current_Inventory database will be copied to it as soon as data is changed in the Current_Inventory database.
SQL Servers
A new SQL Server 2012 instance will be deployed to host the databases for the new system. The databases will be hosted on a Storage Area Network (SAN) that provides highly available storage.
Design Requirements
Your SQL Server infrastructure and database design must meet the following requirements:
Confidential information in the Current_ Inventory database that is accessed by ExternalApp1 must be securely stored.
Direct access to database tables by developers or applications must be denied.
The account used to generate reports must have restrictions on the hours when it is allowed to make a connection.
Deadlocks must be analyzed with the use of Deadlock Graphs.
In the event of a SQL Server failure, the databases must remain available.
Software licensing and database storage costs must be minimized.
Development effort must be minimized.
The Tempdb databases must be monitored for insufficient free space.
Failed authentication requests must be logged.
Every time a new row is added to the ProductTypes table in the Products database, a user defined
function that validates the row must be called before the row is added to the table.
When SPUpdateSalesInfo queries data in the OrderTotals table the first time, the same rows must be returned along with any newly added rows when SPUpdateSalesInfo queries data in the OrderTotals table the second time.
The performance of the SPUpdate2 stored procedure needs to be improved. Your solution must meet the design requirements.
What should your solution include?

• A. A common table expression.
• B. A derived table.
• C. A Cursor.
• D. A table variable.

Answer: A

NEW QUESTION 8
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 change to USP_3 to ensure that the procedure completes only if all of the UPDATE statements complete. Which change should you recommend?

• A. Set the XACT_ABORT option to off
• B. Set the XACT_ABORT option to on.
• C. Set the IMPLICIT_TRANSACTIONS option to off.
• D. Set the IMPLICIT_TRANSACTIONS option to on.

Answer: B

Explanation: Scenario:
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 continues to execute.
- When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

NEW QUESTION 9
You manage a Microsoft SQL Server instance. You have a user named User1.
You need to grant the minimum permissions necessary to allow User1 to review audit logs.
For each action, which option should you use? To answer, select the appropriate options in the answer area.

Answer:

Explanation: Box 1: securityadmin
To access log files for instances of SQL Server that are online, this requires membership in the securityadmin fixed server role.
Box 2: sys.server_audit_specifications
sys.server_audit_specifications contains information about the server audit specifications in a SQL Server audit on a server instance.

NEW QUESTION 10
You have a Microsoft SQL Server database named DB1. Nested and recursive triggers for DB1 are disabled. There is an existing DDL trigger named TableAudit in the database. The trigger captures the name of each table as the table is created.
You define a policy for the database by using SQL Server policy-based management. The policy requires that all table names use the prefix tbl. You set the evaluation mode for the policy to On change: prevent.
You observe that developers are able to add new tables that do not include the required prefix in the table name.
You need to ensure the policy is enforced. What should you do?

• A. Disable the TableAudit trigger.
• B. Enable nested triggers.
• C. Change the policy evaluation mode to On Schedule.
• D. Enable recursive triggers.

Answer: D

NEW QUESTION 11
You work as a Developer at ABC.com.
All databases are hosted on Windows Server 2012 servers running SQL Server 2012. The company has a database named Products.
Tables in the Products database contain data including part numbers, product name, color, type and size. Users in the Marketing department have created brochures for each product.
The brochures have been created in the XML Paper Specification (XPS) format.
You have been asked to add a table to the Products database to store the product brochures. The brochures need to be stored in a folder structure.
Company users will also need to access the brochures from Windows applications using UNC paths. How can you meet these requirements?

• A. By implementing the XMLNAMESPACES feature.
• B. By implementing the FILEGROUP feature.
• C. By implementing the FILETABLE feature.
• D. By implementing the FILESTREAM feature.

Answer: C

NEW QUESTION 12
You have a query that is used by a reporting dashboard. Users report that the query sometimes takes a long time to run. You need to recommend a solution to identify what is causing the issue.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

• A. Set the blocked process threshold, and then run SQL Server Profiler.
• B. Set the blocked process threshold, and then create an alert.
• C. Enable trace flag 1204, and then create an alert.
• D. Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.

Answer: B

Explanation: Step 1: Turn on the blocked process report. This will look for any blocking taking 20 seconds or longer.
- Make sure you don't have any pending changes

Step 2: Set up a trace to capture the blocked process report. Run it as a server side trace.

NEW QUESTION 13
An organization deploys Microsoft SQL Server instances at a main office and the organization's remote office. SQL Server is not using Windows failover clustering.
Remote employef s report connection issues when they attempt to run queries against a specific database that is hosted at the main office.
You need to ensure that remote employees can retrieve data from a database instance that is hosted in the remote office.
Which three actions 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: Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

NEW QUESTION 14
You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2021. You attempt to add the [CorpnetUser1] login to the database.
However, you receive the following error message: "User already exists in current database."
You need to configure the [CorpnetUser1] login to be able to access the Orders database and retain the original permissions.
You need to achieve this goal by using the minimum required permissions. Which Transact-SQL statement should you use?

• A. DROP USER [User1]; CREATE USER [CorpnetUser1] FOR LOGIN [CorpnetUser1]; ALTER ROLE [db_owner] ADD MEM3ER [CorpnetUser1];
• B. ALTER SERVER RCLS Isysadmin] ADD MEMBER [CorpnetUser1];
• C. ALTER USER [CorpnetUser1] WITH LOGIN [CorpnetUser1];
• D. ALTER ROLE [db owner] ADD MEMBBR [CorpnetUser1];

Answer: C

NEW QUESTION 15
You administer several Microsoft SQL Server 2021 database servers.
Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types.
Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do?

• A. Set the Merge agent on the problem subscribers to use the slow link agent profile.
• B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
• C. Change the Merge agent on the problem subscribers to run continuously.
• D. Set the Remote Connection Timeout on the Publisher to 0.

Answer: A

Explanation: You might have different profiles for different instances of an agent. For example, a Merge Agent that connects to the Publisher and Distributor over a dialup connection could use a set of parameters that are better suited to the slower communications link by using the slow link profile.
Note: When replication is configured, a set of agent profiles is installed on the Distributor. An agent profile contains a set of parameters that are used each time an agent runs: each agent logs in to the Distributor during its startup process and queries for the parameters in its profile.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-agent-profiles

NEW QUESTION 16
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.
Users reports that they encounter the following error when they query SalesDb1: “SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x5d672d9b; actual: 0xdd672d98). It occurred during a read of page (1.232) in database ID 12 at offset 0x000000001d0000 in file F:DatabasesMSSQLServer DatabasesMSSQL13.MSSQL2021MSSQLDATASalesDb1.mdt.”
You must restore the impacted page from SalesDb1Full_1.bak. A single backup set named SalesDb1Log.bak was created since the latest full backup operation.
You need to restore the impacted page.
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:
Restore page
Start a page restore with a full database, file, or filegroup backup that contains the page. In the RESTORE DATABASE statement, use the PAGE clause to list the page IDs of all of the pages to be restored.
Step 2:
Restore log file with norecovery. Use the first file (FILE = 1). Step 3:
Backup the tail-end of the log.
Create a new log backup of the database that includes the final LSN of the restored pages, that is, the point at which the last restored page is taken offline.
Step 4:
Restore database with recovery. Use second file (FILE = 2).
Restore the new log backup. After this new log backup is applied, the page restore is completed and the pages are now usable.
Example:
The following example restores four damaged pages of file B with NORECOVERY. Next, two log backups are applied with NORECOVERY, followed with the tail-log backup, which is restored with RECOVERY. This example performs an online restore. In the example, the file ID of file B is 1, and the page IDs of the damaged pages are 57, 202, 916, and 1016.
RESTORE DATABASE <database> PAGE='1:57, 1:202, 1:916, 1:1016'
FROM <file_backup_of_file_B> WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup> WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup> WITH NORECOVERY;
BACKUP LOG <database> TO <new_log_backup>;
RESTORE LOG <database> FROM <new_log_backup> WITH RECOVERY;
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server