[Free] 2018(July) Ensurepass Microsoft 70-764 Dumps with VCE and PDF 11-20

Ensurepass.com : Ensure you pass the IT Exams
2018 July Microsoft Official New Released 70-764
100% Free Download! 100% Pass Guaranteed!

Administering a SQL Database Infrastructure

Question No: 11 HOTSPOT

You have a Microsoft SQL Server instance that hosts a database named DB1 that contains 800 gigabyte (GB) of data. The database is used 24 hours each day. You implement indexes and set the value of the Auto Update Statistics option set to True.

Users report that queries take a long time to complete.

You need to identify statistics that have not been updated for a week for tables where more than 1,000 rows changed.

How should you complete the Transact-SQL statement? To answer, configure the appropriate Transact-SQL segments in the answer area.

Ensurepass 2018 PDF and VCE

Answer:

Ensurepass 2018 PDF and VCE

Explanation:

Ensurepass 2018 PDF and VCE

Box 1: stats_date See example below.

Box 2: rowmodctr See examplebelow.

Box 3: stats_date

You need to identify statistics that have not been updated for a week.

Box 4: rowmodctr

You need to identify that more than 1,000 rows changed.

Rowmodctr counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.

Example: We will query every statistics object which was not updated in the last day and has rows modified since the last update. We will use the rowmodctr field of sys.sysindexes because it shows how many rows were inserted, updated or deleted since the last update occurred. Please note that it is not always 100% accurate in SQL Server 2005 and later, but it can be used to check if any rows were modified.

-Get the list of outdated statistics SELECTOBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr

FROM sys.sysindexes

WHERE STATS_DATE (id, indid)lt;=DATEADD(DAY,-1,GETDATE())

AND rowmodctrgt;0

AND id IN (SELECT object_id FROM sys.tables) GO

After collecting this information, we can decide which statistics require an update. References:

Question No: 12

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

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/

Question No: 13 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.

Ensurepass 2018 PDF and VCE

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

Ensurepass 2018 PDF and VCE

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.

Ensurepass 2018 PDF and VCE

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.

The wait statistics monitoring requirements for the instances are described in the following table.

Ensurepass 2018 PDF and VCE

You need to propose a new process for the StagedExternal database.

Which five actions should you recommended be performed in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Ensurepass 2018 PDF and VCE

Answer:

Ensurepass 2018 PDF and VCE

Explanation:

Ensurepass 2018 PDF and VCE

From scenario: 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 StagedExternaldatabase 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.

All databases use the full recovery model. References:

Question No: 14 DRAG DROP

You are the database administrator for a Microsoft SQL Server instance. You develop an Extended Events package to look for events related to application performance.

You need to change the event session to include SQL Server errors that are greater than error severity 15.

Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Ensurepass 2018 PDF and VCE

Answer:

Ensurepass 2018 PDF and VCE

Explanation:

Ensurepass 2018 PDF and VCE

Step 1: ALTER EVENT SESSION Contoso1 ON SERVER Step 2: ADD EVENT …

Step 3: (ACTION … Step 4: WHERE…

Step 5: ) GO

Example: To start an Extended Events sessions in order to trap SQL Server errors with severity greater than 10,just run the following script:

CREATE EVENT SESSION [error_trap] ON SERVER

ADD EVENT sqlserver.error_reported (

ACTION

(package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.c lient_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username, sqlserver.plan_handle,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlser

ver.tsql_frame,sqlserver.tsql_stack,sqlserver.username) WHERE ([severity]gt;10)

)

ADD TARGET package0.event_file (

SET filename=N#39;D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\XEvents\error_trap.xel#39;

) WITH (

STARTUP_STATE=OFF

) GO

References:

Question No: 15 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 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.

Ensurepass 2018 PDF and VCE

You need to configure the Contoso instance.

How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

Ensurepass 2018 PDF and VCE

Answer:

Ensurepass 2018 PDF and VCE

Explanation:

Ensurepass 2018 PDF and VCE

Box 1: show advanced options

Advanced configuration options are displayed by first setting show advanced option to 1.

Box 2: max worker threads

SQL Server uses the native thread services of the operating systems so that one or more threads support each network that SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users. The default value for max worker threads is 0. This enables SQL Server to automatically configure the number of worker threads at startup. The default setting is best for most systems.

