70-462 | Microsoft 70-462 Free Practice Questions 2021

NEW QUESTION 1
You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica.
You have multiple queries that read data and produce reports from the database.
You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. What should you do?

• A. Set the Availability Mode property of HA/Server02 to Asynchronous commit.
• B. Set the Readable Secondary property of HA/Server02 to Read-intent only.
• C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections.
• D. Set the Availability Mode property of HA/Server01 to Asynchronous commit.

Answer: B

NEW QUESTION 2
You administer a Microsoft SQL Server failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster.
An additional node named Node C has been added to the existing cluster.
You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?

• A. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node A.
• B. Use Node A to install SQL Server on Node C.
• C. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
• D. Use Cluster Administrator to add a new Resource Group to Node B.

Answer: C

Explanation: To add a node to an existing SQL Server failover cluster, you must run SQL Server Setup on the node that is to be added to the SQL Server failover cluster instance. Do not run Setup on the active node.
The Installation Wizard will launch the SQL Server Installation Center. To add a node to an existing failover cluster instance, click Installation in the left-hand pane. Then, select Add node to a SQL Server failover cluster.

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: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!'
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate';
BACKUP CERTIFICATE TDE_Certificate TO FILE = ''d:TDE_Certificate.cer'
WITH PRIVATE KEY (FILE = 'D:TDE_Certificate.key', ENCRYPTION BY PASSWORD =
'MyPassword1!');
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate; ALTER DATABASE Orders SET ENCRYPTION ON;
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. ALTER DATABASE MasterSET ENCRYPTION OFF;
• B. CREATE CERTIFICATE TDE_CertificateFROM FILE = 'd:TDE_Certificate.cer'WITH PRIVATE KEY (FILE = 'D:TDE_Certificate.key',DECRYPTION BY PASSWORD = 'MyPassword1!');
• C. CREATE CERTIFICATE TDE_CertificateWITH SUBJECT = 'TDE Certificate';USE Orders;CREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_256ENCRYPTION BY SERVERCERTIFICATE TDE_Certificate;
• D. CREATE CERTIFICATE TDE_CertificateFROM FILE = 'd:TDE_Certificate.cer';

Answer: B

NEW QUESTION 4
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 16:40 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. Restore the latest full backu
• B. Then, restore each differential backup taken before the time of failure from the most recent full backup.
• C. Perform a partial restore.
• D. Restore the latest full backup, and restore the latest differential backu
• E. Then, restore the latest log backup.
• F. Perform a point-in-time restore.
• G. Restore the latest full backup.
• H. Perform a page restore.
• 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 the latest differential backup.

Answer: H

NEW QUESTION 5
You administer a Microsoft SQL Server database.
You create an availability group named haContosoDbs. Your primary replica is available at Server01Contoso01.
You need to configure the availability group to prevent data loss. In the event of a database failure, the designed secondary database must come online automatically.
Which Transact-SQL statement should you use?

• A. ALTER AVAILABILITY GROUP haContosoDbsMODITY REPLICA ON ‘Server01’Contoso01’WITH (AVAILABILITY _MODE=ASYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC)
• B. ALTER AVAILABILITY GROUP haContosoDbsMODIFY REPLICA ON ‘Server01’Contoso01’WITH (AVAILABILITY _MODE=ASYNCHRONOUS_COMMIT, FAILOVER_MODE=MANUAL)
• C. ALTER AVAILABILITY GROUP haContosoDbsMODITY REPLICA ON ‘Server01’Contoso01’WITH (AVAILABILITY _MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC)
• D. ALTER AVAILABILITY GROUP haContosoDbsMODIFY REPLICA ON‘Server01’Contoso01’WITH (AVAILABILITY_MODE=SYNCHRONOUS_COMMIT,FAILOVER_MODE=MANUAL)

Answer: C

NEW QUESTION 6
You have an index called Employee_IDX on the table Employees. Which of the following will rebuild this index online?

• A. ALTER INDEX Employee_IDX ON Employees REBUILD WITH (ONLINE = ON);
• B. CREATE INDEX Employee_IDX ON Employees REBUILD WITH (ONLINE = ON);
• C. REBUILD ONLINE INDEX Employee_IDX ON Employees;
• D. ALTER INDEX REBUILD ONLINE Employee_IDX;

Answer: A

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

NEW QUESTION 7
Your company has a data warehouse that contains all of the sales data for your company. Several business applications read data from the data warehouse.
You plan to deploy an indexing strategy for a 2-TB table named FactSales contains a clustered index. You need to recommend a solution for index maintenance that meets the following requirements:
✑ Maintenance must be performed only when index fragmentation reaches 50 percent.
✑ Maintenance must minimize the impact on the database reads from the business applications.
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: Step 1: Partition the table
Step 2: Create a partition function
To migrate SQL Server partition definitions to SQL Data Warehouse simply: Eliminate the SQL Server partition scheme.
Add the partition function definition to your CREATE TABLE. Step 3: Create a partition scheme
Partitioning requires a partition function and a partition scheme. Step 4: Rebuild the clustered index
Rebuild the clustered index onto new partition function/scheme. References:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition https://blog.sqlrx.com/2021/02/24/sql-2021-partitioning-an-existing-table-with-a-columnstore-index/

