Exam 70-465 Designing Database Solutions for Microsoft SQL Server

0

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

Exam 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012/2014

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

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

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

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

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

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

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

Design and implement a data warehouse (10–15%)
Design and implement dimensions
Design shared/conformed dimensions; determine if you need support for slowly changing dimensions; determine attributes; design hierarchies; determine whether you need star or snowflake schema; determine the granularity of relationship with fact tables; determine the need for auditing or lineage; determine keys (business transactional or your own data warehouse/surrogate keys); implement dimensions; implement data lineage of a dimension table
Design and implement fact tables
Design a data warehouse that supports many to many relationships; appropriately index a fact table; using columnstore indexes; partitioning; additive measures; semi additive measures; non additive measures; implement fact tables; determining the loading method for the fact tables; implement data lineage of a fact table; design summary aggregation tables

Preparation resources
Introduction to dimensions (Analysis Services – multidimensional data)
Dimension relationships
Columnstore indexes

Extract and transform data (20–25%)
Define connection managers
Plan the configuration of connection managers; package level or project level connection manager; define a connection string; parameterization of connection strings
Design data flow
Define data sources and destinations; distinguish blocking and non-blocking transformations; use different methods to pull out changed data from data sources; determine appropriate data flow components; determine the need for supporting Slowly Changing Dimensions (SCD); determine whether to use SQL Joins or SSIS lookup or merge join transformations; batch processing versus row by row processing; determine the appropriate transform to use for a specific task; determine the need and method for identity mapping and deduplicating; fuzzy lookup, fuzzy grouping and Data Quality Services (DQS) transformation; determine the need for custom data sources, destinations, and transforms; determine what to do with erroneous rows; determine auditing needs; trusted/authoritative data sources, including warehouse metadata; extracting only changed rows
Implement data flow
Debug data flow; use the appropriate data flow components; SQL / SSIS data transformation; create SSIS packages that support slowly changing dimensions; use the lookup task in SSIS; map identities using SSIS fuzzy lookup (advanced); specify a data source and destination; use data flows; different categories of transformations; read, transform and load data; understand which transforms to use to accomplish a specific business task; data correction transformation; performance tune an SSIS dataflow; optimize Integration Services packages for speed of execution; maintain data integrity, including good data flow
Manage SSIS package execution
Schedule package execution by using SQL Server Agent; execute packages by using DTEXEC; execute packages by using SQL Server Management Studio; implement package execution; plan and design package execution strategy; use PowerShell to execute script; monitor the execution using Management Studio; use DTEXECUI; ETL restartability
Implement script tasks in SSIS
Determine if it is appropriate to use a script task; extending the capability of a control flow; perform a custom action as needed (not on every row) during a control flow

Preparation resources
Integration Services (SSIS) connections
Data flow
Slowly changing dimension transformation

Load data (25–30%)
Design control flow
Determine control flow; determine containers and tasks needed; determine precedence constraints; design an SSIS package strategy with rollback, staging and transaction control; decide between one package or multiple packages; determine event handlers; determine variables; determine parameters on package and project level; determine connection managers and whether they are package or project level; determine the need for custom tasks; determine how much information you need to log from a package; determine the need for checkpoints; determine security needs
Implement package logic by using SSIS variables and parameters
User variables; variable scope, data type; implement parameterization of properties using variables; using variables in precedence constraints; referring to SSIS system variables; design dynamic SSIS packages; package configurations (file or SQL tables); expressions; package and project parameters; project level connection managers; variables; implement dynamic package behavior; configure packages in SSIS for different environments, package configurations (xmlconfiguration file, SQLServer table, registry entry; parent package variables, environment variable); parameters (package and project level); project connection managers; property expressions (use expressions for connection managers)
Implement control flow
Checkpoints; debug control flow; implement the appropriate control flow task to solve a problem; data profiling; use sequence containers and loop containers; manage transactions in SSIS packages; managing parallelism; using precedence constraint to control task execution sequence; creating package templates; using the execute package task
Implement data load options
Implement a full and incremental data load strategy; plan for an incremental update of the relational Data Mart; plan for loads into indexed tables; configure appropriate bulk load options; select an appropriate load technique (SSIS Destination versus T-SQL) and load partitioned tables
Implement script components in SSIS
Create an SSIS package that handles SCD Type 2 changes without using the SCD component; work with script component in SSIS; deciding when it is appropriate to use a script component versus a built in; source, transformation, destination component; use cases: web service source and destination, getting the error message

Preparation resources
Integration Services transactions
Developing a custom task
Integration Services (SSIS) parameters

