Category Archives: MCSE

Exam 70-339 Managing Microsoft SharePoint Server 2016

Published: June 20, 2016
Languages: English
Audiences: IT professionals
Technology: Microsoft SharePoint Server 2016
Credit toward certification: 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.

Design SharePoint infrastructure (15–20%)
Design information architecture
Design an inter-site navigational taxonomy; design site columns and content types; design keywords, synonyms, promoted results, managed properties, durable links, and document library accessibility; plan information management policies; plan compliance features, such as document fingerprinting and item-level encryption; plan search for sensitive and non-sensitive content; plan managed site structures; plan term sets; plan for support of Open Document Format (ODF); plan mobile navigation
Design a logical architecture
Plan application pools; plan web applications; plan for software boundaries; plan content databases; plan host-header site collections; plan Fast Site Collection creation; plan zones and alternate access mapping
Design a physical architecture
Design a server farm topology (traditional, streamlined, hybrid) by using MinRole (user roles, robot services roles, cache services roles); design Central Administration deployment; design a storage architecture, including ReFS; configure basic request management; define individual server requirements; define service topologies; plan server load balancing; plan a network infrastructure; plan for large file support; plan an app hosting model
Plan an installation
Identify and configure installation prerequisites, implement scripted deployment, plan Access Services deployment, plan Project Server installation, implement patch slipstreaming, plan and install language packs, plan and configure service connection points, plan installation tracking and auditing, plan and install Office Online Server, implement managed paths for Office 365 migrations, configure SharePoint hybrid cloud settings
Plan a hybrid cloud environment
Plan for deployment of Office Online; configure server-to-server authentication; configure OAuth; configure Audiences and hybrid features, to include Search, OneDrive for Business, Team Sites, extensible App Launcher, and hybrid picker tools

Plan authentication and security (15–20%)
Plan and configure authentication
Plan and configure Windows authentication, plan and configure anonymous authentication, plan connection encryption (TLS, SMTP), plan and configure identity federation, configure claims providers, configure site-to-site (S2S) intra-server and OAuth authentication, configure connections to Access Control Service, configure authentication for hybrid cloud deployment
Plan and configure authorization
Plan and configure SharePoint users and groups, plan and configure People Picker, plan and configure sharing (hints, one-click, create and share), plan and configure permission inheritance, plan and configure anonymous access, plan web application policies
Plan and configure platform and farm security
Plan and configure security isolation, services lockdown, and antivirus settings; plan and configure certificate management; plan for Kerberos support for service applications; plan and configure information rights management (IRM), delegated farm administration, and delegated service application administration; plan and configure managed accounts; plan and configure blocked file types; plan and configure web part security, including WOPI support
Create and configure a User Profile Service (UPA) application
Configure a UPA application, configure social permissions, plan and configure sync connections of MIM 2016 synchronization, configure profile properties, configure claims integration with UPA
Manage site and site collection security
Manage site access requests, manage App permissions, manage anonymous access, manage permission inheritance, configure permission levels, configure HTML field security
Provision and configure web applications
Create managed paths, configure Alternate Access Mappings (AAM), configure SharePoint Designer settings

Plan workload optimization (15–20%)
Create and maintain site collections
Provision Fast Site Collection Creation, configure Host header site collections, configure self-service site creation, maintain site owners, maintain site quotas, configure site policies, configure a team mailbox, plan Sites Page Pinning
Plan SharePoint high availability and disaster recovery solutions
Plan for service distribution, plan for service instance configuration, plan for physical server distribution, plan for network redundancy, plan for server load balancing, plan for SQL Server aliases, plan for SQL Server clustering, plan for SQL Server AlwaysOn Availability Groups, plan for SQL Server Log Shipping, plan for storage redundancy, plan for login replication
Plan backup and restore
Establish a SharePoint backup schedule, establish a SQL Server backup schedule, plan a non-production environment content refresh, plan for farm configuration recovery, plan for service application recovery, plan for content recovery, configure a recovery solution using SQL Database running in Azure and other Azure backup solutions
Plan and configure social workloads
Plan communities, plan My Sites, plan OneDrive redirection, plan social permissions, plan user profiles, plan activity feeds, plan connections, configure Yammer settings
Plan and configure a Web Content Management (WCM) workload
Plan and configure channels, plan and configure product catalog and topic pages, plan and configure Design Manager, plan and configure content deployment, plan and configure display templates, plan and configure variations
Plan and configure an Enterprise Content Management (ECM) workload
Plan and configure E-Discovery; plan and configure document routing; plan and configure co-authoring; plan and configure durable links; plan and configure record disposition and retention; plan large document repositories; plan and configure software boundaries; plan and configure Data Loss Prevention, In-Place Holds, and Document Deletion features

Plan productivity solutions (15–20%)
Evaluate content and customizations
Perform migration pre-check tasks, analyze content database test results, configure web application authentication for upgrade, resolve orphan objects, resolve missing file system components, resolve configuration conflict issues
Plan an upgrade process
Plan removal of servers in rotation, configure a parallel upgrade, configure read-only access for content, configure upgrade farms, measure upgrade performance, plan an installation sequence, plan and migrate SharePoint on-premises to SharePoint Online or a hybrid topology
Create and configure app management
Create and configure the App Store, create and configure subscriptions, configure marketplace connections, configure DNS entries, configure wildcard certificates
Create and configure productivity services
Create and configure Office Online Server, Microsoft Access services, Microsoft Visio services, Microsoft Word Automation services, Microsoft PowerPoint Conversion services, and Translation Services; configure hybrid OneDrive for Business with Profile Redirection and Extensible App Launcher; plan and install Office Online Server, SharePoint Insights, and SharePoint Server Telemetry features
Create and configure a Business Connectivity Service (BCS) and Secure Store application
Import and configure BCS models, configure BCS model security, configure BCS for search, generate a Secure Store master key, manage Secure Store target application permissions, create Secure Store target applications, configure hybrid BCS
Manage SharePoint solutions and applications
Manage sandbox solution quotas, configure sandbox solution management, deploy farm solutions, upgrade farm solutions, deploy Apps, upgrade Apps

Manage search capabilities (10–15%)
Create and configure Enterprise search
Plan and configure a search topology, plan and configure content sources, plan and configure crawl schedules, plan and configure crawl rules, plan and configure crawl performance, plan and configure security trimming, choose and configure hybrid search
Create and configure a Managed Metadata Service (MMS) application
Configure proxy settings for managed service applications, configure content type hub settings, configure sharing term sets, plan and configure content type propagation schedules, configure custom properties, configure term store permissions, configure managed metadata service (MMS) imports
Manage search
Manage result sources; manage query rules; manage display templates; manage Search Engine Optimization (SEO) settings; manage result types; manage a search schema; manage federated search, including integration with Delve and Office Graph
Manage taxonomy
Manage site collection term set access, manage term set navigation, manage topic catalog pages, configure custom properties, configure search refinement, configure list refinement
Plan and configure a search workload
Plan and configure search result relevancy, plan and configure index freshness, plan and configure result sources, plan and configure the end-user experience, plan and configure a search schema, analyze search analytics reports

Plan and configure cloud services (5–10%)
Plan and configure a BI infrastructure
Plan and configure Performance Point, plan and configure reporting services, plan and configure PowerPivot, plan and configure Excel Services (Office Online only), plan and configure PowerView, plan and configure BI security
Create and configure work management
Configure a connection to Exchange, activate and configure a connection to Microsoft Project Server 2016, manage trusts, plan Exchange configuration for E-Discovery
Plan and configure cloud and federated hybrid search
Plan and configure on-premises and Office 365 search and encrypted metadata when content is transferred to the search index in Office 365, configure user specific queries and inbound/outbound or bi-directional hybrid topologies

Monitor and optimize a SharePoint environment (10–15%)
Monitor a SharePoint environment
Define monitoring requirements, configure performance counter capture, configure page performance monitoring, configure usage and health providers, monitor and forecast storage needs, monitor SharePoint hybrid cloud deployments
Tune and optimize a SharePoint environment
Plan and configure SQL optimization, execute database maintenance rules, plan for capacity software boundaries, estimate storage requirements, plan and configure caching and a caching strategy, tune network performance, plan and configure Zero Downtime Patching
Troubleshoot a SharePoint environment
Establish baseline performance, perform client-side tracing, perform server-side tracing, analyze usage data, enable a developer dashboard, analyze diagnostic logs, troubleshoot SharePoint hybrid cloud issues


