70-458|You administer a SQL Server 2012 server that contains a database

Question: 20

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 remove the Select permission for UserA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions. Which Transact-SQL statement should you use?

A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Schema::Customers FROM UserA
C. EXEC sp_addrolemember ‘Sales’, ‘UserA’
D. REVOKE SELECT ON Object::Regions FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. EXEC sp_droproiemember ‘Sales’, ‘UserA’
G. REVOKE SELECT ON Schema::Customers FROM UserA
H. DENY SELECT ON Object::Regions FROM Sales
I. DENY SELECT ON Schema::Customers FROM Sales
J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer: D

70-458|What should you create for each application?

Question: 19

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications’ ability to access data.
The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the applications.

What should you create for each application?

A. Synonyms
B. Common table expressions
C. Views
D. Temporary tables

Answer: C

Section: Extract and transform data
Explanation
Explanation/Reference:
References:
http://msdn.microsoft.com/en-us/library/ms190174.aspx

70-458 | Note: This question is part of a series of questions that use the same set of answer choices.

Question : 18

Note: This question is part of a series of questions that use the same set of answer choices.An answer choice may be correct for more than one question in the series.You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database.The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.The recovery model and backup schedule are configured as shown in the following table:

Untitled

One of the hard disk drives that stores the reporting database fails at 16:40 hours. You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal.What should you do?

A. Perform a page restore.
B. Perform a partial restore.
C. Perform a point-in-time restore.
D. Restore the latest full backup.
E. Restore the latest full backup. Then, restore the latest differential backup.
F. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
G. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
H. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.

Answer : E

70-458 | You administer a Microsoft SQL Server 2012 database. You create an availability group named haContosoDbs.

Question : 17

You administer a Microsoft SQL Server 2012 database. You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01. You need to configure the availability group to have the highest availability.You also need to ensure that no data is lost.Which Transact-SQL statement should you use?

A. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON ‘Server01
\Contoso01’ WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)
B. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON ‘Server01 \Contoso01’ WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)
C. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON ‘Server01
\Contoso01’ WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)
D. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON ‘Server01 \Contoso01’ WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)

Answer : C

70-458 | You administer several Microsoft SQL Server 2012 database servers.

Question : 16

You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN).Many of the tables involved in replication use the XML and varchar (max) data types.Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors.What should you do?

A. Set the Merge agent on the problem subscribers to use the slow link agent profile.
B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
C. Change the Merge agent on the problem subscribers to run continuously.
D. Set the Remote Connection Timeout on the Publisher to 0.

Answer : A

70-458 | You need to localize the Picture Sharer app in the required language. Which actions should you perform?

Question : 15

You need to localize the Picture Sharer app in the required language. Which actions should you perform? (Each correct answer presents part of the solution. Choose all that apply.)

A. Add a Uid attribute to any XAML elements that must be localized.
B. Create a folder named fr-CA at the root of the project.
C. Create a resource file named resources.res.
D. Create a resource file named resources.resw.
E. Create a folder named es-ES at the root of the project.
F. Add a Name attribute to any XAML elements that must be localized.

Answer : ABC

 

70-458 | You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.

Question : 16

You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day. Referential integrity must be enforced declaratively. You need to design a solution that can join a single time dimension to both fact tables. What should you do?

A. Join the two fact tables.
B. Merge the fact tables.
C. Create a time dimension that can join to both fact tables at their respective granularity.
D. Create a surrogate key for the time dimension.

Answer: C

70-458 | You are the data steward for a Business Intelligence project. You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file.

Question : 15

You are the data steward for a Business Intelligence project.You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file.A Data Quality Services (DQS) knowledge base has been created to support this project.You need to produce the CSV file with the least amount of development effort. What should you do?

A. Create an Integration Services package and use a Data Profiling transform.
B. Create a custom .NET application based on the Knowledgebase class.
C. Create a data quality project.
D. Create a CLR stored procedure based on the Knowledgebase class.
E. Create a Master Data Services (MDS) business rule.

Answer : C

70-458 Sample Question : 14

Question : 14

You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS). You need to complete the post-installation configuration. What should you do?

A. Run the Configuration component in the Data Quality Client.
B. Install ADOMD.NET.
C. Run the Data Quality Server Installer.
D. Make the data available for DQS operations.

Answer : C

70-458 Sample Question : 13

Question : 13

You are creating a SQL Server Master Data Services (MDS) model for a company. The source data for the company is stored in a single table that contains the manager-to-subordinate relationships. You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?

A. Natural
B. Explicit
C. Parent
D. Recursive

Answer : D

70-458 Sample Question : 12

Question : 12

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. Execute SQL task
D. Execute Process task
E. Term Extraction transformation

Answer : A

70-458 Sample Question : 11

Question : 11

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.

Answer : A

70-458 Sample Question : 10

Question : 10

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 remove the Select permission for UserA on the Regions table.You also need to ensure that UserA can still access all the tables in the Customers schema,including the Regions table, through the Sales role permissions.Which Transact-SQL statement should you use?

A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Schema::Customers FROM UserA
C. EXEC sp_addrolemember ‘Sales’, ‘UserA’
D. REVOKE SELECT ON Object::Regions FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. EXEC sp_droproiemember ‘Sales’, ‘UserA’
G. REVOKE SELECT ON Schema::Customers FROM UserA
H. DENY SELECT ON Object::Regions FROM Sales
I. DENY SELECT ON Schema::Customers FROM Sales
J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer : D

70-458 Sample Question : 9

Question : 9

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 Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema.Which Transact-SQL statement should you use?

A. REVOKE SELECT ON Schema::Customers FROM UserA
B. DENY SELECT ON Object::Regions FROM UserA
C. EXEC sp_addrolemember ‘Sales’, ‘UserA’
D. DENY SELECT ON Object::Regions FROM Sales
E. REVOKE SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema::Customers FROM Sales
G. DENY SELECT ON Schema::Customers FROM UserA
H. EXEC sp_droprolemember ‘Sales’, ‘UserA’
I. REVOKE SELECT ON Object::Regions FROM Sales
J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer : F

70-458 Sample Question : 8

Question : 8

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application.You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.What should you do?

A. Query the sys.dm_db_index_usage_stats DMV.
B. Query the sys.dm_db_missing_index_details DMV.
C. Use the Database Engine Tuning Advisor.
D. Query the sys.dm_db_missing_index_columns DMV.

Answer : C

70-458 Sample Question : 7

Question : 7

You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment.All DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs access to the SQL Server.You need to create a server role named SpecialDBARole that can perform the following functions:- View all databases.- View the server state.- Assign GRANT,DENY, and REVOKE permissions on logins.You need to add DOMAIN\JrDBAs to the server role.You also need to provide the least level of privileges necessary.Which SQL statement or statements should you use? Choose all that apply.

A. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
D. GRANT VIEW DEFINITION TO [SpecialDBARole];
E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
F. GRANT VIEW SERVER STATE,VIEW ANY DATABASE TO [SpecialDBARole];

Answer : BCF

Search Words: 70-458 Certification real exam questions answers practice test braindumps, free Q&A online dumps download and free 70-458 Certification discount coupon code available.

Leave a Comment.

17 + eighteen =