Online Access Free 70-764 Exam Questions

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.No
• B.Yes

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.
You need to configure a synchronization solution to copy data from the Current_Inventory database the DesABCopAppDB database.
What should you configure?

• A.Database Mirroring.
• B.Snapshot Replication.
• C.Transactional Replication.
• D.Incremental Backups

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

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 administer a SQL Server Instance at Contoso Ltd.
A new employee named Kim Ralls must be able to read data only from the dbo.Order table in the SalesDB database.
You create a SQL login named KimRalls. You then create a SQL user account named KimRalls in SalesDB.
The user account is associated with the SQL login.
You need to grant KimRalls the necessary permission or permissions. Your solution must follow the principle of least privilege.
Solution: You perform the following actions:
* Create a custom database role named OrderReader in SalesDB.
* Grant the Select permission on dbo.Order to the OrderReader role.
* Add KimRalls to the OrderReader role.
Does the solution meet the goal?

• A.No
• B.Yes

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.

Correct 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