QUESTION 1
You are the administrator for a SharePoint 2016 farm. You configure a connection to the SharePoint Store.
Users report that they cannot select or install specific apps from the SharePoint Store. Users can install other apps.
You need to allow users to purchase and install any type of apps from the SharePoint Store.
What should you do?

A. Activate the Internet-facing endpoints feature for apps.
B. Configure the Store settings to allow users to purchase apps.
C. Deploy an App Catalog.
D. Delete theexisting App Catalog. Then, delete and provision the Subscription Setting and App Management service applications.

Answer: A

Explanation:
References:
https://technet.microsoft.com/en-us/library/fp161236.aspx


QUESTION 2
During the upgrade from SharePoint 2013 to SharePoint 2016, you discover there is a missing feature.
You review the logs and observe the following error message:
Message: Database [WSS_Contoso_OR01] has reference(s) to a missing feature:
Id= [4be9a980-965f-486f-9eda-074e284875d7].
Remedy:
The feature with Id
4be9a980-965f-486f-9eda-074e284875d7
is referenced in the database [WSS_Contoso_OR01], but is not installed on the current farm.
The missing feature may cause upgrade to fail. Please install any solution which contains
the feature and restart upgrade if necessary.
You need to identify the page that is missing the feature.
Which of the following will identify the page?

A. On the SharePoint Server, run the following Windows PowerShell
command:Test-SPContentDatabase-Name $dbName-WebApplication $wa| Select Category,
Message | Where {$_.Category-eq “MissingFeature”}

B. On the SharePoint Server, run the following Windows PowerShell
command:Test-SPContentDatabase-Name $dbName-WebApplication $wa| Select Category,
Message | Where{$_.Category-eq “MissingSetupFile”}

C. On the SQL Server, run the following SQL syntax against
WSS_Contoso_OR01:SELECT FeatureId, FullURL From WebsInner JOIN FeaturesOn
Webs.Id=Features.WebIdWHERE FeatureId=‘4be9a980-965f-486f-9eda-074e284875d7’ORDERBY FeatureId

D. On the SharePoint Server, review theHealth Analyzerand find the missing dependencies.

Answer: A

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

MCTS Training, MCITP Trainnig

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

Exam 70-744 Securing Windows Server 2016 (In development)

Published: November 3, 2016
Languages: English
Audiences: IT professionals
Technology: Windows Server 2016
Credit toward certification: 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.

Implement server hardening solutions (25–30%)
Configure disk and file encryption
Determine hardware and firmware requirements for secure boot and encryption key functionality; deploy BitLocker encryption; deploy BitLocker without a Trusted Platform Module (TPM); deploy BitLocker with a TPM only; configure the Network Unlock feature; configure BitLocker Group Policy settings; enable Bitlocker to use secure boot for platform and BCD integrity validation; configure BitLocker on Clustered Shared Volumes (CSVs) and Storage Area Networks (SANs); implement BitLocker Recovery Process using self-recovery and recovery password retrieval solutions; configure BitLocker for virtual machines (VMs) in Hyper-V; determine usage scenarios for Encrypting File System (EFS); configure the EFS recovery agent; manage EFS and BitLocker certificates, including backup and restore
Implement server patching and updating solutions
Install and configure Windows Server Update Services (WSUS), manage updates using WSUS, create computer groups, configure update approvals and deployments, configure automatic updates, configure WSUS reporting, troubleshoot WSUS configuration and deployments
Implement malware protection
Implement antimalware solution with Windows Defender, integrate Windows Defender with WSUS and Windows Update, configure Windows Defender using Group Policy, configure Windows Defender scans using Windows PowerShell, implement AppLocker rules, implement AppLocker rules using Windows PowerShell, implement Control Flow Guard, implement Code Integrity (Device Guard) Policies, create Code Integrity policy rules, create Code Integrity file rules
Protect credentials
Determine requirements for implementing Credential Guard; configure Credential Guard using Group Policy, WMI, command prompt, and Windows PowerShell; implement NTLM blocking
Create security baselines
Install and configure Security Compliance Manager (SCM); create, view, and import security baselines; deploy configurations to domain and non-domain joined servers

Secure a virtualization infrastructure (5–10%)
Implement a Guarded Fabric solution
Install and configure the Host Guardian Service (HGS), configure Admin-trusted attestation, configure TPM-trusted attestation, configure the Key Protection Service using HGS, migrate Shielded VMs to other guarded hosts, configure Nano Server as TPM attested guarded host, troubleshoot guarded hosts
Implement Shielded and encryption-supported VMs
Determine requirements and scenarios for implementing Shielded VMs, create a Shielded VM using only a Hyper-V environment, enable and configure vTPM to allow an operating system and data disk encryption within a VM, determine requirements and scenarios for implementing encryption-supported VMs, troubleshoot Shielded and encryption-supported VMs

Secure a network infrastructure (10–15%)
Configure Windows Firewall
Configure Windows Firewall with Advanced Security; configure network location profiles; configure and deploy profile rules; configure firewall rules for multiple profiles using Group Policy; configure connection security rules using Group Policy, the GUI management console, or Windows PowerShell; configure Windows Firewall to allow or deny applications, scopes, ports, and users using Group Policy, the GUI management console, or Windows PowerShell; configure authenticated firewall exceptions; import and export settings
Implement a software-defined Distributed Firewall
Determine requirements and scenarios for Distributed Firewall implementation with software-defined networking, determine usage scenarios for Distributed Firewall policies and network security groups
Secure network traffic
Configure IPsec transport and tunnel modes, configure IPsec authentication options, configure connection security rules, implement isolation zones, implement domain isolation, implement server isolation zones, determine SMB 3.1.1 protocol security scenarios and implementations, enable SMB encryption on SMB Shares, configure SMB signing via Group Policy, disable SMB 1.0, secure DNS traffic using DNSSEC and DNS policies, install and configure Microsoft Message Analyzer (MMA) to analyze network traffic

Manage privileged identities (25–30%)
Implement an Enhanced Security Administrative Environment (ESAE) administrative forest design approach
Determine usage scenarios and requirements for implementing ESAE forest design architecture to create a dedicated administrative forest, determine usage scenarios and requirements for implementing clean source principals in an Active Directory architecture
Implement Just-in-Time (JIT) Administration
Create a new administrative (bastion) forest in an existing Active Directory environment using Microsoft Identity Manager (MIM), configure trusts between production and bastion forests, create shadow principals in bastion forest, configure the MIM web portal, request privileged access using the MIM web portal, determine requirements and usage scenarios for Privileged Access Management (PAM) solutions, create and implement MIM policies, implement Just-in-Time administration principals using time-based policies, request privileged access using Windows PowerShell
Implement Just-Enough-Administration (JEA)
Enable a JEA solution on Windows Server 2016; create and configure session configuration files, create and configure role capability files, create a JEA endpoint, connect to a JEA endpoint on a server for administration, view logs, download WMF 5.1 to a Windows Server 2008 R2, configure a JEA endpoint on a server using Desired State Configuration (DSC)
Implement Privileged Access Workstations (PAWs) and User Rights Assignments
Implement a PAWS solution, configure User Rights Assignment group policies, configure security options settings in Group Policy, enable and configure Remote Credential Guard for remote desktop access
Implement Local Administrator Password Solution (LAPS)
Install and configure the LAPS tool, secure local administrator passwords using LAPS, manage password parameters and properties using LAPS

Implement threat detection solutions (15–20%)
Configure advanced audit policies
Determine the differences and usage scenarios for using local audit policies and advanced auditing policies; implement auditing using Group Policy and AuditPol.exe; implement auditing using Windows PowerShell; create expression-based audit policies; configure the Audit PNP Activity policy; configure the Audit Group Membership policy; enable and configure Module, Script Block, and Transcription logging in Windows PowerShell
Install and configure Microsoft Advanced Threat Analytics (ATA)
Determine usage scenarios for ATA; determine deployment requirements for ATA, install and configure ATA Gateway on a dedicated server, install and configure ATA Lightweight Gateway directly on a domain controller, configure alerts in ATA Center when suspicious activity is detected, review and edit suspicious activities on the attack time line
Determine threat detection solutions using Operations Management Suite (OMS)
Determine usage and deployment scenarios for OMS, determine security and auditing functions available for use; determine Log Analytics usage scenarios

