Exam Code: 70 462 exam (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Administering Microsoft SQL Server 2012 Databases
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass microsoft 70 462 Exam.
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Microsoft 70-462 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 70-462 Exam Dumps (PDF & VCE):
Available on:
http://www.surepassexam.com/70-462-exam-dumps.html
Q11. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server.
What should you create?
A. An Alert
B. A Resource Pool
C. An Extended Event session
D. A Server Audit Specification
E. A SQL Profiler Trace
F. A Database Audit Specification
G. A Policy
Answer: C
Q12. You administer a Microsoft SQL Server 2012 database. The database is currently configured to log ship to a secondary server.
You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online. You want to perform a tail-log backup.
You need to leave the primary database in a restoring state. Which option of the BACKUP LOG command should you use?
A. NO_TRUNCATE
B. NORECOVERY
C. STANDBY
D. FORMAT
Answer: B
Q13. You administer a Microsoft SQL Server 2012 instance.
You need to configure a new database to support FILETABLES.
What should you do? Choose all that apply.
A. Disable FILESTREAM on the Database.
B. Enable FILESTREAM on the Server Instance.
C. Configure the Database for Partial Containment.
D. Create a non-empty FILESTREAM file group.
E. Enable Contained Databases on the Server Instance.
F. Set the FILESTREAM directory name on the Database.
Answer: BDF
Q14. You administer a Microsoft SQL Server 2012 instance named SQL2012. You are in the process of migrating a database from a SQL Server 2008 instance named SQL2008 to the SQL2012 instance.
You have upgraded a database from the SQL2008 instance by using the side-by-side migration technique.
You need to migrate the SQL Server logins from the SQL2008 instance to the SQL2012 instance.
What should you do?
A. Back up the master database on the SQL2008 instance. Restore the master database on the SQL2012 instance.
B. Use the Transfer Logins task in a Microsoft SQL Server Integrated Services package.
C. Use sp_grantlogin.
D. Use xp_logininfo.
Answer: A
Q15. You administer a Microsoft SQL Server 2012 database instance. You create a new user named UserA.
You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server agent jobs owned by UserA
To which role should you add UserA?
A. DatabaseMailUserRole
B. ServerGroupAdministratorGroup
C. SQLAgentUserRole
D. Securityadmin
Answer: C
Q16. You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure.
You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.
B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
D. Grant the db_owner role on the database to User1.
E. Grant the sysadmin role on the database to User1.
Answer: BC
Q17. You administer a Microsoft SQL Server 2012 instance that has multiple databases. You have a two-node SQL Server failover cluster. The cluster uses a storage area network (SAN).
You discover I/O issues. The SAN is at capacity and additional disks cannot be added.
You need to reduce the I/O workload on the SAN at a minimal cost.
What should you do?
A. Move user databases to a local disk.
B. Expand the tempdb data and log files.
C. Modify application code to use table variables.
D. Move the tempdb files to a local disk.
Answer: D
Q18. 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.
D. Run the sp_who command from a query window.
E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
Answer: E
Q19. 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 UserA is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Object::Regions FROM Sales
C. REVOKE SELECT ON Schema::Customers FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. REVOKE SELECT ON Object::Regions FROM UserA
G. DENY SELECT ON Schema::Customers FROM Sales
H. DENY SELECT ON Schema::Customers FROM UserA
I. EXEC sp_addrolemember 'Sales', 'UserA'
J. EXEC sp droprolemember 'Sales', 'UserA'
Answer: H
Q20. You administer all the deployments of Microsoft SQL Server 2012 in your company.
A database contains a large product catalog that is updated periodically.
You need to be able to send the entire product catalog to all branch offices on a monthly basis.
Which configuration should you use?
A. . Two servers configured in the same data center
. A primary server configured to perform log-shipping every 10 minutes
. A backup server configured as a warm standby
B. . SQL Server that includes an application database configured to perform transactional replication
C. . Two servers configured in the same data center
. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
. One server configured as an Active Secondary D
. Two servers configured in a Windows Failover Cluster in the same data center
. SQL Server configured as a clustered instance
D. . SQL Server that includes an application database configured to perform snapshot replication
E. . Two servers configured in different data centers
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
. One server configured as an Active Secondary
F. . Two servers configured on the same subnet
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
G. . Two servers configured in different data centers
. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
Answer: E