Microsoft 70-457 exam is very popular in Microsoft field, many Microsoft candidates choose this exam to add their credentials. There are many resource online to offering Microsoft 70-457 exam questions, through many good feedbacks, we conclude that GreatExam can help you pass your test easily with Microsoft 70-457 exam questions. Choose GreatExam to get your Microsoft 70-457 certification.
QUESTION 161
Drag and Drop Question
You administer a Microsoft SQL Server 2012 clustered instance that has two nodes named Node 1 and Node 2. Node 1 fails and the cluster fails over to Node 2.
You need to replace Node 1 and add it to the cluster.
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.)
Answer:
QUESTION 162
You administer a Microsoft SQL Server 2012 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB.
What should you do?
A. In SQL Server Management Studio, right-click the instance and select Database Settings.
Set the maximum size of the file for the transaction log.
B. In SQL Server Management Studio, right-click the database, select Properties, and then click Files.
Open the Transaction log Autogrowth window and set the maximum size of the file.
C. Use the ALTER DATABASE…SET LOGFILE command along with the midsize parameter.
D. In SQL Server Management Studio, expand the Storage leaf under the database.
Select the transaction log file and set the maximum size of the file.
Answer: B
QUESTION 163
Drag and Drop Question
You administer a Microsoft SQL Server 2012 server that has a database named Contoso.
The Contoso database has a table named EmployeeSalary in a schema named HumanResources.
You need to create a script that writes audit events into the application log whenever data in the EmployeeSalary table is modified.
Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Answer:
QUESTION 164
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
QUESTION 165
Drag and Drop Question
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.)
Answer:
QUESTION 166
You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012.
You attempt to add the [Corpnet\User1] login to the database.
However, you receive the following error message:
“User already exists in current database.”
You need to configure the [Corpnet\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 [Corpnet\User1] FOR LOGIN [Corpnet\User1];
ALTER ROLE [db_owner] ADD MEM3ER [Corpnet\User1];
B. ALTER SERVER RCLS Isysadmin] ADD MEMBER [Corpnet\User1];
C. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];
D. ALTER ROLE [db owner] ADD MEMBBR [Corpnet\User1];
Answer: C
Explanation:
Http://msdn.microsoft.com/en-us/library/ms176060.aspx
QUESTION 167
You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:
You attempt to restore the Orders database and the restore fails.
You copy the encryption file to the original location. A hardware failure occurs and so a new server must be installed and configured. After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location.
However, you are unable to access the database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ff773063.aspx
QUESTION 168
Drag and Drop Question
You administer a Microsoft SQL Server 2012 server that has multiple databases.
You need to ensure that users are unable to create stored procedures that begin with sp_.
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.)
Answer:
QUESTION 169
You administer a Microsoft SQL Server 2012 database.
You provide temporary securityadmin access to User1 to the database server.
You need to know if User1 adds logins to securityadmin.
Which server-level audit action group should you use?
A. SERVER_STATE_CHANGE_GROUP
B. SERVER_PRINCIPAL_IMPERSONATION_GROUP
C. SUCCESSFUL_LOGIN_GROUP
D. SERVER_ROLE_MEMBER_CHANGE_GROUP
Answer: D
Explanation:
http://technet.microsoft.com/en-us/library/cc280663.aspx
QUESTION 170
You administer a Microsoft SQL Server 2012 instance.
You need to stop a blocking process that has an SPID of 64 without stopping other processes. What should you do?
A. Execute the following Transact-SQL statement:
EXECUTE sp_KillSPID 64
B. Restart the SQL Server service.
C. Execute the following Transact-SQL statement:
KILL 64
D. Execute the following Transact-SQL statement:
ALTER SESSION KILL ’64’
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ms173730.aspx
QUESTION 171
Drag and Drop Question
You administer a Microsoft SQL Server 2012 server.
A variety of issues occur from time to time in the production environment.
You need to identify the appropriate tool for each issue.
Which tool or tools should you use? (To answer, drag the appropriate tool or tools to the correct issue or issues in the answer area. Each tool may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)
Answer:
QUESTION 172
You administer a Microsoft SQL Server 2012 database. Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error.
You need to identify the root cause of the issue by retrieving the error message.
What should you do?
A. Create an Extended Events session by using the sqlserver.error_reported event.
B. Create a SQL Profiler session to capture all ErrorLog and EventLog events.
C. Flag all stored procedures for recompilation by using sp_recompile.
D. Execute sp_who.
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/bb630282.aspx
QUESTION 173
You administer a Microsoft SQL Server 2012 server.
One of the databases on the server supports a highly active OLTP application.
Users report abnormally long wait times when they submit data into the application.
You need to identify which queries are taking longer than 1 second to run over an extended period of time.
What should you do?
A. use SQL Profiler to trace all queries that are processing on the server.
Filter queries that have a Duration value of more than 1,000.
B. Use sp_configure to set a value for blocked process threshold.
Create an extended event session.
C. Use the Job Activity monitor to review all processes that are actively running.
Review the Job History to find out the duration of each step.
C. Run the sp_who command from a query window.
D. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
Answer: A
Explanation:
Trace 1222 is used for deadlocks. To take slow queries better to use Profile! –\Burgos Verified the SQL Profiler and DBCC answers as correct. However, while Profiler will show this information, the best practice with Profiler is to use it short-term.
The question specifically states “over an extended period of time”.
That means Profiler wouldn’t be the best tool for this scenario.
Therefore, DBCC would be the best answer.
http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/
http://msdn.microsoft.com/en-us/library/ms188396.aspx
QUESTION 174
Drag and Drop Question
You administer a Microsoft SQL Server database that is used by an application.
Users of the application report performance issues.
You need to choose the appropriate tool for performance-tuning of SQL Server databases.
Which tool or tools should you use? (To answer, drag the appropriate tool or tools to their corresponding task or tasks in the answer area. Each tool may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)
Answer:
Explanation:
http://msdn.microsoft.com/en-us/library/ms175166.aspx
http://msdn.microsoft.com/en-us/library/ms187827.aspx
QUESTION 175
You administer a Microsoft SQL Server 2012 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB.
What should you do?
A. Execute sp_configure ‘max log size’, 2G.
B. use the ALTER DATABASE…SET LOGFILE command along with the maxsize parameter.
C. In SQL Server Management Studio, right-click the instance and select Database Settings.
Set the maximum size of the file for the transaction log.
D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files.
Open the Transaction log Autogrowth window and set the maximum size of the file.
Answer: D
QUESTION 176
You administer a Microsoft SQL Server 2012.
A process that normally runs in less than 10 seconds has been running for more than an hour.
You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked.
Which Transact-SQL statement should you use?
A. SELECT ~ FROM sys.dm_exec_sessions WHERE session_id = 60
B. DBCC OPENTRAN
C. EXEC sp_helpdb 60
D. SELECT * FROM sys.dm_exec_requests WHERE session_id = 60
Answer: D
Explanation:
The sp_helpdb – sp_helpdb [ [ @dbname= ] ‘name’ – does not accept a spid – http://technet.microsoft.com/en-us/library/ms178568.aspx
QUESTION 177
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 Configuration Manager
B. SQL Server Agent
C. SSIS Upgrade Wizard in SQL Server Management Studio
D. SQL Server DTExecUI utility (dtexecui.exe)
Answer: C
Explanation:
Use the SSIS Package Upgrade Wizard to migrate packages that were developed in SQL Server 2005 Integration Services (SSIS) and SQL Server 2008 Integration Services (SSIS) to the package format that the current release of Integration Services uses.
QUESTION 178
You install a SQL Server 2012 database engine instance on a production server.
A month later, you install SQL Server 2012 Integration Services (SSIS).
You must develop an SSIS project and deploy it to the server by using the Project Deployment model. Operations log records that are outside the configured retention period must be cleaned automatically.
You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Enable CLR Integration.
B. Enable FILESTREAM with Full Access.
C. Enable the Resource Governor.
D. Change the recovery mode of the msdb database to FULL.
E. Change the Server-wide Default Logging Level in SSISDB to Verbose.
F. Start the SQL Server Browser service.
G. Start the SQL Server Agent service.
Answer: AG
QUESTION 179
You install a SQL Server 2012 database engine instance on a production server.
A month later, you install SQL Server 2012 Integration Services (SSIS).
You must develop an SSIS project and deploy it to the server by using the Project Deployment model. Operations Log records that are outside the configured retention period must be cleaned automatically.
You need to create the SSIS catalog on the production server.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Enable XP Command Shell.
B. Enable CLR Integration.
C. Enable OLE Automation.
D. Start the SQL Server Browser service.
E. Enable Cross Database Ownership Chaining.
F. Start the SQL Server Agent service.
G. Enable Ad Hoc Remote Queries.
Answer: BF
Explanation:
http://msdn.microsoft.com/en-us/library/gg471509(v=sql.110).aspx
QUESTION 180
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. SSIS Upgrade Wizard in SQL Server 2008 BIDS
B. SSIS Upgrade Wizard in SQL Server Data Tools
C. SQL Server DTExecUI utility (dtexecui.exe)
D. SQL Server dtexec utility (dtexec.exe)
Answer: B
Microsoft 70-457 exam questions are available in PDF and VCE format. This makes it very convenient for you to follow the course and study the exam whenever and wherever you want. The Microsoft 70-457 exam questions follow the exact paper pattern and question type of the actual 70-457 certification exam, it lets you recreate the exact exam scenario, so you are armed with the correct information for the 70-457 certification exam.