Configure and deploy SSIS solutions (20–25%)
Troubleshoot data integration issues
Performance issues; connectivity issues; execution of a task or transformation failed; logic issues; demonstrate awareness of the new SSIS logging infrastructure; troubleshoot a failed package execution to determine the root cause of failure; troubleshoot SSIS package failure from an invalid datatype; implement break points; data viewers; profile data with different tools; batch cleanup
Install and maintain SSIS components
Software installation (IS, management tools); development box and server; install specifics for remote package execution; planning for installation (32- versus 64-bit); upgrade; provisioning the accounts; creating the catalog
Implement auditing, logging, and event handling
Audit package execution by using system variables; propagate events; use log providers; log an SSIS execution; create alerting and notification mechanisms; use Event Handlers in SSIS to track ETL events and errors; implement custom logging
Deploy SSIS solutions
Create and configure an SSIS catalog; deploy SSIS packages by using the deployment utility; deploy SSIS packages to SQL or file system locations; validate deployed packages; deploy packages on multiple servers; how to install custom components and tasks; deploy SSIS packages by using DTUTIL
Configure SSIS security settings
SSIS catalog database roles; package protection levels; secure Integration Services packages that are deployed at the file system; secure Integration Services parameters, configuration

Preparation resources
Troubleshooting tools for package development
Load-balancing packages on remote servers by using SQL Server Agent
Integration Services (SSIS) logging

Build data quality solutions (15–20%)
Install and maintain data quality services
Installation prerequisites; .msi package; adding users to the DQ roles; identity analysis, including data governance
Implement master data management solutions
Install Master Data Services (MDS); implement MDS; create models, entities, hierarchies, collections, attributes; define security roles; import/export; subscriptions
Create a data quality project to clean data
Profile Online Transaction Processing (OLTP) and other source systems; data quality knowledge base management; create data quality project; use data quality client; improve data quality; identity mapping and deduplicating; handle history and data quality; manage data quality/cleansing

Preparation resources
Install Data Quality Services
Install Master Data Services
Master Data Services features and tasks


QUESTION 1
You manage a member server that runs Windows Server 2008 R2. The server runs the Remote Desktop Gateway (RD Gateway) role service.
You need to find out whether a user named User1 has ever connected to his office workstation through the RD Gateway server.
What should you do?
A. View the events in the Monitoring folder from the RD Gateway Manager console.
B. View the Event Viewer Security log.
C. View the Event Viewer Application log.
D. View the Event Viewer Terminal Services-Gateway log.

Answer: D
Explanation:
By using TS Gateway Manager, you can specify the types of events that you want to monitor, such as unsuccessful or successful connection attempts to internal network computers through a TS Gateway server.
When these events occur, you can monitor the corresponding events by using Windows Event Viewer. TS
Gateway server events are stored in Event Viewer under Application and Services Logs\Microsoft\Windows
\Terminal Services-Gateway\.
Source: http://technet.microsoft.com/en-us/library/cc730618(WS.10).aspx


QUESTION 2
Your company has an Active Directory domain. All the servers in the company run either Windows Server 2008 R2 or Windows Server 2003. A Windows Server 2003 server named
Server1 runs Microsoft SQL Server 2005 SP2 and Microsoft Windows SharePoint Services
(WSS) 2.0.
The company plans to migrate to WSS 3.0 SP2 on a Windows Server 2008 R2 server named Server2.
You need to migrate the configuration and content from Server1 to Server2.
What should you do?
A. Back up the SharePoint configuration and content from Server1. Install WSS 3.0 SP2 on Server2. Restore the backup from Server1 to Server2.
B. Upgrade Server1 to Windows Server 2008 R2. Back up the SharePoint configuration and content from Server1. Install WSS 3.0 SP2 on Server2. Restore the backup from Server1 to Server2.
C. Back up the SQL Server 2005 configuration and the WSS 2.0 databases from Server1. Install SQL Server 2005 on Server2. Restore the SQL Server 2005 backup from Server1 to Server2.
D. Back up the WSS 2.0 configuration and content from Server1. Install WSS 2.0 on Server2. Restore the backup from Server1 to Server2. Perform an in-place upgrade of WSS 2.0 to WSS 3.0 SP2 on Server2.

Answer: D
Explanation:
To migrate to SharePoint Services (WSS) 3.0. from Server1 to Server2 with all the configuration and content, you need to install WSS 2.0 on Server2. Back up the WSS 2.0 configuration and content from Server1 and restore the backup from Server1 to Server2. Perform an in-place upgrade of WSS 2.0 to WSS 3.0 on Server2.
When you run an in-place upgrade, all content and configuration data is upgraded in-place, at one time. When you start the in-place upgrade process, the Web server and Web sites remain offline until the upgrade has been installed. In-place upgrades are best for a stand-alone server and small installations as in this case
Reference: Install and configure Office SharePoint Server for an in-place upgrade
http://technet.microsoft.com/en-us/library/cc263212(TechNet.10).aspx
Reference: Determine upgrade approach (Office SharePoint Server)
http://technet.microsoft.com/en-us/library/cc263447(TechNet.10).aspx


