MCTS SQL Server

MCTS SQL Server

Category:
Microsoft/
$2,495.00
A logo of Microsoft with orange borders

$2,495.00

A. SQL Server 2008, Implementation & Maintenance 432
B. SQL Server 2008, DB Development 433
C. SQL Server 2008 BI Development & Maintenance 448

 

A. SQL Server 2008, Implementation & Maintenance    432

  • Implementing an SSIS Solution (17%)
    • Implement control flow.
      • This objective may include but is not limited to: checkpoints; debug control flow; transactions; implement the appropriate control flow task to solve a problem; data profiling and quality
    • Implement data flow.
      • This objective may include but is not limited to: debug data flow; implement the appropriate data flow components
    • Implement dynamic package behavior by using property expressions.
    • Implement package logic by using variables.
      • This objective may include but is not limited to: system variables; user variables; variable scope
    • Implement package configurations.
    • Implement auditing, logging, and event handling.
      • This objective may include but is not limited to: use system variables for auditing; use event handlers; propagate events; use log providers; data profiling
    • Extend SSIS packages by using .NET code.
      • This objective may include but is not limited to: use the script task; use the script component; use custom assemblies
  • Configuring, Deploying, and Maintaining SSIS (15%)
    • Install and maintain SSIS components.
      • This objective may include but is not limited to: implement disaster recovery for SSIS
    • Deploy an SSIS solution.
      • This objective may include but is not limited to: 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.
      • This objective may include but is not limited to: 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.
      • This objective may include but is not limited to: MSDB database roles; package protection levels
    • Identify and resolve issues related to SSIS solution deployment.
      • This objective may include but is not limited to: validate deployed packages; deploy packages and dependencies between servers
  • Implementing an SSAS Solution (21%)
    • Implement dimensions in a cube.
      • This objective may include but is not limited to: translations; attribute relations; hierarchies
    • Implement measures in a cube.
      • This objective may include but is not limited to: measure groups
    • Implement a data source view.
      • This objective may include but is not limited to: named calculations; named queries
    • Configure dimension usage in a cube.
      • This objective may include but is not limited to: 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.
      • This objective may include but is not limited to: actions; key performance indicators (KPI); calculated members; calculations
    • Implement data mining.
      • This objective may include but is not limited to: implement data mining structures and models; query data mining structures by using DMX; data mining views
    • Implement storage design in a cube.
      • This objective may include but is not limited to: aggregations; partitions; storage modes; proactive caching
  • Configuring, Deploying, and Maintaining SSAS (17%)
    • Configure permissions and roles in SSAS.
      • This objective may include but is not limited to: server roles; SSAS database roles; cube roles; enable client application access; implement custom access to data
    • Deploy SSAS databases and objects.
      • This objective may include but is not limited to: Deployment Wizard; BIDS; SSMS; SSIS Analysis Services Execute DDL task
    • Install and maintain an SSAS instance.
      • This objective may include but is not limited to: disaster recovery
    • Diagnose and resolve performance issues.
      • This objective may include but is not limited to: use SQL Profiler; performance monitor counters; DMVs; Usage Based Optimization Wizard
      • Implement processing options.
  • Implementing an SSRS Solution (17%)
    • Implement report data sources and datasets.
      • This objective may include but is not limited to: query types; dynamic data sources; filter location (dataset vs. query)
    • Implement a report layout.
      • This objective may include but is not limited to: apply conditional formatting; page configuration; headers and footers
    • Extend an SSRS solution by using code.
      • This objective may include but is not limited to: custom .NET assembly; private code
    • Create an SSRS report by using an SSAS data source.
      • This objective may include but is not limited to: MDX in an SSRS report; DMX in an SSRS report
    • Implement report parameters.
      • This objective may include but is not limited to: databound parameters; multi-value parameters
    • Implement interactivity in a report.
      • This objective may include but is not limited to: drilldown; drillthrough; interactive sorting
    • Implement report items.
      • This objective may include but is not limited to: matrix; table; chart; image; list; grouping
    • Embed SSRS reports in custom applications.
      • This objective may include but is not limited to: use the Windows Forms Report Viewer; use the Web Forms Report Viewer; use the SSRS Web service
  • Configuring, Deploying, and Maintaining SSRS (13%)
    • Configure report execution and delivery.
      • This objective may include but is not limited to: subscriptions; report caching; schedules; snapshot history
    • Install and configure SSRS instances.
      • This objective may include but is not limited to: deploy an SSRS Web farm
    • Configure authentication and authorization for a reporting solution.
      • This objective may include but is not limited to: configure server-level and item-level role-based security; configure Windows authentication and custom authentication
    • Deploy an SSRS solution.
      • This objective may include but is not limited to: RS.exe scripts; Report Builder; BIDS
    • Configure SSRS availability.
      • This objective may include but is not limited to: key management; migrate SSRS databases

 

