Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 251:

  In which of the following statements about audit information stored in an SQL Server 2012 environment is TRUE?

  A. It is located in a file specified in the File Path property of the Audit.
  B. It is located in the Audit table in the MSDB database.
  C. Each login has an audit record, which is viewable under Logins -> Audit.
  D. SQL 2012 does not store any audit information.
  A. It is located in a file specified in the File Path property of the Audit.

  ---

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

• Question 252:

  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.

  On Wednesday at 10:00 hours, the development team requests you to refresh the database on a development server by using the most recent version.

  You need to ensure that your backup will continue if any invalid checksum is encountered.

  Which backup option should you use?

  A. STANDBY
  B. Differential
  C. FULL
  D. CHECKSUM
  E. BULK_LOGGED
  F. CONTINUE_AFTER_ERROR
  G. SIMPLE
  H. DBO_ONLY
  I. COPY_ONLY
  J. SKIP
  F. CONTINUE_AFTER_ERROR

  ---

  References: http://msdn.microsoft.com/en-us/library/ms186865.aspx http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backuprestorebase.continueaftererror.aspx

• Question 253:

  You administer all the deployments of Microsoft SQL Server 2012 in your company.

  You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off-loaded from the primary database to another server. You also need to be able to add indexes to the secondary database.

  Which configuration should you use?

  A. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
  B. Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
  C. Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
  D. Two servers configured in different data centers SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
  E. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
  F. SQL Server that includes an application database configured to perform transactional replication
  G. SQL Server that includes an application database configured to perform snapshot replication
  H. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
  F. SQL Server that includes an application database configured to perform transactional replication

  ---

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

• Question 254:

  Every SQL Server 2012 database must have a datafile and which of the following file types associated with it?

  A. log file
  B. index file
  C. transaction log file
  D. instance file
  C. transaction log file

• Question 255:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 database.

  All database traffic to the SQL Server must be encrypted by using secure socket layer (SSL) certificates or the connection must be refused.

  Network administrators have deployed server certificates to the Windows store of all Windows servers on the network from a trusted Certificate Authority. This is the only Certificate Authority allowed to distribute certificates on the network.

  You enable the Force Encryption flag for the MSSQLServer protocols, but client computers are unable to connect. They receive the following error message:

  "A connection was successfully established with the server, but then an error occurred during the pre-login handshake, (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft

  SQLServer)"

  You notice the following entry in the SQL Server log:

  "A self-generated certificate was successfully loaded for encryption."

  You need to configure SQL Server to encrypt all client traffic across the network.

  You also need to ensure that client computers are able to connect to the server by using a trusted certificate.

  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://thesqldude.com/2012/04/21/setting-up-ssl-encryption-for-sql-server-using-certificates-issuestips-tricks/

• Question 256:

  You administer a Microsoft SQL Server instance.

  You need to enable users to execute a stored procedure without granting them EXECUTE rights. You need to do this in a way that allows you to audit the original caller of the stored procedure.

  What should you do?

  A. Grant EXECUTE to the Windows Group.
  B. Sign the stored procedure with a certificate. Create a certificate account. Grant EXECUTE to the certificate account.
  C. Create a role. Grant EXECUTE to the role. Grant the role to the users.
  D. Create the stored procedure using EXECUTE AS. Grant EXECUTE to the user under whom the stored procedure will run.
  D. Create the stored procedure using EXECUTE AS. Grant EXECUTE to the user under whom the stored procedure will run.

  ---

  EXECUTE AS Clause (Transact-SQL)

  In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.

  By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in

  managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit

  permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module.

  Reference:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql

• Question 257:

  You administer a Microsoft Azure SQL Database instance named Inventory that contains a stored procedure named p_AddInventory.

  Users need to be able to SELECT from all tables in the database and execute the stored procedure.

  You need to grant only the necessary permissions.

  What should you do?

  A. Grant EXECUTE permission on p_AddInventory to all users. Grant VIEW DEFINITION to all users.
  B. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.
  C. Add all users to the db_owner role.
  D. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datareader role.
  D. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datareader role.

  ---

  The db_datareader role allows a user to be able to issue a SELECT statement against all tables and views in the database. References: https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/

• Question 258:

  You administer a Microsoft SQL Server database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo owned by

  UserB. GetCustomerOrderInfo selects data from both tables.

  You create a new user named UserC.

  You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to UserC.

  Which permission or permissions should you assign to UserC? Each correct answer presents part of the solution.

  A. The Select permission on Customers
  B. The Execute permission on GetCustomerOrderInfo
  C. The Take Ownership permission on Customers
  D. The Control permission on GetCustomerOrderInfo
  E. The Take Ownership permission on Orders
  F. The Select permission on Orders
  A. The Select permission on Customers
  B. The Execute permission on GetCustomerOrderInfo

  ---

  How Permissions Are Checked in a Chain When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

  Due to ownership chaining, you would only need to give Execute permissions to UserC to access the Orders table since UserB is the owner. You would also need to assign Select permission on the Customers table, as it is owned by UserA.

  References: https://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

• Question 259:

  You administer a Microsoft SQL Server instance.

  You need to configure a new database to support FILETABLES.

  Which three actions should you perform? Each correct answer presents part of the solution.

  A. Disable FILESTREAM on the Database.
  B. Enable FILESTREAM on the Server Instance.
  C. Configure the Database for Partial Containment.
  D. Create a non-empty FILESTREAM file group.
  E. Enable Contained Databases on the Server Instance.
  F. Set the FILESTREAM directory name on the Database.
  B. Enable FILESTREAM on the Server Instance.
  D. Create a non-empty FILESTREAM file group.
  F. Set the FILESTREAM directory name on the Database.

  ---

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

• Question 260:

  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.
  B. Set the Readable Secondary property of HA/Server02 to Read-intent only.

  ---

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