[Free] Download New Updated (February 2016) Microsoft 70-458 Practice Tests 101-110

Ensurepass

QUESTION 101

DRAG DROP

You administer a Microsoft SQL Server 2012 environment that contains a production SQL Server

2005 instance named SQL2005 and a development SQL Server 2012 instance named SQL2012.

The development team develops a new application that uses the SQL Server 2012 functionality.

You are planning to migrate a database from SQL2005 to SQL2012 so that the development team

can test their new application. You need to migrate the database without affecting the

production environment. Which three actions should you perform in sequence?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange

them in the correct order.

 

Select and Place:

clip_image002

 

Correct Answer:

clip_image004

 

 

QUESTION 102

DRAG DROP

You administer a Microsoft SQL Server 2012 database. The database is backed up according to the following schedule:

 

Ÿ   Daily full backup at 23:00 hours.

Ÿ   Differential backups on the hour, except at 23:00 hours.

Ÿ   Log backups every 10 minutes from the hour, except on the hour.

 

The database uses the Full recovery model. A developer accidentally drops a number of tables

and stored procedures from the database between 22:40 hours and 23:10 hours. You perform a

database restore at 23:30 hours to recover the dropped table. You need to restore the database

by using the minimum amount of administrative effort. You also need to ensure minimal data loss. Which three actions should you perform in sequence?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange

them in the correct order.

 

Select and Place:

clip_image006

Correct Answer:

clip_image008

 

 

QUESTION 103

DRAG DROP

You administer a Microsoft SQL Server 2012 clustered instance that has two nodes named Node 1

and Node2. Node 1 fails and the cluster fails over to Node 2. You need to replace Node 1 and add

it to the cluster. Which four actions should you perform in sequence?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange

them in the correct order.

 

Select and Place:

clip_image010

 

Correct Answer:

clip_image012

 

 

QUESTION 104

DRAG DROP

You administer a Microsoft SQL Server instance. You use a two-node SQL Server failover cluster.

Node B is primary, and Node A is secondary. You need to install a security patch on both nodes.

You need to ensure that the following requirements are met:

 

Ÿ   Both nodes receive the update.

Ÿ   Downtime is minimized.

Ÿ   No data is lost.

 

Which three actions should you perform in sequence?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange

them in the correct order.

 

Select and Place:

clip_image014

 

Correct Answer:

clip_image016

 

 

QUESTION 105

DRAG DROP

You administer a Microsoft SQL Server database. Service accounts for SQL Agent are configured

to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created

within a SQL Server Agent job. The SSIS package accesses a network share when exporting data

from a SQL Server database. When you execute the SQL Server Agent job, it fails due to a

permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent

job can execute the SSIS package. Which four actions should you perform in sequence?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange

them in the correct order.

 

Select and Place:

clip_image018

 

Correct Answer:

clip_image020

 

 

QUESTION 106

You use a contained database named ContosoDb within a domain. You need to create a user who

can log on to the ContosoDb database. You also need to ensure that you can port the database to

different database servers within the domain without additional user account configurations.

Which type of user should you create?

 

A.       User mapped to a certificate

B.       SQL user without login

C.       SQL user with login

D.       SQL user without login

E.       User mapped to an asymmetric key

F.        Login mapped to a virtual account

 

Correct Answer: B

 

 

QUESTION 107

You administer a Microsoft SQL Server 2012 instance named SQL2012 that hosts an OLTP

database of 1 terabyte in size. The database is modified by users only from Monday through

Friday from 09:00 hours to 17:00 hours. Users modify more than 30 percent of the data in the

database during the week. Backups are performed as shown in the following schedule:

 

The Finance department plans to execute a batch process every Saturday at 09:00 hours. This

batch process will take a maximum of 8 hours to complete. The batch process will update three

tables that are 10 GB in size. The batch process will update these tables multiple times. When the

batch process completes, the Finance department runs a report to find out whether the batch

process has completed correctly.

 

You need to ensure that if the Finance department disapproves the batch process, the batch

