We're so confident of our products that we provide no hassle product exchange.
Microsoft 070-457 Exam Overview:
| Certification Vendor: | Microsoft |
|---|---|
| Exam Name: | Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 |
| Exam Number: | 70-457 |
| Exam Price: | $150-$165 USD |
| Available Languages: | German, English, Japanese, French, Chinese (Simplified), Portuguese (Brazil) |
| Exam Format: | Drag and drop, Case studies, Build list, Active screen, Multiple choice |
| Exam Duration: | 170 minutes |
| Related Certifications: | MCSA: SQL Server 2012 MCSE: Business Intelligence MCSE: Data Platform |
| Certificate Validity Period: | Retired (Jan 31, 2021); MCSA certification valid for lifetime once earned |
| Real Exam Qty: | 40-60 |
| Passing Score: | 700 |
| Recommended Training: | SQL Server 2012 Transition Training Microsoft Official Curriculum |
| Exam Registration: | Microsoft Learn Exam Page Pearson VUE Microsoft Portal |
| Sample Questions: | Microsoft 070-457 Sample Questions |
| Exam Way: | Proctored exam at Pearson VUE test centers or online via OnVUE; retired and no longer available for registration |
| Pre Condition: | Hold any valid MCTS certification on SQL Server 2008 |
| Official Syllabus URL: | https://learn.microsoft.com/en-us/credentials/certifications/exams/70-457 |
Microsoft 070-457 Exam Syllabus Topics:
| Section | Weight | Objectives |
|---|---|---|
| Create Database Objects | 27-32% | - Create functions and triggers - Create stored procedures - Create and alter indexes - Create and modify views - Design and implement tables |
| Work with Data | 28-33% | - Query data using SELECT statements - Modify data using INSERT, UPDATE, DELETE - Implement subqueries and joins - Apply built-in functions and aggregate functions - Manage transactions and error handling |
| Manage and Maintain Databases | 20-25% | - Implement security principles - Configure SQL Server instances - Implement high availability features - Monitor SQL Server activity - Manage backups and restores |
| Troubleshoot and Optimize Queries | 15-20% | - Identify and resolve performance issues - Optimize indexes and statistics - Analyze execution plans - Use query hints and execution plans |
Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 Sample Questions:
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.)
Build List and Reorder:
Explanation:
--Burgos - NO
Reference: http://thesqldude.com/2012/04/21/setting-up-ssl-encryption-for-sql-server-using-certificatesissues-tips-tricks/
You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured.
When SQL Server Agent jobs fail, the error messages returned by the job steps are truncated. The
following error message is an example of the truncated error message:
"Executed as user CONTOSO\ServiceAccount. ...0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-
2011. All rights reserved. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source
UserImport Description Code 0x00000000 Source Log Import Activity Descript... The package execution
fa... The step failed."
You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs.
What should you do?
- A. Expand agent logging to include information from all events.
- B. Disable the Limit size of job history log feature.
- C. Configure event forwarding.
- D. Configure output files.
Explanation: Only visible for BraindumpQuiz members. You can sign-up / login (it's free).
You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data. BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. You need to ensure that the new column is assigned a GUID for existing rows. Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:
Explanation:
--Burgos - NO
Verified answer as correct. Actually, in the real world, you don't have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it.
Reference: http://msdn.microsoft.com/en-us/library/ms190273.aspx
You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of the
XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.
Which code segment should you use?
To answer, type the correct code in the answer area.
- A. Declare @XML1=XML(Sales.InvoiceSchema)
- B. DECLARE @XML1 XML @XML1 = Sales.InvoiceSchema CREATE XML SCHEMA COLLECTION XML1 AS @XML1
Explanation: Only visible for BraindumpQuiz members. You can sign-up / login (it's free).
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 and 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 the Regions table. Which Transact-SQL statement should you use?
- A. DENY SELECT ON Schema::Customers FROM UserA
- B. REVOKE SELECT ON Schema::Customers FROM UserA
- C. EXEC sp_droprolemember 'Sales', 'UserA'
- D. DENY SELECT ON Object::Regions FROM Sales
- E. DENY SELECT ON Object::Regions FROM UserA
- F. REVOKE SELECT ON Object::Regions FROM Sales
- G. REVOKE SELECT ON Schema::Customers FROM Sales
- H. REVOKE SELECT ON Object::Regions FROM UserA
- I. EXEC sp_addrolemember 'Sales', 'UserA'
- J. DENY SELECT ON Schema::Customers FROM Sales
By Harvey
