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

Recertification for MCSE: Data Platform

Question No: 141 DRAG DROP – (Topic 12)

You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed. SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month.

You need to upgrade the database to SQL Server 2014. The solution must minimize downtime.

Which upgrade steps should you recommend?

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: Box 1: Fail over. Box 2: Upgrade SQL1.

Box 3: Upgrade SQL2. Box 4: Establish a mirror.

Note:

* To perform the rolling upgrade

step 1: For each mirroring session whose mirror server instance has just been upgraded, wait for the session to synchronize. Then, connect to the principal server instance, and manually fail over the session.

Step 2: Upgrade each server instance that is now the mirror server in all mirroring sessions in which it is a partner.

Step 3: Resume the mirroring session.

* When upgrading server instances to SQL Server 2014, you can reduce downtime for each mirrored database to only a single manual failover by performing a sequential upgrade, known as a rolling upgrade. A rolling upgrade is a multi-stage process that in its simplest form involves upgrading the server instance that is currently acting as the mirror server in a mirroring session, then manually failing over the mirrored database, upgrading the former principal server, and resuming mirroring

Question No: 142 – (Topic 12)

You plan to create a database.

The database will be used by a Microsoft .NET application for a special event that will last for two days.

During the event, data must be highly available. After the event, the database will be deleted.

You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications.

What should you recommend?

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

  1. SQL Server 2014 Enterprise

  2. SQL Server 2014 Standard

  3. SQL Azure

  4. SQL Server 2014 Express with Advanced Services

Answer: B

Explanation: Programmability (AMO, ADOMD.Net, OLEDB, XML/A, ASSL) supported by Standard and Enterpirse editions only.

Reference: Features Supported by the Editions of SQL Server 2014

Question No: 143 – (Topic 12)

You have a SQL Server instance on a server named Server1.

You need to recommend a solution to perform the following tasks every week:

->Rebuild the indexes by using a new fill factor.

->Run a custom T-SQL command.

->Back up the databases.

What should you recommend?

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

  1. A trigger

  2. An alert

  3. A maintenance plan

  4. Windows PowerShell

  5. A system policy

Answer: C

Explanation: Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.

Maintenance Plans

Question No: 144 DRAG DROP – (Topic 12)

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 2018 PDF and VCE

Answer:

Ensurepass 2018 PDF and VCE

Explanation:

Ensurepass 2018 PDF and VCE

Explanation/Reference:

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

Logon Triggers

Configure Login Auditing (SQL Server Management Studio)

Question No: 145 DRAG DROP – (Topic 12)

You have a SQL Azure database named Database1.

You need to design the schema for a table named table1. Table1 will have less than one million rows. Table1 will contain the following information for each row:

Ensurepass 2018 PDF and VCE

The solution must minimize the amount of space used to store each row. Which data types should you recommend for each column?

To answer, drag the appropriate data type to the correct column in the answer area.

Ensurepass 2018 PDF and VCE

Answer:

Ensurepass 2018 PDF and VCE

Explanation:

Ensurepass 2018 PDF and VCE

Explanation/Reference: Data Types (Transact-SQL)

Question No: 146 – (Topic 12)

You have two SQL Server instances named SQLDev and SQLProd that have access to various storage media.

You plan to synchronize SQLDev and SQLProd.

You need to recommend a solution that meets the following requirements:

->The database schemas must be synchronized from SQLDev to SQLProd.

->The database on SQLDev must be deployed to SQLProd by using a package.

->The package must support being deployed to Windows Azure SQL Database.

What should you recommend?

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

  1. A database snapshot

  2. SQL Server Integration Services (SSIS)

  3. Change data capture

  4. A data-tier application

Answer: B

Explanation: * SIS supports connections to SQL Database by using the ADO.NET provider. OLEDB is not supported at this time. You can build the SSIS package connecting to SQL Database and create the data flow tasks the same way as you would against a typical on-premise SQL Server.

SSIS for Azure and Hybrid Data Movement

Question No: 147 – (Topic 12)

You are designing a monitoring application for a new SQL Server 2014 instance.

You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance.

What should you include in the recommendation?

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

  1. The SQL Server error log

  2. The sys.dm_os_wait_stats dynamic management view

  3. The DBCC SQLPERF(WAITSTATS) command

  4. SQL Server Profiler

Answer: B

Explanation: sys.dm_os_wait_stats

Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.

Columns include: waiting_tasks_count

Number of waits on this wait type. This counter is incremented at the start of each wait. sys.dm_db_wait_stats (Windows Azure SQL Database)

Question No: 148 DRAG DROP – (Topic 12)

You are planning to deploy a database to Windows Azure SQL Database.

You need to design a stored procedure to update rows. The stored procedure must meet the following requirements:

->If the update fails, an error must be raised to the application and the update must be discarded.

->The stored procedure must be designed to maximize concurrency.

What should you include in your design?

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: Box 1: Begin an explicit transaction. Box 2: Perform an update in a try block.

Box 3: Read the @@ROWCOUNT system variable.

Box 4: Raise an error and roll back the transaction if the row count is less than 1. Box 5: Commit the transaction in a finally block.

Note:

  • Read Committed is SQL Server#39;s default isolation level.

  • @@ROWCOUNT

    eturns the number of rows affected by the last statement.

  • Using TRY…CATCH in a transaction

The following example shows how a TRY…CATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.

BEGIN TRANSACTION;

BEGIN TRY

– Generate a constraint violation error. DELETE FROM Production.Product WHERE ProductID = 980;

END TRY BEGIN CATCH SELECT

ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT gt; 0 ROLLBACK TRANSACTION; END CATCH;

IF @@TRANCOUNT gt; 0 COMMIT TRANSACTION; GO

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Question No: 149 – (Topic 12)

You are the administrator for a SQL Server 2014 instance that stores the data for an online transaction processing sales system.

The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups. These backups are stored on a backup server in the company#39;s data center.

Every week, the company places the full backup on a tape and sends it to a third-party backup storage system.

The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders.

You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations.

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

  1. Set up SQL Server Always On with a SQL Azure database as a replica.

  2. Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine.

  3. Put the differential backup on tape and send it to the third-party backup storage system.

  4. Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location.

Answer: D

Explanation: SQL Server 2012 was the first version to provide the ability to back up databases to the Cloud, and SQL Server 2014 improves on the process.

Microsoft SQL Server Backup to Windows Azure Tool enables backup to Windows Azure Blob Storage and encrypts and compresses SQL Server backups stored locally or in the cloud.

Smart, Secure, Cost-Effective: SQL Server Back Up to Windows Azure – SQL Server Team Blog – Site Home – TechNet Blogs

Question No: 150 – (Topic 12)

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

Ensurepass 2018 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% Ensurepass Free Download!
Download Free Demo:70-469 Demo PDF
100% Ensurepass Free Guaranteed!
Download 2018 EnsurePass 70-469 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

Comments are closed.