Implement workload-specific security (5–10%)
Secure application development and server workload infrastructure
Determine usage scenarios, supported server workloads, and requirements for Nano Server deployments; install and configure Nano Server; implement security policies on Nano Servers using Desired State Configuration (DSC); determine usage scenarios and requirements for Windows Server and Hyper-V containers; install and configure Hyper-V containers
Implement a secure file services infrastructure and Dynamic Access Control (DAC)
Install the File Server Resource Manager (FSRM) role service, configure quotas, configure file screens, configure storage reports, configure file management tasks, configure File Classification Infrastructure (FCI) using FSRM, implement work folders, configure file access auditing, configure user and device claim types, implement policy changes and staging, perform access-denied remediation, create and configure Central Access rules and policies, create and configure resource properties and lists

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

MCTS Training, MCITP Trainnig

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

Exam 70-980 Recertification for MCSE: Server Infrastructure

Published: September 12, 2014
Languages: English, Japanese
Audiences: IT professionals
Technology: Windows Server 2012
Credit toward certification: MCP, MCSE

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.

Plan and deploy a server infrastructure
Plan and deploy Virtual Machine Manager services
Design Virtual Machine Manager service templates, define operating system profiles, configure hardware and capability profiles, manage services, configure image and template libraries, manage logical networks
Plan and implement file and storage services
Planning considerations, including iSCSI SANs, Fibre Channel SANs, Virtual Fibre Channel, storage spaces, storage pools, and data deduplication; configure the iSCSI Target server; configure the Internet Storage Name server (iSNS); configure Network File System (NFS); install Device Specific Modules (DSMs)

Design and implement network infrastructure services
Design a name resolution solution strategy
Design considerations, including secure name resolution, DNSSEC, DNS Socket Pool, cache locking, disjoint namespaces, DNS interoperability, migration to application partitions, IPv6, Single-Label DNS Name Resolution, zone hierarchy, and zone delegation
Design and manage an IP address management solution
Design considerations, including IP address management technologies, such as IPAM, Group Policy based, and manual provisioning, and distributed versus centralized placement; configure role-based access control; configure IPAM auditing; migrate IPs; manage and monitor multiple DHCP and DNS servers; configure data collection for IPAM

Design and implement network access services
Design a VPN solution
Design considerations, including certificate deployment, firewall configuration, client/site to site, bandwidth, protocol implications, and VPN deployment configurations using Connection Manager Administration Kit (CMAK)
Design a DirectAccess solution
Design considerations, including topology, migration from Forefront UAG, DirectAccess deployment, and enterprise certificates
Design a Web Application Proxy solution
Design considerations, including planning for applications, authentication, and authorization, Workplace Join, devices, multifactor authentication, multifactor access control, single sign-on (SSO), certificates, and planning access for internal and external clients
Implement a scalable remote access solution
Configure site-to-site VPN, configure packet filters, implement packet tracing, implement multi-site Remote Access, configure Remote Access clustered with Network Load Balancing (NLB), configure DirectAccess
Design and implement a network protection solution
Design considerations, including Network Access Protection (NAP) enforcement methods for DHCP, IPSec, VPN, and 802.1x, capacity, placement of servers, firewall, Network Policy Server (NPS), and remediation network, configure NAP enforcement for IPsec and 802.1x, monitor for compliance

Design and implement an Active Directory infrastructure (logical)
Design a Group Policy strategy
Design considerations, including inheritance blocking, enforced policies, loopback processing, security, and WMI filtering, site-linked Group Policy Objects (GPOs), slow-link processing, group strategies, organizational unit (OU) hierarchy, and Advanced Group Policy Management (AGPM)

Design and implement an Active Directory infrastructure (physical)
Design a domain controller strategy
Design considerations, including global catalog, operations master roles, Read-Only Domain Controllers (RODCs), partial attribute set, and domain controller cloning
Design and implement a branch office infrastructure
Design considerations, including RODC, Universal Group Membership Caching (UGMC), global catalog, DNS, DHCP, and BranchCache; implement confidential attributes; delegate administration; modify filtered attributes set; configure password replication policy; configure hash publication

Manage and maintain a server infrastructure
Design a monitoring strategy
Design considerations, including monitoring servers using Audit Collection Services (ACS), performance monitoring, centralized monitoring, and centralized reporting; implement and optimize System Center 2012 – Operations Manager management packs; plan for monitoring Active Directory
Implement automated remediation
Create an Update Baseline in Virtual Machine Manager; implement a Desired Configuration Management (DCM) Baseline; implement Virtual Machine Manager integration with Operations Manager; configure Virtual Machine Manager to move a virtual machine (VM) dynamically, based on policy; integrate System Center 2012 for automatic remediation into your existing enterprise infrastructure

Plan and implement failover clustering

Plan and implement failover clustering
Plan for multi-node and multi-site clustering; design considerations, including redundant networks, network priority settings, resource failover and failback, heartbeat and DNS settings, Quorum configuration, and storage placement and replication
Plan and implement highly available network services
Plan for and configure NLB; design considerations, including fault-tolerant networking, multicast versus unicast configuration, state management, and automated deployment of NLB using Virtual Machine Manager service templates
Plan and implement highly available storage solutions
Plan for and configure storage spaces and storage pools; design highly available, multi-replica DFS namespaces; plan for and configure multi-path I/O, including Server Core; configure highly available iSCSI Target and iSNS Server
Plan and implement highly available roles
Plan for a highly available Dynamic Host Configuration Protocol (DHCP) Server, Hyper-V clustering, Continuously Available File Shares, and a DFS Namespace Server; plan for and implement highly available applications, services, and scripts using Generic Application, Generic Script, and Generic Service clustering roles
Plan and implement a business continuity and disaster recovery solution
Plan a backup and recovery strategy; planning considerations, including Active Directory domain and forest recovery, Hyper-V replica, domain controller restore and cloning, and Active Directory object and container restore using authoritative restore and Recycle Bin

Plan and implement a server virtualization infrastructure
Plan and implement virtualization hosts
Plan for and implement delegation of virtualization environment (hosts, services, and VMs), including self-service capabilities; plan and implement multi-host libraries, including equivalent objects; plan for and implement host resource optimization; integrate third-party virtualization platforms
Plan and implement virtual machines
Plan for and implement highly available VMs; plan for and implement guest resource optimization, including shared VHDx; configure placement rules; create Virtual Machine Manager templates
Plan and implement virtualization networking
Plan for and configure Virtual Machine Manager logical networks, including virtual switch extensions and logical switches; plan for and configure IP address and MAC address settings across multiple Hyper-V hosts, including network virtualization; plan for and configure virtual network optimization; plan and implement Windows Server Gateway; plan and implement VLANs and pVLANs; plan and implement virtual machine networks; plan and implement converged networks
Plan and implement virtualization storage
Plan for and configure Hyper-V host clustered storage; plan for and configure Hyper-V virtual machine storage, including virtual Fibre Channel, iSCSI, and shared VHDx; plan for storage optimization; plan and implement storage using SMB 3.0 file shares
Plan and implement virtual machine movement
Plan for and configure live and storage migration between Hyper-V hosts; plan for and manage P2V and V2V; plan and implement virtual machine migration between clouds
Manage and maintain a server virtualization infrastructure
Manage dynamic optimization and resource optimization, integrate Operations Manager with System Center Virtual Machine Manager and System Center Service Manager, update virtual machine images in libraries, plan for and implement backup and recovery of virtualization infrastructure by using System Center Data Protection Manager (DPM)

Design and implement identity and access solutions
Design and implement a federated identity solution
Plan for and implement claims-based authentication, including planning and implementing Relying Party Trusts; plan for and configure Claims Provider and Relying Party Trust claim rules; plan for and configure attribute stores, including Active Directory Lightweight Directory Services (AD LDS); plan for and manage Active Directory Federation Services (AD FS) certificates; plan for and implement Identity Integration with cloud services; integrate Web Application Proxy with AD FS
Design and implement Active Directory Rights Management Services (AD RMS)
Plan for highly available AD RMS deployment, plan for AD RMS client deployment, manage Trusted User Domains, manage Trusted Publishing Domains, manage Federated Identity support, upgrade or migrate AD RMS, decommission AD RMS

QUESTION 1
You implement and authorize the new DHCP servers. You import the server configurations and the scope configurations from PA1 and AM1.
You need to ensure that clients can obtain DHCP address assignments after you shut down PA1 and AM1. The solution must meet the technical requirements.
What should you do?

