Same Microsoft 70-457 Practice Tests Questions From Microsoft Centre 100% Pass! (181-197)

Do you want to pass Microsoft 70-457 Exam ? If you answered YES, then look no further. Braindump2go offers you the best 70-457 exam questions which cover all core test topics and certification requirements. All REAL questions and answers from Microsoft Exam Center will help you be a 70-457 certified!

Vendor: Microsoft
Exam Code: 70-457
Exam Name: Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 Exam

QUESTION 181
You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed.
You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.
You need to configure the SQL Server environment to support these packages.
What should you do?

A.    Create SSIS configuration files for the packages.
B.    Create an Integration Services catalog.
C.    Install Data Quality Services.
D.    Install Master Data services.

Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/hh479588.aspx ¹
http://msdn.microsoft.com/en-us/library/hh213290.aspx ²
http://msdn.microsoft.com/en-us/library/hh213373.aspx ³

QUESTION 182
You work as a senior database administrator at Lead2pass.com.
The Lead2pass.com network consists of a single domain named ABC.com.
ABC.com makes use of Microsoft SQL Server 2012 in their environment.
You are running a training exercise for Microsoft SQL Server 2012 junior administrators.
You are discussing the use of Slowly Changing Dimension Transformation Outputs.
One of the output options causes Derived Column transformations to create columns for the expired row and the current row indicators.
Which option is the output that causes this?

A.    Unchanged Output
B.    Inferred Member Updates Output
C.    Historical Attributes Inserts Output
D.    Fixed Attribute Output
E.     Changing Attributes Updates Output

Answer: C

QUESTION 183
You are designing a data warehouse that uses SQL Server 2012.
The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the INT data type, and the aggregate column uses the MONEY data type.
You need to increase the speed of data retrieval from the factSales table.
Which index type should you add to the table?

A.    Full text
B.    Spatial
C.    Non-clustered
D.    Clustered

Answer: C

QUESTION 184
Which statements best describe SQL Server Development Tools (SSDT)? (Choose all that apply.)

A.    SSDT is an extension of the SQL Server Management Studio that can be used to create SSIS packages by means of a special wizard.
B.    SSDT is a special edition of the SQL Server Management Studio, designed to provide an improved user experience to developers who are not particularly familiar with database administration.
C.    SSDT is a special edition of Visual Studio, distributed with SQL Server 2012, providing a rich database development tool set.
D.    SSDT is a new service in SQL Server 2012 that can be used to perform SQL Server maintenance tasks, such as data movements and similar data management processes.

Answer: C

QUESTION 185
You are designing a data warehouse that uses SQL Server 2012.
The data warehouse contains a table named factSales that stores product sales.
The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the int data type and the aggregate column uses the money data type.
You need to increase the speed of data retrieval from the factSales table.
Which index type should you add to the table?

A.    Clustered
B.    Semantic search
C.    Nonclustered
D.    XML

Answer: C

QUESTION 186
You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012.
Quality Assurance (QA) testers in the company must have permission to perform the following tasks on specific projects:
- View and validate projects and packages
- View Environments and Environment variables
- Execute packages
You need to grant the minimum possible privileges to the QA testers.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A.    In the SSISDB database, add QA Tester logons to the ssis_admin role.
B.    In the msdb database, add QA Tester logons to the db_ssisoperator role.
C.    Grant Modify permission in the projects to the QA Tester logons.
D.    Grant Read permission in the SSIS catalog folder, the projects, and the Environments to the QA
Tester logons.
E.    Grant Execute permission in the projects to the QA Tester logons.
F.    In the msdb database, add QA Tester logons to the db_ssisItduser role.

Answer: BD

QUESTION 187
You work as a senior database administrator at Lead2pass.com.
The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You are running a training exercise for Microsoft SQL Server 2012 junior administrators.
You are discussing the use of Slowly Changing Dimension Transformation Outputs.
One of the output options are used to update the record in the lookup table.
Which of the following is the output option?

A.    Unchanged Output
B.    Inferred Member Updates Output
C.    Historical Attributes Inserts Output
D.    Fixed Attribute Output
E.    Changing Attributes Updates Output

Answer: E

QUESTION 188
You work as a database administrator at Lead2pass.com.
The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You are in the process of creating a SQL Server Integration Services (SSIS) package.
The package will be configured upload a file to a table in a SQL Azure database.
You need to make sure that the code includes an event handler that makes use of an executable to raise the event when its verification is completed.
Which of the following is the event handler that you should use?

A.    The OnPostExecute event handler.
B.    The OnTaskFailed event handler.
C.    The OnPreExecute event handler.
D.    The OnPostValidate event handler.

Answer: D

