How should you develop the data flow?
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports unsorted
data into a data warehouse hosted on SQL Azure.
You have the following requirements:
A destination table must contain all of the data in two source tables.
Duplicate records must be inserted into the destination table.
You need to develop a data flow that imports the data while meeting the requirements.
How should you develop the data flow? (To answer, drag the appropriate transformation
from the list of transformations to the correct location in the answer area.)
You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unres
You administer a Microsoft SQL Server 2012 default instance.
The instance is hosted by a server that has a local firewall configureD. The firewall only
allows inbound connections on port 1433. The server only hosts a single instance of SQL
Server.
You need to ensure that the instance is configured to allow remote connections even if the
SQL Server is unresponsive to client connections.
What should you do? Choose all that apply.
Which three actions should you perform in sequence?
DRAG DROP
You administer a Microsoft SQL Server 2012 database.
You need to convert the database to a contained database. You also need to ensure that all
users are converted to contained users.
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 display the parameters that have been generated for the Connection Manager
HOTSPOT
You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL
Server Data Tools.
A package Connection Manager has been parameterized with project scope.
You need to display the parameters that have been generated for the Connection Manager.
What should you use? To answer, select the appropriate setting or settings in the answer area.
Which isolation level should you use?
You use Microsoft SQL Server 2012 to write code for a transaction that contains several
statements.
There is high contention between readers and writers on several tables used by your
transaction. You need to minimize the use of the tempdb space.
You also need to prevent reading queries from blocking writing queries.
Which isolation level should you use?
Which four actions should you perform in sequence?
DRAG DROP
You administer a Microsoft SQL Server 2012 clustered instance that has two nodes named
Node 1 and Node 2.
Node 1 fails and the cluster fails over to Node 2.
You need to replace Node 1 and add it to the cluster.
Which four 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 ensure that package logging occurs
You are troubleshooting an existing SQL Server Integration Services (SSIS) package. On
several occasions, the package execution does not finish and no data seems to have been
transferred.
You need to ensure that package logging occurs. Your solution must minimize deployment
and development efforts.
What should you do?
Which backup option should you use?
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 implement log shipping of the financial database to another SQL Server 2012 instance.
You decide to failover to this secondary database.
You need to ensure that all transactions will be replicated to the secondary database.
Which backup option should you use?
You need to establish the best technique to log these invalid rows while keeping the amount of development eff
You are implementing a SQL Server Integration Services (SSIS) package that loads data
hosted in a SQL Azure database into a data warehouse.
The source system contains redundant or inconsistent data. When the package finds invalid
data, the row containing the invalid data must be omitted but it must also be written to a text
file for further analysis.
You need to establish the best technique to log these invalid rows while keeping the amount
of development effort to a minimum.
What should you do?
You need to ensure that the job step can run using a different user account
You administer a Microsoft SQL Server 2012 database.
You have a SQL Server Agent job instance that runs using the service account. You have a
job step within the job that requires elevated permissions.
You need to ensure that the job step can run using a different user account.
What should you use?