A. Run the Get-DhcpServerv4Lease cmdlet and the Remove-DhcpServerv4Lease cmdlet. Run the Windows Server Migration Tools.
B. Run the Get-DhcpServerv4Lease cmdlet and the Add-DhcpServerv4Lease cmdlet. Activate the scopes.
C. Run the Get-DhcpServerv4FreeIPAddress cmdlet and the Invoke-DhcpServerv4FailoverReplication cmdlet. Run the Windows Server Migration Tools.
D. Run the Get-DhcpServerv4FreeIPAddress cmdlet and the Invoke-DhcpServerv4FailoverReplication cmdlet Activate the scopes.

Answer: B


QUESTION 2
You need to recommend a management solution for the GPOs. The solution must meet the technical requirements. What should you include in the recommendation?

A. Microsoft Baseline Security Analyzer (MBSA)
B. Microsoft Desktop Optimization Pack (MDOP)
C. Microsoft System Center 2012 Operations Manager
D. Microsoft System Center 2012 Data Protection Manager (DPM)

Answer: B

Explanation:

D:\Documents and Settings\useralbo\Desktop\1.jpg
Microsoft Desktop Optimization Pack
Windows Vista Enterprise helps global organizations and enterprises with complex IT infrastructures lower IT costs, reduce risk, and stay connected. The Microsoft Desktop Optimization Pack for Software Assurance further extends this value by reducing application deployment costs, enabling delivery of applications as services, and allowing for better management and control of enterprise desktop environments. Together these technologies deliver a highly cost-effective and flexible Windows desktop management solution.
What is the Microsoft Desktop Optimization Pack?
The Microsoft Desktop Optimization Pack (MDOP) for Software Assurance is an add-on subscription license available to Software Assurance customers. It uses innovative technologies to help reduce the total cost of ownership (TCO) of the Windows desktop by accelerating operating system and application management and enhancing IT responsiveness and end-user uptime. It will enable you to better control the desktop, accelerate and simplify desktop deployments and management, and create a dynamic infrastructure by turning software into centrally managed services.
MDOP facilitates accelerated deployment and manageability of Windows through these innovative technologies— available only to Windows Software Assurance customers. http://technet.microsoft.com/en-us/library/cc507880.aspx


QUESTION 3
You are planning the decommissioning of research.contoso.com.
You need to ensure that an administrator named Admin5 in the research department can manage the user accounts that are migrated to contoso.com. The solution must minimize the number of permissions assigned to Admin5.
What should you do before you migrate the user accounts?

A. Run the New-Object cmdlet, and then run the Add-ADPrincipalGroupMembershipcmdlet.
B. Create a new organizational unit (OU), and then add Admin5 to the Account Operators group.
C. Create a new organizational unit (OU), and then run the Delegation of Control Wizard.
D. Run the New-Object cmdlet, and then run the Add-ADCentralAccessPolicyMembercmdlet.

Answer: C

Explanation:

D:\Documents and Settings\useralbo\Desktop\1.jpg
http://technet.microsoft.com/en-us/library/dd145344.aspx


QUESTION 4
You need to recommend changes to the DNS environment that support the implementation of the sales.contoso.com domain. The solution must ensure that the users in all of the
domains can resolve both Internet names and the names of the servers in all of the internal domains.
What should you recommend?

A. On the DNS servers in contoso.com, configure a reverse lookup zone. On the DNS servers in sales.contoso.com, configure a conditional forwarder to contoso.com.
B. On the DNS servers in contoso.com, add a conditional forwarder to the sales.contoso.com zone. On the DNS servers in sales.contoso.com, add a forwarder to the DNS servers of the company’s ISP.
C. On the DNS servers in contoso.com, create a zone delegation in the contoso.com zone. On the DNS servers in sales.contoso.com, add a forwarder to the contoso.com DNS servers.
D. On the DNS servers in contoso.com, configure a conditional forwarder to sales.contoso.com. On the DNS servers in sales.contoso.com, configure a reverse zone.

Answer: C

Explanation:

D:\Documents and Settings\useralbo\Desktop\1.jpg


QUESTION 5
You are evaluating the implementation of data deduplication on the planned Windows Server 2012 file servers.
The planned servers will have the identical disk configurations as the current servers.
You need to identify which volumes can be enabled for data deduplication.
Which volumes should you identify? (Each correct answer presents part of the solution. Choose all that apply.)

A. C
B. D
C. E
D. The CSV
E. DATA

Answer: C,E

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

MCTS Training, MCITP Trainnig

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

Exam 70-981 Recertification for MCSE: Private Cloud

Published: August 13, 2014
Languages: English, Japanese
Audiences: IT professionals
Technology: Windows Server 2012
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.

Configure data center process automation
Implement workflows
Implement SCO Runbook automation; automate remediation of incidents, including resolution of System Center Virtual Machine‒based incidents; design and build end-to-end automation, incorporating System Center 2012 technologies; implement change control and problem resolution workflows
Implement service offerings
Add a new service offering to the service catalog, create a custom workflow using the Service Manager console, reference Orchestrator workflows in Service Manager, implement self-service provisioning of virtual machines

Deploy resource monitoring
Deploy end-to-end monitoring
Deploy agents, discover and deploy network device monitoring, import and configure management packs, deploy and configure monitoring of a heterogeneous virtualization infrastructure
Configure end-to-end monitoring
Configure overrides, create synthetic transactions, configure Outside-In monitoring, configure application performance monitoring (APM), create distributed application models, integrate the monitoring infrastructure with Global Service Monitor (GSM)
Create monitoring reports and dashboards
Implement service-level tracking; implement reports, including chargeback reports and System Center Manager data warehouse reports; implement dashboards

Configure and maintain service management
Implement service level management
Define service level objectives (SLOs) and service level agreements (SLAs); implement SLA management and escalation; maintain SLAs, including planning for future growth; analyze SLAs (reporting)
Manage problems and incidents
Customize problem management, link incidents to problems, and resolve problems; raise, assign, and resolve incidents; perform root cause analysis/diagnosis; integrate problem and incident resolution with change management; create and manage a knowledge base
Manage cloud resources
Deploy new applications to private and public clouds; deploy virtual machine (VM)/service (applications, SQL, roles), deploy multi-tier applications by using service templates, manage applications in private and public clouds, manage VM hardware (CPUs, dynamic memory), manage self service

Configure the fabric
Configure the storage fabric
Classification and pools, providers, arrays, file server cluster, storage pools, file shares
Configure the network fabric
Logical networks; VM networks; IP/MAC resource pools; VIP templates; load balancing; logical switches; network isolation, including network virtualization with Windows Server Gateway; private VLANs and VLANs
Configure and manage the deployment and update servers
Integrate a Windows Server Update Services (WSUS) server into the Virtual Machine Manager fabric; configure and manage a library server; integrate Windows Deployment Services (WDS)/PXE into the Virtual Machine Manager fabric, including virtualization host deployment using Windows Deployment Services (WDS)
Configure clouds and virtualization hosts
Clouds and host groups, configure and deploy virtualization hosts

Configure System Center integration
Configure private cloud integration
Configure Operations Manager connectors; Orchestrator integration packs; Service Manager connectors; SharePoint integration; configure IP Address Management (IPAM) server integration, configure AppController
Configure integration of private and public clouds
Configure Service Provider Framework (SPF), configure Windows Azure Pack for Windows Server

QUESTION 1
You need to implement a solution to meet the IM requirements for the help desk users.
What should you do?

A. Install Unified Communications Managed API 3.0 Runtime on LON-SM1.
B. Install the Lync 2010 client on LON-SM1.
C. Create a Service Manager workflow.
D. Create an IM channel.

Answer: B

Explanation:
Ref: http://technet.microsoft.com/en-us/library/hh495482.aspx


QUESTION 2
You need to implement the alert notifications. The solution must meet the technical requirements. A Lync Server administrator enables an account named ADATUM\IMUser for Unified Communications. What should you do next?

A. Configure ADATUM\IMUser as a Run As Account. Install the Lync Management Pack. Create a notification subscriber and an IM channel.
B. Create a mailbox for the ADATUM\IMUser account Install the Exchange Server Management Pack. Create a notification subscriber and an SMTP channel.
C. Create a mailbox for the ADATUM\IMUser account. Install Microsoft Visual C++ 2008 Runtime. Create a notification subscriber and an SMTP channel.
D. Configure ADATUM\IMUser as a Run As Account. Install Unified Communications
Managed API 3.0 Runtime. Create a notification subscriber and an IM channel.

Answer: D


QUESTION 3
You need to recommend a solution to remediate the Exchange Server 2010 service failures. The solution must meet the technical requirements.
What should you include in the recommendation?

