Free Microsoft 070-764 Exam Dumps Questions & Answers

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

• A.No
• B.Yes

A table named Table1 has a clustered columnstore index. The table contains over 10 billion records. This represents 12 years of sales data. The table is stored in a single filegroup.
You drop the existing columnstore index. You allocate 13 new filegroups with files large enough to store the entire table.
You need to implement a new columnstore index that supports maximum data compression for data older than
2 years.
Which four 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.

Correct Answer:

Explanation

Step 1: Create a nonclustered index by year
Step 2: Define a partitioning function
A partition function must first be created in a CREATE PARTITION FUNCTION statement before creating a partition scheme.
Step 3: Define a partitioning scheme
The CREATE PARTITION SCHEME command creates a scheme in the current database that maps the partitions of a partitioned table or index to filegroups. The number and domain of the partitions of a partitioned table or index are determined in a partition function.
Step 4: Create a partitioned clustered columnstore index. Use columnstore_archive data...
For partitioned tables and indexes, you can configure the compression option for each partition, and the various partitions of an object do not have to have the same compression setting.
For partitioned columnstore tables and columnstore indexes, you can configure the archival compression option for each partition, and the various partitions do not have to have the same archival compression setting.
The partition scheme must already exist in the database when you create a columnstore index.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-2
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact-sql?view=sql-server-2017

You view the Restore Transaction Log settings for a database named DB1 as shown in the following exhibit.

Use the drop down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.

Correct Answer:

Explanation

Log shipping secondary database can be kept in two restore modes:
Restoring - This mode is also known as NORECOVERY mode.
Standby - This mode is also known as Read-Only mode.
When you use the NORECOVERY mode, the database will be in a restoring state and inaccessible to users, so uncommitted database transactions are not an issue.
When you use the STANDBY mode, database will be in Read-Only state and users can access this database for read operations. Transactions in process on primary server or any uncommitted transaction cannot be read in secondary database.
References:
https://www.mssqltips.com/sqlservertip/3600/change-the-restore-mode-of-a-secondary-sql-server-database-in-lo

You are a database administrator for an organization. You manage a mission-critical database named DB1 that is hosted on a server named LON-SQL1. An audit record for transactions that occur in DB1 are written to a file every five seconds. If an audit record cannot be recorded, the transaction must be terminated. You create a shared folder named Audits on a server named LON-SVR1.
You need to implement the process for recording audit records.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.

Correct Answer:

Explanation

You deploy a three node Windows Server Failover Clustering (WSFC) cluster. You configure a Microsoft SQL Server instance in failover cluster instance (FCI) mode.
You need to identify the actions that will take place if the motherboard on one server fails and automatic failover occurs.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.

Correct Answer:

Explanation

Explanation
References:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sq