Exam 70-448 Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

Published: September 30, 2008
Languages: English
Audiences: IT professionals
Technology: Microsoft SQL Server 2008
Credit toward certification: MCP, MCSA

Skills measured
This exam measures your ability to accomplish the technical tasks listed below. The percentages indicate the relative weight of each major topic area on the exam. The higher the percentage, the more questions you are likely to see on that content area on the exam. View video tutorials about the variety of question types on Microsoft exams.

Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.

Do you have feedback about the relevance of the skills measured on this exam? Please send Microsoft your comments. All feedback will be reviewed and incorporated as appropriate while still maintaining the validity and reliability of the certification process. Note that Microsoft will not respond directly to your feedback. We appreciate your input in ensuring the quality of the Microsoft Certification program.

If you have concerns about specific questions on this exam, please submit an exam challenge.

If you have other questions or feedback about Microsoft Certification exams or about the certification program, registration, or promotions, please contact your Regional Service Center.

Implementing an SSIS solution (15-20%)
Implement control flow
Checkpoints; debug control flow; transactions; implement the appropriate control flow task to solve a problem; data profiling and quality
Implement data flow
Debug data flow; implement the appropriate data flow components
Implement dynamic package behavior by using property expressions
Implement package logic by using variables
System variables; user variables; variable scope
Implement package configurations
Implement auditing, logging, and event handling
Use system variables for auditing; use event handlers; propagate events; use log providers; data profiling
Extend SSIS packages by using .NET code
Use the script task; use the script component; use custom assemblies

Preparation resources
SQL Server Integration Services
SQL Server Integration Services “How do I?” videos
Extending Integrations Services with the script task and Script Task Plus

Configuring, deploying, and maintaining SSIS (15-20%)
Install and maintain SSIS components
Implement disaster recovery for SSIS
Deploy an SSIS solution
Deploy SSIS packages by using DTUTIL; deploy SSIS packages by using the deployment utility; deploy SSIS packages to SQL or file system locations
Manage SSIS package execution
Schedule package execution by using SQL Server Agent; execute packages by using DTEXEC; execute packages by using SQL Server Management Studio; execute packages by using the SSIS .NET API
Configure SSIS security settings
MSDB database roles; package protection levels
Identify and resolve issues related to SSIS solution deployment
Validate deployed packages; deploy packages and dependencies between servers

Preparation resources
Considerations for installing Integration Services
Security and protection (Integration Services)
Troubleshooting (Integration Services)

Implementing an SSAS solution (20-25%)
Implement dimensions in a cube
Translations; attribute relations; hierarchies
Implement measures in a cube
Measure groups
Implement a data source view
Named calculations; named queries
Configure dimension usage in a cube
Implement reference dimensions; implement many to many relationships; implement fact relationships; implement role-playing relationships; define granularity
Implement custom logic in a cube by using MDX
Actions; key performance indicators (KPI); calculated members; calculations
Implement data mining
Implement data mining structures and models; query data mining structures by using DMX; data mining views
Implement storage design in a cube
Aggregations; partitions; storage modes; proactive caching

Preparation resources
Lesson 2: Defining and deploying a cube
Querying multidimensional data (Analysis Services – multidimensional data)
Tutorials: Designing and implementing data mining models

Configuring, deploying, and maintaining SSAS (15-20%)
Configure permissions and roles in SSAS
Server roles; SSAS database roles; cube roles; enable client application access; implement custom access to data
Deploy SSAS databases and objects
Deployment wizard; BIDS; SSMS; SSIS analysis services execute DDL task
Install and maintain an SSAS instance
Disaster recovery
Diagnose and resolve performance issues
Use SQL Profiler; performance monitor counters; DMVs; usage based optimization wizard Implement processing options
Implement processing options

Preparation resources
Granting server-wide administrative permissions
Deployment (Analysis Services – multidimensional data)
Operations (Analysis Services – multidimensional data)

Implementing an SSRS solution (10-15%)
Implement report data sources and datasets
Query types; dynamic data sources; filter location (dataset vs. query)
Implement a report layout
Apply conditional formatting; page configuration; headers and footers
Extend an SSRS solution by using code
Custom .NET assembly; private code
Create an SSRS report by using an SSAS data source
MDX in an SSRS report; DMX in an SSRS report
Implement report parameters
Databound parameters; multi-value parameters
Implement interactivity in a report
Drilldown; drillthrough; interactive sorting
Implement report items
Matrix; table; chart; image; list; grouping
Embed SSRS reports in custom applications
Use the Windows forms report viewer; use the web forms report viewer; use the SSRS web service