A. Create a service request workflow that triggers a dependent activity.
B. Create an incident event workflow that triggers a dependent activity.
C. Create an incident event workflow that triggers a runbook activity.
D. Create a service request workflow that triggers a runbook activity.

Answer: C


QUESTION 4
You need to recommend a solution to meet the monitoring requirements for App2.
What should you include in the recommendation? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. An aggregate rollup monitor
B. A distributed application
C. A dependency rollup monitor
D. Service monitors

Answer: B


QUESTION 5
You need to recommend a solution to ensure that the sales department managers can create instances of App1 in Cloud1.
What should you include in the recommendation?

A. A subscription in App Controller and a task in Service Manager
B. A dashboard in Operations Manager and a distributed application
C. A task in Operations Manager and a distributed application
D. A service offering in Service Manager and a runbook in Orchestrator

Answer: D

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

MCTS Training, MCITP Trainnig

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

Exam 70-687 Configuring Windows 8.1

Published: September 17, 2012
Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
Audiences: IT professionals
Technology: Windows 8.1
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 January 2014, this exam includes content covering Windows 8.1.

Install and upgrade to Windows 8.1 (10–15%)
Evaluate hardware readiness and compatibility
Choose between an upgrade and a clean installation; determine which SKU to use, including Windows RT; determine requirements for particular features, including Hyper-V, Miracast display, pervasive device encryption, virtual smart cards, and Secure Boot
Install Windows 8.1
Install as Windows To Go, migrate from previous versions of Windows to Windows 8.1, upgrade from Windows 7 or Windows 8 to Windows 8.1, install to VHD, install additional Windows features, configure Windows for additional languages
Migrate and configure user data
Migrate user profiles; configure folder location; configure profiles, including profile version, local, roaming, and mandatory

Preparation resources
Utility spotlight: Are you compatible with Windows 8?
Install, deploy, and migrate to Windows 8
Windows 8 upgrade paths

Configure hardware and applications (10–15%)
Configure devices and device drivers
Install, update, disable, and roll back drivers; resolve driver issues; configure driver settings, including signed and unsigned drivers; manage driver packages
Install and configure desktop apps and Windows Store apps
Install and repair applications by using Windows Installer, configure default program settings, modify file associations, manage access to Windows Store
Control access to local hardware and applications
Configure application restrictions, including Software Restriction Policies and AppLocker; manage installation of and access to removable devices; configure Assigned Access
Configure Internet Explorer 11 and Internet Explorer for the desktop
Configure compatibility view; configure Internet Explorer 11 settings, including add-ons, downloads, security, and privacy
Configure Hyper-V
Create and configure virtual machines, including integration services; create and manage checkpoints; create and configure virtual switches; create and configure virtual disks; move a virtual machine’s storage

Preparation resources
Device drivers and deployment
Managing client access to the Windows Store

Configure network connectivity (15–20%)
Configure IP settings
Configure name resolution, connect to a network, configure network locations
Configure networking settings
Connect to a wireless network, manage preferred wireless networks, configure network adapters, configure location-aware printing
Configure and maintain network security
Configure Windows Firewall, configure Windows Firewall with Advanced Security, configure connection security rules (IPsec), configure authenticated exceptions, configure network discovery
Configure remote management
Choose the appropriate remote management tools; configure remote management settings; modify settings remotely by using MMCs or Windows PowerShell; configure Remote Assistance, including Easy Connect

Preparation resources
Managing the new wireless network (IEEE 802.11) policies settings
Windows Firewall with advanced security and IPsec
Deploy remote server administration tools

Configure access to resources (10–15%)
Configure shared resources
Configure shared folder permissions, configure HomeGroup settings, configure libraries, configure shared printers, set up and configure OneDrive
Configure file and folder access
Encrypt files and folders by using Encrypting File System (EFS), configure NTFS permissions, configure disk quotas, configure file access auditing
Configure authentication and authorization
Configure user rights, manage credentials, manage certificates, configure biometrics, configure picture password, configure PIN, set up and configure Microsoft account, configure virtual smart cards, configure authentication in workgroups or domains, configure User Account Control (UAC) behavior

Preparation resources
Microsoft Virtual Academy: Windows 8 security insights: Module 7—SmartScreen filtering
Windows authentication overview

Configure remote access and mobility (10–15%)
Configure remote connections
Configure remote authentication, configure Remote Desktop settings, configure virtual private network (VPN) connections and authentication, enable VPN reconnect, configure broadband tethering
Configure mobility options
Configure offline file policies, configure power policies, configure Windows To Go, configure sync options, configure WiFi direct
Configure security for mobile devices
Configure BitLocker and BitLocker To Go, configure startup key storage

Preparation resources
Windows 8 VPN get connected
Deploy Windows To Go in your organization
BitLocker Group Policy settings

Monitor and maintain Windows clients (10–15%)
Configure and manage updates
Configure update settings, configure Windows Update policies, manage update history, roll back updates, update Windows Store apps
Manage local storage
Manage disk volumes and file systems, manage storage spaces
Monitor system performance
Configure and analyze event logs, configure event subscriptions, configure Task Manager, monitor system resources, optimize networking performance, configure indexing options

Preparation resources
Windows Update PowerShell module
Windows Performance Monitor
Windows 8: Task Manager retuned

Configure system and data recovery options (10–15%)

Configure system recovery
Configure a recovery drive, configure system restore, perform a driver rollback, perform a refresh or recycle, configure restore points
Configure file recovery
Restore previous versions of files and folders, configure file history, recover files from OneDrive

Preparation resources
Repair and recovery
How to: Set up and use file history on Windows 8
Windows 8 Jump Start Module 6: Recovery and security

QUESTION 1
A company has 100 client computers that run Windows 8.1.
You need to assign static IPv6 addresses to the client computers.
Which Windows Powershellcmdlet should you run?

A. Set-NetTCPSetting
B. Set-NetIPInterface
C. Set-NetlPv6Protocol
D. set-NetIPAddress

Answer: D
Explanation:
http://technet.microsoft.com/en-us/library/hh826151.aspx
Set-NetIPAddress
The Set-NetIPAddress cmdlet modifies IP address configuration properties of an existing IP address.
To create an IPv4 address or IPv6 address, use the New-NetIPAddress cmdlet.


QUESTION 2
A company has an Active Directory Domain Services (AD DS) domain. All client computers run Windows 8.1.
Portable client computers no longer connect to the corporate wireless network.
You need to ensure that when the corporate wireless network is available, the computers always connect to it automatically.
Which two actions would achieve the goal? (Each correct answer presents a complete solution. Choose two.)

A. Create a Group Policy object (GPO) to configure a wireless network policy. Link the GPO to the organizational unit that contains the computers.
B. Configure the corporate wireless network as an unmetered network.
C. Configure the corporate wireless network as a preferred network.
D. Manually connect to the corporate wireless network and select the option to connect automatically to that network.

Answer: C,D
Explanation:

Answer:
Configure the corporate wireless network as a preferred network.
Manually connect to the corporate wireless network and select the option to connect
automatically to that network.
http://blogs.technet.com/b/canitpro/archive/2014/03/05/windows-8-1-tips-manage-wireless-
network-profiles.aspx
Windows 8.1 tips: Managing Wireless Network Profiles
And finally, if you wanted to change the preferred order for your machine to connect to
specific wireless network, you could move a network up in the priority list by using the
command: set profileorder name=goose interface=”Wi-Fi” priority=1


http://www.eightforums.com/tutorials/20152-wireless-networks-priority-change-windows-8-
a.html
How to Change Connection Priority of Wireless Networks in Windows 8 and 8.1
Windows usually connects to networks in this priority order:
? Ethernet
? WiFi (wireless)
? Mobile broadband
When you connect to a new WiFi network, it’s added to the list, and Windows will connect to that network while it’s in range. If you connect to another WiFi network while in range of the first network, Windows will prefer the second network over the first one.
Mobile broadband networks are treated differently. If you manually connect to a mobile
broadband network when there is a WiFi network in range, the mobile broadband network is preferred just for that session. The next time you’re in range of both networks, the WiFi network is preferred. This is because mobile broadband networks typically are metered.
If you want to force your PC to prefer a mobile broadband network over WiFi, tap or click the WiFi network in the list of networks, and then click Disconnect. Windows won’t automatically connect to that WiFi network.


QUESTION 3
A company has client computers that run Windows 8.1. The corporate network is configured for IPv4 and IPv6.
You need to disable Media Sensing for IPv6 on the client computers without affecting IPv4 communications.
What should you do on each client computer?