QUESTION 3
Your company has an Active Directory domain. You have a server named KMS1 that runs Windows Server 2008 R2. You install and configure Key Management Service (KMS) on KMS1. You plan to deploy Windows Server 2008 R2 on 10 new servers. You install the first two servers. The servers fail to activate by using KMS1.
You need to activate the new servers by using the KMS server.
What should you do first?
A. Complete the installation of the remaining eight servers.
B. Configure Windows Management Instrumentation (WMI) exceptions in Windows Firewall on the new servers.
C. Install Volume Activation Management Tool (VAMT) on the KMS server and configure Multiple Activation Key (MAK) Proxy Activation.
D. Install Volume Activation Management Tool (VAMT) on the KMS server and configure Multiple Activation Key (MAK) Independent Activation.

Answer: A
Explanation:
Key Management Service
With KMS, IT pros can complete activations on their local network, eliminating the need for individual computers to connect to Microsoft for product activation. KMS is a lightweight service that does not require a dedicated system and can easily be co-hosted on a system that provides other services. By default, volume editions of Windows 7 and Windows Server 2008 R2 connect to a system that hosts the KMS service to request activation. No action is required from the user.
KMS requires a minimum number of computers (physical or virtual machines) in a network environment.
The organization must have at least five computers to activate Windows Server 2008 R2 and at least 25 computers to activate clients that are running Windows 7. These minimums are referred to as activation thresholds.
To use KMS activation with Windows 7, the computer must have the qualifying OS license (often obtained through OEMs as part of the new PC purchase) and contain a Windows Marker in BIOS.
Source: http://technet.microsoft.com/en-us/library/ff793423.aspx


QUESTION 4
You have four Remote Desktop Session Host Servers that run Windows Server 2008 R2. The Remote Desktop Session Host Servers are named Server1, Server2, Server3, and Server4.
You install the Remote Desktop Connection Broker role service on Server1.
You need to configure load balancing for the four Remote Desktop Session Host Servers. You must ensure that Server2 is the preferred server for Remote Desktop Services sessions.
Which tool should you use?
A. Group Policy Management
B. Remote Desktop Session Host Configuration
C. Remote Desktop Connection Manager
D. RD Gateway Manager

Answer: B
Explanation: ExplanationExplanation:
You can configure a Remote Desktop Session Host (RD Session Host) server to join a farm in RD Connection
Broker, and to participate in RD Connection Broker Load Balancing, by using the Remote Desktop Session Host Configuration tool.
To configure RD Connection Broker settings
1. On the RD Session Host server, open Remote Desktop Session Host Configuration. To open Remote Desktop Session Host Configuration, click Start, point to Administrative Tools, point to Remote Desktop Services, and then click Remote Desktop Session Host Configuration .
2. In the Edit settings area, under RD Connection Broker, double-click Member of farm in RD Connection Broker.
3. On the RD Connection Broker tab of the Properties dialog box, click Change Settings.
4. In the RD Connection Broker Settings dialog box, click Farm member.
5. In the RD Connection Broker server name box, type the name of the RD Connection Broker server.
6. In the Farm name box, type the name of the farm that you want to join in RD Connection
Broker.
7. Click OK to close the RD Connection Broker Settings dialog box.
8. To participate in RD Connection Broker Load Balancing, select the Participate in Connection Broker Load-Balancing check box.
9. Optionally, in the Relative weight of this server in the farm box, modify the server weight. By default, the value is 100. The server weight is relative. Therefore, if you assign one server a value of 50, and one a value of 100, the server with a weight of 50 will receive half the number of sessions.
10. Verify that you want to use IP address redirection. By default, the Use IP address redirection setting is enabled. If you want to use token redirection mode, select Use token redirection. For more information, see About IP Address and Token Redirection.
11. In the Select IP addresses to be used for reconnection box, select the check box next to each IP address that you want to use.
12. When you are finished, click OK.
Source: http://technet.microsoft.com/en-us/library/cc771383.aspx


QUESTION 5
You have a server that runs Windows Server 2008 R2. The server has Microsoft SharePoint Foundation 2010 installed. The server is configured to accept incoming email.
You create a new document library.
You need to ensure that any user can send e-mail to the document library.
What should you do?
A. Modify the RSS setting for the document library.
B. Modify the permissions for the document library.
C. Modify the incoming email settings for the document library.
D. Enable anonymous authentication for the Web application.