Preparation resources
How to: Create a dataset (Report Builder 2.0)
Tutorial: Adding parameters to a report
Designing the report layout (Report Builder 2.0)

Configuring, deploying, and maintaining SSRS (13%)
Configure report execution and delivery
Subscriptions; report caching; schedules; snapshot history
Install and configure SSRS instances
Deploy an SSRS web farm
Configure authentication and authorization for a reporting solution
Configure server-level and item-level role-based security; configure Windows authentication and custom authentication
Deploy an SSRS solution
RS.exe scripts; report builder; BIDS
Configure SSRS availability
Key management; migrate SSRS databases

Preparation resources
Deployment (Reporting Services)
High availability (Reporting Services)
Configuring authentication in Reporting Services


QUESTION 1
You maintain a SQL Server 2008 Analysis Services (SSAS) instance.
You plan to process a cube by using an XML for Analysis (XMLA) script.
You need to ensure that aggregations are populated.
What should you do?

A. Execute a processIndex command.
B. Execute a processUpdate command.
C. Execute a processStructure command.
D. Execute a processData command.

Answer: A

Explanation:


QUESTION 2
You maintain a SQL Server 2008 Analysis Services (SSAS) instance.
You plan to run the Usage-Based Optimization Wizard. You need to enable query logging.
What should you do?

A. Set the LogDir server property to a valid path.
B. Set the QueryLogSampling server property to 10.
C. Set the AllowedBrowsingFolders server property to include the folder from the LogDir setting.
D. Set the QueryLogConnectionString server property to a valid connection string.

Answer: D

Explanation:


QUESTION 3
You maintain a SQL Server Analysis Services (SSAS) database. The database is configured by
using multiple security roles.
The database is accessed by a Microsoft ASP.NET application that runs on a remote computer.
The application is configured to use Windows Authentication.
You need to ensure that the users of the application can successfully access the SSAS database.
You also need to ensure that security restrictions of the roles are applied.
What should you do?

A. Configure Kerberos authentication
B. Configure Analysis Services for https: authentication
C. Set the AnonymousConnectionsEnabled policy to True
D. Set the Security\RequireClientAuthentication property to True

Answer: A

Explanation:


QUESTION 4
You are maintaining a SQL Server 2008 Analysis Services (SSAS) solution in the production environment. You modify the solution to include two new measure groups in the development environment. You need to ensure that only one measure group is deployed to the cube in the production environment. What should you do?

A. Use the Deployment Wizard.
B. Use Microsoft SQL Server Management Studio (SSMS) to issue an XMLA command.
C. Use Microsoft SQL Server Management Studio (SSMS) to issue an UPDATE MEMBER command.
D. Use Business Intelligence Development Studio (BIDS) along with the Deploy only changes option set to True.

Answer: B

Explanation:


QUESTION 5
You maintain a SQL Server 2008 Analysis Services (SSAS) database that contains a dimension named Customer.
You need to configure the Dimension Data settings to meet the following requirements:
– Deny access to the {[Customer].[Country].&[Germany],[Customer].[Country].&[France]} set of attribute members.
– New members added to the attribute are visible by default. What should you do?

A. Add all the country members except those of France and Germany to the Allowed Set property.
B. Add the following set to the Denied Set property. {[Customer].[Country].&[Germany],[Customer].[Country].&[France]}
C. Add the following set to the Denied Set property. Except([Customer].[Country].[Country],{[Customer].[Country].&[Germany],[Customer].[Country].&[ France]})
D. Add the following set to the Allowed Set property. Extract({[Customer].[Country].&[Germany],[Customer].[Country].&[France]},[Customer].[Country])

Answer: B
 

Click here to view complete Q&A of 70-448 exam
Certkingdom Review

MCTS Training, MCITP Trainnig

Best Microsoft MCTS Certification, Microsoft 70-448 Training at certkingdom.com

Skype for Business 2015 Beta Exams 70-333 and 70-334 Q&A, study Guides, Free downloads

Skype for Business 2015 Beta Exams 333 and 334 Now Available

Are you an expert in designing, planning, deploying, and maintaining solutions for unified communications (UC)? Do you deploy Skype for Business Server and Skype for Business Online solutions for end users, endpoint devices, telephony, audio/video and web conferences, security, and high availability? Well, have I got something FUN for you! Take the beta exam, get on the road to earning your MCSE (for free), AND help us improve the quality of this exam!

