Dumps4cert.com : Latest Dumps with PDF and VCE Files
2018 Aug Microsoft Official New Released 70-469
100% Free Download! 100% Pass Guaranteed!

Recertification for MCSE: Data Platform

Question No: 151 DRAG DROP – (Topic 12)

You are the senior database administrator at Contoso, Ltd. You manage a SQL Server 2014 Instance, with multiple databases used for reporting.

You have recently hired a junior database administrator. You want this person to be able to view the database structures on the server, but you do not want him or her to be able to make changes or see the data in the tables.

The new hire#39;s login credentials are as follows: Login name: JFree

Password: Jx672$qse

You want the new hire to be required to change his password on his next login.

The code that is produced should execute no matter the initial database context in which it is started.

You need to write the code required to give the new hire only the desired access, using the smallest number of steps. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation: Box 1:

Dumps4Cert 2018 PDF and VCE

Box 2:

Dumps4Cert 2018 PDF and VCE

Note:

  • MUST_CHANGE

    Applies to: SQL Server 2008 through SQL Server 2014.

    Applies to SQL Server logins only. If this option is included, SQL Server prompts the user for a new password the first time the new login is used.

  • The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

CREATE LOGIN (Transact-SQL) ALTER SERVER ROLE (Transact-SQL)

Question No: 152 – (Topic 12)

You have a 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. http://technet.microsoft.com/en-us/library/hh849759.aspx

Question No: 153 – (Topic 12)

You are designing a SQL Server database for an order fulfillment system. You create a table named Sales.Orders by using the following script:

Dumps4Cert 2018 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 you can retrieve the status of an order on a given date. The solution must ensure that new statuses can be added in the future.

What should you do?

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

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

  2. To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as the order status changes.

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

  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: 154 – (Topic 12)

You are creating a database that will store usernames and credit card numbers for an application.

You need to recommend a solution to store and reuse the credit card numbers in the database.

What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

  1. Data encryption

  2. Transparent Data Encryption (TDE)

  3. Encrypting File System (EPS)

  4. Data hashing

Answer: B

Explanation: If we are going to encrypt credit card number for storage, then we should have Data Encryption Key (DEK) for encrypting the credit card number. http://msdn.microsoft.com/en-us/library/bb934049.aspx

Question No: 155 – (Topic 12)

You are the new database administrator for a SQL Server 2014 instance.

You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring.

You want to set up monitoring to see if query performance is being affected.

You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed.

What should you do?

  1. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key columns in the database.

  2. Use the missing_column_statistics extended event.

  3. Query the sys.statistics system view to see all cases where the statistics were last needed.

  4. Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical.

Answer: B

Explanation: The Missing Column Statistics event class indicates that column statistics that could have been useful for the optimizer are not available.

By monitoring the Missing Column Statistics event class, you can determine if there are statistics missing for a column used by a query. This can cause the optimizer to choose a less efficient query plan than expected.

Missing Column Statistics Event Class

Question No: 156 DRAG DROP – (Topic 12)

You plan to deploy a database to SQL Azure.

You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:

->Prevent data read by USP_1 from being modified by other active processes.

->Allow USP_2 to perform dirty reads.

You need to recommend the isolation level for the stored procedures. The solution must maximize concurrency.

Which isolation levels should you recommend?

To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Dumps4Cert 2018 PDF and VCE

Note:

  • SP1: repeatable read

    a repeatable read scan retains locks on every row it touches until the end of the

    transaction. Even rows that do not qualify for the query result remain locked. These locks ensure that the rows touched by the query cannot be updated or deleted by a concurrent session until the current transaction completes (whether it is committed or rolled back).

  • SP2: read uncommitted permits repeatable reads Transaction Isolation Levels

    Question No: 157 – (Topic 12)

    You are building a stored procedure for a Windows Azure SQL Database. The procedure will add multiple rows to a table.

    You need to design the stored procedure to meet the following requirements:

    If any of the new rows violates a table constraint, then no further additions must be attempted and all changes made by the stored procedure must be discarded.

    If any errors occur, a row must be added to an audit table, and the original error must be returned to the caller of the stored procedure.

    What should you include in the design?

    1. An explicit transaction that has XACT_ABORT disabled

    2. An implicit transaction that has error handling enabled

    3. An explicit transaction that has error handling enabled

    4. An implicit transaction that has XACT.ABORT enabled

    Answer: C

    Explanation: http://technet.microsoft.com/en-us/library/ms175127(v=SQL.105).aspx

    Question No: 158 – (Topic 12)

    You have a server named Server1 that has 16 processors.

    You plan to deploy multiple instances of SQL Server 2014 to Server1.

    You need to recommend a method to allocate processors to each instance.

    What should you include in the recommendation?

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

    1. Processor affinity

    2. Windows System Resource Manager (WSRM)

    3. Max Degree of Parallelism

    4. Resource Governor

    Answer: A

    Explanation: CPU affinity management through Windows System Resource Manager is not recommended for SQL Server multi-instance management. Instead, use the processor affinity settings in SQL Server.

    Server Properties (Processors Page)

    Question No: 159 – (Topic 12)

    You deploy a SQL Server instance named SQLProd that uses SQL Server 2014.

    You need to recommend a solution to monitor the transactions that are running currently against SQLProd. The solution must minimize the amount of custom code required.

    What should you recommend?

    1. Statistics

    2. A dynamic management view

    3. A trigger

    4. User-defined views

    Answer: B

    Explanation: Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

    Transactions can be monitored.

    Dynamic Management Views and Functions (Transact-SQL)

    Question No: 160 – (Topic 12)

    You create a stored procedure that retrieves all of the rows from a table named Table1.

    You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if another transaction is modifying rows in Table1 simultaneously.

    What should you recommend?

    1. Snapshot isolation

    2. A database snapshot

    3. Filegroups

    4. Indexes

    Answer: A

    Explanation: Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction.

    Transaction Isolation Levels

    100% Dumps4cert Free Download!
    70-469 PDF
    100% Dumps4cert Pass Guaranteed!
    70-469 Dumps

    Dumps4cert 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

Comments are closed.