You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You
discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to
reduce fragmentation. You need to achieve this goal without taking the index offline. Which
Transact-SQL batch should you use?

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?

You administer all the deployments of Microsoft SQL Server 2012 in your company. A database
contains a large product catalog that is updated periodically. You need to be able to send the entire
product catalog to all branch offices on a monthly basis. Which configuration should you use?

You administer all the deployments of Microsoft SQL Server 2012 in your company. You need to
ensure that data changes are sent to a non-SQL Server database server in near real time. You also
need to ensure that data on the primary server is unaffected. Which configuration should you use?

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 offloaded 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?

You administer all the deployments of Microsoft SQL Server 2012 in your company. You have two
servers in the same data center that hosts your production database. You need to ensure that the
database remains available if a catastrophic server failure or a disk failure occurs. You also need to
maintain transactional consistency of the data across both servers. You need to achieve these goals
without manual intervention. Which configuration should you use?

You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema.
Some users must be prevented from deleting records in any of the tables in the Sales schema. You
need to manage users who are prevented from deleting records in the Sales schema. You need to
achieve this goal by using the minimum amount of administrative effort. What should you do?

You administer a Microsoft SQL Server 2012 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? Choose all that
apply.

You create an availability group named HaContoso that has replicas named Server01/HA,
Server02/HA, and Server03/HA. Currently, Server01l/HA is the primary replica. You need to ensure
that the following requirements are met:
• Backup operations occur on Server02/HA.
• If Server02/HA is unavailable, backup operations occur on Server03/HA.
• Backup operations do not occur on Server01/HA.
How should you configure HaContoso?

DRAG DROP
You administer a Microsoft SQL Server database. You want to import data from a text file to the
database. You need to ensure that the following requirements are met:
• Data import is performed by using a stored procedure.
• Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the
appropriate data import command or recovery model to the appropriate location or locations in the
answer area. Each data import command or recovery model 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.)