★ 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: https://www.certleader.com/70-462-dumps.html


Q41. You administer a single server that contains a Microsoft SQL Server 2012 default instance on which several production databases have been deployed. 

You plan to install a new ticketing application that requires the deployment of a database on the server. The SQL login for this application requires sysadmin permissions. 

You need to ensure that the login for the ticketing application cannot access other production databases. 

What should you do? 

A. Use the SQL Server default instance and enable Contained Databases. 

B. Use the SQL Server default instance and configure a user-defined server role. Add the login for the ticketing application to this role. 

C. Install a new named SQL Server instance on the server. 

D. Install a new default SQL Server instance on the server. 

Answer: C 


Q42. 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 


Q43. You administer a Microsoft SQL Server 2012 database. 

Users report that a billing application becomes unresponsive during busy times of the day. 

While investigating, you notice large number of processes taking or waiting for table locks. 

You suspect that SQL Server is assigning stronger locks to queries. 

You start a SQL Profiler trace. 

Which event should you select? 

A. Deadlock graph 

B. Lock: Escalation 

C. Lock: Timeout 

D. Lock: Deadlock 

Answer: C 


Q44. You administer a Microsoft SQL Server 2012 database named Contoso that contains a single user-defined database role namedBillingUsers. 

All objects in Contoso are in the dbo schemA. 

You need to grant EXECUTE permission for all stored procedures in Contoso to BillingUsers. 

Which Transact-SQL statement should you use? 

A. GREATE ROLE proc_caller GRANT EXECUTE ON Schema : : dbo TO proc_caller ALTER ROLE proc_caller ADD MEMBER BillingUsers 

B. GRANT EXECUTE ON INFORMATION_SCHEMA.ROUTINES TO BillingUsers 

C. EXEC sp_addrolemember 'executor' , 'BillingUsers' 

D. GREATE ROLE proc_caller GRANT EXECUTE ON ALL PROCEDURES TO proc_caller ALTER MEMBER BillingUsers ADD TO ROLE proc_caller 

Answer: D 


Q45. You administer a Microsoft SQL Server 2012 database. You want to make a full backup of the database to a file on disk. 

In doing so, you need to output the progress of the backup. 

Which backup option should you use? 

A. STATS 

B. COMPRESSION 

C. CHECKSUM 

D. IN IT 

Answer: A 


Q46. You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. 

You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements: 

. Does not return an error 

. Closes all opened transactions 

Which Transact-SQL statement should you use? 

A. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH 

B. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; THROW; END CATCH 

C. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW; END CATCH 

D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH 

Answer: D 


Q47. You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). 

The financial database has the following characteristics: 

A data file of 2 terabytes is located on a dedicated LUN (drive D). 

A transaction log of 10 GB is located on a dedicated LUN (drive E). 

Drive D has 1 terabyte of free disk space. 

Drive E has 5 GB of free disk space. 

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. 

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database. 

These data load operations must occur in the minimum amount of time. 

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. 

You need to ensure that the minimum amount of data is lost. 

Which recovery model should the database use? 

A. FULL 

B. DBO_ONLY 

C. CONTINUE_AFTER_ERROR 

D. CHECKSUM 

E. NO_CHECKSUM 

F. SIMPLE 

G. Transaction log 

H. SKIP 

I. RESTART 

J. COPY_ONLY 

K. NORECOVERY 

L. BULK_LOGGED 

M. Differential 

N. STANDBY 

Answer: L 


Q48. 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 


Q49. You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. 

There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. 

You also need to prevent reading queries from blocking writing queries. 

Which isolation level should you use? 

A. SERIALIZABLE 

B. SNAPSHOT 

C. READ COMMITTED SNAPSHOT 

D. REPEATABLE READ 

Answer: C 


Q50. You administer all the deployments of Microsoft SQL Server 2012 in your company. You have two servers in the same data center that hosts your production database. 

You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs. 

You also need to maintain transactional consistency of the data across both servers. 

You need to achieve these goals without manual intervention. 

Which configuration should you use? 

A. . Two servers configured in a Windows Failover Cluster in the same data center 

. SQL Server configured as a clustered instance 

B. . SQL Server that includes an application database configured to perform transactional replication 

C. . 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 

D. . Two servers configured in different data centers 

. SQL Server Availability Group configured in Synchronous-Commit Availability Mode 

. One server configured as an Active Secondary 

E. . 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 

F. . Two servers configured in different data centers 

. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode 

G. . SQL Server that includes an application database configured to perform snapshot replication 

H. . Two servers configured on the same subnet 

. SQL Server Availability Group configured in Synchronous-Commit Availability Mode 

Answer: H