Online Access Free 70-457 Exam Questions

You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar(max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do?

• A.Set the Merge agent on the problem subscribers to use the slow link agent profile.
• B.Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
• C.Change the Merge agent on the problem subscribers to run continuously.
• D.Set the Remote Connection Timeout on the Publisher to 0.

You administer all the deployments of Microsoft SQL Server 2012 in your company. You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail. You also need to minimize the amount of storage used by the 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 * A primary server configured to perform log-shipping every 10 minutes * A backup server configured as a warm standby
• C.* Two servers configured in a Windows Failover Cluster in the same data center * SQL Server configured as a clustered instance
• D.* 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
• E.* SQL Server that includes an application database configured to perform transactional replication
• F.* SQL Server that includes an application database configured to perform snapshot replication
• G.* Two servers configured on the same subnet * SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• H.* Two servers configured in different data centers * SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named Productld, ProductName, and CreatedDateTime. The table contains a unique constraint on the combination of ProductName and CreatedDateTime. You need to modify the Products table to meet the following requirements:
Remove all duplicates of the Products table based on the ProductName column.
Retain only the newest Products row. Which Transact-SQL query should you use?

• A.WITH CTEDupRecords AS (
  SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
  FROM Products
  GROUP BY ProductName
  HAVING COUNT(*) > 1
  )
  DELETE p
  FROM Products p
  JOIN CTEDupRecords cte ON
  p.ProductName = cte.ProductName
• B.WITH CTEDupRecords AS (
  SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
  FROM Products
  GROUP BY ProductName
  HAVING COUNT(*) > 1
  )
  DELETE p
  FROM Products p
  JOIN CTEDupRecords cte ON
  p.ProductName = cte.ProductName
  AND p.CreatedDateTime > cte.CreatedDateTime
• C.WITH CTEDupRecords AS (
  SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
  FROM Products
  GROUP BY ProductName
  )
  DELETE p
  FROM Products p
  JOIN CTEDupRecords cte ON
  p.ProductName = cte.ProductName
• D.WITH CTEDupRecords AS (
  SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
  FROM Products
  GROUP BY ProductName
  HAVING COUNT(*) > 1
  )
  DELETE p
  FROM Products p
  JOIN CTEDupRecords cte ON
  cte.ProductName = p.ProductName
  AND cte.CreatedDateTime > p.CreatedDateTime

You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and backup schedule are configured as shown in the following table: At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database. You need to ensure that the databases are restored. You also need to ensure that data loss is minimal. What should you do?

• A.Perform a point-in-time restore.
• B.Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
• C.Perform a partial restore.
• D.Restore the latest full backup.
• E.Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
• F.Restore the latest full backup. Then, restore the latest differential backup.
• G.Perform a page restore.
• H.Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.

You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed
column that references a lookup table by using an INNER JOIN against another table.
What should you do?

• A.Reference a user-defined function within the computed column.
• B.Add a default constraint to the computed column that implements hard-coded CASE statements.
• C.Create a BEFORE trigger that maintains the state of the computed column.
• D.Add a default constraint to the computed column that implements hard-coded values.