operation can be rolled back in the minimum amount of time. What should you do on Saturday?

 

A.       Perform a differential backup at 08:59 hours.

B.       Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at

17:01 hours.

C.       Create a database snapshot at 08:59 hours.

D.       Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at

08:59 hours.

E.       Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log

backup at 17:01 hours.

F.        Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log

backup at 08:59 hours.

 

Correct Answer: C

 

 

QUESTION 108

DRAG DROP

You administer a Microsoft SQL Server 2012 database. All database traffic to the SQL Server must

be encrypted by using secure socket layer (SSL) certificates or the connection must be refused.

Network administrators have deployed server certificates to the Windows store of all Windows

servers on the network from a trusted Certificate Authority. This is the only Certificate Authority

allowed to distribute certificates on the network. You enable the Force Encryption flag for the

MSSQLServer protocols, but client computers are unable to connect. They receive the following

error message:

 

“A connection was successfully established with the server, but then an error occurred during the

pre-login handshake, (provider: SSL Provider, error: 0 – The certificate chain was issued by an

authority that is not trusted.) (Microsoft SQL Server)”

 

You notice the following entry in the SQL Server log:

 

“A self-generated certificate was successfully loaded for encryption.”

 

You need to configure SQL Server to encrypt all client traffic across the network. You also need to

ensure that client computers are able to connect to the server by using a trusted certificate.

Which three actions should you perform in sequence?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange

them in the correct order.

 

Select and Place:

clip_image022

 

Correct Answer:

clip_image024

 

 

 

 

QUESTION 109

DRAG DROP

You administer a database named SalesDb that has users named UserA, userB, and UserC. You

need to ensure that the following requirements are met:

 

Ÿ   UserA must be able to provide Windows logins access to the database.

Ÿ   UserB must be able to select, update, delete, and insert data to the database tables.

Ÿ   UserC must be able to create new tables and stored procedures.

 

You need to achieve this goal by granting only the minimum permissions required. To which fixed

database role or roles should you add the users?

 

To answer, drag the appropriate use role or roles. Answer choices may be used once, more than

once, or not at all. Answer targets may be additionally, you may need to drag the split bar

between panes or scroll to view content.

 

Select and Place:

clip_image026

 

Correct Answer:

Box 1:

db_ddladmin: UserC

UserC needs to be able to add tables and procedures. He can do these through the db_ddladmin role (through the create statement).

Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL)

command in a database.

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of

database objects in database.

Examples: CREATE, ALTER, DROP statements

 

Box 2:

db_datareader: UserB

UserB needs to be able to select data.

Members of the db_datareader fixed database role can run a SELECT statement against any table

or view in the database.

 

Box 3:

db_datawriter: UserB

UserB needs to update, delete, and insert data. He can do these through the db_datawriter role.

Members of the db_datawriter fixed database role can add, delete, or change data in all user

tables.

 


Box 4:

db_owner: <leave blank>

Not required in this scenario.

Members of the db_owner fixed database role can perform all configuration and maintenance

activities on the database.

 

Box 5:

db_acccessadmin: UserA

This enables UserA to provide Windows logins access.

Members of the db_accessadmin fixed database role can add or remove access for Windows

logins, Windows groups, and SQL Server logins.

 

 

QUESTION 110

You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.

 

The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL Server.

 

The project must be configured to be able to export data to and from five different production servers that run SQL Server 2012. Each target server requires different values for connection strings and parameters in the SSIS project.

 

You need to meet the requirements by using the least amount of administrative effort.

 

What should you do?

A.

For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time.

B.

Create one registry configuration. Add a connection string for each target server. Choose the appropriate server in the package at build time.

C.

Create one XML configuration file. Add a connection string for each target server. Choose the appropriate server in the package at build time.

D.

For each target server, create a separate package and include each package in the SSIS catalog. Run the appropriate server at package execution time.

 

Correct Answer: C

 

Free VCE & PDF File for Microsoft 70-458 Real Exam

Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…