Microsoft 70-462 Exam Questions and Answers 2021

NEW QUESTION 1
Which of the following SQL Server 2012 tools coordinates transactions across SQL Servers in a clustered environment?

• A. SQL Server Profiler
• B. SQL Server Query Optimizer
• C. SQL Server Management Studio
• D. Microsoft Distributed Transaction Coordinator

Answer: D

Explanation: References:
http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/15/msdtc-recommendations-on-sql-failover-cluster.aspx

NEW QUESTION 2
You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions:

Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?

• A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountryFROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY COUNT(OrderAmount) DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1
• B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountryFROM(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY COUNT(o.OrderAmount) ASC) AS RnkFROM Customer cINNER JOIN Orders oON c.CustomerID = o.CustomerIDGROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) csWHERE Rnk = 1
• C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountryFROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY OrderAmount DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1
• D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountryFROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,COUNT(OrderAmount) DESC) AS OrderAmountFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID =o.CustomerIDORDER BY OrderAmount DESC

Answer: A

NEW QUESTION 3
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

NEW QUESTION 4
You administer a Microsoft SQL Server server. The MSSQLSERVER service uses a domain account named CONTOSOSQLService.
You plan to configure Instant File Initialization.
You need to ensure that Data File Autogrow operations use Instant File Initialization. What should you do? Choose all that apply.

• A. Restart the SQL Server Agent Service.
• B. Disable snapshot isolation.
• C. Restart the SQL Server Service.
• D. Add the CONTOSOSQLService account to the Perform Volume Maintenance Tasks local security policy.
• E. Add the CONTOSOSQLService account to the Server Operators fixed server role.
• F. Enable snapshot isolation.

Answer: CD

Explanation: How To Enable Instant File Initialization
References:
http://msdn.microsoft.com/en-us/library/ms175935.aspx http://www.mssqltips.com/sqlservertip/2752/effect-of-instant-file-initialization-within-sql-server/

NEW QUESTION 5
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

NEW QUESTION 6
You administer several Microsoft SQL Server 2012 servers.
You want to run scheduled checks to confirm that the databases on the servers are not configured to AutoShrink.
You need to create a policy to check the condition.
What should you do? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Answer:

Explanation: The Evaluation Mode defines the combination of 1) when the policy will be evaluated and 2) what the system will do in case of policy violation. The following evaluation modes are supported:

NEW QUESTION 7
You use a contained database named ContosoDb within a domain.
You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?

• A. SQL user without login
• B. User mapped to an asymmetric key
• C. Domain user
• D. login mapped to a virtual account

Answer: C

NEW QUESTION 8
You administer a Microsoft SQL Server 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 backup size is as small as possible. Which backup should you perform every two hours?

• A. BULK_LOGGED
• B. NO_CHECKSUM
• C. FULL
• D. RESTART
• E. CHECKSUM
• F. STANDBY
• G. DBO_ONLY
• H. NORECOVERY
• I. SIMPLE
• J. SKIP
• K. Transaction log
• L. COPY_ONLY
• M. Differential
• N. CONTINUE_AFTER_ERROR

Answer: K

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms186865.aspx http://msdn.microsoft.com/en-us/library/ms191429.aspx http://msdn.microsoft.com/en-us/library/ms179478.aspx

NEW QUESTION 9
You administer a Microsoft Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails.
You add two Windows groups as logins for the server:
CORPEmployees - All company employees
CORPHRAdmins - HR administrators only
HR Administrators are also company employees.
You need to grant users access according to the following requirements:
CORPEmployees should have SELECT access to the Employees table.
Only users in CORPHRAdmins should have SELECT access to the SalaryDetails table.
Logins are based only on Windows security groups.
What should you do?

• A. Create a database role called Employees.Add CORPEmployees to the db_datareader role.Add all company employees except HR administrators to the Employees role.Deny SELECT access to the SalaryDetails table to the Employees role.
• B. Create a database role called HRAdmins.Add all company employees except HR administrators to the db_datareader role,Add all HR administrators to the HRAdmins role.Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the db_datareader role.
• C. Create two database roles: Employees and HRAdmins.Add all company employees to the Employees role.Add HR administrators to the HRAdmins role.Grant SELECT access to all tables except SalaryDetails to the Employees role.Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the Employees role.
• D. Create a database role called Employees.Add all HR administrators to the db_datareader role.Add all company employees to the Employees role.Grant SELECT access to all tables except the SalaryDetails table to the Employees role.Deny SELECT access to the SalaryDetails table to the Employees role.

Answer: D

NEW QUESTION 10
You administer a Microsoft SQL Server database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema.
You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort.
What should you do?

• A. Create a custom database role that includes the user
• B. Deny Delete permissions on the Sales schema for the custom database role.
• C. Include the Sales schema as an owned schema for the db_denydatawriter rol
• D. Add the users to the db_denydatawriter role.
• E. Deny Delete permissions on each table in the Sales schema for each user.
• F. Create a custom database role that includes the user
• G. Deny Delete permissions on each table in the Sales schema for the custom database role.

Answer: A

NEW QUESTION 11
Which of the following is a disaster recovery solution offered in SQL Server 2012 and involves maintaining a standby copy of a primary database?

• A. AlwaysOn
• B. Log Shipping
• C. Clustering
• D. DataGuard

Answer: A

NEW QUESTION 12
You administer a Microsoft SQL Server 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

NEW QUESTION 13
You administer several Microsoft SQL Server servers. Your company has a number of offices across the world connected by using a wide area network (WAN).
Connections between offices vary significantly in both bandwidth and reliability. You need to identify the correct replication method for each scenario.
What should you do? (To answer, drag the appropriate replication method or methods to the correct location or locations in the answer area. Each replication method may be used once, more than once, or not at all.)

Answer:

Explanation:

NEW QUESTION 14
Which of the following is a prerequisite for an SQL Server 2012 installation?

• A. .NET Framework 3.5 SP1
• B. Visual Basic 6
• C. Internet Explorer 10
• D. SQL Server Integration Services

Answer: A

Explanation: References:
https://msdn.microsoft.com/en-us/library/ms143506%28v=sql.110%29.aspx

NEW QUESTION 15
You administer a Microsoft SQL Server 2012 server.
You need to install the Power View components on a stand-alone server.
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:

Explanation:

NEW QUESTION 16
You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores.
You discover performance issues when complex calculations are performed on large amounts of data under heavy system load.
You need to limit the number of cores that process the calculations. What should you configure?

• A. Max worker threads
• B. Processor affinity
• C. I/O affinity
• D. Lightweight pooling

Answer: B

Explanation: To carry out multitasking, the operating system sometimes moves process threads among different processors. This is efficient from an operating system point of view, but can reduce SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association between a thread and a processor is called processor affinity.
References:
https://msdn.microsoft.com/en-us/library/ms189629%28v=sql.110%29.aspx

NEW QUESTION 17
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. Update all stored procedure to use a TRY…CATCH block.
• 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

NEW QUESTION 18
You administer a database that has User A, B and C.
User A must be able to create new tables and stored procedures, User B must be able to select, update, delete and insert data.
User C must be able to provide windows logins to the database. To which role or roles should you add the users?

• A. db_accessadmin
• B. db_ddladmin
• C. db_datawriter
• D. db_datareader
• E. db_owner

Answer: ABCD