References:https://docs.microsoft.com/en-us/sql/database-engine/configure- windows/configure-the-max-worker-threads-server-configuration-option

Question No: 16

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.

A company has a server that runs Microsoft SQL Server 2016 Web edition. The server has a default instance that hosts a database named DB1.

You need to ensure that you can perform auditing at the database level for DB1.

Solution: You migrate DB1 to the default instance on a server that runs Microsoft SQL Server 2016 Standard edition.

Does the solution meet the goal?

  1. Yes

  2. No

Answer: B Explanation:

All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.

References:https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql- server-audit-database-engine

Question No: 17

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.

Ensurepass 2018 PDF and VCE

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

Ensurepass 2018 PDF and VCE

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.

Ensurepass 2018 PDF and VCE

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.

The wait statistics monitoring requirements for the instances are described in the following

table.

Ensurepass 2018 PDF and VCE

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. Weekly full backups, nightly differential backups, nightly transaction log backups.

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.

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

Question No: 18 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.

Ensurepass 2018 PDF and VCE

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

Ensurepass 2018 PDF and VCE

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.

Ensurepass 2018 PDF and VCE

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.

The wait statistics monitoring requirements for the instances are described in the following table.

Ensurepass 2018 PDF and VCE

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.

Ensurepass 2018 PDF and VCE

Answer:

Ensurepass 2018 PDF and VCE

Explanation:

Ensurepass 2018 PDF and VCE

Reporting system: Connect to any current read-only replica instance

We configure Read-OnlyAccess on an Availability Replica. We select Read-intent only. Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.

From Scenario: 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.

Operating system: Connect to the current primary replica SQL instance

By default both read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas of an Always On availability group.

From scenario: 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.

References:https://docs.microsoft.com/en-us/sql/database-engine/availability- groups/windows/configure-read-only-access-on-an-availability-replica-sql-server

Question No: 19 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’s profile security and meet the following requirements: In the table below. identify the profile type that must be used for each account.

NOTE: Make only one selection in each column.

Ensurepass 2018 PDF and VCE

Answer:

Ensurepass 2018 PDF and VCE

Explanation:

Ensurepass 2018 PDF and VCE

SMTP1_Account1: Private Profile

When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the

default public profile for the msdb database.

SMTP1_Account2: Default Profile

Execute permissions forsp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database.

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

Question No: 20

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 manage a Microsoft SQL Server environment. You implement Transparent Data Encryption (TDE).

A user will assist in managing TDE.

You need to ensure that the user can view the TDE metadata while following the principle of lease 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

Answer: G Explanation:

Viewing the metadata involved with TDE requires the VIEW DEFINITION permissionon the certificate.

References: https://docs.microsoft.com/en-us/sql/relational- databases/security/encryption/transparent-data-encryption-tde

100% Ensurepass Free Download!
Download Free Demo:70-764 Demo PDF
100% Ensurepass Free Guaranteed!
70-764 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No


