Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 131:

  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

  ---

  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

• Question 132:

  You administer a Microsoft SQL Server database server.

  When errors that have severity level of 18 or higher occur, SQL Server must send an email message to the database administrators.

  You need to configure SQL Server to send the email messages.

  What should you configure?

  A. Alerts and operators in SQL Server Agent
  B. An Extended Events session
  C. Policies under Policy-Based Management
  D. SQL Mail
  A. Alerts and operators in SQL Server Agent

  ---

  The SQL Server Agent must be configured to use Database Mail to send alerts to an operator via e-mail.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail

• Question 133:

  You are implementing a SQL Server two-node failover cluster.

  You need to choose a quorum configuration.

  Which configuration should you use?

  A. Distributed File System (DFS)
  B. Node Majority
  C. Node and Disk Majority
  D. Cluster Shared Volume (CSV)
  C. Node and Disk Majority

  ---

  Node and Disk Majority is recommended for clusters with an even number of nodes.

  Incorrect Answers:

  A, D: These are not quorum configuration choices.

  B: Node Majority is recommended for clusters with an odd number of nodes.

  References: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc731739(v=ws.11)

• Question 134:

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

  All objects in Contoso are in the dbo schema.

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

  Which Transact-SQL statement should you use?

  A. GRANT EXECUTE ON Schema::dbo TO BillingUsers
  B. EXEC sp_addrolemember 'db_procexecutor', 'BillingUsers'
  C. GRANT EXECUTE ON INFORMATION_SCHEMA.ROUTINES TO BillingUsers
  D. GRANT EXECUTE ON Schema::Contoso TO BillingUsers
  A. GRANT EXECUTE ON Schema::dbo TO BillingUsers

  ---

  If you want to do it on schema level:

  GRANT EXECUTE ON SCHEMA ::dbo TO

  Incorrect:

  Not B: DB_Executor is none of the predefined SQL Server database role

  Not C, Not D: Incorrect schema.

  References: http://www.sqlservercentral.com/articles/Permissions/107472/

• Question 135:

  DRAG DROP

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

  Select and Place:

  

  

  ---

  Reference: http://msdn.microsoft.com/en-us/library/ms190941.aspx http://msdn.microsoft.com/en-us/library/ms189852.aspx

• Question 136:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 server that has a database named Contoso. The Contoso database has a table named EmployeeSalary in a schema named HumanResources.

  You need to create a script that writes audit events into the application log whenever data in the EmployeeSalary table is modified.

  Which four 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.)

  Select and Place:

  

  

• Question 137:

  You administer a Microsoft SQL Server 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. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  A. OptionA

  ---

  Syntax: GRANT permission [ ,...n ] ON SCHEMA :: schema_name

  TO database_principal [ ,...n ]

  [ WITH GRANT OPTION ]

  [ AS granting_principal ]

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql

• Question 138:

  DRAG DROP

  You have a server that has SQL Server 2016 installed.

  You need to configure native SQL Server backups for a database named Customers. The solution must use backup encryption.

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

  Select and Place:

  

  

  ---

  The following are prerequisites for encrypting a backup:

  Create a Database Master Key for the master database:

  Create a certificate or asymmetric Key to use for backup encryption.

  You can encrypt a backup when creating the backup of a database in any of the following dialog boxes:

  Back Up Database (Backup Options Page) On the Backup Options page, you can select Encryption, and specify the encryption algorithm and the certificate or asymmetric key to use for the encryption.

  Using Maintenance Plan Wizard When you select a backup task, on the Options tab of the Define Backup ()Task page, you can select Backup Encryption, and specify the encryption algorithm and the certificate or key to use for the

  encryption.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption

• Question 139:

  You have the following table.

  

  You need to create a full-text index for the table.

  Which two statements should you execute? Each correct answer presents part of the solution.

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  E. OptionE
  D. OptionD
  E. OptionE

  ---

  The CREATE FULLTEXT INDEX ON table_name command creates a full-text index on a table or indexed view in a database in SQL Server.

  Example: The following example creates a full-text catalog, production_catalog, in the AdventureWorks sample database. The example then creates a full-text index that uses this new catalog.

  CREATE FULLTEXT CATALOG production_catalog;

  GO

  CREATE FULLTEXT INDEX ON Production.ProductReview

  ..

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql

• Question 140:

  You administer two Microsoft SQL Server 2012 servers. Each server resides in a different, untrusted domain.

  You plan to configure database mirroring.

  You need to be able to create database mirroring endpoints on both servers.

  What should you do?

  A. Configure the SQL Server service account to use Network Service.
  B. Use a server certificate.
  C. Use a database certificate.
  D. Configure the SQL Server service account to use Local System.
  B. Use a server certificate.