Answer: C
Explanation:
Enable and configure email settings for a library
Use this procedure to enable and configure email settings for a library to receive email
messages in the
SharePoint document library in a site.
Enable and configure email settings for a library
1. Open the site in which you want to receive email messages by using either of the following methods:
– In Internet Explorer, type the URL o the site.
– On the View Site Collection page, click the site collection that you want to view.
2. In the left navigation pane of the home page, click View All Site Content.
3. In the Documents section, click a document library name to open the library for which you want to enable and configure email settings.
4. On the Settings menu, click Document Library Settings, Picture Library Settings, or
Form Library Settings, depending on the kind of library that you are enabling and
configuring.
5. In the Communications section, click Incoming email settings.
6. In the Email section, select Yes to enable this library to receive email messages.
7. In the Email address box, type a unique name to use as part of the email address for this library.
8. In the Email Attachments section, decide where to save and how to group the email attachments in this library, and then choose whether to overwrite files that have the same name.
Note: If you decide not to overwrite files that have the same name and then later try to save a file that has the same name as one that already exists in the library, four random digits are appended to the file name for the new attachment. If this action fails, a globally unique identifier (GUID) is appended to the file name. If neither of these actions can produce a unique file name, the attachment is discarded.
9. In the Email Message section, choose whether to save the original email message in this library. If you select Yes, the original message is saved as a separate item in the library.
10. In the Email Meeting Invitations section, choose whether to save the attachments to your meeting invitations in this library.
11. In the Email Security section, choose whether to archive email messages only from members of the site who can write to the library or to archive regardless of who sends the email message.
12. Click OK to save the changes that you have made in the settings.
Source: http://technet.microsoft.com/en-us/library/cc262800.aspx

 

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

MCTS Training, MCITP Trainnig

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

Answer: A


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

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

Answer: B

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


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

A. GRANT DELETE ON Purchases. Suppliers TC ContosoUser

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

C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser

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

Answer: B

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


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

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

Answer: B

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


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

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

Answer: C

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

MCTS Training, MCITP Trainnig

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

 

Exam 70-461 Querying Microsoft SQL Server 2012/2014

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

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

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

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

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

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

In December 2015, this exam was evaluated for applicability to both SQL Server 2012 and SQL Server 2014. It was determined that no updates to exam objectives were required.

Create database objects (20–25%)
Create and alter tables using T-SQL syntax (simple statements)
Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE
Create and alter views (simple statements)
Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP
Design views
Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications
Create and modify constraints (simple statements)
Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints
Create and alter DML triggers
Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

Preparation resources
Tables
Views
table_constraint (Transact-SQL)

Work with data (25–30%)
Query data by using SELECT statements
Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce
Implement sub-queries
Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement
Implement data types
Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns
Implement aggregate queries
New analytic functions; grouping sets; spatial aggregates; apply ranking functions
Query and manage XML data
Understand XML datatypes and their schemas and interop w/, limitations and restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML; XML indexing

Preparation resources

SELECT (Transact-SQL)
INTO clause (Transact-SQL)
FROM (Transact-SQL)

Modify data (20–25%)
Create and alter stored procedures (simple statements)
Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL
Modify data by using INSERT, UPDATE, and DELETE statements
Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement
Combine datasets
Difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements
Work with functions
Understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)

Preparation resources
CREATE PROCEDURE (Transact-SQL)
ALTER PROCEDURE (Transact-SQL)
INSERT (Transact-SQL)

Troubleshoot and optimize (25–30%)
Optimize queries
Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in
Manage transactions
Mark a transaction; understand begin tran, commit, and rollback; implicit vs explicit transactions; isolation levels; scope and type of locks; trancount
Evaluate the use of row-based operations vs. set-based operations
When to use cursors; impact of scalar UDFs; combine multiple DML operations
Implement error handling
Implement try/catch/throw; use set based rather than row based logic; transaction management

Preparation resources
Transaction statements (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)


QUESTION 1
You develop a database for a travel application. You need to design tables and other database objects.
You create the Airline_Schedules table.
You need to store the departure and arrival dates and times of flights along with time zone information.
What should you do?

A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.

Answer: I

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ff848733.aspx
Reference: http://msdn.microsoft.com/en-us/library/bb630289.aspx


QUESTION 2
You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.

Answer: E


QUESTION 3
You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You
discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to
reduce fragmentation. You need to achieve this goal without taking the index offline. Which
Transact-SQL batch should you use?

A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP
EXISTING
B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C. ALTER INDEX ALL ON OrderDetail REBUILD
D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Answer: B


QUESTION 4
You develop a Microsoft SQL Server 2012 database. The database is used by two web applications
that access a table named Products. You want to create an object that will prevent the applications
from accessing the table directly while still providing access to the required data. You need to
ensure that the following requirements are met:
* Future modifications to the table definition will not affect the applications’ ability to access data.
* The new object can accommodate data retrieval and data modification.
* You need to achieve this goal by using the minimum amount of changes to the existing
applications.
What should you create for each application?

A. views
B. table partitions
C. table-valued functions
D. stored procedures

Answer: A


QUESTION 5
You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets
the following requirements:
* Returns a result set based on supplied parameters.
* Enables the returned result set to perform a join with a table.
Which object should you use?

A. Inline user-defined function
B. Stored procedure
C. Table-valued user-defined function
D. Scalar user-defined function

Answer: C


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

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

Answer: A


