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

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

Question No.31

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 create a backup plan for Instance4. Which backup plan should you create?

  1. Weekly full backups, nightly differential. No transaction log backups are necessary.

  2. Weekly full backups, nightly differential backups, transaction log backups every 5 minutes.

  3. Weekly full backups, nightly differential backups, transaction log backups every 12 hours.

  4. Full backups every 60 minutes, transaction log backups every 30 minutes.

Correct Answer: B

Explanation:

From scenario: Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O. The recovery point objective of Instancse4 is 60 minutes. RecoveryPoint Objectives are commonly described as the amount of data that was lost during the outage and recovery period. You should minimize both the time required to restore the databases and the space required to store backups.

References:

http://sqlmag.com/blog/sql-server-recovery-time-objectives-and-recovery-point-objectives

Question No.32

HOTSPOT

You deploy a Microsoft SQL Server instance to support a global sales application. The instance includes the following tables: TableA and TableB.

TableA is a partitioned table that uses an incrementing integer number for partitioning. The table has millions of rows in each partition. Most changes to the data in TableA affect recently added data. The UPDATE STATISTICS for TableA takes longer to complete than the allotted maintenance window.

Thousands of operations are performed against TableB each minute. You observe a large number of Auto Update Statistics events for TableB.

You need to address the performance issues with each table.

In the table below, identify the action that will resolve the issues for each table. NOTE: Make only one selection in each column.

image

Correct Answer:

image

Question No.33

You administer a Microsoft SQL Server 2016 database.

Users report that a billing application becomes unresponsive during busy times of the day. While investigating, you notice large number of processes taking or waiting for table locks. You suspect that SQL Server is assigning stronger locks to queries.

You start a SQL Profiler trace. Which event should you select?

  1. Deadlock graph

  2. Lock: Escalation

  3. Lock: Timeout

  4. Lock: Deadlock

Correct Answer: B

Question No.34

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.

One instance hosts a user database named HRDB. The database contains sensitive human

resources data.

You need to grant an auditor permission to view the SQL Server audit logs 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 Server State

  7. View Definition

  8. sysadmin

Correct Answer: F

Explanation:

Unless otherwise specified, viewing catalog views requires a principal to have one of the following:

image

image

Membership in the sysadmin fixed server role. The CONTROL SERVER permission.

image

image

The VIEW SERVER STATE permission. The ALTER ANY AUDIT permission.

image

The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog view).

References:

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

Question No.35

HOTSPOT

A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instanced. The environments host several customer databases.

You host a local database and a Stretch database that has a table named Members for one specific customer.

You need to provide the customer with information about the space used in the databases.

In the table below, identify the query that provides the required information for each database. NOTE: Make only one selection in each column.

image

Correct Answer:

image

Question No.36

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 examining information about users, sessions, and processed in an on-premises Microsoft SQL Server Database Engine instance.

You need to return information about processes that are not idle, that belong to a specific user, or that belong to a specific session.

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: B

Explanation:

Use sp_who3 to first view the current system load and to identify a session of interest. You should execute the query several times to identify which session id is most consuming teh system

resources.

Parameters

sp_who3 null – who is active;

sp_who3 1 or #39;memory#39; – who is consuming the memory;

sp_who3 2 or #39;cpu#39; – who has cached plans that consumed the most cumulative CPU (top 10); sp_who3 3 or #39;count#39; – who is connected and how many sessions it has;

sp_who3 4 or #39;idle#39; – who is idle that has open transactions;

sp_who3 5 or #39;tempdb#39; – who is running tasks that use tempdb (top 5); and, sp_who3 6 or #39;block#39; – who is blocking.

Question No.37

HOTSPOT

You are the database administrator of a Microsoft SQL Server instance. Developers are writing stored procedures to send emails using sp_send_dbmail. Database Mail is enabled.

You need to configure each account#39;s profile security and meet the following requirements:

image

Account SMTP1_Account must only be usable by logins that have been given explicit permissions to use the SMTP1_profile.

image

Account SMTP2_Account must only be usable by logins who are a member of the [DatabaseMailUserRole] role in msdb.

In the table below. identify the profile type that must be used for each account. NOTE: Make only one selection in each column.

image

Correct Answer:

image

Question No.38

You administer a Microsoft SQL Server 2016 database instance.

You create a new user named UserA. You need to ensure that UserA is able to create SQL Server Agent jobs and to execute SQL Server Agent jobs.

To which role should you add UserA?

  1. Securityadmin

  2. RSExecRole

  3. SQLAgentUserRole

  4. DatabaseMailUserRole

Correct Answer: C

Question No.39

HOTSPOT

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 create the connection strings for the operations and reporting systems. In the table below, identify the option that must be specified in each connection string. NOTE: Make only one selection in each column.

image

Correct Answer:

image

Question No.40

You administer a Microsoft SQL Server 2016 server.

When transaction logs grow, SQL Server must send an email message to the database administrators.

You need to configure SQL Server to send the email messages. What should you configure?

  1. SQL Mail

  2. An Extended Events session

  3. Alerts and operators in SQL Server Agent

  4. Policies under Policy-Based Management

Correct Answer: C

Explanation:

Operators are aliases for people or groups that can receive electronic notification when jobs have completed or alerts have been raised. The SQL Server Agent service supports the notification of administrators through operators. Operators enable notification and monitoring capabilities of SQL Server Agent.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server- agent-mail-to-use-database-mail

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

Leave a Comment

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