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

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

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

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

 

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 https:://msdn.microsoft.com/en-us/library/ms180152.aspx https:://msdn.microsoft.com/en-us/library/ms186243.aspx https:://msdn.microsoft.com/en-us/library/ms186734.aspx https:://www.sqlserver-training.com/how-to-use-offset-fetch-option-in-sql-server-order-byclause/-https:://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

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

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

Plan and deploy a server infrastructure (20–25%)
Design and plan an automated server installation strategy
Design considerations including images and bare metal/virtual deployment; design a server implementation using Windows Assessment and Deployment Kit (ADK); design a virtual server deployment
Plan for deploying servers to Microsoft Azure infrastructure as a service (IaaS); plan for deploying servers to public and private cloud by using AppController and Windows PowerShell; plan for multicast deployment; plan for Windows Deployment Services (WDS)
Implement a server deployment infrastructure
Configure multi-site topology and transport servers; implement a multi-server topology, including stand-alone and Active Directory–integrated Windows Deployment Services (WDS) servers; deploy servers to Microsoft Azure IaaS; deploy servers to public and private cloud by using AppController and Windows PowerShell
Plan and implement server upgrade and migration
Plan for role migration; migrate server roles; migrate servers across domains and forests; design a server consolidation strategy; plan for capacity and resource optimization
Plan and deploy Virtual Machine Manager services
Design Virtual Machine Manager service templates; plan and deploy profiles, operating system profiles, hardware and capability profiles, application profiles, and SQL profiles; plan and manage services including scaling out, updating and servicing services; configure Virtual Machine Manager libraries; plan and deploy services to non-trusted domains and workgroups
Plan and implement file and storage services
Planning considerations include iSCSI SANs, Fibre Channel SANs, Virtual Fibre Channel, storage spaces, storage pools including tiered storage and data de-duplication; configure the Internet Storage Name server (iSNS); configure Services for Network File System (NFS); plan and implement SMB 3.0 based storage; plan for Windows Offloaded Data Transfer (ODX)

Preparation resources
Windows deployment with the Windows ADK
Windows Deployment Services overview
Install, use, and remove Windows Server migration tools

Design and implement network infrastructure services (20–25%)
Design and maintain a Dynamic Host Configuration Protocol (DHCP) solution
Design considerations including a highly available DHCP solution including split scope, DHCP failover, and DHCP failover clustering, DHCP interoperability, and DHCPv6; implement DHCP filtering; implement and configure a DHCP management pack; maintain a DHCP database
Design a name resolution solution strategy
Design considerations including Active Directory integrated zones, 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 including IPAM, Group Policy based, manual provisioning, and distributed, centralized, hybrid placement, and database storage; configure role-based access control; configure IPAM auditing; migrate IPs; manage and monitor multiple DHCP and DNS servers; configure data collection for IPAM; integrate IPAM with Virtual Machine Manager (VMM)

Preparation resources
DHCP design guide
Reviewing DNS concepts
IP Address Management (IPAM) overview

Design and implement network access services (15–20%)
Design a VPN solution
Design considerations including certificate deployment, firewall configuration, client/site to site, bandwidth, protocol implications, connectivity to Microsoft Azure IaaS and VPN deployment configurations using Connection Manager Administration Kit (CMAK)
Design a DirectAccess solution
Design considerations including deployment topology, migration from Forefront UAG, One Time Password (OTP), and use of certificates issued by enterprise Certificate Authority (CA)
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, 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); implement an advanced DirectAccess solution, configure multiple RADIUS server groups and infrastructure, configure Web Application Proxy for clustering
Design and implement 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

Preparation resources
Plan the Remote Access deployment
DirectAccess design, deployment, and troubleshooting guides
Microsoft Virtual Academy: Multi site and high availability DirectAccess

Design and implement an Active Directory infrastructure (logical) (20–25%)
Design a forest and domain infrastructure
Design considerations including multi-forest architecture, trusts, functional levels, domain upgrade, domain migration, forest restructure, Microsoft Azure Active Directory and DirSync
Implement a forest and domain infrastructure
Configure domain rename; configure Kerberos realm trusts; implement a domain upgrade; implement a domain migration; implement a forest restructure; deploy and manage a test forest including synchronization with production forests
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), and Group Policy caching
Design an Active Directory permission model
Design considerations including Active Directory object security and Active Directory quotas; customize tasks to delegate in Delegate of Control Wizard; deploy administrative tools on the client devices; delegate permissions on administrative users (AdminSDHolder); plan for Kerberos delegation

Preparation resources
AD DS design guide
Domain Rename technical reference
Advanced Group Policy management

Design and implement an Active Directory infrastructure (physical) (20–25%)
Design an Active Directory sites topology
Design considerations including proximity of domain controllers, replication optimization, and site link; monitor and resolve Active Directory replication conflicts
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, and domain controller placement
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

