May 22, 2018

Microsoft MCSA 70-764 Exam FREE Dumps

Want to get MCSA: SQL 2016 Database Administration certification? Need to pass Microsoft MCSA 70-764 exam at the first attempt? Do not know which site providing the best and FREE 70-764 exam dumps? You are at the right place now. Passing Microsoft MCSA 70-764 exam is not difficult now.

Microsoft MCSA 70-764 exam free dumps are available, which are part of valid 70-764 dumps. Our Microsoft MCSA 70-764 exam dumps are prepared by certified 70-764 experts, who have years of experience on this certification. Test Microsoft MCSA 70-764 exam FREE dumps below.

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.

Set the blocked process threshold, and then run SQL Server Profiler.

Set the blocked process threshold, and then create an alert.

Enable trace flag 1204, and then create an alert.

Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.

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.

Query the sys.dm_exec_requests dynamic management view.

Create a trace in SQL Server Profiler that contains the Deadlock graph event.

Query the sys.dm_exec_sessions dynamic management view.

Enable trace flag 1222, and then view the SQL Server error log.

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.

AlwaysOn availability groups

Database mirroring

Log shipping

Failover Clustering

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?

sys.dm_exec_requests, sys.dm_exec_sessions, sys.objects

sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects

sys.sysprocesses, sys.dm_exec_query_text, sys.objects

sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text

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.

The SQL Server error log

The sys.dm_os_wait_stats dynamic management view

The DBCC SQLPERF(WAITSTATS) command

SQL Server Profiler

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.

Create a SQL Server Integration Services (SSIS) package, and then run the package.

Back up the database, and then restore the database.

Extract a data-tier application, and then import the application.

Generate scripts to create all of the all database objects and all of the data, and then execute the scripts by using SQL Azure.

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.

Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.

Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.

Create a database snapshot. If the deployment fails, revert the database to the database snapshot.

Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.

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.

Policy-Based Management

A Data Definition Language (DDL) trigger

SQL Server Agent jobs

A maintenance plan

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?

The bulk-logged recovery model

The full recovery model

A new partitioned table

A new log file

A new file group

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.

Resource Governor

Max Degree of Parallelism

Windows System Resource Manager (WSRM)

Processor affinity

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.

Implement change data capture on the Sales.Orders table.

To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as the order status changes.

Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.

To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.

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?

Change data capture

Statistics

A trigger

Master Data Services

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

Server1

Server2

Server3

Seiver4

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.

Set up SQL Server Always On with a SQL Azure database as a replica.

Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine.

Put the differential backup on tape and send it to the third-party backup storage system.

Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location.

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.

Multi server jobs

Policy-Based Management

Common criteria compliance

Maintenance plans

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?

clustered columnstore indexes

nonclustered columnstore indexes

data manipulation language (DML) statements

partitioning

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.

processor affinity

Max Degree of Parallelism

Windows System Resource Manager (WSRM)

Resource Governor

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?

VIEW ANY DEFINITION

VIEW SERVER STATE

VIEW DEFINITION

CONTROL SERVER

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.

dynamic management views

Database Engine Tuning Advisor

SQL Server Profiler

Activity Monitor

Data Profile Viewer

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?

Create a user-defined database role and add users to the role.

Create stored procedures that use EXECUTE AS clauses.

Create a Policy-Based Management Policy.

Create functions that use EXECUTE AS clauses.

About The Author

dumps

From our dumpsbase platform you could search what exams you need then test or practice online by yourself. Download the PDF file if you need directly. Any other questions you can mail [email protected]

Related Posts

About The Author

dumps

Add a Comment