We are opening up 300 beta seats for these beta exams… This means you can take the exams for free!! BUT… the seats are limited to first come, first served basis–so, register today (these codes will only work through 1/15/16, meaning you have to register AND take the exams on or before that date)–and we need you take the exam as soon as possible so we can leverage your comments, feedback, and exam data in our evaluation of the quality of the questions. The sooner you take the exam, the more likely it is that we will be able to use your feedback to make improvements to the exam. This is your chance to have a voice in the questions we include on the exam when it goes live.

To prepare for these exams, review our prep guides and practice the skills listed: https://www.microsoft.com/en-us/learning/exam-70-333.aspx and https://www.microsoft.com/en-us/learning/exam-70-334.aspx .

***Register for the 333 beta exam from its prep guide page and use code SKYPE4B333 to take it for free.

***Register for the 334 beta exam from its prep guide page and use code SKYPE4B334 to take it for free.

Remember: There are a limited amount of spots, so when they’re gone, they’re gone. You should also be aware that there are some country limitations where the beta code will not work (e.g., Turkey, Pakistan, India, China, Vietnam); you will not be able to take the beta exam for free in those countries.

Also, keep in mind that this exam is in beta, which means that you will not be scored immediately. You will receive your final score and passing status once the exam is live.

Register today before all the seats are gone!


QUESTION 1
You collect the following call statistics for users in Los Angeles:
Peak call concurrency is 25 percent.
Fifty percent of all placed calls are routed to the public switched telephone network (PSTN)
Thirty percent of all placed calls are internal calls to users in the other offices. Twenty percent of all placed calls are conference calls.
The average bandwidth for peer-to-peer calls is 65 kilobits per second (Kbps). The average bandwidth used for conference calls is 100 Kbps.
You need to allocate the minimum amount of bandwidth required on the wide area network (WAN) link for the planned implementation of the voice gateway in the Los Angeles office. All calls to the PSTN will be routed through the local voice gateway in the Los Angeles office.
How much bandwidth in megabits per second (Mbps) should you allocate?

A. 7.20 Mbps
B. 3.95 Mbps
C. 10.00 Mbps
D. 2.25 Mbps

Answer: A


QUESTION 2
You are implementing the Enterprise Voice solution. You create the objects described as shown in the following table.
You need to recommend which action must be performed to meet the technical requirements for Enterprise Voice routing.
What should you add?

A. PSTN1 to VP1
B. PSTN1 to VP2
C. PSTN2 to VP1
D. PSTN2 to VP2

Answer: C


QUESTION 3
You need to create the voice routes.
How many voice routes should you create?

A. 1
B. 2
C. 4
D. 40
E. 270

Answer: C


QUESTION 4
Remote users report that they fail to access their voice mail by using the Call Voice Mail option in the Skype tor Business client. Remote users can access voice mail by using the subscriber access number. Internal users do not report any issues accessing voice mail.
You need to enable voice mail access without manually dialing the subscriber number.
Which two sets of ports should you open? Each correct answer presents part of the solution.

A. TCP 443 and UDP 3478 on the external firewall
B. TCP 5062 and 8057 on the internal firewall. TCP 5062 and 8057 on the external firewall
C. TCP 443 and UDP 3478 on the internal firewall
D. UDP 50,000 to 59,999 on the internal firewall
E. UDP 50,000 to 59,999 on the external firewall

Answer: A,D

Click here to view complete Q&A of 70-333 exam
Certkingdom Review

MCTS Training, MCITP Trainnig

Best Microsoft MCTS Certification, Microsoft 70-333 Training at certkingdom.com

Exam 70-432 Microsoft SQL Server 2008, Implementation and Maintenance

Published: September 30, 2008
Languages: English, Japanese
Audiences: IT professionals
Technology: Microsoft SQL Server 2008
Credit toward certification: MCP, MCSA

Skills measured
This exam measures your ability to accomplish the technical tasks listed below. The percentages indicate the relative weight of each major topic area on the exam. The higher the percentage, the more questions you are likely to see on that content area on the exam. View video tutorials about the variety of question types on Microsoft exams.

Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.

Do you have feedback about the relevance of the skills measured on this exam? Please send Microsoft your comments. All feedback will be reviewed and incorporated as appropriate while still maintaining the validity and reliability of the certification process. Note that Microsoft will not respond directly to your feedback. We appreciate your input in ensuring the quality of the Microsoft Certification program.

If you have concerns about specific questions on this exam, please submit an exam challenge.

If you have other questions or feedback about Microsoft Certification exams or about the certification program, registration, or promotions, please contact your Regional Service Center.