Preparation resources

Planning domain controller placement
RODC frequently asked questions
Branch office infrastructure solution

---

QUESTION 1
What method should you use to deploy servers?

A. WDS
B. AIK
C. ADK
D. EDT

Answer: A

Explanation: WDS is a server role that enables you to remotely deploy Windows operating systems. You can use it to set up new computers by using a network-based installation. This means that you do not have to install each operating system directly from a CD, USB drive, or DVD.
Reference: What’s New in Windows Deployment Services in Windows Server

---

QUESTION 2
You need to recommend a solution for DHCP logging. The solution must meet the technical requirement.
What should you include in the recommendation?

A. Event subscriptions
B. IP Address Management (IPAM)
C. DHCP audit logging
D. DHCP filtering

Answer: B

Explanation: * Scenario: A central log of the IP address leases and the users associated to those leases must be created.
* Feature description
IPAM in Windows Server 2012 is a new built-in framework for discovering, monitoring, auditing, and managing the IP address space used on a corporate network. IPAM provides for administration and monitoring of servers running Dynamic Host Configuration Protocol
(DHCP) and Domain Name Service (DNS). IPAM includes components for:
• Automatic IP address infrastructure discover)’: IPAM discovers domain controllers, DHCP servers, and DNS servers in the domains you choose. You can enable or disable management of these servers by IPAM.
• Custom IP address space display, reporting, and management: The display of IP addresses is highly customizable and detailed tracking and utilization data is available. IPv4 and IPv6 address space is organized into IP address blocks, IP address ranges, and individual IP addresses. IP addresses are assigned built-in or user-defined fields that can be used to further organize IP address space into hierarchical, logical groups.
• Audit of server configuration changes and tracking of IP address usage: Operational events are displayed for the IPAM server and managed DHCP servers. IPAM also enables IP address tracking using DHCP lease events and user logon events collected from Network Policy Server (NPS), domain controllers, and DHCP servers. Tracking is available by IP address, client ID, host name, or user name.
• Monitoring and management of DHCP and DNS services: IPAM enables automated service availability monitoring for Microsoft DHCP and DNS servers across the forest. DNS zone health is displayed, and detailed DHCP server and scope management is available using the IPAM console.
Reference: IP Address Management (IPAM) Overview

---

QUESTION 3
After the planned upgrade to Windows Server 2012, you restore a user account from the Active Directory Recycle Bin.
You need to replicate the restored user account as quickly as possible.
Which cmdlets should you run?

A. Get-ADReplicationSite and Set-ADReplicationConnection
B. Get-ADReplicationAttributeMetadata and Compare-Object
C. Get-ADReplicationUpToDatenessVectorTable and Set-ADReplicationSite
D. Get ADDomainController and Sync-ADObject

Answer: D

Explanation:
* Scenario:
All of the domain controllers are global catalog servers.
The FSMO roles were not moved since the domains were deployed.
* The Get-ADDomainController cmdlet gets the domain controllers specified by the parameters.
You can get domain controllers by setting the Identity, Filter or Discover parameters.
* The Sync-ADObject cmdlet replicates a single object between any two domain controllers that have partitions in common. The two domain controllers do not need to be direct replication partners. It can also be used to populate passwords in a read-only domain
controller (RODC) cache.
Reference: Get-ADDomainController, Sync-ADObject

---

QUESTION 4
You need to recommend a fault-tolerant solution for the VPN. The solution must meet the technical requirements.
What should you include in the recommendation?

A. Network adapter teaming
B. Network Load Balancing (NLB)
C. Failover Clustering
D. DirectAccess

Answer: B

Explanation:
* Scenario: Core networking services in each office must be redundant if a server fails.
* The Network Load Balancing (NLB) feature distributes traffic across several servers by using the TCP/IP networking protocol. By combining two or more computers that are running applications into a single virtual cluster, NLB provides reliability and performance for web servers and other mission-critical servers.
Reference: Network Load Balancing Overview
http://technet.microsoft.com/en-us/library/hh831698.aspx

---

QUESTION 5
You are planning the migration of research.contoso.com.
You need to identify which tools must be used to perform the migration.
Which tools should you identify?

A. Active Directory Migration Tool version 3.2 (ADMT v3.2) and Group Policy Management Console (GPMC)
B. Active Directory Federation Services (AD FS) and Microsoft Federation Gateway
C. Active Directory Migration Tool version 3.2 (ADMT v3.2) and Active Directory Federation Services (AD FS)
D. Active Directory Lightweight Directory Services (AD LDS) and Group Policy Management
Console (GPMC)

Answer: A

Explanation:
* Scenario:
All of the users and the Group Policy objects (GPOs) in research.contoso.com will be migrated to contoso.com.
two domain controllers for the research.contoso.com domain. The domain controllers run Windows Server 2008 R2.

 

---

 

 

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

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