Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 171:

  You administer a Microsoft SQL Server 2012 database.

  You provide temporary securityadmin access to User1 to the database server.

  You need to know if User1 adds logins to securityadmin.

  Which server-level audit action group should you use?

  A. SERVER_STATE_CHANGE_GROUP
  B. SERVER_PRINCIPAL_IMPERSONATION_GROUP
  C. SUCCESSFUL_LOGIN_GROUP
  D. SERVER_ROLE_MEMBER_CHANGE_GROUP
  D. SERVER_ROLE_MEMBER_CHANGE_GROUP

• Question 172:

  You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance.

  The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance.

  What should you do?

  A. Configure the application as data-tiered.
  B. Open port 1433 on the Windows firewall on the server.
  C. Configure the named SQL Server instance to use an account that is a member of the Domain Admins group.
  D. Start the SQL Server Browser Service.
  D. Start the SQL Server Browser Service.

• Question 173:

  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:

  

  

• Question 174:

  You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.

  

  You discover that all files except Sales_2.ndf are corrupt.

  You need to recover the corrupted data in the minimum amount of time.

  What should you do?

  A. Perform a restore from a full backup.
  B. Perform a transaction log restore.
  C. Perform a file restore.
  D. Perform a filegroup restore.
  A. Perform a restore from a full backup.

• Question 175:

  You develop a Microsoft SQL Server 2012 database.

  You need to create a batch process that meets the following requirements:

  Returns a result set based on supplied parameters.

  Enables the returned result set to perform a join with a table.

  Which object should you use?

  A. inline user-defined function
  B. stored procedure
  C. Table-valued user-defined function
  D. Scalar user-defined function
  C. Table-valued user-defined function

• Question 176:

  You administer a Microsoft SQL Server 2012 database.

  The database uses SQL Server Agent jobs to perform regular FULL and LOG backups. The database uses the FULL recovery model.

  You plan to perform a bulk import of a very large text file. You need to ensure that the following requirements are met during the bulk operation:

  The database transaction log is minimally affected.

  The database is online and all user transactions are recoverable. All transactions are fully recoverable prior to the import.

  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:

  

  

• Question 177:

  You administer a Microsoft SQL Server 2012 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
  B. Use Node A to install SQL Server on Node
  C. Run the Add Node to SQL Server Failover Cluster Wizard on Node
  D. Use Cluster Administrator to add a new Resource Group to Node
  C. Run the Add Node to SQL Server Failover Cluster Wizard on Node

• Question 178:

  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.

• Question 179:

  You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.)

  

  The procedure can be called within other transactions.

  You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails.

  Which code segment should you add to line 14?

  A. IF @@TRANCOUNT = 0
  B. IF (XACT_STATE ( ) ) = 0
  C. IF (XACT_STATE ( ) ) = 1
  D. IF @@TRANCCUNT = l
  C. IF (XACT_STATE ( ) ) = 1

• Question 180:

  You generate a daily report according to the following query: You need to improve the performance of the query. What should you do?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  A. Option A