Online Access Free 070-467 Exam Questions

Exam Code:	070-467
Exam Name:	Designing Business Intelligence Solutions with Microsoft SQL Server
Certification Provider:	Microsoft
Free Question Number:	185
Posted:	Sep 09, 2025

Rating

100%

Page: 1 / 37
Total 185 questions

Question 1

You need to roll back the compatibility level of the Research database.
What should you do?

A.Change the CompatibilityLevel property in the XMLA script, and then execute the script.
B.Restore a backup of the previous version of the database.
C.Use an ALTER DATABASE statement to set the compatibility option.
D.In SQL Server Management Studio (SSMS), change the compatibility level in the database properties.

Question 2

You need to upgrade the SSASCluster.
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.)

Correct Answer:

Explanation
Box 1:

Box 2:

Box 3:

Note:
* From scenario:
/ The current SQL Server environment consists of a single instance failover cluster of SQL Server 2008 R2 Analysis Services (SSAS). The virtual server name of the cluster is SSASCluster. The cluster includes two nodes: Node1 and Node2. Node1 is currently the active node. In anticipation of the upgrade, the prerequisites and shared components have been upgraded on both nodes of the cluster, and each node was rebooted during a weekly maintenance window.
The upgrade must minimize user intervention and administrative effort. So we'll upgrade using the scripted method rather than the GUI method.
* (box 1)
/ SQL Server Setup provides the ability to generate a configuration file based upon the system default and run-time inputs. You can use the configuration file to deploy SQL Server throughout the enterprise with the same configuration. You can also standardize manual installations throughout the enterprise, by creating a batch file that launches Setup.exe.
/ How to generate a configuration file
Insert the SQL Server installation media. From the root folder, double-click Setup.exe. To install from a network share, locate the root folder on the share, and then double-click Setup.exe.
Follow the wizard through to the Ready to Install page. The path to the configuration file is specified in the Ready to Install page in the configuration file path section. For more information about how to install SQL Server, see Install SQL Server 2012 from the Installation Wizard (Setup).
Cancel the setup without actually completing the installation, to generate the INI file.
* (box 2) First upgrade the passive node node2.
To be able to use the configuration file we use the command prompt. After the upgrade, the cluster will fail over to the upgraded node.
*( Box 3) upgrade Node1 (which is now the passive node)
* You can upgrade a SQL Server failover cluster to a SQL Server 2008 failover cluster by using the SQL Server Installation Wizard or a command prompt. One of the main features of SQL Server 2008 failover clustering is minimal downtime for rolling upgrades and updates.
* To control the failover behavior of cluster nodes during the upgrade process, run the upgrade operation at the command prompt and use the /FAILOVERCLUSTERROLLOWNERSHIP parameter.
* To upgrade a SQL Server failover cluster to SQL Server 2008 R2, you must run the Setup on one failover cluster node at a time, starting with the passive nodes. Setup determines when to fail over to the upgraded node, depending on the total number of nodes in the failover cluster instance, and the number of nodes that have already been upgraded. When half of the nodes or more have already been upgraded, Setup by default will cause a failover to an upgraded node.
To control the failover behavior of cluster nodes during the upgrade process, run the upgrade operation at the command prompt and use the /FAILOVERCLUSTERROLLOWNERSHIP parameter to control the failover behavior before the upgrade operation takes the node offline. Use of this parameter is as follows:
/FAILOVERCLUSTERROLLOWNERSHIP=0 will not roll cluster ownership (move group) to upgraded nodes, and does not add this node to the list of possible owners of the SQL Server cluster at the end of upgrade.
/FAILOVERCLUSTERROLLOWNERSHIP=1 will roll cluster ownership (move group) to upgraded nodes, and will add this node to the list of possible owners of the SQL Server cluster at the end of upgrade.
/FAILOVERCLUSTERROLLOWNERSHIP=2 is the default setting. It will be used if this parameter is not specified. This setting indicates that SQL Server Setup will manage cluster ownership (move group) as needed.
Reference: Install SQL Server 2012 Using a Configuration File
Reference: How to: Install SQL Server 2008 R2 from the Command Prompt

Question 3

You are designing a SQL Server Reporting Services (SSRS) solution.
A report project must access multiple SQL Azure databases. Each database is on a different host. The databases have identical schema and security configurations.
You have the following requirements:
* The report must support subscriptions.
* Users must be able to select the host when running the report.
What should you do?
To answer, drag the appropriate phrase or phrases from the list to the correct location or locations in the answer area. (Answer choices may be used once, more than once, or not all.)

Question 4

You install SQL Server Integration Services (SSIS) and develop an SSIS project to load a data warehouse. The project defines 10 parameters that have different values for the development, test and production environments. Some packages have additional parameters defined.
You plan to deploy the SSIS packages to one server. The server will also host the production data warehouse.
You need to deploy and configure the packages.
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.

Question 5

You are designing a business intelligence (BI) solution for a hotel booking system. Each hotel booking may have more than one guest.
The BI solution contains a fact table named Booking, a factless fact table named Booking_Guest and a dimension named Guest.
Booking contains an entry for each booking. Booking_Guest contains an entry for each guest in each booking.
Guest contains an entry for each guest.
You need to define the many-to-many relationship in SQL Server Analysis Services (SSAS) for the hotel booking system.
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.