You need to design an ETL process that meets the requirements
You are designing an extract, transform, load (ETL) process for loading data from a SQL
Server database into a large fact table in a data warehouse each day with the prior day’s
sales data.
The ETL process for the fact table must meet the following requirements:
Load new data in the shortest possible time.
Remove data that is more than 36 months old.
Ensure that data loads correctly.
Minimize record locking.
Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements. What should you do?
(More than one answer choice may achieve the goal. Select the BEST answer.)
You need to ensure that database users and required server logins are migrated to Windows Azure SQL Database
You administer a Microsoft SQL Server 2012 database instance.
You plan to migrate the database to Windows Azure SQL Database. You verify that all
objects contained in the database are compatible with Windows Azure SQL Database.
You need to ensure that database users and required server logins are migrated to Windows
Azure SQL Database.
What should you do?
You need to design the fact table to meet the requirements
You are designing a fact table in a SQL Server database.
The fact table must meet the following requirements:
Include a columnstore index.
Allow users to choose up to 10 dimension tables and up to five facts at one time.
Maximize performance of queries that aggregate measures by using any of the 10
dimensions.
Support billions of rows.
Use the most efficient design strategy.
You need to design the fact table to meet the requirements. What should you do? (More
than one answer choice may achieve the goal. Select the BEST answer.)
Which three actions should you perform in sequence?
DRAG DROP
You are validating whether a SQL Server Integration Services (SSIS) package named
Master.dtsx in the SSIS catalog is executing correctly.
You need to display the number of rows in each buffer passed between each data flow
component of the package.
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.)
Which Transact-SQL statement should you use?
You administer a Microsoft SQL Server 2012 database named Orders.
Orders contains a table named OrderShip that is defined as follows:
A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL.
Customers require a procedure that will return orders for all customers from a specified
country. You create a new procedure:
Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum
amount of resources.
Which Transact-SQL statement should you use?
Which option or options should you choose in the Login Properties of the login for User1?
HOTSPOT
You administer a Microsoft SQL Server 2012 instance.
You need to configure an existing SQL Authenticated Login that meets the following
requirements:
Enforces password policy
No password expiration enforcement
Resets the default database to AdventureWorks database
Which option or options should you choose in the Login Properties of the login for User1?
(To answer, configure the appropriate option or options in the dialog box in the answer area.)
Which configuration type should you use?
HOTSPOT
You are designing a SQL Server Integration Services (SSIS) package configuration strategy.
The package configuration must meet the following requirements:
Include multiple properties in a configuration.
Force packages to load all settings in the configuration.
Support Encrypting File System (EFS) formats.
You need to select the appropriate configuration.
Which configuration type should you use?
To answer, select the appropriate option from the drop-down list in the dialog box.
Which option of the BACKUP LOG command should you use?
You administer a Microsoft SQL Server 2012 database. The database is currently configured
to log ship to a secondary server.
You are preparing to cut over to the secondary server by stopping log-shipping and bringing
the secondary database online. You want to perform a tail-log backup.
You need to leave the primary database in a restoring state.
Which option of the BACKUP LOG command should you use?
You need to design the SSIS solution to meet the requirements
You are designing a SQL Server Integration Services (SSIS) solution that will load multiple
Online Transactional Processing (OLTP) data sources into a SQL Server data mart.
You have the following requirements:
Ensure that the process supports the creation of an exception report that details possible
duplicate key values, null ratios within columns, and column-length distributions of values.
Ensure that users can generate the exception report in an XML format.
Use the minimum development effort.
You need to design the SSIS solution to meet the requirements. What should you do? (More
than one answer choice may achieve the goal. Select the BEST answer.)
Which options should you select on the Options page of the Restore Database window?
HOTSPOT
You administer a Microsoft SQL Server database.
The database is in the Simple recovery mode.
You schedule the following backup plan:
Full backup every day at midnight
Differential backups every hour on the hour, except at midnight
The database fails at 20:45 hours.
You need to use SQL Server Management Studio (SSMS) to begin restoring the database to
a new server instance with a minimum amount of data loss.
Which options should you select on the Options page of the Restore Database window? (To
answer, configure the appropriate option or options in the dialog box in the answer area.)