A. Run the Disable-NetAdapterBinding Windows PowerShell cmdlet.
B. Run the Disable-NetAdapter Windows PowerShell cmdlet.
C. Run the Set-NetlPv6Protocol Windows PowerShell cmdlet.
D. Run the Set-NetlPv4Protocol Windows PowerShell cmdlet.

Answer: C
Explanation: http://technet.microsoft.com/en-us/library/hh826144.aspx Set-NetIPv6Protocol
Set-NetIPv6Protocol -DhcpMediaSense<DhcpMediaSense>
Specifies a value for Media Sense. The cmdlet modifies the value for this setting.
Media Sense provides a mechanism for the network adapter to notify the protocol stack of media connect and disconnect events. These events trigger the DHCP client to take an action, such as attempting to renew a DHCP lease or removing routes that are related to a disconnected network. When Media Sense is enabled, the network parameters on the laptop of a roaming user are automatically and transparently updated without requiring a restart when the user moves from one location to another. The acceptable values for this parameter are:
— Enabled
— Disabled
The default value is Enabled.
Further information: Disable-NetAdapterBinding
The Disable-NetAdapterBinding cmdlet disables a binding to a network adapter. Running this cmdlet causes loss of network connectivity depending on the binding that is disabled. Note: Disabling some adapter bindings can automatically enable other network adapter bindings.
Disable-NetAdapter
The Disable-NetAdapter cmdlet disables a network adapter. A network adapter must be enabled to connect to a network. This cmdlet causes loss of network connectivity of the specified network adapter. Note: Do not disable the network adapter being used to manage a remote computer. By default the user will be prompted to confirm the network adapter should be disabled
Set-NetlPv4Protocol
Is not a valid cmdlet.


QUESTION 4 DRAG DROP
A company has an Active Directory Domain Services (AD DS) domain. All client computers run Windows 8.1. Two computers named COMPUTER1 and COMPUTER2 are connected to one network switch and joined to the domain. Windows Firewall is turned off on both computers.
You are planning a remote management solution.
You have the following requirements:
? Ensure that COMPUTER2 can run remote commands on COMPUTER1.
? Test the solution by successfully running a command from COMPUTER2 that
executes on COMPUTER1.
You need to select the commands to run on COMPUTER1 and COMPUTER2 to meet the remote management requirements.
Which commands should you run? (To answer, drag the appropriate command or commands to the correct location or locations in the answer area. Commands may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)


Answer:


QUESTION 5
A company has 100 client computers that run Windows 8.1. The client computers are members of a workgroup.
A custom application requires a Windows Firewall exception on each client computer.
You need to configure the exception on the client computers without affecting existing firewall settings.
Which Windows PowerShell cmdlet should you run on each client computer?

A. New-NetFirewallRule
B. Set-NetFirewallSetting
C. Set-NetFirewallRule
D. Set-NetFirewallProfile
E. New-NetIPSecMainModeRule

Answer: A
Explanation:
http://technet.microsoft.com/en-us/library/jj554908.aspx
New-NetFirewallRule
The New-NetFirewallRule cmdlet creates an inbound or outbound firewall rule and adds the rule to the target computer.
Further information: Set-NetFirewallSetting
The Set-NetFirewallSetting cmdlet configures properties that apply to the firewall and IPsec settings, regardless of which network profile is currently in use. This cmdlet allows the administrator to specify global firewall behavior.
Set-NetFirewallRule
The Set-NetFirewallRule cmdlet modifies existing firewall rule properties.
Set-NetFirewallProfile
The Set-NetFirewallProfile cmdlet configures options for the profiles, including domain, public, and private, that are global, or associated with the input rules.
New-NetIPSecMainModeRule
The New-NetIPsecMainModeRule cmdlet creates an IPsec main mode rule.
A main mode rule contains a set of local and remote end points to determine the peers to which it applies. When an application on the local computer attempts to communicate with one of these specified remote hosts, the computer attempts to establish a security association (SA) with the remote server.

 

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

MCTS Training, MCITP Trainnig

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

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.

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

MCTS Training, MCITP Trainnig

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

Exam 70-467 Designing Business Intelligence Solutions 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.

Plan business intelligence (BI) infrastructure (15–20%)
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
Plan for scalability
Change binding options for partitions; choose the appropriate Multidimensional OLAP (MOLAP), Relational OLAP (ROLAP), and Hybrid OLAP (HOLAP) storage modes
Plan and manage upgrades
Plan change management for a BI solution
Maintain server health
Design an automation strategy

Preparation resources
Create and manage a local partition
Partition storage modes and processing
Designing Aggregations (Analysis Services – Multidimensional)

Design BI infrastructure (15–20%)
Design a security strategy
Configure security and impersonation between SQL Server service, analysis services, and front end; implement Dynamic Dimension Security within a cube; configure security for an extranet environment; configure Kerberos security; design authentication mechanisms; design security tests; build secure solutions end to end; design roles for calculated measures; understand the tradeoffs between regular SSAS security and dynamic security
Design a SQL partitioning strategy
Choose the proper partitioning strategy for the data warehouse and cube, implement a parallel load to fact tables by using partition switching, use data compression
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 logging and auditing strategy
Design a new SSIS logging infrastructure (for example, information available through the catalog views), validate data is balancing and reconciling correctly

Preparation resources
Granting cube access
Connection string properties
Partitioned tables and indexes

Design a reporting solution (20–25%)
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 a data acquisition strategy
Identify the data sources that need to be used to pull in the data, determine the changes (incremental data) in the data source (time window), identify the relationship and dependencies between the data sources, determine who can access which data, determine what data can be retained for how long (regulatory compliance, data archiving, aging), design a data movement strategy, profile source data
Plan and manage reporting services configuration
Choose the appropriate reporting services requirements (including native mode and SharePoint mode)
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

Preparation resources
Configure Windows Authentication on the Report Server
Different ways to update data in PowerPivot
Add dataset filters, data region filters, and group filters (Report Builder and SSRS)

Design BI data models (30–35%)

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 a schema
Multidimensional modeling starting from a star or snowflake schema, design relational modeling for a Data Mart
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 fact tables
Design a data warehouse that supports many to many dimensions with factless fact tables
Design BI semantic models
Plan for a multidimensional cube; support a many-to-many relationship between tables; choose between multidimensional and tabular, depending on the type of data and workload
Design and create MDX calculations
Design MDX queries, identify the structures of MDX and the common functions (tuples, sets, TopCount, SCOPE, VisualTotals, and more), create calculated members in an MDX statement, identify which MDX statement would return the required result, implement a custom MDX or logical solution for a pre-prepared case task

Preparation resources
Defining a many-to-many relationship
Define Relationship dialog box (Analysis Services – multidimensional data)
Set aggregation options (Usage-Based Optimization Wizard)

Design an ETL solution (10–15%)
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
Design package configurations for SSIS packages
Avoid repeating configuration information entered in SSIS packages, and use configuration files

Preparation resources
Data profiling task
Create package configurations
Deployment of projects and packages

QUESTION 1
You need to create the Sales Reporting shared SSRS data source.
Which SSRS data connection type should you use?

A. OData
B. Microsoft SQL Server
C. ODBC
D. OLE DB

Answer: B


QUESTION 2
You need to grant appropriate permissions to the SSISOwners SQL Server login.
What should you do?

A. Map the login to the SSISDB database. Assign the user to the ssis_admin role.
B. Map the login to the msdb database. Assign the user to the db_owner role.
C. Map the login to the msdb database. Assign the user to the db_ssisadmin role.
D. Map the login to the SSISDB database. Assign the user to the db_ssisadmin role.
E. Map the login to the SSISDB database. Assign the user to the db_owner role.
F. Map the login to the msdb database. Assign the user to the ssis_admin role.

Answer: D


QUESTION 3
You need to configure data refresh for the Manufacturing Performance PowerPivot workbook.
What should you do? (Each correct answer presents part of the solution. Choose ail that apply.)

A. Configure the PowerPivot Data Refresh Timer Job to run every 60 minutes.
B. Restore the PowerPivot workbook to an SSAS instance in tabular mode.
C. Script a process command and configure a SQL Server Agent job to execute the command every 60 minutes.
D. Restore the PowerPivot workbook to an SSAS instance in PowerPivot for SharePoint mode.

Answer: A


QUESTION 4
You need to configure package execution logging to meet the requirements.
What should you do?

