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

Ensurepass

QUESTION 51

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012.

You attempt to add a SQL login, [User1], to the database. However, you receive the following

error message:

 

“User already exists in current database.”

 

You need to configure the [User1] login to be able to access the Orders database and retain the

original permissions. You need to achieve this goal by using the minimum required permissions.

Which Transact-SQL statement should you use?

 

A.       DROP USER [User1];

CREATE USER [User1]FOR LOGIN [User1]; ALTER ROLE [db_owner] ADD MEMBER [User1];

B.       ALTER ROLE; db_owner] ADD MEMBER [User1];

C.       ALTER USER [User1]WITH LOGIN [User1];

D.       ALTER SERVER ROLE [sysadmin] ADD MEM3ER [User1];

 

Correct Answer: C

 

 

 

QUESTION 52

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.     SQL user with login

B.     SQL user without login

C.     Domain user

D.     User mapped to an asymmetric key

 

Correct Answer: A

 

 

QUESTION 53

You administer a Microsoft SQL Server 2012 database. You create an availability group named

haContosoDbs. Your primary replica is available at Server01Contoso01. You need to configure

the availability group to minimize transaction latency on any available secondary databases. In

the event of a database failure, the designated secondary database should come online

automatically. Which Transact-SQL statement should you use?

 

A.       ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON ‘Server01Contoso01’

WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)

B.       ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON ‘Server01Contoso01’

WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)

C.       ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON ‘Server01Contoso01’

WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)

D.       ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON ‘Server01Contoso01’

WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)

 

Correct Answer: A

 

 

QUESTION 54

You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a

SQL Azure database into a data warehouse. The source system contains redundant or

inconsistent data. When the package finds invalid data, the row containing the invalid data must

be omitted but it must also be written to a text file for further analysis. You need to establish the

best technique to log these invalid rows while keeping the amount of development effort to a

minimum. What should you do?

 

A.       Add an OnError event handler to the SSIS project.

B.       Use an msi file to deploy the package on the server.

C.       Open a command prompt and run the gacutil command.

D.       Open a command prompt and run the dtutil /copy command.

E.       Open a command prompt and run the dtexec /rep /conn command.

F.        Open a command prompt and run the dtexec /dumperror /conn command.

G.       Open a command prompt and execute the package by using the SQL Log provider and

running the dtexecui.exe utility.

H.       Create a reusable custom logging component and use it in the SSIS project.

I.         Configure the SSIS solution to use the Project Deployment Model.

J.        Configure the output of a component in the package data flow to use a data tap.

K.       Run the dtutil command to deploy the package to the SSIS catalog and store the

configuration in SQL Server.

 

Correct Answer: J

 

 

QUESTION 55

To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS)

package logging methodology. The methodology has the following requirements:

 

Ÿ   Centralized logging in SQL Server

Ÿ   Simple deployment

Ÿ   Availability of log information through reports or T-SQL

Ÿ   Automatic purge of older log entries

Ÿ   Configurable log details

 

You need to configure a logging methodology that meets the requirements while minimizing the

amount of deployment and development effort. What should you do?

 

A.       Use the gacutil command.

B.       Use the dtutil /copy command.

C.       Use the Project Deployment Wizard.

D.       Create an OnError event handler.

E.       Create a reusable custom logging component.

F.        Run the package by using the dtexec /rep /conn command.

G.       Run the package by using the dtexec /dumperror /conn command.

H.       Run the package by using the dtexecui.exe utility and the SQL Log provider.

I.         Add a data tap on the output of a component in the package data flow.

J.        Deploy the package by using an msi file.

K.       Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to

store the configuration.

 

Correct Answer: H

 

 

 

QUESTION 56

You are developing a SQL Server Integration Services (SSIS) package. To process complex scientific

data originating from a SQL Azure database, a custom task component is added to the project.

You need to ensure that the custom component is deployed on a test environment correctly.

What should you do?

 

A.       Add an OnError event handler to the SSIS project.

B.       Use an msi file to deploy the package on the server.