QUESTION 189
You work as a senior database administrator at Lead2pass.com.
The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You are running a training exercise for Microsoft SQL Server 2012 junior administrators.
You are discussing replication type that is used when subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
Which of the following is the replication type being discussed?

A.    Snapshot replication.
B.    Transactional replication.
C.    Merge replication.
D.    Peer-to-peer replication

Answer: C

QUESTION 190
You work as a senior database developer at Lead2pass.com.
The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You are running a training exercise for Microsoft SQL Server 2012 junior developers.
You are discussing the SorLead2passeyPosition property for a data flow.
Which of the following represents the data type for this property?

A.    Integer
B.    IDTSExternalMetadataColumn100
C.    String
D.    Boolean

Answer: A

QUESTION 191
You work as a database administrator at Lead2pass.com.
The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You have received reports regarding a current existing SQL Server Integration Services (SSIS) package not completing its execution, and not transferring data.
You have decided to make use of the Project Deployment Model.
Which of the following is TRUE with regards to using the Project Deployment Model?

A.    CLR integration is required on the database engine.
B.    CLR integration is not required on the database engine.
C.    Events that are created by the package are captured automatically and saved to the catalog during execution.
D.    Packages are executed using the dtExec and DTExecUI execution utilities.

Answer: AC

QUESTION 192
You need to extract data from a table in a SQL Server 2012 database.
What connection manager types can you use? (Choose all that apply.)

A.    An ODBC connection manager
B.    An OLE DB connection manager
C.    A File connection manager
D.    An ADO.NET connection manager

Answer: ABD

QUESTION 193
You maintain a SQL Server Integration Services (SSIS) package.
The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS). The package includes custom scripts that must be upgraded.
You need to upgrade the package to SQL Server 2012.
Which tool should you use?

A.    SQL Server dtexec utility (dtexec.exe)
B.    SQL Server DTExecUI utility (dtexecui.exe)
C.    SSIS Upgrade Wizard in SQL Server Data Tools
D.    SQL Server Integration Services Deployment Wizard

Answer: C
Explanation:
Use the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS) packages and SQL Server 2008 Integration Services (SSIS) packages to the package format for the current (2012) release of SQL Server Integration Services.

QUESTION 194
You are installing the Data Quality Server component of Data Quality Services.
You need to provision the hardware and install the software for the server that runs the Data Quality Server.
You must ensure that the minimum Data Quality Server prerequisites are met.
What should you do?

A.    Install Microsoft Internet Explorer 6.0 SP1 or later.
B.    Install SQL Server 2012 Database Engine.
C.    Make sure the server has at least 4 GB of RAM.
D.    Install Microsoft SharePoint Server 2010 Standard Edition.

Answer: B
Explanation:
Data Quality Server Minimum System Requirements
* SQL Server 2012 Database Engine.
* Memory (RAM):
Minimum: 2 GB
Recommended: 4 GB or more
Note: SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2012 that contains the following two components: Data Quality Server and Data Quality Client.

QUESTION 195
You are preparing to install SQL Server 2012 Master Data Services (MDS).
You need to ensure that the database requirements are met.
What should you install?

A.    Microsoft SharePoint Server 2010 Standard Edition SP1
B.    Microsoft SharePoint Server 2010 Enterprise Edition SP1
C.    SQL Server 2012 Data Center (64-bit) x64 on the database server
D.    SQL Server 2012 Enterprise (64-bit) x64 on the database server

Answer: D

QUESTION 196
You are a database developer of a Microsoft SQL Server 2012 database.
You are designing a table that will store Customer data from different sources.
The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.
You need to ensure that the table has no duplicate CustomerID within a SourceID .
You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL stat.ement should you use?

A.    CREATE TABLE Customer
(SourceID int NOT NULL IDENTITY,
CustomerID int NOT NULL IDENTITY,
CustomerName varchar(255) NOT NULL);
B.    CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName varchar(255) NOT NULL);
C.    CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);
D.    CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
(SourceID, CustomerID));

Answer: D

QUESTION 197
You are designing a data warehouse that uses SQL Server 2012.
You are preparing to update the contents of a fact table that contains a non-clustered columnstore index.
You need to run an update statement against the table.
What should you do first?

A.    Pause the columnstore index.
B.    Change the recovery model of the database to Bulk-logged.
C.    Change the non-clustered columnstore index to be a clustered columnstore index.
D.    Drop the columnstore index.

Answer: D

http://www.braindump2go.com/70-457.html

1. http://msdn.microsoft.com/en-us/library/hh479588.a spx
2. http://msdn.microsoft.com/en-us/library/hh213290.a spx
3. http://msdn.microsoft.com/en-us/library/hh213373.a spx