Installing and configuring SQL Server 2008 (11%)
Install SQL Server 2008 and related services
File locations; default paths; service accounts
Configure SQL Server instances
sp_configure; Dynamic Management Views (DMVs)
Configure SQL Server services
Configuration manager; SQL browser
Configure additional SQL Server components
SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), replication; MDS
Implement database mail
Set up and configure
Configure full-text indexing
Enable/disable, index population

Preparation resources
Installing SQL Server 2008
Configuring SQL Server 2008

Maintaining SQL Server instances (9%)
Manage SQL Server Agent jobs
Create and schedule jobs; notification of job execution; disable/enable jobs; change job step order; logging
Manage SQL Server Agent alerts
Performance condition alerts; SQL event alerts; Windows Management Instrumentation (WMI) alerts
Manage SQL Server Agent operators
Operator schedules; fail safe operator; add a new operator; notification methods
Implement the declarative management framework (DMF)
Create a policy; verify a policy; schedule a policy compliance check; enforce a policy; create a condition
Back up a SQL Server environment
Operating system-level concepts; SQL Server Utility; compression media families

Preparation resources
Scheduling SQL Server Agent jobs
Sample: Creating a SQL Server Agent alert by using the WMI Provider for server events
Managing the SQL Server Utility

Managing SQL Server security (18%)
Manage logins and server roles
Create logins; disable/enable logins; security model (authentication mode); password policy enforcement; fixed server roles; alter logins; create credentials; certificate logins
Manage users and database roles
User mapping; user-defined roles; fixed roles; guest, public, dbo; creating and deleting user roles; application roles
Manage SQL Server instance permissions
Logon triggers; permissions versus fixed role membership; cross-database ownership chaining; impersonation; endpoint permissions
Manage database permissions
Impersonation; cross-database ownership chaining
Manage schema permissions and object permissions
Manage schema ownership; object rights
Audit SQL Server instances
Use DDL triggers and logon triggers; C2; common criteria; login failures; event notifications
Manage transparent data encryption (TDE)
Impact of transparent data encryption on backups; certificate management; symmetric keys
Configure surface area
sp_configure

Preparation resources
Managing users, roles, and logins
Auditing in Microsoft SQL Server 2008
SQL Server 2008 transparent data encryption

Maintaining a SQL Server database (17%)
Back up databases
Full backups; differential backups; transaction log; compressed backups; file and filegroup backups; verifying backup; TDE backups
Restore databases
Online restores; full restores; differential restores; transaction log; file and filegroup restores; verifying restore; tail of the transaction log; TDE restores
Manage and configure databases
Files, file groups, and related options; database options; recovery model; attach/detach data
Manage database snapshots
Create, drop, revert
Maintain database integrity
DBCC CHECKDB; suspect pages; page level restores
Maintain a database by using maintenance plans
Maintenance Plan Wizard; Maintenance Plan Designer

Preparation resources
Backing up and restoring databases in SQL Server
Maintenance plans

Performing data management tasks (10%)
Import and export data
BCP; BULK INSERT; OPENROWSET; GUI tools
NOT: SSIS
Manage data partitions
Switching data from one partition to another; add a filegroup; alter a partition function; alter a partition scheme
NOT: designing partition tables/indexes
Implement data compression
Sparse columns; page/row; Unicode compression
Maintain indexes
Create spatial indexes; create partitioned indexes; clustered and non-clustered indexes; XML indexes; disable and enable indexes; filtered index on sparse columns; indexes with included columns; rebuilding/reorganizing indexes; online/offline; statistics on filtered indexes
NOT: designing new indexes
Manage collations
Column collation; database collation; instance collation

Preparation resources
The Data Loading Performance Guide
Creating compressed tables and indexes
Setting and changing collations

Monitoring and troubleshooting SQL Server (14%)
Identify SQL Server service problems
DB Engine service; SQL Agent service; SQL Browser service
Identify concurrency problems
Blocks, locks, deadlocks, activity monitor; relevant Dynamic Management Views
Identify SQL Agent job execution problems
Proxy accounts; credentials; job history
Locate error information
Error log; agent log; job execution history; event logs

Preparation resources
Troubleshooting Database Engine
Understanding Concurrency Control
Viewing the SQL Server Error Log

Optimizing SQL Server performance (12%)
Implement Resource Governor
Use the Database Engine Tuning Advisor
Collect trace data by using SQL Server Profiler
Collect performance data by using Dynamic Management Views
Collect performance data by using System Monitor
Use Performance Studio