QUESTION 7
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named SalesDB.
The SalesDB database is used by an in-house application named ABCApp3. ABC.com users
report that ABCApp3 is functioning sluggishly.
You discover that application consumes considerable memory when it runs single-use dynamic
queries against the SalesDB database. You suspect that these queries are making excessive use
of procedure cache.
How would you reduce procedure cache if you cannot create new indexes on the SalesDB
database?

A. You should replace the queries with recursive stored procedures.
B. You should add make use of the INCLUDE clause in the index.
C. You should make use of the READPAST hint in the queries.
D. You should make use of the READCOMMITTED hint in the queries.
E. You should make use of the optimize for ad hoc workloads option.

Answer: E

Explanation:


QUESTION 8
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named ABCDB.
The ABCDB database is used by an in-house application named ABCApp3 that queries a readonly
table with a clustered index. ABC.com users report that ABCApp3 is functioning sluggishly.
You suspect query the application uses is causing the problem. You analyze the query and
discover that column referenced in the WHERE clause is not part of the clustered index. You also
notice that the query returns five columns, as well as a COUNT (*) clause grouped on the five columns.
How would you improve the efficiency of this query?

A. You should replace the query with recursive stored procedure.
B. You should replace the COUNT (*) clause with a persisted computed column.
C. You should create nonclustered indexes on all columns used in the query.
D. You should create a filtered index on the column used in the WHERE clause.
E. You should add an INCLUDE clause to the clustered index.
F. You should create a columnstore index on all columns used in the query.
G. You should create a unique clustered index on the column used in the WHERE clause.

Answer: F

Explanation:

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

MCTS Training, MCITP Trainnig

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

Exam 70-458 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2

Published: June 11, 2012
Languages: English, Japanese
Audiences: IT professionals
Technology: Microsoft SQL Server 2012
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.

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

Preparation resources
Back up and restore of SQL Server databases
File restores (full recovery model)
DBCC INDEXDEFRAG (Transact-SQL)

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

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

Implement high availability
Implement AlwaysOn
Implement a mirroring solution using AlwaysOn; failover
Implement database mirroring
Set up mirroring; monitor the performance of database mirroring
Implement replication
Troubleshoot replication problems; identify appropriate replication strategy

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

Design and implement a data warehouse
Design and implement dimensions
Design shared/conformed dimensions; determine whether you need support for slowly changing dimensions; determine attributes; design hierarchies; determine whether you need star or snowflake schema; determine the granularity of relationship with fact tables; determine the need for auditing or lineage; determine keys (business transactional or your own data warehouse/surrogate keys); implement dimensions; implement data lineage of a dimension table
Design and implement fact tables
Design a data warehouse that supports many to many relationships; appropriately index a fact table using columnstore indexes; partitioning; additive measures; semi-additive measures; non-additive measures; implement fact tables; determine the loading method for the fact tables; implement data lineage of a fact table; design summary aggregation tables

Preparation resources
Introduction to dimensions (Analysis Services – multidimensional data)
Dimension relationships
Columnstore indexes

Extract and transform data
Design data flow
Define data sources and destinations; distinguish blocking and non-blocking transformations; use different methods to pull out changed data from data sources; determine appropriate data flow components; determine the need for supporting Slowly Changing Dimensions (SCD); determine whether to use SQL Joins or SSIS lookup or merge join transformations; batch processing vs. row by row processing; determine the appropriate transform to use for a specific task; determine the need and method for identity mapping and deduplicating; fuzzy lookup, fuzzy grouping, and Data Quality Services (DQS) transformation; determine the need for text mining; determine the need for custom data sources, destinations, and transforms; determine what to do with erroneous rows; determine auditing needs; determine sampling needs for data mining; trusted/authoritative data sources, including warehouse metadata
Implement data flow
Debug data flow; use the appropriate data flow components; SQL/SSIS data transformation; create SSIS packages that support slowly changing dimensions; use the Lookup task in SSIS; map identities using SSIS Fuzzy Lookup; specify a data source and destination; use data flows; different categories of transformations; read, transform, and load data; understand which transforms to use to accomplish a specific business task; data correction transformation; performance tune an SSIS dataflow; optimize Integration Services packages for speed of execution; maintain data integrity, including good data flow
Implement script tasks in SSIS
Determine whether it is appropriate to use a script task; extend the capability of a control flow; perform a custom action as needed (not on every row) during a control flow

Preparation resources
Data flow
Slowly changing dimension transformation
Script task

