Microsoft 070-457 Value Pack (Frequently Bought Together)

You administer three Microsoft SQL Server 2008 R2 instances. Database mirroring is configured in High-Safety mode with Automatic Failover between the following three servers:
SQL1 is the Principal server.
SQL2 is the mirror server.
SQL3 is the witness server.
You need to upgrade SQL1 and SQL2 to SQL Server 2012. You need to ensure that downtime is minimized during the upgrade. Which six 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.)
Build List and Reorder:

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you use?

• A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
  FROM (
  SELECT SalesOrderId, SalesAmount
  FROM DomesticSalesOrders
  UNION
  SELECT SalesOrderId, SalesAmount
  FROM InternationalSalesOrders
  ) AS p
• B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
  FROM (
  SELECT SalesOrderId, SalesAmount
  FROM DomesticSalesOrders
  UNION ALL
  SELECT SalesOrderId, SalesAmount
  FROM InternationalSalesOrders
  ) AS p
• C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders
• D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders

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 @@TRANCOUNT = 0
  ROLLBACK TRANSACTION;
  THROW;
  END CATCH
• B. BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@ERROR != 0
  ROLLBACK TRANSACTION;
  END CATCH
• C. BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@ERROR != 0
  ROLLBACK TRANSACTION;
  THROW;
  END CATCH
• D. BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@ TRANCOUNT = 0
  ROLLBACK TRANSACTION;
  END CATCH

You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:

You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

• A. Implement row-level compression.
• B. Implement Unicode Compression.
• C. Implement page-level compression.
• D. Convert all indexes to Column Store indexes.

You administer a Microsoft SQL Server 2012 server. The MSSQLSERVER service uses a domain account named CONTOSO\SQLService. You plan to configure Instant File Initialization. You need to ensure that Data File Autogrow operations use Instant File Initialization. What should you do? Choose all that apply.

• A. Add the CONTOSO\SQLService account to the Server Operators fixed server role.
• B. Disable snapshot isolation.
• C. Enable snapshot isolation.
• D. Restart the SQL Server Service.
• E. Restart the SQL Server Agent Service.
• F. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.