A. Configure logging in each ETL package to log the OnError, OnInformation, and Diagnostic events.
B. Set the SSIS catalog’s Server-wide Default Logging Level property to Performance.
C. Set the SSIS catalog’s Server-wide Default Logging Level property to Basic.
D. Set the SSIS catalog’s Server-wide Default Logging Level property to Verbose.
E. Configure logging in each ETL package to log the OnError, OnPreExecute, and OnPostExecute events.

Answer: B

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

MCTS Training, MCITP Trainnig

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

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

 

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

MCTS Training, MCITP Trainnig

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

Exam 70-465 Designing Database Solutions for 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.

Design a database structure (25–30%)
Design for business requirements
Translate business needs to data structures; de-normalize a database by using SQL Server features, including materialization using indexed views, distributed partitioned views, filtered and non-key column indexes, and snapshots
Design physical database and object placement
Design a physical database, including file placement, FILESTREAM, FILETABLE, file groups, and RAID; configure system database settings
Design a table and index partitioning strategy
Develop the optimal strategy for indexing, archive using partitions and tables, design columnstore indexes, design XML indexes
Design a migration, consolidation, and upgrade strategy
Upgrade with minimal downtime; design a cross-cluster migration; plan a database deployment, including Windows PowerShell, Server Core, and contained databases; migrate query plans; design a migration strategy using Distributed Replay Controller; design a SQL Server virtualization strategy
Design SQL Server instances
Identify hardware for new instances; design CPU affinity; design clustered instances using Microsoft Distributed Transaction Control (MSDTC); define instance memory allocation; design installation strategies, including sysprep, slipstream, and SMB file server; define cross db ownership chaining
Design backup and recovery
Design a backup strategy based on business needs, including differential, file, log, and striped; design a database snapshot strategy; design appropriate recovery models; design a system database backup strategy; recover Tail-Log backups

Preparation resources
Create indexed views
Move user databases
Partitioned tables and indexes

Design databases and database objects (30–35%)
Design a database model
Design a logical schema; design a data access and data layer architecture; design a database schema; design a security architecture; design a cross-server instance database model, including linked servers, security, providers, distributed transactions, distributed partitioned views, and Service Broker
Design tables
Design tables appropriately, including physical tables, temp tables, temp table variables, common table expressions, columnstore indexes, user defined table types, FILESTREAM, FILETABLE, and In-Memory OLTP; design views and table valued functions; design a compression strategy, including row and page; select an appropriate data type; design computed columns
Design for concurrency
Develop a strategy to maximize concurrency; define a locking and concurrency strategy; design a transaction isolation strategy, including server database and session; design triggers for concurrency
Design T-SQL stored procedures
Create stored procedures; design a data access strategy using stored procedures; design appropriate stored procedure parameters, including input, output, and Table Valued; design error handling; design an In-Memory OLTP strategy for stored procedures
Design a management automation strategy
Create a data archiving solution; design automation and auditing, including jobs, alerts, operators, SSIS, CDC, auditing, DDL triggers, and Windows PowerShell; automate across multiple databases and instances; design data batch processing: design a database load test; deploy to different environments, including development, staging, and production
Design for transactions
Manage transactions, including time, savepoint, and mark; design for implicit and explicit transactions; ensure data integrity by using transactions; design error handling for transactions, including TRY, CATCH, and THROW

Preparation resources
ALTER DATABASE SET options (Transact-SQL)
In-Memory OLTP (In-Memory Optimization)

Design database security (10–15%)
Design an application strategy to support security
Design security, including security roles, signed stored procedures, encryption, contained logins, EXECUTE AS, and credentials; implement schemas and schema security; design security maintenance, including SQL logins, integrated authentication, permissions, and mirroring
Design database, schema, and object security permissions
Design a database schema that meets security requirements, schema ownership, ownership chaining, cross database chaining
Design instance-level security configurations
Implement separation of duties using different login roles; choose an authentication type, including logon triggers, regulatory requirements, and certificates; implement data encryption, including database master key and configuration; implement Data Description Language (DDL) triggers; define a secure service account

Preparation resources
Tutorial: Signing stored procedures with a certificate
cross db ownership chaining server configuration option
DDL triggers

Design a troubleshooting and optimization solution (25–30%)
Design a maintenance strategy for database servers
Design maintenance plans; design index maintenance, including rebuild, defragmentation, statistics, online rebuilds, offline rebuilds, and thresholds; maintain physical and logical consistency (DBCC); manage database files, including LDF, MDF, In-Memory OLTP, and garbage collection; define a retention policy
Troubleshoot and resolve concurrency issues
Examine deadlocking issues using SQL Server logs and trace flags; design a reporting database infrastructure, including replicated databases; monitor concurrency, including Dynamic Management Views (DMV); diagnose blocking, including live locking and deadlocking; diagnose waits; use Extended Events; implement query hints to increase concurrency
Design and implement a high availability solution
Configure failover clustering, including multi-subnet; design readable mirrors; create a highly available configuration with low recovery time; design and ensure uptime requirements, including monitoring and patching; design and implement a replication architecture; implement AlwaysOn Availability Groups and AlwaysOn failover clusters
Design a solution to monitor performance and concurrency
Identify performance monitor counters; monitor for performance and bottlenecks, including Wait Stats; design a query monitoring and review strategy; monitor for missing statistics
Design a monitoring solution at the instance level
Design auditing strategies, including Extended Events, Event traces, SQL Audit, Profiler-scheduled or event-based maintenance, Performance Monitor, and DMV usage; set up file and table growth monitoring; collect performance indicators and counters; create jobs to monitor server health; audit using Windows Logs

Preparation resources
sys.dm_tran_locks (Transact-SQL)
Overview of AlwaysOn Availability Groups (SQL Server)
Monitoring and troubleshooting merge for data and delta file pairs

QUESTION 1
You need to recommend a solution for the deployment of SQL Server 2014. The solution must meet the business requirements.
What should you include in the recommendation?

A. Create a new instance of SQL Server 2014 on the server that hosts the SQL Server 2008 instance.
B. Upgrade the existing SQL Server 2008 instance to SQL Server 2014.
C. Deploy two servers that have SQL Server 2014 installed and implement Failover Clustering.
D. Deploy two servers that have SQL Server 2014 installed and implement database mirroring.

Answer: C

Explanation:
Scenario: The databases must be available if the SQL Server service fails.
Reference:
Failover Clustering Overview
Windows Server Failover Clustering (WSFC) with SQL Server


QUESTION 2
You need to recommend a solution to allow application users to perform tables. The solution must meet the business requirements.
What should you recommend?

A. Create a Policy-Based Management Policy.
B. Create a user-defined database role and add users to the role.
C. Create stored procedures that use EXECUTE AS clauses.
D. Create functions that use EXECUTE AS clauses.

Answer: D

Explanation:
* c Clause (Transact-SQL)
In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.
Reference: Using EXECUTE AS in Modules


QUESTION 3
You need to recommend a feature to support your backup solution.
What should you include in the recommendation?

A. Transparent Data Encryption (TDE)
B. Column-level encryption
C. An NTFS file permission
D. A Secure Sockets Layer (SSL)

Answer: A

Explanation:
* Scenario: You must encrypt the backup files to meet regulatory compliance requirements. The encryption strategy must minimize changes to the databases and to the applications.
* Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery.
Reference: Transparent Data Encryption (TDE)


QUESTION 4
You need to recommend a solution to synchronize Database2 to App1_Db1.
What should you recommend?

A. Change data capture
B. Snapshot replication
C. Master Data Services
D. Transactional replication

Answer: D

Explanation:
Scenario:
* Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named App1_Db1 as soon as changes occur to the data in Database2.
* All data in Database2 is recreated each day and does not change until the next data creation process.
Reference:
Transactional Replication


QUESTION 5
You need to recommend a solution to improve the performance of usp.UpdateInventory. The solution must minimize the amount of development effort.
What should you include in the recommendation?

A. A table variable
B. A common table expression
C. A subquery
D. A cursor

Answer: A

