Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 171:

  You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA.

  Currently, Server01/HA is the primary replica.

  You need to ensure that the following requirements are met:

  Backup operations occur on Server02/HA.

  If Server02/HA is unavailable, backup operations occur on Server03/HA.

  Backup operations do not occur on Server01/HA.

  How should you configure HaContoso?

  A. Set the backup preference of HaContoso to Prefer Secondary. Set the backup priority of Server02/HA to 20. Set the backup priority of Server03/HA to 10.
  B. Set the backup preference of HaContoso to Secondary only. Set the backup priority of Server02/HA to 20. Set the backup priority of Server03/HA to 10.
  C. Set the backup preference of HaContoso to Secondary only. Set the backup priority of Server02/HA to 10. Set the backup priority of Server03/HA to 20.
  D. Set the exclude replica of Server01/HA to true. Set the backup priority of Server02/HA to 10. Set the backup priority of Server03/HA to 20.
  B. Set the backup preference of HaContoso to Secondary only. Set the backup priority of Server02/HA to 20. Set the backup priority of Server03/HA to 10.

  ---

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

• Question 172:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 database.

  You use an OrderDetail table that has the following definition:

  

  You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column.

  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:

  

  

  ---

  According to these references, this answer looks correct. References: http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/ms189280.aspx

• Question 173:

  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 server. Filter queries that have a Duration value of more than 1,000.
  B. Use sp_configure to set a value for blocked process threshold. Create an extended event session.
  C. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step.
  D. Run the sp_who command from a query window.
  E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
  A. 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.

  ---

  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

• Question 174:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 database instance that uses transparent database encryption.

  You plan to move the database from the current server to a new server by using Backup and Restore.

  Select and Place:

  

  

  ---

  References: http://sqlsailor.com/2011/12/29/tdetransparent-data-encryption-in-sql-server-2012-rc-0/

• Question 175:

  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 implement log shipping of the financial database to another SQL Server 2012 instance.

  You decide to failover to this secondary database.

  You need to ensure that all transactions will be replicated to the secondary database.

  Which backup option should you use?

  A. Differential
  B. Transaction Log
  C. FULL
  D. SIMPLE
  E. SKIP
  F. RESTART
  G. STANDBY
  H. CHECKSUM
  I. DBO_ONLY
  J. COPY_ONLY
  K

  ---

  Reference: http://technet.microsoft.com/en-us/library/ms187103.aspx http://msdn.microsoft.com/en-us/library/ms191233.aspx http://msdn.microsoft.com/en-us/library/ms178117.aspx

• Question 176:

  You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.

  You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2.

  Which Transact-SQL query should you use?

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  E. OptionE
  F. OptionF
  G. OptionG
  H. OptionH
  D. OptionD

• Question 177:

  DRAG DROP

  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.

  Select and Place:

  

  

  ---

  The following illustration shows the architecture of TDE encryption.

  

  Note: In SQL:

  USE master;

  GO

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';

  go

  CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';

  go

  USE AdventureWorks2012;

  GO

  CREATE DATABASE ENCRYPTION KEY

  WITH ALGORITHM = AES_128

  ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

  GO

  ALTER DATABASE AdventureWorks2012

  SET ENCRYPTION ON;

  GO

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017

  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 = '';

  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

  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

• Question 178:

  Which of the following is the default port for an SQL Server database to communicate over?

  A. 1433
  B. 21
  C. 1344
  D. 1521
  A. 1433

  ---

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

• Question 179:

  DRAG DROP

  You administer a database named SalesDb that has users named UserA, UserB, and UserC.

  You need to ensure that the following requirements are met:

  

  UserA must be able to provide Windows logins access to the database.

  

  UserB must be able to select, update, delete, and insert data to the database tablets.

  

  UserC must be able to create new tables and stored procedures.

  You need to achieve this goal by granting only the minimum permissions required.

  To which fixed database role or roles should you add the users? (To answer, drag the appropriate user or users to the correct database role or roles. Answer 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.)

  Select and Place:

  

  

  ---

  UserA: db_accessadmin

  Members of the db_accessadmin fixed database role can add or remove access for Windows logins, Windows groups, and SQL Server logins.

  UserB: db_datareader, db_datawriter

  Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.

  Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

  UserC: db_ddladmin

  Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

  Incorrect Answers:

  Not db_owner

  Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database.

  References:

  https://technet.microsoft.com/en-us/library/ms191451(v=sql.90).aspx

  https://technet.microsoft.com/en-us/library/ms190667(v=sql.90).aspx

• Question 180:

  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.
  B. Perform a side-by-side installation of a 64-bit SQL Server 2012 instance.
  D. Detach the database from the old instance and attach it to the new instance.
  E. Change the compatibility level of the database.

  ---

  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.