Leave a Reply

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

  • Categories

  • Tags

  • Hot Exams

  • Hot Exams

  • Hot Catageories

  • microsoft dumps

    62-193 Dumps
    70-243 Dumps
    70-246 Dumps
    70-247 Dumps
    70-331 Dumps
    70-332 Dumps
    70-333 Dumps
    70-334 Dumps
    70-339 Dumps
    70-341 Dumps
    70-342 Dumps
    70-345 Dumps
    70-346 Dumps
    70-347 Dumps
    70-348 Dumps
    70-354 Dumps
    70-355 Dumps
    70-357 Dumps
    70-383 Dumps
    70-384 Dumps
    70-385 Dumps
    70-398 Dumps
    70-410 Dumps
    70-411 Dumps
    70-412 Dumps
    70-413 Dumps
    70-414 Dumps
    70-417 Dumps
    70-461 Dumps
    70-462 Dumps
    70-463 Dumps
    70-464 Dumps
    70-465 Dumps
    70-466 Dumps
    70-467 Dumps
    70-469 Dumps
    70-470 Dumps
    70-473 Dumps
    70-475 Dumps
    70-480 Dumps
    70-481 Dumps
    70-482 Dumps
    70-483 Dumps
    70-484 Dumps
    70-485 Dumps
    70-486 Dumps
    70-487 Dumps
    70-488 Dumps
    70-489 Dumps
    70-490 Dumps
    70-491 Dumps
    70-492 Dumps
    70-494 Dumps
    70-496 Dumps
    70-497 Dumps
    70-498 Dumps
    70-499 Dumps
    70-517 Dumps
    70-532 Dumps
    70-533 Dumps
    70-534 Dumps
    70-535 Dumps
    70-537 Dumps
    70-640 Dumps
    70-642 Dumps
    70-646 Dumps
    70-673 Dumps
    70-680 Dumps
    70-681 Dumps
    70-682 Dumps
    70-684 Dumps
    70-685 Dumps
    70-686 Dumps
    70-687 Dumps
    70-688 Dumps
    70-689 Dumps
    70-692 Dumps
    70-694 Dumps
    70-695 Dumps
    70-696 Dumps
    70-697 Dumps
    70-698 Dumps
    70-703 Dumps
    70-705 Dumps
    70-713 Dumps
    70-734 Dumps
    70-735 Dumps
    70-740 Dumps
    70-741 Dumps
    70-742 Dumps
    70-743 Dumps
    70-744 Dumps
    70-745 Dumps
    70-761 Dumps
    70-762 Dumps
    70-764 Dumps
    70-765 Dumps
    70-767 Dumps
    70-768 Dumps
    70-773 Dumps
    70-774 Dumps
    70-775 Dumps
    70-776 Dumps
    70-778 Dumps
    70-779 Dumps
    70-980 Dumps
    70-981 Dumps
    70-982 Dumps
    74-343 Dumps
    74-344 Dumps
    74-409 Dumps
    74-678 Dumps
    74-697 Dumps
    77-418 Dumps
    77-419 Dumps
    77-420 Dumps
    77-421 Dumps
    77-422 Dumps
    77-423 Dumps
    77-424 Dumps
    77-425 Dumps
    77-426 Dumps
    77-427 Dumps
    77-428 Dumps
    77-600 Dumps
    77-601 Dumps
    77-602 Dumps
    77-603 Dumps
    77-604 Dumps
    77-605 Dumps
    77-725 Dumps
    77-726 Dumps
    77-727 Dumps
    77-728 Dumps
    77-729 Dumps
    77-730 Dumps
    77-731 Dumps
    77-853 Dumps
    77-881 Dumps
    77-882 Dumps
    77-883 Dumps
    77-884 Dumps
    77-885 Dumps
    77-886 Dumps
    77-887 Dumps
    77-888 Dumps
    77-891 Dumps
    98-349 Dumps
    98-361 Dumps
    98-362 Dumps
    98-363 Dumps
    98-364 Dumps
    98-365 Dumps
    98-366 Dumps
    98-367 Dumps
    98-368 Dumps
    98-369 Dumps
    98-372 Dumps
    98-373 Dumps
    98-374 Dumps
    98-375 Dumps
    98-379 Dumps
    98-380 Dumps
    98-381 Dumps
    98-382 Dumps
    98-383 Dumps
    98-388 Dumps
    AZ-100 Dumps
    AZ-101 Dumps
    AZ-102 Dumps
    INF-203x Dumps
    INF-204x Dumps
    INF-205x Dumps
    INF-206x Dumps
    MB2-700 Dumps
    MB2-701 Dumps
    MB2-702 Dumps
    MB2-703 Dumps
    MB2-704 Dumps
    MB2-706 Dumps
    MB2-707 Dumps
    MB2-708 Dumps
    MB2-709 Dumps
    MB2-710 Dumps
    MB2-711 Dumps
    MB2-712 Dumps
    MB2-713 Dumps
    MB2-714 Dumps
    MB2-715 Dumps
    MB2-716 Dumps
    MB2-717 Dumps
    MB2-718 Dumps
    MB2-719 Dumps
    MB2-877 Dumps
    MB5-705 Dumps
    MB6-700 Dumps
    MB6-701 Dumps
    MB6-702 Dumps
    MB6-703 Dumps
    MB6-704 Dumps
    MB6-705 Dumps
    MB6-884 Dumps
    MB6-885 Dumps
    MB6-886 Dumps
    MB6-889 Dumps
    MB6-890 Dumps
    MB6-892 Dumps
    MB6-893 Dumps
    MB6-894 Dumps
    MB6-895 Dumps
    MB6-896 Dumps
    MB6-897 Dumps
    MB6-898 Dumps