Load data
Design control flow
Determine control flow; determine containers and tasks that are needed; determine precedence constraints; design an SSIS package strategy with rollback, staging, and transaction control; decide between one package or multiple packages; determine event handlers; determine variables; determine parameters on package and project level; determine connection managers and whether they are package or project level; determine the need for custom tasks; determine how much information you need to log from a package; determine the need for checkpoints; determine security needs
Implement package logic by using SSIS variables and parameters
User variables; variable scope, data type; implement parameterization of properties using variables; use variables in precedence constraints; refer to SSIS system variables; design dynamic SSIS packages; package configurations (file or SQL tables); expressions; package and project parameters; project level connection managers; implement dynamic package behavior; configure packages in SSIS for different environments, package configurations (xmlconfiguration file, SQLServer table, registry entry; parent package variables, environment variable); parameters (package and project level); project connection managers; property expressions (use expressions for connection managers)
Implement control flow
Checkpoints; debug control flow; implement the appropriate control flow task to solve a problem; data profiling; use sequence containers and loop containers; manage transactions in SSIS packages; manage parallelism; use precedence constraint to control task execution sequence; create package templates; use the execute package task
Implement data load options
Implement a full and incremental data load strategy; plan for an incremental update of the relational Data Mart

Preparation resources
Integration Services transactions
Developing a custom task
Integration Services (SSIS) parameters

Configure and deploy SSIS solutions
Troubleshoot data integration issues
Performance issues; connectivity issues; execution of a task or transformation failed; logic issues; demonstrate awareness of the new SSIS logging infrastructure; troubleshoot a failed package execution to determine the root cause of failure; troubleshoot SSIS package failure from an invalid datatype; implement break points; data viewers; profile data with different tools; batch cleanup
Implement auditing, logging, and event handling
Audit package execution by using system variables; propagate events; use log providers; log an SSIS execution; create alerting and notification mechanisms; use Event Handlers in SSIS to track ETL events and errors; implement custom logging
Deploy SSIS solutions
Create and configure an SSIS catalog; deploy SSIS packages by using the deployment utility; deploy SSIS packages to SQL or file system locations; validate deployed packages; deploy packages on multiple servers; install custom components and tasks; deploy SSIS packages by using DTUTIL

Preparation resources
Troubleshooting tools for package development
Enable package logging in SQL Server data tools
Integration Services (SSIS) logging

Build Data Quality solutions
Install and maintain Data Quality Services
Installation prerequisites; use Data Quality Server Installer; add users to the DQ roles; identity analysis, including data governance
Implement master data management solutions
Install Master Data Services (MDS); implement MDS; create models, entities, hierarchies, collections, and attributes; define security roles; import/export; subscriptions
Create a data quality project to clean data
Profile Online Transaction Processing (OLTP) and other source systems; data quality knowledge base management; create a data quality project; use Data Quality Client; improve data quality; identity mapping and deduplicating; handle history and data quality; manage data quality/cleansing

Preparation resources
Install Data Quality Services
Install Master Data Services
Master Data Services features and tasks


QUESTION 1
You administer a Microsoft SQL Server 2012 database that has Trustworthy set to on.
You create a stored procedure that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure.
You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure.
You need to achieve this goal by granting the minimum permissions required.
What should you do? Choose all that apply.

A. Grant the db_datareader role on the database to User1.
B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
D. Move the stored procedure to the User1 schema.
E. Grant the VIEW SERVER STATE permission to User1.

Answer: B,C


QUESTION 2
You administer a SQL Server 2012 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster.
Which user account should you use?

A. a domain user
B. the SQLBrowser account
C. the BUILTIN\SYSTEM account
D. a local user with Run as Service permissions

Answer: A


QUESTION 3
You administer a Microsoft SQL Server 2012 database instance.
You plan to migrate the database to Windows Azure SQL Database. You verify that all objects contained in the database are compatible with Windows Azure SQL Database.
You need to ensure that database users and required server logins are migrated to Windows Azure SQL Database.
What should you do?

A. Back up the database from the local server and restore it to Windows Azure SQL Database.
B. Use the Copy Database wizard.
C. Use the Database Transfer wizard.
D. Use SQL Server Management Studio to deploy the database to Windows Azure SQL Database.

Answer: D


QUESTION 4
You are a database administrator for a Microsoft SQL Server 2012 environment.
You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.
You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.
Which feature should you use?

A. peer-to-peer replication
B. snapshot replication
C. failover clustering
D. database audits

Answer: A


QUESTION 5
Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named userA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to remove the Select permission for userA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the
Regions table, through the Sales role permissions.
Which Transact-SQL statement should you use?

A. DENY SELECT ON Object::Regions FROM Sales
B. DENY SELECT ON Schema:: Customers FROM Soles
C. REVOKE SELECT ON Object::Regions FROM Soles
D. REVOKE SELECT ON Schema: Customers FROM Soles
E. DENY SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema: Customers FROM UserA
G. REVOKE SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Schema::Customers FROM UserA
I. EXEC sp_oddrolemember ‘Sales’, ‘UserA’
J. EXEC 3p_droprolemember ‘Sales’, ‘UserA’

Answer: G


QUESTION 6
Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named userA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the following requirements are met:
The Sales role does not have the Select permission on the Customers schema.UserA has the Select permission on the Regions table.
Which Transact-SQL statement should you use?

