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

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

Question No.91

You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA.

Currently, Server01l/HA is the primary replica.

You need to ensure that the following requirements are met:

image

Backup operations occur on Server02/HA.

image

image

If Server02/HA is unavailable, backup operations occur on Server03/HA. Backup operations do not occur on Server01/HA.

How should you configure HaContoso?

  1. Set the backup preference of HaContoso to Prefer Secondary. Set the backup priority of Server02/HA to 20. Set the backup priority of Server03/HA to 10.

  2. Set the backup preference of HaContoso to Secondary only. Set the backup priority of Server02/HA to 20. Set the backup priority of Server03/HA to 10.

  3. Set the backup preference of HaContoso to Secondary only. Set the backup priority of Server02/HA to 10. Set the backup priority of Server03/HA to 20.

  4. set the exclude replica of Server01/HA to true. Set the backup priority of Server02/HA to 10. Set the backup priority of Server03/HA to 20.

Correct Answer: B

Explanation:

Secondary only: Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.

Backup Priority (Lowest=1, Highest=100)

Specifies your priority for performing backups on this replica relative to the other replicas in the same availability group. The value is an integer in the range of 0..100. 1 indicates the lowest priority, and 100 indicates the highest priority. If Backup Priority = 1, the availability replica would be chosen for performing backups only if no higher priority availability replicas are currently

available.

References:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure- backup-on-availability-replicas-sql-server

Question No.92

DRAG DROP

You have a database. The existing backups for the database and their corresponding files are listed in the following table.

image

You purchase a new server. You must restore the database to the new server. You need to restore the data to the most recent time possible.

Which three files should you restore in sequence?

To answer, move the appropriate files from the list of files to the answer area and arrange them in the correct order.

image

Correct Answer:

image

Question No.93

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 are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are shown in the following table.

image

You need to configure auditing for WDWDB.

In the table below, identify the event type that you must audit for each activity.

image

Correct Answer:

image

Question No.94

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.

A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.

One customer reports that their database is not responding as quickly as the service level agreements dictate.

You observe that the database is fragmented. You need to optimize query performance.

Solution: You rebuild all indexes. Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: A

Explanation:

You can remedy index fragmentation by either reorganizing an index or by rebuilding an index.

References:

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

Question No.95

You administer a Microsoft SQL Server 2016 database that includes a table named Application.Events.

Application.Events contains millions of records about user activity in an application. Records in Application.Events that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention with inserts.

You need to be able to modify Application.Events without requiring any changes to the applications that utilize Application.Events.

Which type of solution should you use?

  1. Partitioned tables

  2. Online index rebuild

  3. C.Change data capture

  4. Change tracking

Correct Answer: A

Question No.96

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 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database. You need to ensure that the transactional database is 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. Perform a point-in-time restore.

  4. Restore the latest full backup.

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

  6. Perform a page restore.

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

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

Correct Answer: F

Explanation:

The goal of a page restore is to restore one or more damaged pages without restoring the whole database. Typically, pages that are candidates for restore have been marked as quot;suspectquot; because of an error that is encountered when accessing the page.

Note:

Requirements for Restoring Pages

A page restore is subject to the following requirements:

The databases must be using the full or bulk-logged recovery model. Etc.

References:

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

Question No.97

You plan to integrate an on-premises Microsoft SQL Server environment with Microsoft Azure. You need to create the authentication object so that you can connect to Azure.

Which Windows PowerShell command or commands should you run?

  1. Invoke-Sqlcmd quot;CREATE EXTERNAL DATA SOURCE MyAzureStorage WITH (LOCATION =

    `wasbs://Azure@myaccount.blob.core.windows.net/quot;, CREDENTIAL = Pa$$w0rd)quot;

  2. New-SqlAzureKeyVaultColumnMasterKeySettings- KeyUrlhttps://myvault.vault.contoso.net:443/keys/CMK/4c05fla41b12488f9cba2ea964b6a700

  3. Invoke-Sqlcmd quot;CREATE CREDENTIAL AzureCred WITH IDENTITY = `AzureKey#39;, SECRET =

    `Pa$$w0rdquot;

  4. Invoke-Sqlcmd quot;CREATE LOGIN AzureCred WITH CREDENTIAL = `AzureKey#39;, PASSWORD =

`Pa$$w0rdquot;

Correct Answer: C

Explanation:

Invoke-Sqlcmd runs a script containing statements supported by the SQL Server SQLCMD utility.

The following example creates a SQL Server credential for the Database Engine to use when accessing the Azure Key Vault using the SQL Server Connector for Microsoft Azure Key Vault.

CREATE CREDENTIAL Azure_EKM_TDE_cred WITH IDENTITY = #39;ContosoKeyVault#39;,

SECRET = #39;EF5C8E094D2A4A769998D93440D8115DSECRET_DBEngine#39;

FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ; References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql

Question No.98

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 are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are shown in the following table.

image

You need to monitor WingDB and gather information for troubleshooting issues. What should you use?

  1. sp_updatestats

  2. sp_lock

  3. sys.dm_os_waiting_tasks

  4. D.sys.dm_tran_active_snapshot_database_transactions

  5. Activity Monitor

Correct Answer: B

Explanation:

The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that are happening on your system. This procedure returns much of its information from the syslock info in the master database, which is a system table that contains information on all granted, converting, and waiting lock requests.

Note:

sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To obtain information about locks in the SQL Server Database Engine, use the sys.dm_tran_locks dynamic management view.

sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2008and later. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock- transact-sql

Question No.99

You administer a Microsoft SQL Server 2016 environment.

One of the SQL Server 2016 instances contains a database named Sales. You plan to migrate Sales to Windows Azure SQL Database.

To do so, you need to implement a contained database.

What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  1. Set database containment to AZURE.

  2. Enable server property contained database authentication.

  3. Disable server property cross db ownership chaining.

  4. Set database containment to PARTIAL.

  5. Disable server property contained database authentication.

  6. Set database containment to FULL.

Correct Answer: BD

Question No.100

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 have an on-premises server that runs Microsoft SQL Server 2016 Standard Edition. You need to identify missing indexes.

What should you use?

  1. Activity Monitor

  2. Sp_who3

  3. SQL Server Management Studio (SSMS) Object Explorer

  4. SQL Server Data Collector

  5. SQL Server Data Tools (SSDT)

  6. SQL Server Configuration Manager

Correct Answer: D

Explanation:

Data Collector can gather performance information from multiple SQL Server instances and store it in a single repository. It has three built-in data collecting specifications (data collectors) designed to collect the most important performance metrics. The information collected by default is about disk usage, query statistics, and server activity.

The Query Statistics data collection set collects information about query statistics, activity, execution plans and text on the SQL Server instance.

Missing indexes can be found with the execution plans. References:

https://www.sqlshack.com/sql-server-performance-monitoring-data-collector/

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.