Monday, 27 June 2016

Exam 70-485 Advanced Windows Store App Development Using C#

Published: October 18, 2012
Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
Audiences: Partners, developers
Technology: Visual Studio 2012
Credit toward certification: MCP, MCSD

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.

As of December 2, 2013, this exam includes content covering Windows 8.1 and Visual Studio 2013.

Develop Windows Store apps (15–20%)
Create background tasks
Implement the Windows.applicationmodel.background classes; implement the IBackgroundTask interface
Consume background tasks
Use timing and system triggers; keep communication channels open; request lock screen access; use the BackgroundTransfer class to finish downloads
Create and consume WinMD components
Create a WinMD component in C#; consume a WinMD component; handle WinMD reference types; reference a WinMD component

Preparation resources
Background task sample
Windows Runtime Components in a .NET World

Discover and interact with devices (15–20%)
Capture media with the camera and microphone
Use CameraCaptureUI to capture pictures or video; use MediaCapture to capture pictures, video, or audio; configure camera settings; set media formats; handle media capture events; implement advanced photo capabilities, such as sequence mode, thumbnails, and focus mode
Get data from sensors
Determine the availability of a sensor (Windows.devices.sensors); add sensor requests to the app manifest; handle sensor events; get sensor properties; determine location via GPS; enable geofencing
Enumerate and discover device capabilities
Discover the capabilities of a device (for example, GPS, accelerometer, near field communication, and camera)
Implement device access
USB; Bluetooth; Human Interface Device (HID); 3D printer support; Point of Service (PoS) devices

Preparation resources
CameraCaptureUI Sample
Bluetooth device support
USB device support

Program user interaction (15–20%)
Implement printing by using contracts and charms
Implement the print contract; create a custom print template; construct a print preview; handle print pagination; implement in-app printing; expose printer settings within your app
Implement Play To by using contracts and charms
Register your app for Play To; use PlayToManager to stream media assets; register your app as a PlayToReceiver; programmatically implement Play To functionality
Notify users by using Windows Push Notification Service (WNS)
Authenticate with WNS; request, create, and save a notification channel; call and poll the WNS; configure and implement push notifications by using Azure Mobile Services

Preparation resources
Azure Mobile Services for Windows Store app and Android app developers

Enhance the user interface (15–20%)
Design for and implement UI responsiveness
Choose an asynchronous strategy for your app; implement the Task Parallel library for multi-processor utilization; convert asynchronous operations to tasks
Create animations and transitions
Apply animations from the animation library; create and customize animations and transitions, including XAML transitions; implement storyboards and transformations; utilize built-in animations for controls
Create custom controls
Choose the appropriate base control to create a custom control template; style a control through control templates; design the control template to respond to changes in windowing modes
Design Windows Store apps for globalization and localization
Implement .resw files to translate text; implement collation and grouping to support different reading directions; implement culture-specific formatting for dates and times

Preparation resources
XAML user and custom controls sample
Globalizing your app (Windows Store apps using JavaScript and HTML)

Manage data and security (15–20%)
Design and implement data caching
Choose which types of items (user data, settings, app data) in an app should be persisted to the cache according to requirements; choose when items are cached; choose where items are cached (Microsoft Azure, Azure Mobile Services, remote storage); select the caching mechanism; store data by using LocalStorage and SessionStorage
Save and retrieve files from the file system
Handle file streams; save and retrieve files by using StorageFile and StorageFolder classes; set file extensions and associations; save and retrieve files by using the file picker classes; compress files to save space; access libraries and KnownFolders, for example, pictures, documents, and videos; manage appearance of the file picker; improve searchability by using Windows Index; integrate OneDrive with apps; compare files; manage libraries; secure application data
Secure app data
Encrypt data by using the Windows.Security.Cryptography namespace; enroll and request certificates; encrypt data by using certificates; revoke file permissions

Prepare for a solution deployment (15–20%)
Design and implement monetization features in an app
Set up a timed trial; set up a feature-based trial; set up in-app purchases; transition an app from trial to full; implement in-app purchases
Design for error handling
Design the app so that errors and exceptions never reach the user; application class for global collection; handle device capability errors; handle asynchronous errors
Design and implement a test strategy
Recommend a functional test plan; implement a coded UI test; recommend a reliability test plan (performance testing, stress testing, scalability testing, duration testing); implement unit testing in an app; simulate in-app purchases
Design a diagnostics and monitoring strategy
Design profiling, tracing, performance counters, audit trails (events and information), and usage reporting; decide where to log events (local vs. centralized reporting)
Evaluate and configure for Windows store deployment
Configure app options to submit to store, such as age restrictions, privacy statement, permissions, images, and contact information; create application files, resource files, and application bundles; verify application readiness by using the Windows Application Certification Kit (WACK)

Preparation resources
Testing Windows Store Apps
Debugging and testing


QUESTION 1
You need to implement the requirements for the playback of media.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A. Add the following line of code at line MC02. private void ShowPlayTo()

{
Windows.Media.PlayTo.PlayToManager.ShowPlayToUI();
}

B. Add the following line of code at line MC06. ptMgr.DefauitSourceSelection = false;
C. Add the following line of code at line MC10. ptMgr.PlayRequested += SourceRequestHandler;
D. Add the following line of code at line MC05. ptMgr.SourceRequested += SourceRequestHandler;

Answer: B,D

QUESTION 2
You need to ensure that the VideoProcessor component can be used by the Windows Store app.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A. Add the following attribute to line IP19. [Windows.Foundation.Metadata.DefaultOverload()]
B. Replace line IP01 with the following line of code. Static class VideoProcessor
C. Replace line IP09 with the following line of code. PublicVideoProcessor(string videoName, int ID)
D. Add the following attribute to line IP14. [Windows.Foundation.Metadata.DefaultOverload()]
E. Replace line IP01 with the following line of code. Public sealed class VideoProcessor

Answer: A,C,E

QUESTION 3
You need to implement the requirements for streaming media.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A. Enable access to the Videos Library.
B. Ensure that the app stays in the foreground while media is being streamed.
C. Enable access to the Pictures Library.
D. Register for the SourceRequested event.
E. Enable access to the Music Library.
F. Register for the PlayRequested event.

Answer: A,D

