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

Ensurepass

QUESTION 21

You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb

contains a schema named Customers that has a table named Regions. A user named UserA is a

member of a role named Sales. UserA is granted the Select permission on the Regions table. The

Sales role is granted the Select permission on the Customers schema. You need to ensure that the

following requirements are met:

 

Ÿ   The Sales role does not have the Select permission on the Customers schema.

Ÿ   UserA has the Select permission on the Regions table.

 

Which Transact-SQL statement should you use?

 

A.       DENY SELECT CN Object::Regions FROM Sales

B.       DENY SELECT CN Schema::Customers FROM Sales

C.       REVOKE SELECT ON Object::Regions FROM Sales

D.       REVOKE SELECT ON Schema::Customers FROM Sales

E.       DENY SELECT CN Object::Regions FRCM UserA

F.        DENY SELECT CN Schema::Customers FRCM UserA

G.       REVOKE SELECT ON Object::Regions FROM UserA

H.       REVOKE SELECT ON Schema::Customers FOR UserA

I.         EXEC sp addrolemember ‘Sales’, ‘UserA’

J.        EXEC sp droprolemember ‘Sales’. ‘UserA’

 

Correct Answer: D

 

 

QUESTION 22

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on

a storage area network (SAN). The financial database has the following characteristics:

 

Ÿ   A data file of 2 terabytes is located on a dedicated LUN (drive D).

Ÿ   A transaction log of 10 GB is located on a dedicated LUN (drive E).

Ÿ   Drive D has 1 terabyte of free disk space.

Ÿ   Drive E has 5 GB of free disk space.

 

The database is continually modified by users during business hours from Monday through Friday

between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The

Finance department loads large CSV files into a number of tables each business day at 11:15

hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of

data to the database. These data load operations must occur in the minimum amount of time. A

full database backup is performed every Sunday at 10:00 hours. Backup operations will be

performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to

ensure that your backup will continue if any invalid checksum is encountered. Which backup

option should you use?

 

A.       STANDBY

B.       Differential

C.       FULL

< font style="font-size: 10.5pt">D.       CHECKSUM

E.       BULK_LOGGED

F.        CONTINUE_AFTER_ERROR

G.       SIMPLE

H.       DBO_ONLY

I.         COPY_ONLY

J.        SKIP

K.       RESTART

L.        Transaction log

M.     NO_CHECKSUM

N.      NORECOVERY

 

Correct Answer: F

 

 

 

 

QUESTION 23

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on

a storage area network (SAN). The financial database has the following characteristics:

 

Ÿ   A data file of 2 terabytes is located on a dedicated LUN (drive D).

Ÿ   A transaction log of 10 GB is located on a dedicated LUN (drive E).

Ÿ   Drive D has 1 terabyte of free disk space.

Ÿ   Drive E has 5 GB of free disk space.

 

The database is continually modified by users during business hours from Monday through Friday

between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The

Finance department loads large CSV files into a number of tables each business day at 11:15

hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of

data to the database. These data load operations must occur in the minimum amount of time. A

< font face="Calibri">full database backup is performed every Sunday at 10:00 hours. Backup operations will be

performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. On

Wednesday at 10:00 hours, the development team requests you to refresh the database on a

development server by using the most recent version. You need to perform a full database

backup that will be restored on the development server. Which backup option should you use?

 

A.     NORECOVERY

B.     FULL

C.     NO_CHECKSUM

D.     CHECKSUM

E.      Differential

F.      BULK_LOGGED

G.     STANDBY

H.     RESTART

I.       SKIP

J.       Transaction log

K.      DBO ONLY

L.      COPY_ONLY

M.    SIMPLE

N.     CONTINUE_AFTER_ERROR

 

Correct Answer: K

 

 

 

 

 

 

 

 

QUESTION 24

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on

a storage area network (SAN). The financial database has the following characteristics:

 

Ÿ   A data file of 2 terabytes is located on a dedicated LUN (drive D).

Ÿ   A transaction log of 10 GB is located on a dedicated LUN (drive E).

Ÿ   Drive D has 1 terabyte of free disk space.

Ÿ   Drive E has 5 GB of free disk space.

 

The database is continually modified by users during business hours from Monday through Friday

between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The

Finance department loads large CSV files into a number of tables each business day at 11:15

hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of

data to the database. These data load operations must occur in the minimum amount of time. A

full database backup is performed every Sunday at 10:00 hours. Backup operations will be

performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You

implement log shipping of the financial database to another SQL Server 2012 instance. You

decide to failover to this secondary database. You need to ensure that all transactions will be