A. DENY SELECT ON Object::Regions FROM Sales
B. DENY SELECT OH Schema:: Customers FROM Soles
C. REVOKE SELECT ON Object::Regions FROM Soles
D. REVOKE SELECT ON Schema:Customers FROM Soles
E. DENY SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema:Customers FROM UserA
G. REVOKE SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Schema::Customers FROM UserA
I. EXEC sp_oddrolemember ‘Sales’, ‘UserA’
J. EXEC sp_droprolemember ‘Sales’, ‘UserA’

Answer: D

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

MCTS Training, MCITP Trainnig

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

Exam 70-457 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

Published: June 11, 2012
Languages: English, Japanese
Audiences: IT professionals
Technology: Microsoft SQL Server 2012
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.

Create database objects
Create and alter tables using T-SQL syntax (simple statements)
Create tables without using the built-in tools; ALTER; DROP; ALTER COLUMN; CREATE
Design views
Ensure code non regression by keeping consistent signature for procedure, views, and function (interfaces); security implications
Create and alter DML triggers
Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

Preparation resources
Tables
Views
CREATE TRIGGER

Work with data
Query data by using SELECT statements
Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new code items such as synonyms and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; CASE versus ISNULL versus COALESCE
Implement sub-queries
Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement
Implement data types
Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use which data type for columns

Preparation resources
SELECT (Transact-SQL)
INTO clause (Transact-SQL)
FROM (Transact-SQL)

Modify data
Create and alter stored procedures (simple statements)
Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of stored procedure results; create a stored procedure for data access layer; program stored procedures, triggers, and functions with T-SQL
Modify data by using INSERT, UPDATE, and DELETE statements
Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement
Work with functions
Understand deterministic and non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)

Preparation resources
CREATE PROCEDURE (Transact-SQL)
ALTER PROCEDURE (Transact-SQL)
INSERT (Transact-SQL)

Troubleshoot and optimize queries
Optimize queries
Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios in which they would be used
Manage transactions
Mark a transaction; understand begin tran, commit, and rollback; implicit vs. explicit transactions; isolation levels; scope and type of locks; trancount
Evaluate the use of row-based operations vs. set-based operations
When to use cursors; impact of scalar UDFs; combine multiple DML operations
Implement error handling
Implement try/catch/throw; use set based rather than row based logic; transaction management