Explanation:
*Scenario: Database2 will contain a stored procedure named usp_UpdateInventory. Usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies.
* A table variable can be very useful to store temporary data and return the data in the table format.
table
* Example: The following example uses a self-join to find the products that are supplied by more than one vendor.
Because this query involves a join of the ProductVendor table with itself, the ProductVendor table appears in two roles. To distinguish these roles, you must give the ProductVendor table two different aliases (pv1 and pv2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement:
USE AdventureWorks2008R2; GO
SELECT DISTINCT pv1.ProductID, pv1.VendorID FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID <> pv2.VendorID
ORDER BY pv1.ProductID
Incorrect:
Not B: Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

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

MCTS Training, MCITP Trainnig

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

 

Exam 70-464 Developing Microsoft SQL Server Databases

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.

Implement database objects (30–35%)
Create and alter tables
Develop an optimal strategy for using temporary objects, including table variables and temporary tables; define alternatives to triggers; define data version control and management; implement @Table and #table appropriately; create calculated columns; implement partitioned tables, schemas, and functions; implement column collation; implement online transaction processing (OLTP); implement columnstore and sparse columns
Design, implement, and troubleshoot security
Implement data control language statements appropriately, troubleshoot connection issues, implement execute as statements, implement certificate-based security, create loginless users, define appropriate database roles and permissions, implement contained users, implement cross db ownership chaining, implement schema security, implement server roles
Design the locking granularity level
Choose the right lock mechanism for a given task; handle deadlocks; design index locking properties; fix locking and blocking issues; analyze a deadlock scenario; design appropriate isolation level, including Microsoft ActiveX data objects defaults; design for locks and lock escalation; design transactions that minimize locking; reduce locking contention; identify bottlenecks in data design; design appropriate concurrency control, such as pessimistic or optimistic
Implement indexes
Inspect physical characteristics of indexes and perform index maintenance; identify unused indexes; implement indexes; optimize indexes, including full, filter, statistics, and force
Implement data types
Select appropriate data types, including BLOBs, GUIDs, XML, and spatial data; develop a Common Language Runtime (CLR) data type; implement appropriate use of @Table and #table; determine values based on implicit and explicit conversions
Create and modify constraints
Create constraints on tables, define constraints, modify constraints according to performance implications, implement cascading deletes, configure constraints for bulk inserts

Preparation resources
CREATE TABLE (Transact-SQL)
CREATE USER (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Implement programming objects (15-20%)
Design and implement stored procedures
Create stored procedures and other programmatic objects; implement different types of stored procedure results; create a stored procedure for the data access layer; analyze and rewrite procedures and processes; program stored procedures by using T-SQL and CLR; implement parameters, including table valued, input, and output; implement error handling, including TRY…CATCH; configure appropriate connection settings
Design T-SQL table-valued and scalar functions
Modify scripts that use cursors and loops into a SET-based operation, design deterministic and non-deterministic functions
Create, use, and alter user-defined functions (UDFs)
Implement deterministic or non-deterministic functions; implement CROSS APPLY by using UDFs; implement CLR functions
Create and alter views
Set up and configure partitioned tables and partitioned views; create indexed views

Preparation resources
sys.dm_os_volume_stats (Transact-SQL
SQL Server agent stored procedures (Transact-SQL)
Processing stored procedure results

Design database objects (25–30%)
Design tables
Apply data design patterns; develop appropriately normalized and de-normalized SQL tables; design transactions; design views; implement GUID as a clustered index appropriately; design temp tables appropriately, including # vs. @; design an encryption strategy; design table partitioning; design a BLOB storage strategy, including filestream and filetable; design tables for In-Memory OLTP
Design for concurrency
Develop a strategy to maximize concurrency; define a locking and concurrency strategy; design a transaction isolation strategy, including server database and session; design triggers for concurrency
Design indexes
Design indexes and data structures; design filtered indexes; design an indexing strategy, including column store, semantic indexes, and INCLUDE; design statistics; assess which indexes on a table are likely to be used, given different search arguments (SARG); design spatial and XML indexes
Design data integrity
Design a table data integrity policy, including checks, primary key, foreign key, uniqueness, XML schema, and nullability; select a primary key
Design for implicit and explicit transactions
Manage transactions; ensure data integrity by using transactions; manage distributed transaction escalations; design savepoints; design error handling for transactions, including TRY, CATCH, and THROW

Preparation resources
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
CREATE INDEX (Transact-SQL)
Transaction Statements (Transact-SQL)

Optimize and troubleshoot queries (25–30%)
Optimize and tune queries
Tune a poorly performing query, including avoiding unnecessary data type conversions; identify long-running queries; review and optimize code; analyze execution plans to optimize queries; tune queries using execution plans and Microsoft Database Tuning Advisor (DTA); optimize queries using pivots and common table expressions (CTE); design database layout to optimize queries; implement query hints; tune query workloads; implement recursive CTE; implement full text and semantic search; analyze execution plans; implement plan guides
Troubleshoot and resolve performance problems
Interpret performance monitor data; integrate performance monitor data with SQL Traces
Optimize indexes
Develop an optimal strategy for clustered indexes; analyze index usage; optimize indexes for workload, including data warehousing and OLTP; generate appropriate indexes and statistics by using INCLUDE columns; create filtered indexes; implement full-text indexing; implement columnstore indexes; optimize online index maintenance
Capture and analyze execution plans
Collect and read execution plans, create an index based on an execution plan, batch or split implicit transactions, split large queries, consolidate smaller queries, review and optimize parallel plans
Collect performance and system information
Monitor performance using Dynamic Management Views, collect output from the Database Engine Tuning Advisor, design Extended Events Sessions, review and interpret Extended Event logs; optimize Extended Event session settings, use Activity Monitor to minimize server impact and determine IO bottlenecks, monitor In-Memory OLTP resources

Preparation resources
Database Engine Tuning Advisor
DBCC SHRINKFILE (Transact-SQL)
Create indexes with included columns

QUESTION 1
You attempt to process an invoice by using usp_InsertInvoice.sql and you receive the following error message: “Msg 515, Level 16, State 2, Procedure usp_InsertInvoice, Line 10
Cannot insert the value NULL into column ‘InvoiceDate’, table ‘DB1.Accounting.Invoices’; column does not allow nulls. INSERT fails.”
You need to modify usp_InsertInvoice.sql to resolve the error.
How should you modify the INSERT statement?

A. InvoiceDate varchar(l00) ‘InvoiceDate’,
B. InvoiceDate varchar(100) ‘Customer/InvoiceDate’, ‘
C. InvoiceDate date ‘@InvoiceDate’,
D. InvoiceDate date ‘Customer/@InvoiceDate’,

Answer: C


QUESTION 2
You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID.
Which line of code should you modify in CountryFromID.sql?

A. 04
B. 05
C. 06
D. 19

Answer: D

Explanation:
http://msdn.microsoft.com/en-us/library/ms186755.aspx
http://msdn.microsoft.com/en-us/library/ms191320.aspx


QUESTION 3
You execute IndexManagement.sql and you receive the following error message:
“Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =,! =, <, <= ,>, > = or when the subquery is used as an expression.”
You need to ensure that IndexManagement.sql executes properly.
Which WHILE statement should you use at line 18?

A. WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable)
B. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)
C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)
D. WHILE @counter < (SELECT SUM(RowNumber) FROM @indextabie)

Answer: B


QUESTION 4
You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateSpeakerName.
What should you include in the recommendation?

A. The Distributed Transaction Coordinator (DTC)
B. Transactional replication
C. Change data capture
D. Change tracking

Answer: A


QUESTION 5
You are evaluating the table design.
You need to recommend a change to Tables.sql that reduces the amount of time it takes for usp_AttendeesReport to execute.
What should you add at line 14 of Tables.sql?

A. FullName nvarchar(100) NOT NULL CONSTRAINT DF_FuIlName DEFAULT
(dbo.CreateFullName (FirstName, LastName)),
B. FullName AS (FirstName +‘ ’+ LastName),
C. FullName nvarchar(100) NOT NULL DEFAULT (dbo.CreateFullName (FirstName, LastName)).
D. FullName AS (FirstName +‘ ’+ LastName) PERSISTED,

Answer: D

Explanation:
http://msdn.microsoft.com/en-us/library/ms188300.aspx
http://msdn.microsoft.com/en-us/library/ms191250.aspx


QUESTION 6
You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required.
What should you add to usp_SelectSpeakersByName?

A. A table variable
B. An OFFSET-FETCH clause
C. The ROWNUMBER keyword
D. A recursive common table expression

Answer: B

Explanation:
http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-serverpaging-methods/
http://msdn.microsoft.com/en-us/library/ms188385.aspx http://msdn.microsoft.com/en-us/library/ms180152.aspx http://msdn.microsoft.com/en-us/library/ms186243.aspx http://msdn.microsoft.com/en-us/library/ms186734.aspx http://www.sqlserver-training.com/how-to-use-offset-fetch-option-in-sql-server-order-byclause/-http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/11/30/using-offset-and-fetch/

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

MCTS Training, MCITP Trainnig

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