B. SQL Server 2008, DB Development    433

  • Implementing Tables and Views (14 percent)
    • Create and alter tables.
      • This objective may include but is not limited to: computed and persisted columns; schemas; scripts to deploy changes to multiple environments, for example, dev, test, production; manage permissions (GRANT, DENY, REVOKE)
    • Create and alter views.
      • This objective may include but is not limited to: WITH ENCRYPTION; WITH SCHEMABINDING; WITH CHECK OPTION; manage permissions (GRANT, DENY, REVOKE)
    • Create and alter indexes.
      • This objective may include but is not limited to: filtered, included columns, unique, clustered, non-clustered, FILL FACTOR; CREATE STATISTICS; indexing views, compression
    • Create and modify constraints.
      • This objective may include but is not limited to: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling, NOCHECK; SET IDENTITY_INSERT
    • Implement data types.
      • This objective may include but is not limited to: FILESTREAM; spatial, structured, and semi-structured; collations
    • Implement partitioning solutions.
      • This objective may include but is not limited to: partitioned tables and indexes (constraints, partition functions, partition schemes, MERGE, SPLIT, SWITCH); distributed partitioned views (constraints, linked servers)
  • Implementing Programming Objects (16 percent)
    • Create and alter stored procedures.
      • This objective may include but is not limited to: table-valued parameters (TVPs), EXECUTE AS, RECOMPILE, parameter direction (output); WITH ENCRYPTION; manage permissions (GRANT, DENY, REVOKE)
    • Create and alter user-defined functions (UDFs).
      • This objective may include but is not limited to: WITH SCHEMABINDING, EXECUTE AS; manage permissions (GRANT, DENY, REVOKE)
    • Create and alter DML triggers.
      • This objective may include but is not limited to: INSERTED, DELETED, INSTEAD OF, EXECUTE AS
    • Create and alter DDL triggers.
      • This objective may include but is not limited to: enabling/disabling; return event data
    • Create and deploy CLR-based objects.
      • This objective may include but is not limited to: permission sets (SAFE, UNSAFE, EXTERNAL_ACCESS), SET TRUSTWORTHY
    • Implement error handling.
      • This objective may include but is not limited to: TRY/CATCH, RAISERROR, retrieving error information, custom error messages, @ERROR
    • Manage transactions.
      • This objective may include but is not limited to: BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL, named transactions
  • Working with Query Fundamentals (21 percent)
    • Query data by using SELECT statements.
      • This objective may include but is not limited to: LIKE, WHERE, ORDER BY, INTO
    • Modify data by using INSERT, UPDATE, and DELETE statements.
      • This objective may include but is not limited to: row constructors, DELETE FROM, UPDATE FROM, TRUNCATE TABLE
    • Return data by using the OUTPUT clause.
      • This objective may include but is not limited to: INSERTED, DELETED, INTO
    • Modify data by using MERGE statements.
      • This objective may include but is not limited to: INSERTED, DELETED, OUTPUT
    • Implement aggregate queries.
      • This objective may include but is not limited to: built-in aggregate functions, GROUPING SETS, GROUP BY, HAVING
    • Combine datasets.
      • This objective may include but is not limited to: CROSS APPLY, OUTER APPLY, all join types; UNION, UNION ALL, INTERSECT, EXCEPT
    • Apply built-in scalar functions.
      • This objective may include but is not limited to: CAST and CONVERT; REPLACE; LEN and DATALENGTH; PATINDEX and CHARINDEX
  • Applying Additional Query Techniques (15 percent)
    • Implement subqueries.
      • This objective may include but is not limited to: simple, correlated, scalar, list, table valued
    • Implement CTE (common table expression) queries.
      • This objective may include but is not limited to: recursive, non-recursive
    • Apply ranking functions.
      • This objective may include but is not limited to: RANK, PARTITION BY, DENSE_RANK, OVER, ROW_NUMBER, NTILE
    • Control execution plans.
      • This objective may include but is not limited to: table hints, query hints
    • Manage international considerations.
      • This objective may include but is not limited to: collations, defining custom errors, filtering data, sort order, nvarchar, database collation, column collation
  • Working with Additional SQL Server Components (10 percent)
    • Integrate Database Mail.
    • Implement full-text search.
      • This objective may include but is not limited to: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE, STOPLIST
    • Implement scripts by using Windows PowerShell and SQL Server Management Objects (SMOs).
      • This objective may include but is not limited to: cmdlets
    • Implement Service Broker solutions.
      • This objective may include but is not limited to: services, queues, messages, message types, message validation, contracts, activation procedures, routes
    • Track data changes.
      • This objective may include but is not limited to: change tracking; database audit specification; CHANGETABLE, Change Data capture
  • Working with XML Data (12 percent)
    • Retrieve relational data as XML.
      • This objective may include but is not limited to: FOR XML
    • Transform XML data into relational data.
      • This objective may include but is not limited to: OPENXML, sp_xml_preparedocument, sp_xml_removedocument
    • Query XML data.
      • This objective may include but is not limited to: XQUERY, XPATH
    • Manage XML data.
      • This objective may include but is not limited to: XML datatype, XML indexes, XML schema collections
  • Gathering Performance Information (11 percent)
    • Capture execution plans.
      • This objective may include but is not limited to: graphical execution plans; SHOWPLAN, index details in Query Plans
    • Gather trace information by using the SQL Server Profiler.
    • Collect output from the Database Engine Tuning Advisor.
      • This objective may include but is not limited to: prepare a workload
    • Collect information from system metadata.
      • This objective may include but is not limited to: Dynamic Management Views (DMVs), catalog views

 

