Online Access Free 070-457 Exam Questions

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo. ModifyData meets the following requirements:
Does not return an error
Closes all opened transactions
Which Transact-SQL statement should you use?

• A.BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@ERROR != 0
  ROLLBACK TRANSACTION;
  END CATCH
• B.BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@TRANCOUNT = 0
  ROLLBACK TRANSACTION;
  THROW;
  END CATCH
• C.BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@ TRANCOUNT = 0
  ROLLBACK TRANSACTION;
  END CATCH
• D.BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@ERROR != 0
  ROLLBACK TRANSACTION;
  THROW;
  END CATCH

You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?

• A.CREATE TABLE DocumentStore
  (
  [Id] INT NOT NULL PRIMARY KEY,
  [Document] VARBINARY(MAX) NULL
  )
  GO
• B.CREATE TABLE DocumentStore
  (
  [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
  [Document] VARBINARY(MAX) FILESTREAM NULL
  )
  GO
• C.CREATE TABLE DocumentStore AS FileTable
• D.CREATE TABLE DocumentStore
  (
  [Id] hierarchyid,
  [Document] NVARCHAR NOT NULL
  )
  GO

You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured.
When SQL Server Agent jobs fail, the error messages returned by the job steps do not provide the required
detail. The following error message is an example error message:
"The job failed. The Job was invoked by User CONTOSO\ServiceAccount. The last step to run was step 1
(Subplan_1)."
You need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained.
What should you do?

• A.Expand agent logging to include information from all events.
• B.Configure event forwarding.
• C.Configure output files.
• D.Disable the Limit size of job history log feature.

DRAG AND DROP You administer a Microsoft SQL Server 2012 server. A variety of issues occur from time to time in the production environment. You need to identify the appropriate tool for each issue. Which tool or tools should you use? (To answer, drag the appropriate tool or tools to the correct issue or issues in the answer area. Each tool may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)
Select and Place:

Correct Answer:

Explanation:
--Burgos - NO

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 Availability Mode property of HA/Server01 to Asynchronous commit.
• C.Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections.
• D.Set the Readable Secondary property of HA/Server02 to Read-intent only.