Free Microsoft 070-764 Practice Test & Real Exam Questions

You administer a Microsoft SQL Server 2016 server.
The MSSQLSERVER service uses a domain account named CONTOSO\SQLService.
You plan to configure Instant File Initialization.
You need to ensure that Data File Autogrow operations use Instant File Initialization.
What should you do? Choose all that apply.

You manage a SQL Server 2014 instance that contains a database named DB1.
Users report that some queries to DB1 take longer than expected.
Although most queries run in less than one second, some queries take up to 20 seconds to run. You need to view all of the performance statistics for each database file.
Which method should you use?

You have a Microsoft SQL Server audit named Audit1. Audit1 contains an audit specification named AuditSpec that audits failed logins.
You need to configure the audit to capture CREATE, ALTER, and DROP statements.
Which three statements should you execute in sequence? To answer, move the appropriate statements from the list to the answer area and arrange them in the correct order.

You have a SQL Azure database named Database1.
You need to design the schema for a table named table1.
Table1 will have less than one million rows.
Table1 will contain the following information for each row:

The solution must minimize the amount of space used to store each row.
Which data types should you recommend for each column? To answer, drag the appropriate data type to the correct column in the answer area.

You are the database administrator for a Microsoft SQL Server instance. You develop an Extended Events package to look for events related to application performance.
You need to change the event session to include SQL Server errors that are greater than error severity 15.
Which five 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.

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 view the information about the corrupted pages on SalesDb3.
How should you complete the Transact-SQL statement?
Select two.

You are creating a database that will store usernames and credit card numbers for an application. You need to recommend a solution to store and reuse the credit card numbers in the database.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

You administer a Microsoft SQL Server 2016 database named Contoso on a server named Server01.
You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso.
You also need to ensure minimum impact to the server.
What should you create?

You are the database administrator of a Microsoft SQL Server instance. Developers are writing stored procedures to send emails using sp_send_dbmail. Database Mail is enabled.
You need to configure each account's profile security and meet the following requirements:
* Account SMTP1_Account must only be usable by logins that have been given explicit permissions to use the SMTP1_profile.
* Account SMTP2_Account must only be usable by logins who are a member of the
[DatabaseMailUserRole] role in msdb.
In the table below. identify the profile type that must be used for each account.
NOTE: Make only one selection in each column.

You have two SQL Server instances named SQLDev and SQLProd that have access to various storage media.
You plan to synchronize SQLDev and SQLProd.
You need to recommend a solution that meets the following requirements:
* The database schemas must be synchronized from SQLDev to SQLProd.
* The database on SQLDev must be deployed to SQLProd by using a package.
* The package must support being deployed to SQL Azure.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

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 addresses the installation issues.
What should you include in the recommendation?

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.
Start of repeated scenario.
You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.

Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.

Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.
* Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
* Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table.

End of repeated scenario.
You need to reduce the amount of time it takes to back up OperationsMain.
What should you do?

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?

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 a Microsoft SQL Server environment in Microsoft Azure. The databases are stored directly in Azure blob storage.
You need to ensure that you can restore a database to a specific point in time between backups while minimizing the number of Azure storage containers required.
Which option should you use?

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 have a database named DB1 that contains a table named Table1.
You need to audit all updates to TablID.
Solution: You create a trace on a local disk that captures all the update statements.
Does this meet the goal?