You need to design a table structure to ensure that certain users can see sales data for only certain district
You are designing a data warehouse hosted on SQL Azure. The data warehouse currently
includes the dimUser 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?
What should you do?
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 also need to ensure that data loss is minimal.
What should you do?
You need to redesign the dimension to enable the full historical reporting of changes to multiple customer att
You are reviewing the design of a customer dimension table in an existing data warehouse
hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to customer
attributes such as Postcode.
You need to redesign the dimension to enable the full historical reporting of changes to
multiple customer attributes including Postcode.
What should you do?
You need to ensure that the transactional database is restored
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 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are
corrupted on the transactional database. You need to ensure that the transactional database
is restored. You also need to ensure that data loss is minimal.
What should you do?
You need to ensure that the indexing strategy meets the requirements
You are implementing the indexing strategy for a fact table in a data warehouse. The fact
table is named Quotes. The table has no indexes and consists of seven columns:
• [ID]
• [QuoteDate]
• [Open]
• [Close]
• [High]
• [Low]
• [Volume]
Each of the following queries must be able to use a columnstore index:
• SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate]
BETWEEN ‘20100101’ AND ‘20101231’.
• SELECT AVG([High] – [Low]) AS [AverageRange] FROM Quotes WHERE
[QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.
• SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate]
BETWEEN ‘20100101’ AND ‘20101231’.
You need to ensure that the indexing strategy meets the requirements. The strategy must
also minimize the number and size of the indexes.
What should you do?
You need to ensure that the reporting database is restoreD
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:
One of the hard disk drives that stores the reporting database fails at 16:40 hours.
You need to ensure that the reporting database is restoreD. You also need to ensure that
data loss is minimal.
What should you do?
You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all
You are designing an enterprise star schema that will consolidate data from three
independent data marts. One of the data marts is hosted on SQL Azure.
Most of the dimensions have the same structure and content. However, the geography
dimension is slightly different in each data mart.
You need to design a consolidated dimensional structure that will be easy to maintain while
ensuring that all dimensional data from the three original solutions is represented.
What should you do?
Which Transact-SQL statement should you use?
You administer a Microsoft SQL Server 2012 database.
You create an availability group named haContosoDbs. Your primary replica is available at
Server01 \Contoso01.
You need to configure the availability group to have the highest availability. You also need to
ensure that no data is lost.
Which Transact-SQL statement should you use?
Which three actions should you perform in sequence?
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.)
You need to configure a logging methodology that meets the requirements while minimizing the amount of deploym
To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a
logging methodology is put in place.
The methodology has the following requirements:
The deployment process must be simplified.
All the logs must be centralized in SQL Server.
Log data must be available via reports or T-SQL.
Log archival must be automated.
You need to configure a logging methodology that meets the requirements while minimizing
the amount of deployment and development effort.
What should you do?