70-462 | Approved 70-462 Exam Questions 2021

NEW QUESTION 1
You maintain several databases on a 32-bit Microsoft SQL Server 2005 instance on a Windows Server 2008 R2 64-bit server.
You need to migrate the databases to a 64-bit SQL Server 2012 instance on the same server. You also need to ensure that the new Transact-SQL functionality in SQL Server 2012 can be used in the database after the migration.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Perform a side-by-side installation of a 32-bit SQL Server 2012 instance.
• B. Perform a side-by-side installation of a 64-bit SQL Server 2012 instance.
• C. Perform an in-place upgrade to 64-bit SQL Server 2012.
• D. Detach the database from the old instance and attach it to the new instance.
• E. Change the compatibility level of the database.

Answer: BDE

Explanation: Use detach and attach operations to upgrade a SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2 database in SQL Server 2012. After being attached to SQL Server 2012, the database is available immediately and is automatically upgraded.

NEW QUESTION 2
You administer a Microsoft SQL Server 2012 database.
You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated permissions.
You need to ensure that the job step can run using a different user account.
What should you use?

• A. A notification
• B. A schedule
• C. A job category
• D. A proxy

Answer: D

NEW QUESTION 3
You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to-subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?

• A. Natural
• B. Explicit
• C. Parent
• D. Recursive

Answer: D

NEW QUESTION 4
Which of the following is the default instance name in an SQL Server 2012 installation?

• A. SQLSERVER12
• B. MSSQLSERVER
• C. MSSQL
• D. 2012SQL

Answer: B

Explanation: References:
https://msdn.microsoft.com/en-us/library/ms143547(v=sql.110).aspx

NEW QUESTION 5
Which of the following Microsoft SQL Server 2012 components is responsible for executing scheduled administrative tasks?

• A. SQL Server Instance
• B. SQL Server Jobs File
• C. SQL Server Agent
• D. SQL Server SSIS

Answer: C

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

NEW QUESTION 6
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 7
You administer a Microsoft SQL Server database. The database is in the Simple recovery mode. You schedule the following backup plan:
✑ Full backup every day at midnight
✑ Differential backups every hour on the hour, except at midnight
The database fails at 20:45 hours.
You need to use SQL Server Management Studio (SSMS) to begin restoring the database with a minimum amount of data loss.
Which options should you select on the Options page of the Restore Database window? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Answer:

Explanation: First, restore full database backup, differential database backup and all transaction log backups WITH NORECOVERY Option. After that, bring back database online using WITH RECOVERY option.
References:
https://blog.sqlauthority.com/2009/07/15/sql-server-restore-sequence-and-understanding-norecovery-and-recove

NEW QUESTION 8
You administer a Microsoft SQL Server instance named SQL2012 that hosts an OLTP database of 1 terabyte in size.
The database is modified by users only from Monday through Friday from 09:00 hours to 17:00 hours. Users modify more than 30 percent of the data in the database during the week.
Backups are performed as shown in the following schedule:

The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete.
The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times.
When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly.
You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time.
What should you do on Saturday?

• A. Perform a differential backup at 08:59 hours.
• B. Record the LSN of the transaction log at 08:59 hour
• C. Perform a transaction log backup at 17:01 hours.
• D. Create a database snapshot at 08:59 hours.
• E. Record the LSN of the transaction log at 08:59 hour
• F. Perform a transaction log backup at 08:59 hours.
• G. Create a marked transaction in the transaction log at 08:59 hour
• H. Perform a transaction log backup at 17:01 hours.
• I. Create a marked transaction in the transaction log at 08:59 hour
• J. Perform a transaction log backup at 08:59 hours.

Answer: C

NEW QUESTION 9
Note: This question is part of a series of questions that use the same set of answers choices. An answer choice may be correct for more than one question in the series.
You administer a Microsoft SQL Server server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.
The recovery model and backup schedule are configured as shown in the following table:

One of the hard disk drives that stores the reporting database fails at 23:32 hours.
You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal. What should you do?

• A. Perform a page restore.
• B. Perform a partial restore.
• C. Perform a point-in-time restore.
• D. Restore the latest full backup.
• E. Restore the latest full backu
• F. Then, restore the latest differential backup.
• G. Restore the latest full backup, and restore the latest differential backu
• H. Then, restore the latest log backup.
• I. Restore the latest full backup, and restore the latest differential backu
• J. Then, restore each log backup taken before the time of failure from the most recent differential backup.
• K. Restore the latest full backu
• L. Then, restore each differential backup taken before the time of failure from the most recent full backup.

