MCTS SQL Server

Dade Institute Of Technology and Health > MCTS SQL Server

MCTS SQL Server

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