C. SQL Server 2008 BI Development & Maintenance    448

  • Installing and Configuring SQL Server 2008 (11%)
    • Install SQL Server 2008 and related services.
      • This objective may include but is not limited to: file locations; default paths; service accounts
    • Configure SQL Server instances.
      • This objective may include but is not limited to: sp_configure; Dynamic Management Views (DMVs)
    • Configure SQL Server services.
      • This objective may include but is not limited to: configuration manager; SQL browser
    • Configure additional SQL Server components.
      • This objective may include but is not limited to: SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), replication; MDS
    • Implement database mail.
      • This objective may include but is not limited to: set up and configure
    • Configure full-text indexing.
      • This objective may include but is not limited to: enable/disable, index population
  • Maintaining SQL Server Instances (9%)
    • Manage SQL Server Agent jobs.
      • This objective may include but is not limited to: create and schedule jobs; notification of job execution; disable/enable jobs; change job step order; logging
    • Manage SQL Server Agent alerts.
      • This objective may include but is not limited to: performance condition alerts; SQL event alerts; Windows Management Instrumentation WMI alerts
    • Manage SQL Server Agent operators.
      • This objective may include but is not limited to: operator schedules; fail safe operator; add a new operator; notification methods
    • Implement the declarative management framework (DMF).
      • This objective may include but is not limited to: create a policy; verify a policy; schedule a policy compliance check; enforce a policy; create a condition
    • Back up a SQL Server environment.
      • This objective may include but is not limited to: operating system-level concepts; SQL Server Utility; Compression media families
  • Managing SQL Server Security (18%)
    • Manage logins and server roles.
      • This objective may include but is not limited to: 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.
      • This objective may include but is not limited to: user mapping; user-defined roles; fixed roles; guest, public, dbo; creating and deleting user roles; application roles
    • Manage SQL Server instance permissions.
      • This objective may include but is not limited to: logon triggers; permissions vs. fixed role membership; cross-database ownership chaining; impersonation; end point permissions
    • Manage database permissions.
      • This objective may include but is not limited to: impersonation; cross-database ownership chaining
    • Manage schema permissions and object permissions.
      • This objective may include but is not limited to: manage schema ownership; object rights
    • Audit SQL Server instances.
      • This objective may include but is not limited to: use DDL triggers and logon triggers; C2; common criteria; login failures; event notifications
    • Manage transparent data encryption.
      • This objective may include but is not limited to: impact of transparent data encryption on backups; certificate management; symmetric keys
    • Configure surface area.
      • This objective may include but is not limited to: sp_configure
  • Maintaining a SQL Server Database (17%)
    • Back up databases.
      • This objective may include but is not limited to: full backups; differential backups; transaction log; compressed backups; file and filegroup backups; verifying backup; TDE Backups
    • Restore databases.
      • This objective may include but is not limited to: 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.
      • This objective may include but is not limited to: files, file groups, and related options; database options; recovery model; attach/detach data
    • Manage database snapshots.
      • This objective may include but is not limited to: create, drop, revert
    • Maintain database integrity.
      • This objective may include but is not limited to: DBCC CHECKDB; suspect pages; page level restores
    • Maintain a database by using maintenance plans.
      • This objective may include but is not limited to: Maintenance Plan Wizard; Maintenance Plan Designer
  • Performing Data Management Tasks (10%)
    • Import and export data. NOT: SSIS
      • This objective may include but is not limited to: BCP; BULK INSERT; OPENROWSET; GUI tools
    • Manage data partitions.
      • This objective may include but is not limited to: 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.
      • This objective may include but is not limited to: sparse columns; page/row; Unicode Compression
    • Maintain indexes.
      • This objective may include but is not limited to: 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.
      • This objective may include but is not limited to: column collation; database collation; instance collation
  • Monitoring and Troubleshooting SQL Server (14%)
    • Identify SQL Server service problems.
      • This objective may include but is not limited to: DB Engine service; SQL Agent service; SQL Browser service
    • Identify concurrency problems.
      • This objective may include but is not limited to: blocks, locks, deadlocks, activity monitor; relevant Dynamic Management Views
    • Identify SQL Agent job execution problems.
      • This objective may include but is not limited to: proxy accounts; credentials; job history
    • Locate error information.
      • This objective may include but is not limited to: error log; agent log; job execution history; event logs
  • 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 (DMVs).
    • Collect performance data by using System Monitor.
    • Use Performance Studio.
  • Implementing High Availability (9%)
    • Implement database mirroring.
      • This objective may include but is not limited to: monitoring, configuring, failover
    • Implement a SQL Server clustered instance.
      • This objective may include but is not limited to: monitoring, configuring, failover
    • Implement log shipping.
      • This objective may include but is not limited to: monitoring, configuring, failover
    • Implement replication.
      • This objective may include but is not limited to: monitoring, configuring