[Free] 2019(Nov) EnsurePass Microsoft 70-764 Dumps with VCE and PDF 61-70

Get Full Version of the Exam
http://www.EnsurePass.com/70-764.html

Question No.61

You have a database named DB1 that stores more than 700 gigabyte (GB) of data and serves millions of requests per hour.

Queries on DB1 are taking longer than normal to complete. You run the following Transact-SQL statement:

SELECT * FROM sys.database_query_store_options

You determine that the Query Store is in Read-Only mode.

You need to maximize the time that the Query Store is in Read-Write mode. Which Transact-SQL statement should you run?

  1. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)

  2. ALTER DATABASE DB1SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 50)

  3. ALTER DATABASE DB1SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));

  4. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE)

Correct Answer: C

Explanation:

Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries.

By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.

Avoid keeping historical data that you do not plan to use. This will reduce changes to read-only status. The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Use Management Studio or the following script to configure time-based cleanup policy:

ALTER DATABASE [QueryStoreDB]

SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));

References:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the- query-store

Question No.62

You administer a Microsoft SQL Server 2016 database.

You want to make a full backup of the database to a file on disk. In doing so, you need to output the progress of the backup.

Which backup option should you use?

  1. STATS

  2. COMPRESSION

  3. CHECKSUM

  4. IN IT

Correct Answer: A

Question No.63

You administer all the deployments of Microsoft SQL Server 2016 in your company.

You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail.

You also need to minimize the amount of storage used by the database. Which configuration should you use?

  1. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary

  2. SQL Server that includes an application database configured to perform transactional replication

  3. Two servers configured in the same data center SQL Server Availability Group configured in AsynchronousCommit Availability Mode One server configured as an Active Secondary

  4. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode

  5. Two servers configured in the same data center A primary server configured to perform log- shipping every 10 minutes A backup server configured as a warm standby

  6. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode

  7. SQL Server that includes an application database configured to perform snapshot replication

  8. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance

Correct Answer: H

Explanation:

A Windows Server Failover Cluster (WSFC) is a group of independent servers that work together to increase the availability of applications and services. SQL Server takes advantage of WSFC services and capabilities to support Always On availability groups and SQL Server Failover Cluster Instances.

References:

https://docs.micosoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover- clustering-wsfc-with-sql-server

Question No.64

You administer a Microsoft SQL Server 2016 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day.

The reporting database is only updated from the transactional database.

The recovery model and backup schedule are configured as shown in the following table:

image

At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database.

You need to ensure that the databases are restored.

You also need to ensure that data loss is minimal. What should you do?

  1. Perform a partial restore.

  2. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.

  3. Restore the latest full backup.

  4. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.

  5. Perform a page restore.

  6. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.

  7. Perform a point-in-time restore.

  8. Restore the latest full backup. Then, restore the latest differential backup.

Correct Answer: H

Explanation:

At restore time, before you restore a differential backup, you must restore its base. Then, restore only the most recent differential backup to bring the database forward to the time when that differential backup was created. Typically, you would restore the most recent full backup followed

by the most recent differential backup that is based on that full backup.

References:

https://technet.microsoft.com/en-us/library/ms345448(v=sql.105).aspx

Question No.65

You administer all the deployments of Microsoft SQL Server 2016 in your company.

You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off- loaded from the primary database to another server.

You also need to be able to add indexes to the secondary database. Which configuration should you use?

  1. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary

  2. Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary

  3. Two servers configured in the same data center A primary server configured to perform log- shipping every 10 minutes A backup server configured as a warm standby

  4. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode

  5. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode

  6. SQL Server that includes an application database configured to perform transactional replication

  7. SQL Server that includes an application database configured to perform snapshot replication

  8. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance

Correct Answer: F

Explanation:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional- replication

Question No.66

HOTSPOT

You are planning the deployment of two new Always On Failover Cluster Instances (FCIs) of Microsoft SQL Server to a single Windows Server Cluster with three nodes. The planned configuration for the cluster is shown in the Server Layout exhibit. (Click the Exhibit button.)

image

The SAN team has configured storage for the cluster and sent the configuration to you in the email shown in the SAN Team Email exhibit. (Click the Exhibit button.)

image

Each node of the cluster has identical local storage available as shown in the Local Storage exhibit. (Click the Exhibit button.)

image

All local storage is on SSD.

You need to plan specific configurations for the new cluster.

For each of the following statement, select Yes if the statement is true. Otherwise, select No.

image

Correct Answer:

image

Question No.67

Note: This question is part of a series of question that present the same scenario. Each question in the series contains I 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.

Your company has several Microsoft SQL Saver instance. Each instance hosts many database. You observe I/O corruption on some of the instance.

You need to perform the following actions:

image

image

identify databases where the PAGE verity option is not set. Configure full page protection for the identified databases.

Solution: You run the following Transact-SQL statement:

image

For each database that you identify, you run the following Transact-SQL statement:

image

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: A

Question No.68

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.

You are the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.

You plan to delegate encryption operations to a user.

You need to grant the user permission to implement cell-level encryption while following the principle of least privilege.

Which permission should you grant?

  1. DDLAdmin

  2. db_datawriter

  3. dbcreator

  4. dbo

  5. View Database State

  6. View ServerState

  7. View Definition

  8. sysadmin

Correct Answer: G

Explanation:

The following permissions are necessary to perform column-level encryption, or cell-level encryption.

CONTROL permission on the database.

CREATE CERTIFICATE permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.

ALTER permission on the table.

Some permission on the key and must not have been denied VIEW DEFINITION permission. References:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column- of-data

Question No.69

DRAG DROP

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

image

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

image

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.

image

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.

image

You need to configure a new replica of AG1 on Instance6. How should you complete the Transact-SQL statement?

To answer, drag the appropriate Transact-SQL statements to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

image

Correct Answer:

image

Question No.70

You manage a Microsoft SQL Server environment. You plan to encrypt data when you create backups.

You need to configure the encryption options for backups. What should you configure?

  1. a certificate

  2. an MD5 hash

  3. a DES key

  4. an AES 256-bit key Correct Answer: D Explanation:

To encrypt during backup, you must specify an encryption algorithm, and an encryptor to secure the encryption key. The following are the supported encryption options:

Encryption Algorithm: The supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES

Encryptor: A certificate or asymmetric Key References:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption

Get Full Version of the Exam
70-764 Dumps
70-764 VCE and PDF

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>