C.       Open a command prompt and run the gacutil command.

D.       Open a command prompt and run the dtutil /copy command.

E.       Open a command prompt and run the dtexec /rep /conn command.

F.        Open a command prompt and run the dtexec /dumperror /conn command.

G.       Open a command prompt and execute the package by using the SQL Log provider and

running the dtexecui.exe utility.

H.       Create a reusable custom logging component and use it in the SSIS project.

I.         Configure the SSIS solution to use the Project Deployment Model.

J.        Configure the output of a component in the package data flow to use a data tap.

K.       Run the dtutil command to deploy the package to the SSIS catalog and store the

configuration in SQL Server.

 

Correct Answer: C

 

 

QUESTION 57

You are troubleshooting an existing SQL Server Integration Services (SSIS) package. On several

occasions, the package execution does not finish and no data seems to have been transferred.

You need to ensure that package logging occurs. Your solution must minimize deployment and

development efforts. What should you do?

 

A.       Add an OnError event handler to the SSIS project.

B.       Use an msi file to deploy the package on the server.

C.       Open a command prompt and run the gacutil command.

D.       Open a command prompt and run the dtutil /copy command.

E.       Open a command prompt and run the dtexec /rep /conn command.

F.        Open a command prompt and run the dtexec /dumperror /conn command.

G.       Open a command prompt and execute the package by using the SQL Log provider and

running the dtexecui.exe utility.

H.       Create a reusable custom logging component and use it in the SSIS project.

I.         Configure the SSIS solution to use the Project Deployment Model.

J.        Configure the output of a component in the package data flow to use a data tap.

K.       Run the dtutil command to deploy the package to the SSIS catalog and store the

configuration in SQL Server.

 

Correct Answer: A

QUESTION 58

You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of

rows from a SQL Azure database, The project uses the Package Deployment Model. This project is

deployed to SQL Server on a test server. You need to ensure that the project is deployed to the

SSIS catalog on the production server. What should you do?

 

A.       Add an OnError event handler to the SSIS project.

B.       Use an msi file to deploy the package on the server.

C.       Open a command prompt and run the gacutil command.

D.       Open a command prompt and run the dtutil /copy command.

E.       Open a command prompt and run the dtexec /rep /conn command.

F.        Open a command prompt and run the dtexec /dumperror /conn command.

G.       Open a command prompt and execute the package by using the SQL Log provider and

running the dtexecui.exe utility.

H.       Create a reusable custom logging component and use it in the SSIS project.

I.         Configure the SSIS solution to use the Project Deployment Model.

J.        Configure the output of a component in the package data flow to use a data tap.

K.       Run the dtutil command to deploy the package to the SSIS catalog and store the

configuration in SQL Server.

 

Correct Answer: I

 

 

QUESTION 59

You are developing a SQL Server Integration Services (SSIS) package that imports data into a data

warehouse. You add an Execute SQL task to the control flow. The task must execute a simple

INSERT statement. The task has the following requirements:

 

Ÿ   The INSERT statement must use the value of a string package variable. The variable name is

StringVar.

Ÿ   The Execute SQL task must use an OLE DB Connection Manager.

 

In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only

parameter. You must configure the SQLStatement property of the Execute SQL task. Which SQL

statement should you use?

 

A.     INSERT INTO dbo.Table (variablevalue) VALUES ($Project::StringVar)

B.     INSERT INTO dbo.Table (variablevalue) VALUES (@StringVar)

C.     INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)

D.     INSERT INTO dbo.Table (variablevalue) VALUES (?)

 

Correct Answer: D

 

 

QUESTION 60

DRAG DROP

You are designing an extract, transform, load (ETL) process with SQL Server Integration Services

(SSIS). Two packages, Package A and Package B, will be designed. Package A will execute Package

B. Both packages must reference a file path corresponding to an input folder where files will be

located for further processing. You need to design a solution so that the file path can be easily

configured with the least administrative and development effort. 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_image002

 

Correct Answer:

clip_image004

 

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…