Preparation resources
SQL Server 2008 – Resource Governor
Using Database Engine Tuning Advisor
SQL Server Performance Survival Guide

Implementing high availability (9%)
Implement database mirroring
Monitoring, configuring, failover
Implement a SQL Server clustered instance
Monitoring, configuring, failover
Implement log shipping
Monitoring, configuring, failover
Implement replication
Monitoring, configuring

Preparation resources
Technical considerations and best practices for disaster recovery and high availability for SQL Server
Database mirroring and failover clustering
Deployment (replication)


Sample Questions


QUESTION 1
You maintain a SQL Server 2008 instance that contains a database named DB1. DB1
stores customer data for the company. The customers use a Web application to access their profile data. You need to protect the customer data such that data files, log files, and subsequent backups are as secure as possible even if the backup media is lost. Your solution must not affect the Web application or impact performance.
What should you do?

A. Encrypt the customer data at the cell level and then back up DB1.
B. Configure access to DB1 to only use stored procedures and functions.
C. Enable Transparent Database Encryption for DB1 and then back up the transaction logs.
D. Encrypt the customer data at the folder level by using Encrypted File System (EFS) and then back up the transaction logs.

Answer: C


QUESTION 2
You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition:


Data is grouped into quarterly partitions.
You need to configure the groupings into 12 monthly partitions.
What should you do?

A. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION … SPLIT RANGE statement.
C. Use the ALTER TABLEstatement to remove the COLLATEoption.
D. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
E. • Create a new filegroup.
• Create a new database file.
• Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
• Use ALTER INDEX REORGANIZE statement. F. • Create a new Filegroup.
• Create a new database File.
• Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
• Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause. G. • Create a new table.
• Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
• Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H. • Create a new partition function.
• Create a new partition scheme.
• Add a clustered index to place the data onto the partition scheme.
I. Run the following statement:
CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG
ALL TO (SECONDARY);
J. Run the following statement: EXECUTE sp_tableoption @TableNamePattern =’OrderItem3′, @OptionName= ‘PartltionByYear’; @OptionValue= ‘true’;

Answer: B


QUESTION 3
You administer a SQL Server 2008 instance.
You need to configure the instance to use a single thread for queries that have an estimated execution cost less than 3.
Which sp_configure configuration option should you set?

A. priority boost
B. precompute rank
C. max worker threads
D. query governor cost limit
E. cost threshold for parallelism

Answer: E


QUESTION 4
You design a maintenance plan for a SQL Server 2008 instance that contains a database named SalesDB.
The SalesDB database includes spatial indexes to support queries on spatial data.
You need to perform physical consistency checks on SalesDB. You also need to ensure that the performance effect on the SalesDB database is minimized.
Which Transact-SQL statement should you execute?

A. DBCC SYS_CHECK (SalesDB);
B. DBCC SQLPERF (SalesDB);
C. DBCC RSPAIRDB (SalesDB);
D. DBCC CHECKDB (SalesDB);

Answer: A


QUESTION 5
You administer a Microsoft SQL Server 2008 R2 instance.
You need to ensure that no suspect pages have been detected in your database.
What should you do?

A. Execute sp_helpfile.
B. Execute DBCC CHECKDB.
C. Examine the msdb..suspect_pagestable.
D. Execute DBCC CHECKDBalong with the REPAIR_FASTclause.
E. Execute DBCC CHECKDBalong with the REPAIR_REBUILDclause.
F. Restore the database from the most recent full backup. Apply any differential and log backups.
G. Use the ALTER DATABASEstatement along with the SET EMERGENCYclause.
H. Use the RESTORE DATABASEstatement along with the PAGESclause. Create a new log backup. Apply all differential and log backups, including the most recent backup.
I. Use the RESTORE DATABASEstatement along with the PAGESclause. Apply any differential and log backups. Create a new log backup and then restore the new log backup.

Answer: C


QUESTION 6
You are mastering the company database, On a SQL Server 2008, you find out that one of the data files computer is broken. You should reserve the database which is from the most recent configurations of backups. In order to cut the lost, you should find the method as quickly as possible. Which is the correct answer?

A. You should run a transaction log backup for the database.
B. You should reserve the old database backup for the database.
C. You should reserve the most recent store produce log backup for the database.
D. You should run the whole database backup.

Answer: A

 

 

Click here to view complete Q&A of 70-432 exam
Certkingdom Review

MCTS Training, MCITP Trainnig

Best Microsoft MCTS Certification, Microsoft 70-432 Training at certkingdom.com