Microsoft MCSA 70-764 Exam FREE Dumps

1. 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.

2. You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify the isolation level used by the query when a deadlock occurs.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

3. You have four databases that are accessed by using an Online Transaction Processing (OLTP) application. The databases are stored on a server named SQL1 that has SQL Server 2016 installed.
You plan to deploy an additional server that has SQL Server 2016 installed.
You need to design a high-availability solution for the databases that meets the following requirements:
If SQL1 fails, the databases must be available.
Users must be able to run reports against a secondary copy of the databases.
What should you include in the design?
More than one answer choice may achieve the goal. Select the BEST answer.

4. You administer a SQL Server 2016 instance.
Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues.
You want to write a query of the system views that will report the following:
・ Number of users that have a connection to the server
・ Whether a user's connection is active
・ Whether any connections are blocked
・ What queries are being executed
Whether the database restore is still executing and, if it is, what percentage of the restore is complete.
Which system objects should you use in your query to best achieve this task?

5. You are designing a monitoring application for a new SQL Server 2016 instance.
You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.

6. You have a database hosted on SQL Server 2012 R2. The database contains 5 million rows.
You need to recommend a repeatable method to migrate the database to SQL Azure.
Which method should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

7. You are designing a database named DB1.
Changes will be deployed to DB1 every Wednesday night.
You need to recommend a strategy to deploy the changes to DB1.
The strategy must meet the following requirements:
・ The strategy must not disrupt backup operations.
・ DB1 must be online while the changes are deployed.
・ You must be able to undo quickly any changes made to objects.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

8. You have a server that has SQL Server 2016 installed. The server contains 100 user databases. You need to recommend a backup solution for the user databases.
The solution must meet the following requirements:
・ Perform a transaction log backup every hour.
・ Perform a full backup of each database every week.
・ Perform a differential backup of each database every day.
・ Ensure that new user databases are added automatically to the backup solution.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.

9. You have a SQL Server 2016 database named DB1. You plan to import a large number of records from a SQL Azure database to DB1. You need to recommend a solution to minimize the amount of space used in the transaction log during the import operation.
What should you include in the recommendation?

10. You have a server named Server1 that has 2 processors.
You plan to deploy multiple instances of SQL Server 2016 to Server1. Each instance will have multiple databases.
You need to recommend a method to allocate processor time to each database.
What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

11. You are designing a SQL Server database for an order fulfillment system.
You create a table named Sales.Orders by using the following script:


Each order is tracked by using one of the following statuses:
・ Fulfilled
・ Shipped
・ Ordered
・ Received
You need to design the database to ensure that you can retrieve the status of an order on a given date. The solution must ensure that new statuses can be added in the future.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

12. You deploy a database by using SQL Server 2016. 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?

13. You have a SQL Server 2016 environment that includes four servers.
The servers are configured as shown in the following table.


You plan to configure Policy-Based Management to enforce the following rules:
・ On Server1, enable SQL Server password policies and enable the default trace.
・ On Server3, ensure that the names of user-defined stored procedures begin with the prefix "usp_" and ensure that all databases use a case-sensitive collation.
You need to recommend which server you must configure as a Central Management Server.
Which server should you recommend? (Each correct answer presents a complete solution. Choose all that apply.)

14. You are the administrator for a SQL Server 2016 instance that stores the data for an online transaction processing sales system.
The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups. These backups are stored on a backup server in the company's data center.
Every week, the company places the full backup on a tape and sends it to a third-party backup storage system.
The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders.
You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

15. You have a SQL Server 2016 environment that contains 20 servers. The corporate security policy states that all SQL Server 2016 instances must meet specific security standards. You need to recommend a management strategy for the SQL Server 2016 servers.
What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

16. You have a customer who has several SQL Server 2012 database servers.
You are designing a data warehouse for the customer. The data warehouse will use columnstore indexes.
The customer identifies that the following must be supported for the column store indexes.
・ Data manipulation language (DML) statements
・ Nonclustered columnstore indexes
・ Clustered columnstore indexes
・ Partitioning
You need to identify which technology requires the customer to implement an SQL Server 2016 database.
What should you identify?

17. You have a server named Server1 that has 2 processors.
You plan to deploy multiple instances of SQL Server 2016 to Server1. Each instance will have multiple databases.
You need to recommend a method to set limits on processor time for each database.
What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

18. You are using dynamic management views to monitor an SQL Server server named SQL1.
A database administrator named Dba1 must monitor the health of SQL1.
You need to ensure that Dba1 can access dynamic management views for SQL1. The solution must use the principle of least privilege.
Which permissions should you assign to Dba1?

19. You have an SQL Server 2016 server named SQL1.
You are designing a performance monitoring solution.
You need to monitor the following events on SQL1:
・ A deadlock graph
・ Missing column statistics
・ CPU performance statistics
・ A batch of completed Transact-SQL statements
Which two tools should you use? Each correct answer presents a complete solution.

20. You need to recommend a solution to allow application users to perform UPDATE operations on the database tables. The solution must meet the business requirements.
What should you recommend?