Preparation resources
Transaction statements (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
Cursors

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

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

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

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

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

Preparation resources
Configure login auditing (SQL Server Management Studio)
Data collection
Administer servers by using policy-based management


QUESTION 1
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named WeeklySales. The WeeklySales table records the sales
amount for each of ABC.com’s 20 sales representatives.
You need to write a Transact-SQL query that ranks the sales representatives by the average sales
amount for the past year. You want the sales representatives with the same average sales amount
to be ranked in the same sequence as they are being processed with no rank being skipped.
Which ranking function should you use?

A. The RANK( ) OVER function.
B. The NTILE( ) OVER function
C. The DENSE_RANK( ) OVER function
D. The ROW_NUMBER( ) OVER function
E. The FORMAT function.

Answer: C

Explanation:


QUESTION 2
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named WeeklySales. The WeeklySales table records the sales
amount for each of ABC.com’s 20 sales representitives.
You need to write a Transact-SQL query that ranks the sales representatives by the average sales
amount for the past year. You want the sales representatives with the same average sales amount
to have the same rank with the subsequent rank being skipped.
Which ranking function should you use?

A. The RANK( ) OVER function.
B. The NTILE( ) OVER function
C. The DENSE_RANK( ) OVER function
D. The ROW_NUMBER( ) OVER function
E. The FORMAT function.

Answer: C

Explanation:
Ref: http://msdn.microsoft.com/en-us/library/ms189798.aspx


QUESTION 3
You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes
over the last 12 months. You want the top 10 cricket players to have a rank of 1, the next 10 to
have a rank of 2, and the last 10 to have a rank of 3.
Which of the following SELECT statement would you use?
To answer, type the correct code in the answer area.

Answer: SELECT TOP 50 PlayerName,
NTILE (3) OVER (ORDER BY AVG (Votes) DESC) AS AveVotes
FROM WeeklyVotes
GROUP BY PlayerName


QUESTION 4
You work as a database developer at ABC.com. ABC has an in-house application named
ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database.
You want to run an execution plan against the query that will provide detailed information on
missing indexes.
How would you accomplish this task?

A. You should make use of the READPAST hint in the queries.
B. You should make use of the READCOMMITTED hint in the queries.
C. You should make use of the SET SHOWPLAN_XML ON statement in the query.
D. You should make use of the SET STATISTICS XML ON statement in the query.
E. You should make use of the SET XACT_ABORT OFF statement in the query.
F. You should make use of the SET CONTEXT_INFO statement in the query.

Answer: C

Explanation:


QUESTION 5
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named ABCDB.
The ABCDB database is used by an in-house application named ABCApp3 that queries a readonly
table with a clustered index. ABC.com users report that ABCApp3 is functioning sluggishly.
You suspect query the application uses is causing the problem. You analyze the query and
discover that column referenced in the WHERE clause is not part of the clustered index. You also
notice that the query returns five columns, as well as a COUNT (*) clause grouped on the five
columns.
How would you improve the efficiency of this query?

A. You should replace the query with recursive stored procedure.
B. You should replace the COUNT (*) clause with a persisted computed column.
C. You should create nonclustered indexes on all columns used in the query.
D. You should create a filtered index on the column used in the WHERE clause.
E. You should add an INCLUDE clause to the clustered index.
F. You should create a columnstore index on all columns used in the query.
G. You should create a unique clustered index on the column used in the WHERE clause.

Answer: F

Explanation:

 

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

MCTS Training, MCITP Trainnig

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

Answer: A

Explanation:


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

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

Answer: D

Explanation:


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

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

Answer: A

Explanation:


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

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

Answer: B

Explanation:


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

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

Answer: B
 

 

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

MCTS Training, MCITP Trainnig

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

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

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

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

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

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

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

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

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

Preparation resources
Installing SQL Server 2008
Configuring SQL Server 2008

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Sample Questions


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

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

Answer: C


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


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

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

Answer: B


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

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

Answer: E


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

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

Answer: A


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

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

Answer: C


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

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

Answer: A

 

 

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

MCTS Training, MCITP Trainnig

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

Dynamics CRM Online Deployment Exam MB2-706

Today I took and passed the Dynamics CRM Online Deployment exam MB2-706. The title only mention Dynamics CRM Online, however when I look more carefully on the skills measured section, there are some topics that quite overlapped with the Dynamics CRM Installation exam MB2-708.

First of all, I won’t break the NDA of the exam but I would like to give my thought on the exam. And if you ask me anything about sample questions and answers, I would say upfront, but sorry I won’t give anything.

As I mentioned earlier on the post that there are some overlapped topics with the installation exam, however I was quite unprepared when I was given some questions that supposedly only applicable for on-premise installation. Then I spent the time of the feedback to point out the questions that not accurate for the Online Deployment. Apart from that, the rest of the exams questions completely reflects the items that listed on the skills measured.

For the preparation of the exam, I just used the Dynamics CRM Online Deployment MOC training material from PartnerSource, however the training itself is not sufficient to pass the exam. Referring back to the skills measured, there are some topics that were not covered in the Online Deployment training, maybe combined with the installation course would be beneficial. Reading the Implementation and Administration guide also a good supplement to prepare the exam.

And one more tip for the exam: Second Shot. Microsoft is offering the second chance if you don’t pass the exam for the first time. The Second Shot offer valid for MCP exams that is taken between January 5, 2015, and May 31, 2015. My tip on this Second Shot is not use this opportunity to memorise the questions, but to identify on which section(s) that we are weak at, then do better preparation for the second chance.

I hope this helps!

I have just completed and passed the MB2-706 certification. (Online deployment certification.)

Interesting as the first certification I have completed online, not sure I’ll rush towards taking the exam on-line again.

I can’t / won’t break the NDA by mentioning specific questions but if you are preparing for the exam, the first tip I have it to actually prepare for the MB2-708 and MB2-706 exams together. There is a large amount of cross over. (MB2-708 being the CRM installations exam.)

I expected the main focus of MB2-706 to be Office 365 admin of CRM subscriptions and instances. Don’t get me wrong that stuff is covered but the exam goes quite a bit wider than that. Review the skills measured on the Microsoft site and ensure you have EVERYTHING covered.

I was unprepared for the number of questions about Outlook deployment and Email Router set-up. So much so that I did the unthinkable and failed first time out! Just shows that the old saying of “fail to prepare, prepare to fail” is bang on.

The “positive” was I got to try out using second shot! Your second attempt is currently still free with second shot. Originally this involved a lengthy process of obtaining a voucher and entering the code when you book the exam. They have made the process smoother these days, if you are eligible for second shot its automatically allocated when you book the exam.

Several questions came up about the process of upgrading from CRM2013 to CRM2015. When you are conducting research for the exam don’t just focus on a vanilla install. Look at any specific considerations for folks moving from earlier versions of CRM.

Be prepared for frustration with some questions! All the questions are valid in the wider context of a CRM deployment but quite a few seemed irrelevant for an online deployment exam. But to be fair Microsoft do make this clear in the skills measured. The word server appears six times in the administration section of the skills measured, that should have been my clue that some questions might feel more aligned to an on premise deployment exam. This area of the skills measured does not have a focus on online deployment and as this is up to 25% of the questions you should expect a significant number of questions that might feel off topic.

Click here to view complete Q&A of MB2-706 exam

MCTS Training, MCITP Trainnig

Best Microsoft MCTS Certification, Microsoft MB2-706 Training at certkingdom.com

Go to Top