Learn About Microsoft SQL Server 2008
BackBy Certification Magazine — 23 October 2008
These questions are based on 70-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and MaintenanceA Self Test Software Practice Test
Objective: Implement an SSIS Solution.
Sub-objective: Implement package logic by using variables.
Single answer, multiple-choice
You are developing a SQL Server 2008 SQL Server Integration Services (SSIS) package. You have included a Script component and an Execute SQL task in the package. The Script component calculates the number of records in a SQL Server 2008 table. You want to use the value calculated by the Script component in the Execute SQL task. What should you do?
A. Create a user variable and set the scope as the Script task.
B. Create a user variable and set the scope as the package.
C. Create a system variable and set the scope as the Script task.
D. Create a system variable and set the scope as the package.
Answer:
B
Tutorial:
You should create a user variable and set the scope as the package to ensure the value calculated by the Script component can be used by the Execute SQL task. Integration Services uses two namespaces: User and System. Variables in the User namespace are custom created by the user, but System namespace variables cannot be created. User-created variables can have literal values or beset to an expression.
When you create a variable in the User namespace, you also need to specify its scope as a package, a container or a task. When a variable is created with the package scope, it is available to all the tasks and containers in the package. Variables with a container scope are accessible by all the tasks in the container, but not to any task outside the container. When the variable is created with the task scope, it is available only to that task and not to any other task in the package.
In the given scenario, you should create a user variable and specify the scope as the package to ensure the variable can be accessed by both the Script component and the Execute SQL task.
You should not create a user variable and set the scope as the Script task because, when a variable is created with the scope as a task, the variable is accessible to only that task and not to any other task in the package. Therefore, the variable will not be accessible to the Execute SQL task.
The options stating that you should create a system variable are incorrect because you cannot create variables in the System namespace. You can only create variables in the User namespace.
Reference:
Objective: Configure, deploy and maintain SSIS.
Sub-objective: Manage SSIS package execution.
Multiple answer, multiple-choice
You are the administrator for the SQL Server 2008 Servers in your company. You create packages that are configured to run multiple tasks related to file archiving and business requirements. You want to configure a file to log information as the package runs. If your package fails, you want to correct the problem and restart the package from the point of failure. What should you configure? (Choose three.)
A. Configure the CheckpointFileName property on the package.
B. Configure SaveCheckPoints property on the package to True.
C. Configure SaveCheckPoints property on the package to False.
D. Configure the FailPackageOnFailure property to True for each task.
E. Configure the FailPackageOnFailure property to False for each task.
Answer:
A, B, D
Tutorial:
You should configure the CheckpointFileName property on the package, configure SaveCheckPoints property to True on the package and configure the FailPackageOnFailure property to True for each task.
The CheckpointFileName property specifies the name of the checkpoint file. The checkpoint file is used to restart the package from the point of failure.
Setting the value of the SaveCheckpoints property to True will allow the package to save information to the checkpoint file on each checkpoint. In this scenario, if the package fails, it can use the saved information from the checkpoint file to restart the execution from the point of failure. After successful completion of the package, the checkpoint file will be deleted. If you set the SaveCheckpoints property to False, there will be no checkpoints from which the package can restart.
The FailPackageOnFailure property specifies the restart point for the package on failure of the task. You should set the FailPackageOnFailure property to True for each task in this scenario. If the package fails, you correct the problem with first task and restart the package. At the next failure, you correct the problem with the second task and restart the package; then correct the problem with any other successive tasks. The package will always start up from the point of failure, allowing you to save time, effort and resources over starting the package from the beginning.
If you set the FailPackageOnFailure property to False, the package will not fail when a task fails to complete, making it more difficult to fix problems in multiple tasks within a package.
References:
SQLServerCentral.com > What's new with SSIS?
Objective: Implement an SSAS solution.
Sub-objective: Implement a data-source view.
Single answer, multiple-choice
You are the database administrator of your company. You install SQL Server Analysis Services on a computer named SQL1. You want to create a data source view to define logical relationship between two tables in the same data source. You open the Create Relationship dialog box. In which direction should you define the relationship?
A. From the primary key of the source table to the foreign key of the destination table.
B. From the foreign key of the source table to the primary key of the destination table.
C. From the foreign key of the source table to the foreign key of the destination table.
D. From the primary key of the source table to the primary key of the destination table.
Answer:
B
Tutorial:
You should define the relationship from the foreign key of the source table to the primary key of the destination table. In SQL Server 2008 Analysis Services, relationships are required for identifying fact and dimension tables and for constructing queries to retrieve data from data sources. When you use the Data Source View Wizard and Data Source View Designer, relationships automatically are defined between tables that you add to the data-source view. This relationship is based on the relationships of the underlying database or on the specified name matching criteria.
In addition to the automatically defined relationships, you should also define logical relationships to supplement the automatically defined relationships. Using the Create Relationship dialogue box, you should select the appropriate table in the Source (foreign key) table list and the Destination (primary key) table list. Next, you should select the columns from the Source Columns and Destination Columns lists to define a relationship between the two tables. The direction of the relationship must be from the foreign key of the source table to the primary key of the destination table. If you specify the wrong direction, you will be prompted to correct the order.
You should not define the relationship from the primary key of the source table to the foreign key of the destination table. The direction of the relationship must be from the foreign key of the source table to the primary key of the destination table. When you define the relationship from the primary key of the source table to the foreign key of the destination table, you will be prompted to reverse the order.
You should not define the relationship from the foreign key of the source table to the foreign key of the destination table because this will not allow you to create the logical relationship. The direction of the relationship must be from the foreign key of the source table to the primary key of the destination table.
You should not define the relationship from the primary key of the source table to the primary key of the destination table because this will not allow you to create the logical relationship. The direction of the relationship must be from the foreign key of the source table to the primary key of the destination table.
References:
Objective: Implement an SSRS solution.
Sub-objective: Implement interactivity in a report.
Multiple answer, multiple-choice
You are creating reports for your company, which sells sporting goods, using SQL Server 2008 Reporting Services (SSRS). You want to control whether a report item in the InventoryPrice report is hidden when a user views the report. You want the report to show summary data, while also enabling the user to drill down via a toggle on a text box to show or hide more detailed data in the report.
Which render extensions would support the show-and-hide toggle of hidden items on the InventoryPrice report? (Choose two.)
A. PDF
B. XML
C. Excel
D. HTML
E. DOCX
F. XPS
Answer:
B, C
Tutorial:
You should use the XML and the Excel rendering extensions to support toggling hidden items. The Excel rendering extension displays and expands hidden rows and columns. The XML rendering extension displays all report items, even the ones that are hidden.
You should not use the PDF rendering extension. This rendering extension does not support hidden report items. You should use the XML or Excel rendering extension for hidden report items.
You should not use the HTML rendering extension. This rendering extension does not display hidden items. These hidden items are not visible in the report or in the HTML source. You should use the XML or Excel rendering extension for hidden report items.
The DOCX and XPS extensions are not valid rendering extensions in SSRS. A DOCX extension is used with files created and saved in Microsoft Office 2007 Word. An XPS extension is a replacement for an Enhanced Metafile (.EMF). XPS files can be opened with Microsoft's XPS Viewer that comes with Windows Vista and Server 2008.
Reference:
Objective: Configure, deploy and maintain SSRS.
Sub-objective: Configure SSRS availability.
Single answer, multiple-choice
You are the administrator for the SQL Server 2008 Reporting Services (SSRS) in the metroil.com domain. You plan to move the report server databases to another computer. You already have performed a backup of all the databases on the source computer. How should you move the report server databases to the target computer?
A. Use the Copy Database Wizard to copy the MSDB, reportserver and reportservertempdb databases to the new target computer.
B. Use the Copy Database Wizard to copy the reportserver and reportservertempdb databases to the new target computer.
C. Restore the MSDB, reportserver and reportservertempdb databases to the new target computer.
D. Restore the reportserver and reportservertempdb databases to the new target computer.
Answer:
D
Tutorial:
You should restore the reportserver and reportservertempdb databases to the new target computer. When moving databases, there are three possible techniques: You can use the backup and restore method, the attach and detach method or directly copy the databases and delete the source afterward. The backup and restore method is a better choice than copying the databases because, if you use the Copy Database Wizard to move the databases, the permission settings are not preserved.
If you use the attach and detach method, you must take the report server offline while you detach the reportserver and reportservertempdb databases. You do not have to take the report server offline to back up the reportserver and reportservertempdb databases.
You should not restore the MSDB, reportserver and reportservertempdb databases to the new target computer. You should restore the reportserver and reportservertempdb databases, but not the MSDB database. The MSDB database contains information on jobs, schedules and backup histories of the source server. This database does not need to be restored to the target server.
You should not use the Copy Database Wizard to move the reportserver and reportservertempdb databases to the new target computer. If you use the Copy Database Wizard to move the databases, the permission settings are not preserved.
Reference:
Viewed 8489 times.
