You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes
the dimllser and dimDistrict dimension tables and the factSales fact table. The dimUser table
contains records for each user permitted to run reports against the warehouse, and the dimDistrict
table contains information about sales districts. The system is accessed by users from certain
districts, as well as by area supervisors and users from the corporate headquarters. You need to
design a table structure to ensure that certain users can see sales data for only certain districts.
Some users must be permitted to see sales data from multiple districts. What should you do?

DRAG DROP
You are creating a sales data warehouse. When a product exists in the product dimension, you
update the product name. When a product does not exist, you insert a new record. In the current
implementation, the DimProduct table must be scanned twice, once for the insert and again for the
update. As a result, inserts and updates to the DimProduct table take longer than expected. You
need to create a solution that uses a single command to perform an update and an insert. How
should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate
answer choice from the list of options to the correct location or locations in the answer area. You
may need to drag the split bar between panes or scroll to view content.)

You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and
Database3. You have permissions on both Database1 and Database2. You plan to write and deploy a
stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute
other stored procedures in the other databases. You need to ensure that callers that do not have
permissions on Database1 or Database2 can execute the stored procedure. Which Transact-SQL
statement should you use?

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?

DRAG DROP
You administer three Microsoft SQL Server 2012 servers named ServerA, ServerB, and ServerC.
ServerA is the acting principal and ServerB is the mirror. You need to add ServerC as a witness to the
existing mirroring session between ServerA and ServerB. You need to achieve this goal without
delaying synchronization. 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.)

DRAG DROP
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.)

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a
storage area network (SAN). The financial database has the following characteristics:
• A data file of 2 terabytes is located on a dedicated LUN (drive D).
• A transaction log of 10 GB is located on a dedicated LUN (drive E).
• Drive D has 1 terabyte of free disk space.
• Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday
between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The
Finance department loads large CSV files into a number of tables each business day at 11:15 hours
and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to
the database. These data load operations must occur in the minimum amount of time. A full
database backup is performed every Sunday at 10:00 hours. Backup operations will be performed
every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to ensure that the
minimum amount of data is lost. Which recovery model should the database use?

You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains
a schema named Customers that has a table named Regions. A user named UserA is a member of a
role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is
granted the Select permission on the Customers schema. You need to ensure that UserA is
disallowed to select from any of the tables in the Customers schema. Which Transact-SQL statement
should you use?

DRAG DROP
You administer two Microsoft SQL Server 2012 servers named ServerA and ServerB. You use a
database named AdventureWorks. You need to prepare the AdventureWorks database for database
mirroring. ServerB will act as the mirror in a mirroring partnership along with ServerA. 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.)

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a
table named Suppliers and a column named IsActive in the Purchases schema.
You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the
Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from
Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which
Transact-SQL statement should you use?