NEW QUESTION 8
Which of the following files keeps track of all transactions that occur in an SQL Server database?

• A. Transaction Log
• B. Redo Log
• C. Archive Log
• D. Data File

Answer: A

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

NEW QUESTION 9
Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL Server database 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 the Regions table. Which Transact-SQL statement should you use?

• A. DENY SELECT ON Object::Regions FROM Sales
• B. DENY SELECT ON Schema::Customers FROM Sales
• C. REVOKE SELECT ON Object::Regions FROM Sales
• D. REVOKE SELECT ON Schema::Customers FROM Sales
• E. DENY SELECT ON Object::Regions FROM UserA
• F. DENY SELECT ON Schema::Customers FROM UserA
• G. REVOKE SELECT ON Object::Regions FROM UserA
• H. REVOKE SELECT ON Schema::Customers FROM UserA
• I. EXEC sp_addrolemember 'Sales', 'UserA'
• J. EXEC sp_droprolemember 'Sales', 'UserA'

Answer: C

NEW QUESTION 10
You administer a SQL Server 2012 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster. Which user account should you use?

• A. The BUILTINLocalService account
• B. A domain user
• C. A local administrative user
• D. The BUILTINNetworkService account

Answer: B

NEW QUESTION 11
You administer a Microsoft SQL Server database.
The database contains a table that has the following definition:

You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard. You need to ensure that the following requirements are met:
✑ The first row of the file contains the first row of data.
✑ Each record is of the same length.
✑ The date follows the U.S. date format.
✑ The file supports international characters.
What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Answer:

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms178804.aspx http://msdn.microsoft.com/en-us/library/ms187828.aspx

NEW QUESTION 12
You administer a Microsoft SQL Server instance.
An application executes a large volume of dynamic queries.
You need to reduce the amount of memory used for cached query plans.
Which three 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:

Explanation: Box 1: EXEC sp_configure 'show advanced options', 1; RECONFIGURE Box 2: sp_CONFIGURE ‘optimize for ad hoc workloads’,1
Box 3: RECONFIGURE
SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization
Every batch (T-SQL, SP etc) when ran creates execution plan which is stored in system for re-use. Due to this reason a large number of query plans are stored in system. However, there are plenty of plans which are only used once and have never re-used again. One time ran batch plans wastes memory and resources.
Let us now enable the option of optimizing ad hoc workload. This feature is available in all the versions of SQL Server 2008.
sp_CONFIGURE 'show advanced options',1 RECONFIGURE
GO
sp_CONFIGURE ‘optimize for ad hoc workloads’,1 RECONFIGURE
GO
SQL Server 2008 has feature of optimizing ad hoc workloads. References:
https://blog.sqlauthority.com/2009/03/21/sql-server-2008-optimize-for-ad-hoc-workloads-advance-performance-

NEW QUESTION 13
You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2.
You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases.
You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.
Which Transact-SQL statement should you use?

• A. USE Database2
• B. EXECUTE AS OWNER
• C. USE Database1
• D. EXECUTE AS CALLER

Answer: B

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms188354.aspx
http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-callingstoredprocedure-o

NEW QUESTION 14
You administer two Microsoft SQL Server 2012 servers named ServerA and ServerB. You use a database named AdventureWorks.
You need to prepare the AdventureWorks database for database mirroring. ServerB will act as the mirror in a mirroring partnership along with ServerA.
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 15
You administer a Microsoft SQL Server 2012 environment that contains a production SQL Server 2005 instance named SQL2005 and a development SQL Server 2012 instance named SQL2012.
The development team develops a new application that uses the SQL Server 2012 functionality. You are planning to migrate a database from SQL2005 to SQL2012 so that the development team can test their new application.
You need to migrate the database without affecting the production environment.
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 have a two-node SQL Server 2014 cluster that has an AlwaysOn availability group configured in synchronous mode.
You plan to provide a reporting solution by using a third node in the cluster.
You need to add the third node. The solution must prevent any impact on the performance of database writes. You install another server that has SQL Server installed.
Which three additional 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: Step 1: Add a new availability replica in asynchronous mode.
Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.
Incorrect: Add a new availability replica in synchronous mode.
Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.
Step 2: Set Readable Secondary to Read-intent only.
For the secondary role, select a new value from the Readable secondary drop list, as follows: Read-intent only
Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
Step 3: Configure read-only routing.
Note: Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). To support read-only routing, the availability group must possess an availability group listener. Read-only clients must direct their connection requests to this listener, and the client's connection strings must specify the application intent as "read-only." That is, they must be read-intent connection requests.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-f
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-avail

NEW QUESTION 17
You administer a Microsoft SQL Server database.
You want to import data from a text file to the database. You need to ensure that the following requirements are met:
✑ Data import is performed from a Windows batch file.
✑ Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer areAnswer: choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)

Answer:

Explanation:

NEW QUESTION 18
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages. What should you do?

• A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.
• B. Disable the guest user in the msdb database.
• C. Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile.
• D. In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile.

Answer: A