replicated to the secondary database. Which backup option should you use?

 

A.     Differential

B.     Transaction

C.     FULL

D.     SIMPLE

E.      BULK_LOGGED

F.      SKIP

G.     RESTART

H.     STANDBY

I.       CHECKSUM

J.       DBO_ONLY

K.      COPY ONLY

L.      NORECOVERY

M.    NO_CHECKSUM

N.     CONTINUE_AFTER_ERROR

 

Correct Answer: L

 

 

 

 

 

 

 

 

QUESTION 25

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on

a storage area network (SAN). The financial database has the following characteristics:

 

Ÿ   A data file of 2 terabytes is located on a dedicated LUN (drive D).

Ÿ   A transaction log of 10 GB is located on a dedicated LUN (drive E).

Ÿ   Drive D has 1 terabyte of free disk space.

Ÿ   Drive E has 5 GB of free disk space.

 

The database is continually modified by users during business hours from Monday through Friday

between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The

Finance department loads large CSV files into a number of tables each business day at 11:15

hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of

data to the database. These data load operations must occur in the minimum amount of time. A

full database backup is performed every Sunday at 10:00 hours. Backup operations will be

performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to

ensure that the backup size is as small as possible. Which backup should you perform every two

hours?

 

A.       BULK_LOGGED

B.       NO_CHECKSUM

C.       FULL

D.       RESTART

E.       CHECKSUM

F.        STANDBY

G.       DBO.ONLY

H.       NORECOVERY

I.         SIMPLE

J.        SKIP

K.       Transaction tog

L.        COPY_ONLY

M.     Differential

N.      CONTINUE_AFTER_ERROR

 

Correct Answer: B

< font style="font-size: 10.5pt" color="#000000"> 

 

 

 

 

 

 

 

 

QUESTION 26

DRAG DROP

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

warehouse. You are developing the part of the SSIS package that populates the ProjectDates

dimension table. The business key of the ProjectDates table is the ProjectName column. The

business user has given you the dimensional attribute behavior for each of the four columns in

the ProjectDates table:

 

Ÿ   ExpectedStartDate – New values should be tracked over time.

Ÿ   ActualStartDate – New values should not be accepted.

Ÿ   ExpectedEndDate – New values should replace existing values.

Ÿ   ActualEndDate – New values should be tracked over time.

 

You use the SSIS Slowly Changing Dimension Transformation. You must configure the Change

Type value for each source column. Which settings should you select?

 

To answer, select the appropriate setting or settings in the answer area.

 

Select and Place:

clip_image001

 

Correct Answer:

clip_image002

 

 

 

 

 

QUESTION 27

You are developing a data flow transformation to merge two data sources. One source contains

product data and the other source contains data about the country in which the product was

manufactured. Both data sources contain a two-character CountryCode column and both use SQL

Server. Both data sources contain an ORDER BY clause to sort the data by the CountryCode

column in ascending order. You use a Merge Join transformation to join the data.

 

clip_image004

 

You need to ensure that the Merge Join transformation works correctly without additional

transformations. What should you do? (Each answer presents a part of the solution. Choose all

that apply.)

 

A.     Set the appropriate SortKeyPosition properties on the data sources.

B.     Set the ValidateExternalMetaData property on the Merge Join transformation to True.

C.     Set the IsSorted property on both data sources.

D.     Set the MaxBuffersPerlnput property on the Merge Join transformation to 2.

 

Correct Answer: AC

 

 

QUESTION 28

You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from

two different sources. One source is hosted in a SQL Azure database. Each source contains

products for different distributors. Products for each distributor source must be combined for

insertion into a single product table destination. You need to select the appropriate data flow

transformation to meet this requirement. Which transformation types should you use? (Each

correct answer presents a complete solution. Choose all that apply.)

 

A.     Multicast

B.     Merge Join

C.     Term Extraction

D.     union All

E.      Merge

 

Correct Answer: DE

 

 

QUESTION 29

You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup

transformation. The reference data to be used in the transformation does not change. You need

to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance. What

should you do?

 

A.     Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.

B.     Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.

C.     Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.

D.     Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.

E.      Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.

 

Correct Answer: A

 

 

QUESTION 30

You are developing a SQL Server Integration Services (SSIS) package. You need to design a

package to change a variable value during package execution by using the least amount of

development effort. What should you use?

 

A.     Expression task

B.     Script task

C.     < span lang="EN-US">Execute SQL task

D.     Execute Process task

E.      Term Extraction transformation

 

Correct Answer: A

 

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…

tagged with , , , , , , ,