Explanation: From scenario:
Team members must be able to stream video clips to other devices in the vicinity of the team member's device. The app will not support the streaming of photographs.
D: You can use Play To to stream the audio or video in your application, as well as images, by implementing the Play To contract. To implement the Play To contract in your application, register for the sourceRequested event.
Note:
To register for the sourceRequested event, get a reference to the current PlayToManager by calling the getForCurrentView method. You can then call addEventHandler on the PlayToManager to associate your event handler with the sourceRequested event. In your event handler, pass the media element from your application to the setSource method of the PlayToSourceRequestedEventArgs object passed to the event handler as shown in the following example.
// Play To Contract
private Windows.Media.PlayTo.PlayToManager ptm = Windows.Media.PlayTo.PlayToManager.GetForCurrentView();
protected override void OnNavigatedTo(NavigationEventArgs e)
{
ptm.SourceRequested += sourceRequestHandler;
}
private void sourceRequestHandler( Etc.


QUESTION 4
You need to implement the behavior requirements for the photo viewer.
Which controls should you create?

A. Create two SemanticZoom controls and one ListView control.
B. Create one SemanticZoom control and one ListView control.
C. Create one ScrollViewer control, one SemanticZoom control, and one GridView control.
D. Create two GridView controls and one SemanticZoom control.

Answer: D


QUESTION 5
You need to implement the photo viewer control to meet the requirements.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A. Add the themes\generic.xaml file to the project and reference it from the control.
B. Create a composite control.
C. Create a user control.
D. Create a custom control.
E. In the constructor of the class, set the value of the DefaultStyleKey to the type of the control.

Answer: C,D,E


Friday, 24 June 2016

Exam 70-484 Essentials of Developing Windows Store Apps Using C#

Published: October 23, 2012
Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
Audiences: Partners, developers Technology Visual Studio 2012
Credit toward certification: MCP, MCSD

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.

Design Windows Store apps (20-25%)
Design the UI layout and structure
Evaluate the conceptual design and decide how the UI will be composed; design for the inheritance and re-use of visual elements (e.g., styles, resources); design for accessibility; decide when custom controls are needed; use the Hub App template
Design for separation of concerns (SOC)
Plan the logical layers of your solution to meet app requirements; design loosely coupled layers; incorporate WinMD components
Apply the MVVM pattern to your app design
Design and implement the appropriate data model to support business entities; design your viewmodel to support your view based on your model; develop a view to meet data-binding requirements; create view models using INotifyPropertyChanged, ObservableCollection, and CollectionViewSource
Design and implement Process Lifetime Management (PLM)
Choose a state management strategy; handle the suspend event; prepare for app termination; handle the Resume event; handle the OnActivated event; check the ActivationKind and previous state
Plan for an app deployment
Plan a deployment based on Windows Store app certification requirements; prepare an app manifest (capabilities and declarations); sign an app; plan the requirements for an enterprise deployment

Develop Windows Store apps (15-20%)
Access and display contacts
Call the ContactsPicker class; filter which contacts to display; display a set number of contacts; create and modify contact information; select specific contact data
Design for charms and contracts
Choose the appropriate charm based on app requirements; design your app in a charm- and contract-aware manner; configure app manifest for correct permissions
Implement search
Provide search suggestions using the SearchPane class and SearchBox control; search for and launch other apps; provide and constrain search within an app, including inside and outside of Search charm; provide search result previews; implement activation from within search; configure search contracts
Implement Share in an app
Use the DataTransferManager class to share data with other apps; accept sharing requests by implementing activation from within Share; limit the scope of sharing using the DataPackage object; implement in-app Share outside of Share charm; use web links and application links
Manage app settings and preferences
Choose which app features are accessed in AppSettings; add entry points for AppSettings in the Settings window; create settings flyouts; store and retrieve settings from the roaming app data store
Integrate media features
Support DDS images; implement video playback; implement XVP and DXVA; implement Text to Speech (TTS)

Create the user interface (20-25%)
Create layout aware apps to handle windowing modes
Respond to changes in orientation; adapt to new windowing modes by using the ViewManagement namespace; manage settings for an apps view
Implement layout controls
Implement the Grid control to structure your layout; set the number of rows/columns and size; enable zoom and scroll capabilities in layout controls; manage text flow and presentation
Design and implement the app/nav bar
Determine what to put on the app/nav bar based on app requirements; style and position app/nav bar items; design the placement of controls on the app/nav bar; handle app/nav bar events; design the placement of controls on the app/nav bar
Design and implement data presentation
Choose and implement data controls and properties to meet app requirements (e.g., ListView, GridView, FlipView, DatePicker, TimePicker, Hyperlink, PlaceholderText, menu flyouts, and CommandBar); create data templates to meet app requirements
Create and manage XAML styles and templates
Implement and extend styles and templates; implement gradients; modify styles based on event and property triggers; create shared resources and themes

Preparation resources
XAML AppBar control sample

Program the user interaction (20-25%)
Create and manage tiles
Create and update tiles and tile contents; create and update badges (TileUpdateManager class); respond to notification requests; choose an appropriate tile update schedule based on app requirements
Notify users by using toast
Enable an app for toast notifications; populate toast notifications with images and text using the ToastUpdateManager class; play sounds with toast notifications; respond to toast events; control toast duration; configure and use Azure Mobile Services for push notifications
Manage input devices
Capture Gesture library events; create custom gesture recognizers; listen to mouse events or touch gestures; manage Stylus input and inking
Design and implement navigation in an app
Handle navigation events, check navigation properties, and call navigation functions by using the Navigation framework; design navigation to meet app requirements; Semantic Zoom

Preparation resources
Tile and tile notification overview (Windows Store apps)
Toast notification overview (Windows Store apps)
Navigation model (using C#/VB/C++ and XAML)

Manage security and data (20-25%)
Choose an appropriate data access strategy
Choose the appropriate data access strategy (file based, web service, remote storage, including Microsoft Azure storage and Azure Mobile Services) based on requirements
Retrieve data remotely
Use HttpClient to retrieve web services; set the appropriate HTTP verb for REST; consume SOAP/WCF services; use WebSockets for bi-directional communication; handle the progress of data requests
Implement data binding
Choose and implement data-bound controls; bind collections to items controls; implement the IValueConverter interface; create and set dependency properties; validate user input; enable filtering, grouping, and sorting data in the user interface
Manage Windows Authentication and Authorization
Retrieve a user's roles or claims; store and retrieve credentials by using the PasswordVault class; implement the CredentialPicker class; verify credential existence by using credential locker; store account credentials in app settings
Manage Web Authentication
Use the Windows.Security.Authentication.Web namespace; set up oAuth2 for authentication; implement the CredentialPicker class; set up single sign-on (SSO); implement credential roaming; implement the WebAuthenticationBroker class; support proxy authentication for enterprises

Preparation resources
Connecting to web services (Windows Store apps using C#/VB/C++ and XAML)
Data binding overview (Windows Store apps using C#/VB/C++ and XAML)
Managing user info (Windows Store apps using C#/VB/C++ and XAML)

QUESTION 1
You need to ensure that launching the app displays the required information.
From which ApplicationExecutionState enumeration should you configure the user interface state?

A. ClosedByUser
B. Suspended
C. NotRunning
D. Running
E. Terminated

Answer: E

Explanation: The user closes the app through the close gesture or Alt+F4 and takes longer than 10 seconds to activate the app again.
*From scenario: The app must meet the following technical requirements: Retain state for each user and each device.
Restore previously saved state each time the app is launched.
Your app can use activation to restore previously saved data in the event that the operating system terminates your app, and subsequently the user re-launches it. The OS may terminate your app after it has been suspended for a number of reasons. The user may manually close your app, or sign out, or the system may be running low on resources.
Ref: http://msdn.microsoft.com/en-us/library/windows/apps/hh464925.aspx


QUESTION 2
You need to choose the appropriate data binding strategy for the image list box.
Which method should you use?

A. System.Drawing.ImageConverter.ConvertToString()
B. IValueConverter.ConvertBack()
C. IValueConverter.Convert()
D. System.Drawing. ImageConverter-ConvertFromStrin()

Answer: C

Explanation: IValueConverter.Convert
The data binding engine calls this method when it propagates a value from the binding source to the binding target.

QUESTION 3
You need to ensure that only the correct information is preserved when the user switches to another app.
Which actions should you perform? (Each correct answer presents part of the solution. Choose all that apply.)

A. Save application state by calling the SaveDataToRoamingStorage() method,
B. Save photographs by calling the SaveDataToLocalStorage() method.
C. Save photographs by calling the SaveDataToWebService() method.
D. save application state by calling the SaveDataToLocalStorage() method.

Answer: A,B

Explanation: A: From scenario: The app must meet the following technical requirements: Retain state for each user and each device.
B: From scenario: The app must store cached images on the device only


QUESTION 4
You need to ensure that the app resumes according to the requirements.
Which actions should you perform? (Each correct answer presents part of the solution. Choose all that apply.)

A. Retrieve new user content by using the Window.Current.Dispatcher.ProcessEvents()
method in the App_Resuming event handler.
B. update the user interface by using the Window.Current.Dispatcher.Invoke() method in the App_Resuming event handler.
C. Override the OnLaunched event handler.
D. Retrieve new user content by using the Window.Current.Dispatcher.ProcessEvents() method in the OnLaunched event handler when the ActivationKind is Launch.
E. Update the user interface by using the Window.Current.Dispatcher.Invoke() method in the OnLaunched event handler when the ActivationKind is Launch.
F. Register the App_Resuming event handler for the Resuming event.

Answer: A,B

Explanation:
From scenario:
The app must meet the following technical requirements:
/ When the app resumes after a period of suspension, refresh the user interface, tile images, and data with current information from the web service.


QUESTION 5
You need to choose the appropriate data binding strategy for the image list box.
Which method should you use?

A. System.Drawing.ImageConverter.ConvertTo(value, typeof(Image))
B. IValueConverter.Convert()
C. System.Drawing.ImageConverter.ConvertFrom(value, typeof(Image), CultureInfo.CurrentUICulture)
D. IValueConverter.ConvertBack ()

Answer: B

Explanation: IValueConverter.Convert
The data binding engine calls this method when it propagates a value from the binding source to the binding target.


Friday, 17 June 2016

600-199 SCYBER Securing Cisco Networks with Threat Detection and Analysis

Exam Number 600-199 SCYBER
Associated Certifications Cisco Cybersecurity Specialist
Duration 60 minutes (50 - 60 questions)
Available Languages English
Register Pearson VUE
Exam Policies Read current policies and requirements
Exam Tutorial Review type of exam questions

This exam is aimed at testing the knowledge and skills required to proactively detect and mitigate network security threats by leveraging features that exist in Cisco and other industry network security products today. Designed for professional security analysts, the exam covers essential areas of competency, including event monitoring, security event/alarm/traffic analysis, and incident response.

The following course is the recommended training for this exam.

Securing Cisco Networks with Threat Detection and Analysis
Courses listed are offered by Cisco Learning Partners-the only authorized source for Cisco IT training delivered exclusively by Certified Cisco Instructors. Check the List of Learning Partners for a Cisco Learning Partner nearest you.

Exam Description
The Securing Cisco Networks with Threat Detection and Analysis (SCYBER) exam is the exam associated with the Cisco Cybersecurity Specialist certification. This exam is aimed at testing the knowledge and skills required to proactively detect and mitigate network security threats by leveraging features that exist in Cisco and other industry network security products today. Designed for professional security analysts, the exam covers essential areas of competency including event monitoring, security event/alarm/traffic analysis, and incident response.

Recommended Training
The following course is the recommended training for this exam:

Securing Cisco Networks with Threat Detection and Analysis – (SCYBER)

1.0 Information Gathering and Security Foundations 13%
1.1 Describe basic network topologies, application architecture, and host configuration standards
1.2 Identify the services a network and security operations center offers to an organization
1.3 Describe traditional hacking techniques
1.4 Describe basic operational procedures and incident response processes of a security operations center
1.5 Describe basic network security events
1.6 Describe mission-critical network traffic and functions, applications, services, and device behaviors
1.7 Describe corporate security policies
1.8 Describe the role of a network security analyst
1.9 Describe the primary sources of data on vendor vulnerabilities, current threats, exploits, and active attacks
1.10 Describe how vulnerability, attack, and threat data impact operations
1.11 Describe the baseline of a network profile
1.12 Describe correlation baselines (use NetFlow output to validate normal traffic vs. non-normal)
1.13 Describe security around local business process and infrastructure and applications
1.14 Describe risk analysis mitigation

2.0 Event Monitoring 16%
2.1 Describe the various sources of data and how they relate to network security issues
2.2 Monitor the collection of network data as it relates to network security issues
2.3 Monitor and validate health state and availability of devices
2. Monitor DNS query log output (monitor telemetry data to validate devices)
2.5 Identify a security incident (single or recurrent)
2.6 Describe the best practices for evidence collection and forensic analysis
2.7 Describe the different types and severity of alarms and events

3.0 Security Events and Alarms 16%
3.1 Identify and dismiss false positive indicators correctly
3.2 Describe event correlation within the context of the various alarms and corporate infrastructure architecture
3.3 Assess traffic and events in relation to stated policies
3.4 Identify actionable events
3.5 Identify basic incident types
3.6 Describe event metrics and diagnostic procedures

4.0 Traffic Analysis, Collection, and Correlation 24%
4.1 Describe IP packet structures
4.2 Describe TCP and UDP header information
4.3 Analyze network traces or TCP dumps and trace back to actual activities
4.4 Describe packet analysis in IOS
4.5 Describe access packets in IOS
4.6 Acquire network traces
4.7 Configure packet capture

5.0 Incident Response 16%
5.1 Describe standard corporate incident response procedure and escalation policies
5.2 Identify necessary changes to enhance the existing procedure, policy, and decision tree
5.3 Describe the basic emergency mitigation of high-level threats, exploits, and vulnerabilities
5.4 Evaluate and recommend responses to vulnerabilities to ensure adequate monitoring response and mitigation
5.5 Assist level 2 incident response team to mitigate issues
5.6 Describe best practices for post-event investigation
5.7 Describe common legal and compliance issues in security event handling

6.0 Operational Communications 15%
6.1 Describe the communication vehicles related to post-threat remediation
6.2 Generate incident reports and interpret the information to determine the direction of the escalation
6.3 Describe the different types of available metrics and channel to appropriate personnel
6.4 Process incident handling communications and provide context awareness for stakeholders
6.5 Articulate details of problems to remediating teams (constituent-based groups)
6.6 Maintain awareness regarding vulnerabilities and the recommended critical security patches as a result from incident handling
6.7 Communicate recurring issues based on incident handling and provide recommendations for architectural changes or modifications and articulate
6.8 Describe the post-mortem process

QUESTION 1
Which network management protocol relies on multiple connections between a managed device
and the management station where such connections can be independently initiated by either
side?

A. SSH
B. SNMP
C. Telnet
D. NetFlow

Answer: B

Explanation:


QUESTION 2
When an IDS generates an alert for a correctly detected network attack, what is this event called?

A. false positive
B. true negative
C. true positive
D. false negative

Answer: C

Explanation:


QUESTION 3
When is it recommended to establish a traffic profile baseline for your network?

A. outside of normal production hours
B. during a DDoS attack
C. during normal production hours
D. during monthly file server backup

Answer: C

Explanation:


QUESTION 4
Which two activities would you typically be expected to perform as a Network Security Analyst?
(Choose two.)

A. Verify user login credentials.
B. Troubleshoot firewall performance.
C. Monitor database applications.
D. Create security policies on routers.

Answer: B,D

Explanation:


QUESTION 5
Which protocol is typically considered critical for LAN operation?

A. BGP
B. ARP
C. SMTP
D. GRE

Answer: B

Explanation:

Wednesday, 8 June 2016

500-275 SSFAMP Securing Cisco Networks with Sourcefire FireAMP Endpoints (SSFAMP)


QUESTION 1
The FireAMP connector monitors the system for which type of activity?

A. vulnerabilities
B. enforcement of usage policies
C. file operations
D. authentication activity

Answer: C

Explanation:


QUESTION 2
Which disposition can be returned in response to a malware cloud lookup?

A. Dirty
B. Virus
C. Malware
D. Infected

Answer: C

Explanation:


QUESTION 3
The FireAMP Mobile endpoint connector currently supports which mobile OS device?

A. Firefox
B. HTML5
C. Android
D. iPhone

Answer: C

Explanation:


QUESTION 4
If a file's SHA-256 hash is sent to the cloud, but the cloud has never seen the hash before, which
disposition is returned?

A. Clean
B. Neutral
C. Malware
D. Unavailable

Answer: B

Explanation:


QUESTION 5
Which statement describes an advantage of the FireAMP product?

A. Signatures are pushed to endpoints more quickly than other antivirus products.
B. Superior detection algorithms on the endpoint limit the amount of work the cloud must perform.
C. It provides enterprise visibility.
D. It relies on sandboxing.

Answer: C

Explanation:

Sunday, 5 June 2016

Exam 70-470 Recertification for MCSE: Business Intelligence

Published: August 10, 2014
Languages: English, Japanese
Audiences: IT professionals
Technology: Microsoft SQL Server 2014
Credit toward certification: MCP, MCSE

Skills measured
This exam measures your ability to accomplish the technical tasks listed below. 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.

Build an analysis services multidimensional database
Implement a cube
Use SQL Server Data Tools - Business Intelligence (SSDT-BI) to build the cube; use SSDT-BI to do non-additive or semi-additive measures in a cube, define measures, specify perspectives, define translations, define dimension usage, define cube-specific dimension properties, define measure groups, implement reference dimensions, implement many-to-many relationships, implement fact relationships, implement role-playing relationships, create and manage linked measure groups and linked dimensions, create actions
Implement custom logic in a data model
Define key performance indicators (KPIs); define calculated members; create relative measures (growth, YoY, same period last year), percentage of total using MDX; named sets; add Time Intelligence; implement ranking and percentile; define MDX script to import partial PowerPivot model
Select an appropriate model for data analysis
Select Tabular versus Multidimensional based on scalability needs, traditional hierarchical, data volume; select appropriate organizational BI, such as corporate BI or PowerBI, and team and personal BI needs and data status

Manage, maintain, and troubleshoot a SQL Server Analysis Services (SSAS) database
Process data models
Define processing of tables or partitions for tabular and multidimensional models; define processing of databases, cubes, and dimensions for multidimensional models; select full processing versus incremental processing; define remote processing; define lazy aggregations; automate with Analysis Management Objects (AMO) or XML for Analysis (XMLA); process and manage partitions by using PowerShell
Install and maintain an SSAS instance
Install SSAS; install development tools; identify development and production installation considerations; upgrade SSAS instance; define data file and program file location; plan for Administrator accounts; define server and database level security; support scale-out read-only; update SSAS (service packs); install and maintain each instance type of Analysis Services, including PowerPivot; restore and import PowerPivot; back up and restore by using PowerShell

Build a tabular data model
Implement a tabular data model
Define tables, import data, define calculated columns, define relationships, define hierarchies and perspectives, manage visibility of columns and tables, embed links, optimize BISM for Power View, mark a date table, sort a column by another column
Implement data access for a tabular data model
Manage partitions, processing, select xVelocity versus DirectQuery for data access

Build a report with SQL Server Reporting Services (SSRS)
Design a report
Select report components (crosstab report, Tablix, design chart, data visualization components), design report templates (Report Definition Language), identify the data source and parameters; design a grouping structure; drill-down reports, drill-through reports; determine if any expressions are required to display data that is not coming directly from the data source
Manage a report environment
Manage subscriptions and subscription settings; define data-driven subscriptions; manage data sources; integrate SharePoint Server; define email delivery settings; manage the number of snapshots; manage schedules, running jobs, and report server logs; manage report server databases; manage the encryption keys; set up the execution log reporting; review the reports; configure site-level settings; design report lifecycle; automate management of reporting services; create a report organization structure; install and configure reporting services; deploy custom assemblies
Configure report data sources and datasets
Select appropriate query types (stored procedure versus table versus text only); configure parameterized connection strings (dynamic connection strings); define filter location (dataset versus query); configure data source options, for example, extract and connect to multiple data sources; shared and embedded data sources and datasets; use custom expressions in data sources; connect to Microsoft Azure SQL database; connect to Microsoft Azure Marketplace; implement DAX and MDX queries to retrieve appropriate data sets; work with non-relational data sources, such as XML or SharePoint lists; connect to HDInsight Server

Plan business intelligence (BI) infrastructure
Plan for performance
Optimize batch procedures: extract, transform, load (ETL) in SQL Server Integration Services (SSIS)/SQL and processing phase in Analysis Services; configure Proactive Caching within SQL Server Analysis Services (SSAS) for different scenarios; understand performance consequences of named queries in a data source view; analyze and optimize performance, including Multidimensional Expression (MDX) and Data Analysis Expression (DAX) queries; understand the difference between partitioning for load performance versus query performance in SSAS; appropriately index a fact table; optimize Analysis Services cubes in SQL Server Data Tools; create aggregations

Design BI infrastructure
Design a high availability and disaster recovery strategy
Design a recovery strategy, back up and restore SSAS databases, back up and restore SSRS databases, move and restore the SSIS Catalog, design an AlwaysON solution

Design a reporting solution
Design a Reporting Services dataset
Design appropriate data query parameters, create appropriate SQL queries, create appropriate DAX queries for an application, manage data rights and security, extract data from analysis services by using MDX queries, balance query-based processing versus filter-based processing, manage data sets through the use of stored procedures
Manage Excel Services/reporting for SharePoint
Configure data refresh schedules for PowerPivot published to SharePoint, publish BI info to SharePoint, use SharePoint to accomplish BI administrative tasks, install and configure Power View, publish PowerPivot and Power View to SharePoint
Design BI reporting solution architecture
Linked drill-down reports, drill-through reports, and sub reports; design report migration strategies; access report services API; design code-behind strategies; identify when to use Reporting Services (RS), Report Builder (RB), or Power View; design and implement context transfer when interlinking all types of reports (RS, RB, Power View, Excel); implement BI tools for reporting in SharePoint (Excel Services versus PowerView versus Reporting Services); select a subscription strategy; enable Data Alerts; design map visualization

Design BI data models
Design the data warehouse
Design a data model that is optimized for reporting; design and build a cube on top; design enterprise data warehouse (EDW) and OLAP cubes; choose between natural keys and surrogate keys when designing the data warehouse; use SQL Server to design, implement, and maintain a data warehouse, including partitioning, slowly changing dimensions (SCD), change data capture (CDC), Index Views, and column store indexes; identify design best practices; implement a many-to-many relationship in an OLAP cube; design a data mart/warehouse in reverse from an Analysis Services cube; implement incremental data load; choose between performing aggregation operations in the SSIS pipeline or the relational engine
Design cube architecture
Partition cubes and build aggregation strategies for the separate partitions; design a data model; choose the proper partitioning strategy for the data warehouse and cube; design the data file layout; identify the aggregation method for a measure in a MOLAP cube; performance tune a MOLAP cube using aggregations; design a data source view; design for cube drill-through and write back actions; choose the correct grain of data to store in a measure group; design analysis services processing by using indexes, indexed views, and order by statements

Design an ETL solution
Design SSIS package execution
Use the new project deployment model; pass values at execution time; share parameters between packages; plan for incremental loads versus full loads; optimize execution by using Balanced Data Distributor (BDD); choose optimal processing strategy (including Script transform, flat file incremental loads, and Derived Column transform)
Plan to deploy SSIS solutions
Deploy the package to another server with different security requirements, secure integration services packages that are deployed at the file system, demonstrate awareness of SSIS packages/projects and how they interact with environments (including recoverability), decide between performing aggregation operations in the SSIS pipeline or the relational engine, plan to automate SSIS deployment, plan the administration of the SSIS Catalog database
QUESTION 1
You need to identify the reports that produce the errors that Marc is receiving.
What should you do?

A. Write a query by using the Subscriptions table in the report server database.
B. Use the Windows Event Viewer to search the Application log for errors.
C. Write a query by using the ExecutionLog3 view in the report server database.
D. Search the ReportServerService_<timestamp>.log file for errors.

Answer: C


QUESTION 2
You need to deploy the StandardReports project.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

A. Deploy the project from SQL Server Data Tools (SSDT).
B. Use the Analysis Services Deployment utility to create an XMLA deployment script.
C. Use the Analysis Services Deployment wizard to create an MDX deployment script.
D. Use the Analysis Services Deployment wizard to create an XMLA deployment script.

Answer: A,D

Explanation: There are several methods you can use to deploy a tabular model project. Most of the deployment methods that can be used for other Analysis Services projects, such as multidimensional, can also be used to deploy tabular model projects.
A: Deploy command in SQL Server Data Tools
The Deploy command provides a simple and intuitive method to deploy a tabular model project from the SQL Server Data Tools authoring environment.
Caution:
This method should not be used to deploy to production servers. Using this method can overwrite certain properties in an existing model.
D: The Analysis Services Deployment Wizard uses the XML output files generated from a Microsoft SQL Server Analysis Services project as input files. These input files are easily modifiable to customize the deployment of an Analysis Services project. The generated deployment script can then either be immediately run or saved for later deployment.
Incorrect:
not B: The Microsoft.AnalysisServices.Deployment utility lets you start the Microsoft SQL Server Analysis Services deployment engine from the command prompt. As input file, the utility uses the XML output files generated by building an Analysis Services project in SQL Server Data Tools (SSDT).


QUESTION 3
You need create the data source view for the StandardReports project.
What should you do?

A. Generate a relational schema from the dimensions and cubes by using the Schema Generation wizard.
B. Create a data source, connect it to the data warehouse, and then use the Data Source View wizard.
C. Execute the Import from Table wizard and then use the Data Source View wizard.
D. Create a new data source view and then use the Import from Table wizard.

Answer: B


QUESTION 4
You need to ascertain why Marc did not receive his reports.
What should you do?

A. Search the ReportServerService_<timestamp>.log file for errors.
B. Search the registry for errors.
C. Use the Windows Event Viewer to search the Application log for errors.
D. Use SQL Server Management Studio to search the SQL Server logs for errors.

Answer: B


QUESTION 5
You need to create a measure for DOD sales.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A. Specify a date table by using a Mark as Date table.
B. Use the Data Analysis Expressions (DAX) PARALLELPERIOD() function.
C. Use the Business Intelligence Wizard to define time intelligence.
D. Use the Multidimensional Expressions (MDX) LAG() function.

Answer: A,C

Explanation: * From scenario:
A measure must be created to calculate day-over-day (DOD) sales by region based on order date.
A: Specify Mark as Date Table for use with Time Intelligence (SSAS Tabular)
In order to use time intelligence functions in DAX formulas, you must specify a date table and a unique identifier (datetime) column of the Date data type. Once a column in the date table is specified as a unique identifier, you can create relationships between columns in the date table and any fact tables.
C: The time intelligence enhancement is a cube enhancement that adds time calculations (or time views) to a selected hierarchy. This enhancement supports the following categories of calculations:
Period to date.
Period over period growth. Moving averages.
Parallel period comparisons.

Wednesday, 25 May 2016

Exam 70-466 Implementing Data Models and Reports with Microsoft SQL Server

Published: June 11, 2012
Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
Audiences: IT professionals
Technology: Microsoft SQL Server
Credit toward certification: MCP, MCSE

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.

As of February 18, 2016, this exam includes content covering both SQL Server 2012 and 2014. Please note that this exam does not include questions on features or capabilities that are present only in the SQL Server 2012 product. For more information, please download and review this document.

Build an analysis services multidimensional database (35-40%)
Design dimensions and measures
Given a requirement, identify the dimension/measure group relationship that should be selected; design patterns for representing business facts and dimensions (many-to-many relationships); design dimensions to support multiple related measure groups (many related fact tables); handle degenerate dimensions in a cube; identify the attributes for dimensions; identify the measures; aggregation behavior for the measures; build hierarchies; define granularity of dimension relationships
Implement and configure dimensions in a cube
Translations, define attribute relationships, implement hierarchies, implement SQL Server Analysis Services (SSAS) dimensions and cubes, create the Attribute Relationships that should be made for a given set of attributes in a dimension, develop new custom attributes on dimensions, detect possible design flaws in attribute relationships, implement time dimensions in cubes, manage SSAS parent-child dimensions, dimension type
Design a schema to support cube architecture
Multidimensional modeling starting from a star schema, relational modeling for a data source view, choose or create a topology, identify the appropriate data types with correct precision and size
Create and configure measures
Logically group measures and configure Measure Group Properties, select appropriate aggregation functions, format measures, design the measure group for the correct granularity
Implement a cube
Use SQL Server Data Tools - Business Intelligence (SSDT-BI) to build the cube; use SSDT-BI to do non-additive or semi-additive measures in a cube, define measures, specify perspectives, define translations, define dimension usage, define cube-specific dimension properties, define measure groups, implement reference dimensions, implement many-to-many relationships, implement fact relationships, implement role-playing relationships, create and manage linked measure groups and linked dimensions, create actions
Create Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX) queries
Identify the structures of MDX and the common functions (tuples, sets, TopCount, SCOPE, and more); identify which MDX statement would return the required result; implement a custom MDX or logical solution for a pre-prepared case task; identify the structure of DAX and common functions, including CALCULATE, EVALUATE, and FILTER; identify which DAX query would return the required result
Implement custom logic in a data model
Define key performance indicators (KPIs); define calculated members; create relative measures (growth, YoY, same period last year), percentage of total using MDX; named sets; add Time Intelligence; implement ranking and percentile; define MDX script to import partial PowerPivot model
Implement storage design in a multidimensional model
Create aggregations, create partitions, storage modes, define proactive caching, manage write-back partitions, implement linked cubes, implement distributed cubes
Select an appropriate model for data analysis
Select Tabular versus Multidimensional based on scalability needs, traditional hierarchical, data volume; select appropriate organizational BI, such as corporate BI, and team and personal BI needs and data status

Preparation resources
Dimension relationships
Defining dimension granularity within a measure group
Linked measure groups

Manage, maintain, and troubleshoot a SQL Server Analysis Services (SSAS) database (15-20%)
Analyze data model performance
Identify performance consequences of data source view design, optimize performance by changing the design of the cube or dimension, analyze and optimize performances of an MDX/DAX query, optimize queries for huge data sets, optimize MDX in the calculations, performance monitor counters, select appropriate Dynamic Management Views for Analysis Services, analyze and define performance counters, monitor growth of the cache, define and view logging options
Process data models
Define processing of tables or partitions for tabular and multidimensional models; define processing of databases, cubes, and dimensions for multidimensional models; select full processing versus incremental processing; define remote processing; define lazy aggregations; automate with Analysis Management Objects (AMO) or XML for Analysis (XMLA); process and manage partitions by using PowerShell
Troubleshoot data analysis issues
Use SQL Profiler; troubleshoot duplicate key dimension processing errors; error logs and event viewer logs of SSAS, mismatch of data: incorrect relationships or aggregations; dynamic security issues; validate logic and calculations
Deploy SSAS databases
Deployment Wizard, implement SSDT-BI, deploy SSMS; test solution post deployment, decide whether or not to process, test different roles
Install and maintain an SSAS instance
Install SSAS; install development tools; identify development and production installation considerations; upgrade SSAS instance; define data file and program file location; plan for Administrator accounts; define server and database level security; support scale-out read-only; update SSAS (service packs); install and maintain each instance type of Analysis Services, including PowerPivot; restore and import PowerPivot; back up and restore by using PowerShell

Preparation resources
Multidimensional model object processing
Performance counters (SSAS)

Build a tabular data model (15-20%)
Configure permissions and roles in a tabular model
Configure server roles, configure SSAS database roles, implement dynamic security (custom security approaches), role-based access, test security permissions, implement cell-level permissions
Implement a tabular data model
Define tables, import data, define calculated columns, define relationships, define hierarchies and perspectives, manage visibility of columns and tables, embed links, optimize BISM for Power View, mark a date table, sort a column by another column
Implement business logic in a tabular data model
Implement measures and KPIs, implement Data Analysis Expressions (DAX), define relationship navigation, implement time intelligence, implement context modification
Implement data access for a tabular data model
Manage partitions, processing, select xVelocity versus DirectQuery for data access

Preparation resources
Using DirectQuery in the tabular BI Semantic Model
Roles (SSAS tabular)
Hierarchies (SSAS tabular)

Build a report with SQL Server Reporting Services (SSRS) (25-30%)
Design a report
Select report components (crosstab report, Tablix, design chart, data visualization components), design report templates (Report Definition Language), identify the data source and parameters; design a grouping structure; drill-down reports, drill-through reports; determine if any expressions are required to display data that is not coming directly from the data source
Implement a report layout
Formatting; apply conditional formatting; page configuration; implement headers and footers; implement matrixes, table, chart, images, list, indicators, maps, and groupings in reports; use Report Builder to implement a report layout; create a range of reports using different data regions; define custom fields (implementing different parts of the report); implement collections (global collections); define expressions; implement data visualization components; identify report parts; implement group variables and report variables; design for multiple delivery extension formats
Configure authentication and authorization for a reporting solution
Configure server-level and item-level role-based security, configure reporting service security (setup or addition of role), authenticate against data source, store credential information, describe Report Server security architecture and site level security, create system level roles, item level security, create a new role assignment, assign Windows users to roles, secure reports using roles, configure SharePoint groups and permissions, define varying content for different role memberships
Implement interactivity in a report
Drilldown; drillthrough; interactive sorting; parameters: (databound parameters, multi-value parameters); create dynamic reports in SSRS using parameters; implement show/hide property; actions (jump to report); filters; parameter list; fixed headers; document map, embedded HTML
Troubleshoot reporting services issues
Query the ReportServer database; view Reporting Services log files; use Windows Reliability and Performance monitor data for troubleshooting; use the ReportServer: define service and web service objects; monitor for long-running reports, rendering, and connectivity issues; use SQL Profiler; perform data reconciliation for incorrect relationships or aggregations; detect dynamic security issues; validate logic and calculations
Manage a report environment
Manage subscriptions and subscription settings; define data-driven subscriptions; manage data sources; integrate SharePoint Server; define email delivery settings; manage the number of snapshots; manage schedules, running jobs, and report server logs; manage report server databases; manage the encryption keys; set up the execution log reporting; review the reports; configure site-level settings; design report lifecycle; automate management of reporting services; create a report organization structure; install and configure reporting services; deploy custom assemblies
Configure report data sources and datasets
Select appropriate query types (stored procedure versus table versus text only); configure parameterized connection strings (dynamic connection strings); define filter location (dataset versus query); configure data source options, for example, extract and connect to multiple data sources; shared and embedded data sources and datasets; use custom expressions in data sources; connect to Microsoft Azure SQL database; implement DAX and MDX queries to retrieve appropriate data sets; work with non-relational data sources, such as XML or SharePoint lists

Preparation resources
Tablix data region (Report Builder and SSRS)
Built-in Globals and Users references (Report Builder and SSRS)
Create data-driven subscription page (Report Manager)
QUESTION 1
You need to recommend a solution for the sales department that meets the security requirements.
What should you recommend?

A. Create one role for all of the sales department users. Add a DAX filter that reads the current user name and retrieves the user's region.
B. Create one role for each region. Configure each role to have read access to a specific region. Add the sales department users to their corresponding role.
C. Create a table for each region. Create a role for each region. Grant each role read access to its corresponding table.
D. Create one role for all of the sales department users. Configure the role to have read access to the sales transactions. Ensure that all of the reports that access the sales transaction data restrict read access to the data from the corresponding sales department region only.

Answer: C

Explanation: Scenario: Tailspin Toys identifies the following security requirement:
•Sales department users must be allowed to view the sales transactions from their region only.
•Sales department users must be able to view the contents of the manufacturing reports. •Sales department users must NOT be able to create new manufacturing reports.


QUESTION 2
You need to configure the dataset for the ManufacturingIssues report. The solution must meet the technical requirements and the reporting requirements.
What should you do?

A. Configure the dataset to use a stored procedure. Add the necessary parameters to the stored procedure.
B. Add a query to retrieve the necessary data from the database. Configure the dataset to use query parameters.
C. Add a query to retrieve the necessary data from the database. Configure the dataset to use filter parameters.
D. Configure the dataset to use a table. Ensure that the database has a table that contains the necessary information.

Answer: B


QUESTION 3
You need to ensure that all reports meet the reporting requirements.
What is the best way to achieve the goal? More than one answer choice may achieve the goal. Select the BEST answer.

A. Create a report part. Publish the report part to a server that has SSRS installed. Add the report part to each new report that is created.
B. Create a report part. Publish the report part to a SharePoint site. Add the report part to each new report that is created.
C. Create a report. Copy the report to source code control. Create each new report by using the report template in source code control.
D. Create a report. Copy the report to the PrivateAssemblies\ProjectItems\ReportProject folder in the Visual Studio directory. Create each new report by using the locally stored report

Answer: D


QUESTION 4
You need to configure a hierarchy for DimProduct that meets the technical requirements.
What should you do?

A. Set ProductName as the parent of ProductSubCategory and set ProductSubcategory as the parent of ProductCategory. For ProductSubcategory, click Hide if Name Equals Parent.
B. Set ProductCategory as the parent of ProductSubCategory and set ProductSubcategory as the parent of ProductName. For ProductSubcategory, click Hide if Name Equals Parent.
C. Set ProductName as the parent of ProductSubcategory and set ProductSubCategory as the parent of ProductCategory. For ProductCategory, click Hide if Name Equals Parent.
D. Set ProductCategory as the parent of ProductSubcategory and set ProductSubCategory as the parent of ProductName. For ProductCategory, click Hide if Name Equals Parent.

Answer: B


QUESTION 5
You need to recommend a solution to meet the requirements for the
ManufacturingIssues.rdl report.
What is the best solution that you should include in the recommendation? More than one answer choice may achieve the goal. Choose the BEST answer.

A. Add a dataset to the report that uses an ad hoc SQL statement. Configure the dataset to include the parameters required for the different views. Add a dataset for each parameter created. Configure each parameter to use the values in the dataset.
B. Add a dataset to the report that uses an ad hoc SQL statement. Configure the dataset to include the parameters required for the different views. Update each parameter to use a set of values from Report Designer.
C. Add a dataset to the report that uses an ad hoc SQL statement. Configure the dataset to include the parameters required for the different views. Use the default display for the parameters.
D. Add a dataset to the report that uses a stored procedure. Configure the dataset to include the parameters required for the different views. Update each parameter to use a set of values from Report Designer.

Answer: C

Sunday, 15 May 2016

Exam 70-462 Administering Microsoft SQL Server 2012/2014 Databases

Published: June 11, 2012
Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
Audiences: IT professionals
Technology: Microsoft SQL Server 2012/2014
Credit toward certification: MCP, MCSA, MCSE

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.

As of February 18, 2016, this exam includes content covering both SQL Server 2012 and 2014. Please note that this exam does not include questions on features or capabilities that are present only in the SQL Server 2012 product. For more information, please download and review this document.

Install and configure (20–25%)
Plan installation
Evaluate installation requirements; design the installation of SQL Server and its components (drives, service accounts, etc.); plan scale-up vs. scale-out basics; plan for capacity, including if/when to shrink, grow, autogrow, and monitor growth; manage the technologies that influence SQL architecture (for example, service broker, full text, scale out, etc.); design the storage for new databases (drives, filegroups, partitioning); design database infrastructure; configure a SQL Server standby database for reporting purposes; Windows-level security and service level security; Core mode installation; benchmark a server before using it in a production environment (SQLIO, Tests on SQL Instance); choose the right hardware
Install SQL Server and related services
Test connectivity; enable and disable features; install SQL Server database engine and SSIS (not SSRS and SSAS); configure an OS disk
Implement a migration strategy
Restore vs detach/attach; migrate security; migrate from a previous version; migrate to new hardware; migrate systems and data from other sources
Configure additional SQL Server components
Set up and configure all SQL Server components (Engine, AS, RS and SharePoint integration) in a complex and highly secure environment; configure full-text indexing; SSIS security; filestream; filetable
Manage SQL Server Agent
Create, maintain, and monitor jobs; administer jobs and alerts; automate (setup, maintenance, monitoring) across multiple databases and multiple instances; send to "Manage SQL Server Agent jobs"

Preparation resources
Understanding surface area configuration
Hardware and software requirements for installing SQL Server 2012
Quick-start installation of SQL Server 2012

Maintain instances and databases (15–20%)
Manage and configure databases
Design multiple file groups; database configuration and standardization: autoclose, autoshrink, recovery models; manage file space, including adding new filegroups and moving objects from one filegroup to another; implement and configure contained databases; data compression; configure TDE; partitioning; manage log file growth; DBCC
Configure SQL Server instances
Configure and standardize a database: autoclose, autoshrink, recovery models; install default and named instances; configure SQL to use only certain CPUs (affinity masks, etc.); configure server level settings; configure many databases/instance, many instances/server, virtualization; configure clustered instances including MSDTC; memory allocation; database mail; configure SQL Server engine: memory, filffactor, sp_configure, default options
Implement a SQL Server clustered instance
Install a cluster; manage multiple instances on a cluster; set up subnet clustering; recover from a failed cluster node
Manage SQL Server instances
Install an instance; manage interaction of instances; SQL patch management; install additional instances; manage resource utilization by using Resource Governor; cycle error logs

Preparation resources
ALTER DATABASE file and filegroup options (Transact-SQL)
Contained databases
Data compression

Optimize and troubleshoot (15–20%)
Identify and resolve concurrency problems
Examine deadlocking issues using the SQL server logs using trace flags; design reporting database infrastructure (replicated databases); monitor via DMV or other MS product; diagnose blocking, live locking and deadlocking; diagnose waits; performance detection with built in DMVs; know what affects performance; locate and if necessary kill processes that are blocking or claiming all resources
Collect and analyze troubleshooting data
Monitor using Profiler; collect performance data by using System Monitor; collect trace data by using SQL Server Profiler; identify transactional replication problems; identify and troubleshoot data access problems; gather performance metrics; identify potential problems before they cause service interruptions; identify performance problems;, use XEvents and DMVs; create alerts on critical server condition; monitor data and server access by creating audit and other controls; identify IO vs. memory vs. CPU bottlenecks; use the Data Collector tool
Audit SQL Server instances
Implement a security strategy for auditing and controlling the instance; configure an audit; configure server audits; track who modified an object; monitor elevated privileges as well as unsolicited attempts to connect; policy-based management

Preparation resources
blocked process threshold server configuration option
Configure login auditing (SQL Server Management Studio)
Data collection

Manage data (20–25%)
Configure and maintain a back-up strategy
Manage different backup models, including point-in-time recovery; protect customer data even if backup media is lost; perform backup/restore based on proper strategies including backup redundancy; recover from a corrupted drive; manage a multi-TB database; implement and test a database implementation and a backup strategy (multiple files for user database and tempdb, spreading database files, backup/restore); back up a SQL Server environment; back up system databases
Restore databases
Restore a database secured with TDE; recover data from a damaged DB (several errors in DBCC checkdb); restore to a point in time; file group restore; page level restore
Implement and maintain indexes
Inspect physical characteristics of indexes and perform index maintenance; identify fragmented indexes; identify unused indexes; implement indexes; defrag/rebuild indexes; set up a maintenance strategy for indexes and statistics; optimize indexes (full, filter index); statistics (full, filter) force or fix queue; when to rebuild vs. reorg and index; full text indexes; column store indexes
Import and export data
Transfer data; bulk copy; bulk insert

Preparation resources
Backup and restore of SQL Server databases
File restores (full recovery mode)
DBCC INDEXDEFRAG (Transact-SQL)

Implement security (15–20%)
Manage logins and server roles
Configure server security; secure the SQL Server using Windows Account / SQL Server accounts, server roles; create log in accounts; manage access to the server, SQL Server instance, and databases; create and maintain user-defined server roles; manage certificate logins
Manage database security
Configure database security; database level, permissions; protect objects from being modified; auditing; encryption
Manage users and database roles
Create access to server / database with least privilege; manage security roles for users and administrators; create database user accounts; contained login
Troubleshoot security
Manage certificates and keys; endpoints

Preparation resources
Server-level roles
Permissions (database engine)
Database-level roles

Implement high availability (5–10%)
Implement AlwaysOn
Implement AlwaysOn availability groups; implement AlwaysOn failover clustering
Implement replication
Troubleshoot replication problems; identify appropriate replication strategy

Preparation resources
AlwaysOn Availability Groups (SQL Server)
Microsoft SQL Server AlwaysOn solutions guide for high availability and disaster recovery
AlwaysOn architecture guide: Building a high availability and disaster recovery solution by using AlwaysOn Availability Groups

QUESTION 1
You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema.
Some users must be prevented from deleting records in any of the tables in the Sales schema.
You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort.
What should you do?

A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.
C. Deny Delete permissions on each table in the Sales schema for each user.
D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.

Answer: A


QUESTION 2
You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster.
An additional node named Node C has been added to the existing cluster.
You need to ensure that the SQL Server instance can use all nodes of the cluster.
What should you do?

A. Run the New SQL Server stand-alone installation Wizard on Node C.
B. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
C. Use Node B to install SQL Server on Node C.
D. Use Node A to install SQL Server on Node C.

Answer: B

Explanation:
http://technet.microsoft.com/en-us/library/ms191545.aspx
To add a node to an existing SQL Server failover cluster, you must run SQL Server Setup on the node that is to be added to the SQL Server failover cluster instance. Do not run Setup on the active node.
The Installation Wizard will launch the SQL Server Installation Center. To add a node to an existing failover cluster instance, click Installation in the left-hand pane. Then, select Add node to a SQL Server failover cluster.


QUESTION 3
You administer a Microsoft SQL Server 2012 database named ContosoDB. The database contains a table named Suppliers and a column named IsActive in the Purchases schemA.
You create a new user named ContosoUser in ContosoDB. ContosoUser has no permissions to the Suppliers table.
You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions.
Which Transact-SQL statement should you use?

A. GRANT DELETE ON Purchases. Suppliers TC ContosoUser

B. CREATE PROCEDURE Purchases.PurgelnactiveSuppliers WITH EXECUTE AS USER = 'dbo'
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser

D. CREATE PROCEDURE Purchases. PurgeInactiveSuppliers AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases. PurgeInactiveSuppliers TO ContosoUser

Answer: B

Explanation: Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms188354.aspx
http://msdn.microsoft.com/en-us/library/ms187926.aspx

QUESTION 4
You administer a Microsoft SQL Server 2012 instance. After a routine shutdown, the drive that contains tempdb fails.
You need to be able to start the SQL Server.
What should you do?

A. Modify tempdb location in startup parameters.
B. Start SQL Server in minimal configuration mode.
C. Start SQL Server in single-user mode.
D. Configure SQL Server to bypass Windows application logging.

Answer: B

Explanation: Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms186400.aspx
http://msdn.microsoft.com/en-us/library/ms345408.aspx


QUESTION 5
You use a contained database named ContosoDb within a domain.
You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?

A. User mapped to a certificate
B. SQL user without login
C. Domain user
D. SQL user with login

Answer: C