Answer: E

Explanation: To recover the database, you must restore the latest full backup and then restore the latest differential backup.

NEW QUESTION 10
You administer a Microsoft SQL Server instance that contains a database of confidential data. You need to enable Transparent Data Encryption.
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:

Explanation: The steps to setup TDE are: Step 1: Create a master key
We must first create the master key. It must be created in the master database. Example:
USE master; GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
Step 2: Create or obtain a certificate protected by the master key
Once the master key is created, we will go ahead and create the actual certificate in the master database, not the user database.
Example:
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'; go
USE AdventureWorks2012; GO
Step 3: Create a database encryption key and protect it by the certificate
Now, we must utilize our USE command to switch to the database, the user database, that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database.
Example: USE <DB> GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert; GO
Step 4: Set the database to use encryption Example:
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON; GO
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

NEW QUESTION 11
You administer a Microsoft SQL Server database 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 serve
• B. Filter queries that have a Duration value of more than 1,000.
• C. Use sp_configure to set a value for blocked process threshol
• D. Create an extended event session.
• E. Use the Job Activity monitor to review all processes that are actively runnin
• F. Review the Job History to find out the duration of each step.
• G. Run the sp_who command from a query window.
• H. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.

Answer: A

Explanation: 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. Incorrect:
Not B: The SQL Server lock monitor is responsible for implementing the logic to detect a blocking scenario if the ‘blocked process threshold’ value is greater than 0. However, the lock monitor only wakes up every 5 seconds to detect this condition (it is also looking for other conditions such as deadlocks). Therefore, if you set a ‘blocked process threshold’ value to 1, it will not detect a process that has been blocking for 1 second. The minimum time it can detect a blocked process is 5 seconds.
Not E: The Traceflag 1222 Shows Deadlocks, not the Duration of an query.
References: https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler

NEW QUESTION 12
You administer a single server that contains a Microsoft SQL Server 2012 default instance.
You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.
You need to ensure that the application login is unable to access other production databases. What should you do?

• A. Use the SQL Server default instance and configure an affinity mask.
• B. Install a new named SQL Server instance on the server.
• C. Use the SQL Server default instance and enable Contained Databases.
• D. Install a new default SQL Server instance on the server.

Answer: B

NEW QUESTION 13
You are a database administrator for a Microsoft SQL Server instance.
You need to ensure that data can be migrated from a production server to two reporting servers with minimal data latency. You also need to ensure that data on the reporting server is always accessible.

• A. Database Snapshot
• B. Log Shipping
• C. Availability Groups
• D. Change Data Capture

Answer: C

NEW QUESTION 14
Which of the following SQL Server 2012 tools assists DBAs in running and analyzing trace results?

• A. SSIS
• B. SQL Profiler
• C. AlwaysOn
• D. EXP

Answer: B

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

NEW QUESTION 15
You are a database administrator of a Microsoft SQL Server 2012 environment. The environment contains two servers named SQLServer01 and SQLServer02. The database Contoso exists on SQLServer01.
You plan to mirror the Contoso database between SQLServer01 and SQLServer02 by using database mirroring.
You need to prepare the Contoso database for database mirroring.
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 database named Orders. Orders contain a table named OrderShip that is defined as follows:

A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL. Customers require a procedure that will return orders for all customers from a specified country. You create a new procedure:

Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.
Which Transact-SQL statement should you use?

• A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode)WHERE CountryCode IS NOT NULL
• B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode)WHERE CountryCode IS NOT NULL
• C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)
• D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID)WHERE CountryCode IS NOT NULL

Answer: B

Explanation: Filtered statistics can improve query performance for queries that select from well-defined subsets of data. References: https://msdn.microsoft.com/en-us/library/ms190397.aspx

NEW QUESTION 17
You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:
Takes an input variable
Returns a table of values
Cannot be referenced within a view Which object should you use?

• A. Scalar-valued function
• B. Inline function
• C. User-defined data type
• D. Stored procedure

Answer: D

NEW QUESTION 18
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:

Explanation: