[Free] 2017(Sep) EnsurePass Testking Microsoft 70-465 Dumps with VCE and PDF 51-60

EnsurePass
2017 Sep Microsoft Official New Released 70-465
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/70-465.html

Designing Database Solutions for Microsoft SQL Server

Question No: 51 DRAG DROP – (Topic 5)

You plan to deploy SQL Server 2014.

Your company identifies the following monitoring requirements for the database:

->An e-mail message must be sent if the SQL Server Authentication mode changes.

->An e-mail message must be sent if CPU utilization exceeds 90 percent.

You need to identify which feature meets each monitoring requirement.

Which features should you identify?

To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Explanation:

Ensurepass 2017 PDF and VCE

  • Policy-Based Management is helpful in resolving the issues presented in the following scenario:

    A company policy prohibits enabling Database Mail or SQL Mail. A policy is created to check the server state of those two features. An administrator compares the server state to the policy. If the server state is out of compliance, the administrator chooses the Configure mode and the policy brings the server state into compliance.

  • Events are generated by SQL Server and entered into the Microsoft Windows application log. SQL Server Agent reads the application log and compares events written there to alerts that you have defined. When SQL Server Agent finds a match, it fires an alert, which is an automated response to an event. In addition to monitoring SQL Server events, SQL Server Agent can also monitor performance conditions and Windows Management Instrumentation (WMI) events.

To define an alert, you specify:

The name of the alert.

The event or performance condition that triggers the alert.

The action that SQL Server Agent takes in response to the event or performance condition.

Question No: 52 DRAG DROP – (Topic 5)

You are designing an authentication strategy for a new server that has SQL Server 2014 installed. The strategy must meet the following business requirements:

->The account used to generate reports must be allowed to make a connection during certain hours only.

->Failed authentication requests must be logged.

You need to recommend a technology that meets each business requirement. The solution must minimize the amount of events that are logged.

Which technologies should you recommend?

To answer, drag the appropriate solution to the correct business requirement in the answer area.

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Explanation:

Ensurepass 2017 PDF and VCE

  • Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.

    You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login.

  • Login auditing can be configured to write to the error log on the following events. Failed logins

Successful logins

Both failed and successful logins

Incorrect:

* C2 audit mode can be configured through SQL Server Management Studio or with the c2

audit mode option in sp_configure. Selecting this option will configure the server to record both failed and successful attempts to access statements and objects.

References: Logon Triggers

Configure Login Auditing (SQL Server Management Studio)

Question No: 53 DRAG DROP – (Topic 5)

You define a table by using the following statement.

Ensurepass 2017 PDF and VCE

The Teachers table contains one million records. A sample record of the XML data output is as follows:

Ensurepass 2017 PDF and VCE

You plan to create queries against the table based on grade.

You need to recommend which indexes should be applied to the XML fields. The solution must meet the following requirements:

->Reduce the amount of time required to query the table based on grade.

->Minimize the size of the index.

What should you recommend? To answer, drag the appropriate index types to the correct fields. Each index type 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.

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Ensurepass 2017 PDF and VCE

Explanation:

XML indexes fall into the following categories:

  • Primary XML index

  • Secondary XML index

    The first index on the xml type column must be the primary XML index.

    Question No: 54 – (Topic 5)

    You have a database named DB1.

    You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next.

    Occasionally, the identifying value must be reset to its initial value.

    You need to design a mechanism to hold the identifying values for the stored procedure to use.

    What should you do?

    More than one answer choice may achieve the goal. Select the BEST answer.

    1. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Reset the value by using an ALTER SEQUENCE statement as needed.

    2. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Increment the sequence object to the next value by using an ALTER SEQUENCE statement. Reset the value as needed by using a different ALTER SEQUENCE statement.

    3. Create a fourth table that holds the next value in the sequence. At the end each transaction, update the value by using the stored procedure. Reset the value as needed by using an UPDATE statement.

    4. Create an identity column in each of the three tables. Use the same seed and the same increment for each table. Insert new rows into the tables by using the stored procedure. Use the DBCC CHECKIDENT command to reset the columns as needed.

    Answer: A Explanation:

  • an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function.

  • ALTER SEQUENCE

Includes argument:

RESTART [ WITH lt;constantgt; ]

The next value that will be returned by the sequence object. If provided, the RESTART WITH value must be an integer that is less than or equal to the maximum and greater than or equal to the minimum value of the sequence object. If the WITH value is omitted, the sequence numbering restarts based on the original CREATE SEQUENCE options.

  • CREATE SEQUENCE

    Creates a sequence object and specifies its properties. A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.

    Sequence Numbers

    Question No: 55 – (Topic 5)

    You have a server named Server1 that has 2 processors.

    You plan to deploy multiple instances of SQL Server 2014 to Server1. Each instance will have multiple databases.

    You need to recommend a method to allocate processor time to each database. What should you include in the recommendation?

    More than one answer choice may achieve the goal. Select the BEST answer.

    1. Resource Governor

    2. Max Degree of Parallelism

    3. Windows System Resource Manager (WSRM)

    4. Processor affinity

    Answer: A Explanation:

    SQL Server Resource Governor is a feature than you can use to manage SQL Server

    workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.

    Reference: Resource Governor

    Incorrect:

    D: PROCESS AFFINITY

    Enables hardware threads to be associated with CPUs.

    Question No: 56 – (Topic 5)

    You are creating a database that will store usernames and passwords for an application. You need to recommend a solution to store the passwords in the database.

    What should you recommend?

    More than one answer choice may achieve the goal. Select the BEST answer.

    1. One-way encryption

    2. Encrypting File System (EFS)

    3. Transparent Data Encryption (TDE)

    4. Reversible encryption

    Answer: C Explanation:

  • Transparent Data Encryption (TDE) is a special case of encryption using a symmetric key. TDE encrypts an entire database using that symmetric key called the database encryption key. The database encryption key is protected by other keys or certificates which are protected either by the database master key or by an asymmetric key stored in an EKM module.

  • SQL Server provides the following mechanisms for encryption: Transact-SQL functions

    Asymmetric keys

    Symmetric keys Certificates

    Transparent Data Encryption

    Reference: Transparent Data Encryption (TDE)

    Question No: 57 – (Topic 5)

    You administer a SQL Server 2014 instance.

    Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues.

    You want to write a query of the system views that will report the following:

    ->Number of users that have a connection to the server

    ->Whether a user#39;s connection is active

    ->Whether any connections are blocked

    ->What queries are being executed

    Whether the database restore is still executing and, if it is, what percentage of the restore is complete.

    Which system objects should you use in your query to best achieve this task?

    1. sys.dm_exec_requests, sys.dm_exec_sessions, sys.objects

    2. sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects

    3. sys.sysprocesses, sys.dm_exec_query_text, sys.objects

    4. sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text

    Answer: D Explanation:

  • sys.dm_exec_requests

    Returns information about each request that is executing within SQL Server.

  • sys.dm_exec_sessions

    Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a

    server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.

  • sys.dm_exec_query_text

Returns the text of the SQL batch that is identified by the specified sql_handle.

References:

sys.dm_exec_requests (Transact-SQL) sys.dm_exec_sessions (Transact-SQL)

Incorrect:

  • sys.dm_exec_query_stats

    Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself.

  • sys.objects

Contains a row for each user-defined, schema-scoped object that is created within a database.

Question No: 58 – (Topic 5)

You are designing a Windows Azure SQL Database for an order fulfillment system. You create a table named Sales.Orders with the following script.

Ensurepass 2017 PDF and VCE

Each order is tracked by using one of the following statuses:

->Fulfilled

->Shipped

->Ordered

->Received

You need to design the database to ensure that that you can retrieve the following information:

->The current status of an order

->The previous status of an order.

->The date when the status changed.

The solution must minimize storage.

More than one answer choice may achieve the goal. Select the BEST answer.

  1. To the Sales.Orders table, add three columns named Status, PreviousStatus and ChangeDate. Update rows as the order status changes.

  2. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.

  3. Implement change data capture on the Sales.Orders table.

  4. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.

Answer: B

Question No: 59 – (Topic 5)

You have an SQL Server 2014 instance named SQL1.

SQL1 creates error events in the Windows Application event log.

You need to recommend a solution that will run an application when SQL1 logs a specific error in the Application log.

Which SQL elements should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)

  1. A policy

  2. A maintenance plan

  3. An alert

  4. A job

  5. A trigger

Answer: D,E Explanation:

Use a trigger that starts a job which executes the application. Reference: http://technet.microsoft.com/en-us/library/hh849759.aspx

Question No: 60 – (Topic 5)

You have a SQL Server 2014 environment that includes four servers. The servers are configured as shown in the following table.

Ensurepass 2017 PDF and VCE

You plan to configure Policy-Based Management to enforce the following rules:

->On Server1, enable SQL Server password policies and enable the default trace.

->On Server3, ensure that the names of user-defined stored procedures begin with the prefix quot;usp_quot; and ensure that all databases use a case-sensitive collation.

You need to recommend which server you must configure as a Central Management Server.

Which server should you recommend? (Each correct answer presents a complete solution. Choose all that apply.)

  1. Server1

  2. Server2

  3. Server3

  4. Seiver4

Answer: A,C Explanation:

Need Standard or Enterprise edition of SQL Server.

100% Free Download!
Download Free Demo:70-465 Demo PDF
100% Pass Guaranteed!
Download 2017 EnsurePass 70-465 Full Exam PDF and VCE

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

2017 EnsurePass IT Certification PDF and VCE

Leave a Reply