The MCITP: Database Administrator/Developer 2008 certification requires four exams 70-432, 70-450, 70-433 & 70-451.
Students take these test onsite at our training facility while attending our course. Below are the objectives of these exams as presented by Microsoft.
Microsoft SQL Server 2008, Implementation and Maintenance
Installing and Configuring SQL Server 2008
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
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
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
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
Managing SQL Server Security
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
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
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
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
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
Configure surface area.
This objective may include but is not limited to: sp_configure
Maintaining a SQL Server Database
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
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
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
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
Import and export data.
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
Implement data compression.
This objective may include but is not limited to: sparse columns; page/row
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
Manage collations.
This objective may include but is not limited to: column collation; database collation; instance collation
Monitoring and Troubleshooting SQL Server
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
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
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, failover
Designing, Optimizing, and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008
Designing a SQL Server Instance and a Database Solution
Design for CPU, memory, and storage capacity requirements.
This objective may include but is not limited to: RAID, calculating table size, IO throughput, transaction per second, data compression, non-uniform memory access (NUMA), tempdb capacity
Design SQL Server instances.
This objective may include but is not limited to: instance configuration, surface area configuration, CPU affinity, memory allocation, max degree of parallelism (MAXDOP), collation
Design physical database and object placement.
This objective may include but is not limited to: heap and index placement, filestream, data and log files, filegroups, partition placement, large object placement, full text catalog
Design a migration, consolidation, and upgrade strategy.
This objective may include but is not limited to: multi-instance considerations, SQL Server version upgrade, instance and database collation, server-level and instance-level objects, service pack application
Designing a Database Server Security Solution
Design instance authentication.
This objective may include but is not limited to: choosing authentication type, logon triggers, regulatory requirements
Design instance-level security configurations.
This objective may include but is not limited to: Windows service accounts, filestream, proxy, credentials, instance-level permissions, certificate and key management, endpoint security, using SSL certificates, TCP ports
Design database, schema, and object security parameters.
This objective may include but is not limited to: users, roles, certificate and key management, Service broker, Common Language Runtime (CLR), ownership chains
Design a security policy and an audit plan.
This objective may include but is not limited to: Policy-Based Management Framework, security functions, sp_helprotect, catalog views, extended events, notifications
Design an encryption strategy.
This objective may include but is not limited to: Transparent Data Encryption, encrypting protected data, certificate and key management, filestream
Designing a Database Solution for High Availability
Design a failover clustering solution.
This objective may include but is not limited to: cluster resource group, cluster setup considerations, number of nodes, service accounts
Design database mirroring.
This objective may include but is not limited to: whether to use a witness server, Windows Server considerations, suspend vs. stop, automatic or manual failover, automatic page repair, database snapshots for reporting, managing instance-level objects
Design a high-availability solution that is based on replication.
This objective may include but is not limited to: different replication types, topologies, recover from replication failure, synchronization, health monitoring
Design a high-availability solution that is based on log shipping.
This objective may include but is not limited to: manage instance-level objects, changing roles, reporting secondary instance for reporting, monitor server, reinitializing, consistency check on secondary instance
Select high-availability technologies based on business requirements.
This objective may include but is not limited to: failover clustering, database mirroring, log shipping, replication
Designing a Backup and Recovery Solution
Design a backup strategy.
This objective may include but is not limited to: recovery model, compression, choosing backup types, scheduling, backup media, file and filegroups backup, verifying backups, key management, mirrored backups, cluster considerations
Design a recovery strategy.
This objective may include but is not limited to: page, file, filegroup, partial and online restores, orphan users, instance rebuild, encryption considerations, handling media failures, transaction logs, point in time and mark recovery, filestreams
Design a recovery test plan.
This objective may include but is not limited to: log shipping, replication, hardware considerations, scheduling a database restore test, handling high availability failures
Designing a Monitoring Strategy
Design a monitoring solution at the operating system level.
This objective may include but is not limited to: system monitor counters, event logs, dynamic management views and functions, Windows Management Instrumentation (WMI), remote monitoring, analyze results
Design a monitoring solution at the instance level.
This objective may include but is not limited to: instance, database and object monitoring, data collection, event notifications, dynamic management objects, analyze results
Design a solution to monitor performance and concurrency.
This objective may include but is not limited to: Dedicated Administrator Connection (DAC), locking, blocking, deadlocks, dynamic management objects, index utilization, tracing, analyze
Designing a Strategy to Maintain and Manage Databases
Design a maintenance strategy for database servers.
This objective may include but is not limited to: rebuild for page-level compression, index and heap maintenance, partition management, statistics
Design a solution to govern resources.
This objective may include but is not limited to: Resource Governor (CPU, memory, number of requests per second; resource pools, resource groups), query governor
Design policies by using Policy-Based Management.
This objective may include but is not limited to: designing policies and conditions
Design a data compression strategy.
This objective may include but is not limited to: row vs. page level, update frequency, compression ratio, compressing partitions, specific indexes
Design a management automation strategy.
This objective may include but is not limited to: SQL Server PowerShell, WMI, SQL Server Agent, event notifications, DDL triggers
Designing a Strategy for Data Distribution
Administer SQL Server Integration Services (SSIS) packages.
This objective may include but is not limited to: design security for accessing packages, troubleshoot and restart package, schedule package execution, deploy packages to same or different instances
Design a strategy to use linked servers.
This objective may include but is not limited to: security, providers, distributed transactions
Design a replication strategy for data distribution.
This objective may include but is not limited to: selecting replication types, conflict resolution, health monitoring, horizontal and vertical partitioning
Microsoft SQL Server 2008, Database Development
Implementing Tables and Views
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
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
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
Working with Query Fundamentals
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 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
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
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
Track data changes.
This objective may include but is not limited to: change tracking; database audit specification; CHANGETABLE
Working with XML Data
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
Capture execution plans.
This objective may include but is not limited to: graphical execution plans; SHOWPLAN
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
Designing Database Solutions and Data Access Using Microsoft SQL Server 2008
Designing a Database Strategy
Identify which SQL Server components to use to support business requirements.
This objective may include but is not limited to: SQL Server Agent, DB mail, Service Broker, Full-Text Search, Distributed Transaction Coordinator (DTC), linked servers
Design a database model.
This objective may include but is not limited to: normalization, entities, entity relationships
Design a data model by using the Entity Framework.
This objective may include but is not limited to: defining and maintaining mapping (query vs. stored proc), defining a data model, entity SQL
Design an application strategy to support security.
This objective may include but is not limited to: application roles, schema ownership, execution context, Windows vs. SQL authentication, permissions and database roles
Design a solution by using Service Broker.
This objective may include but is not limited to: designing services, contracts, activation, routes, message types, queues, remote service binding, priorities
Design a Full-Text Search strategy.
This objective may include but is not limited to: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE
Designing Database Tables
Identify the appropriate usage of new data types.
This objective may include but is not limited to: geography, geometry, hierarchyid, date, time, datetime2, datetimeoffset, varbinary (max) filestream
Design tables.
This objective may include but is not limited to: table width, sizing data types, IN_ROW_DATA (BLOBs), overflow data, sparse columns, computed columns, persisted computed columns
Design data integrity.
This objective may include but is not limited to: primary key, foreign key, check constraint, default constraint, NULL/NOT NULL, unique constraint, DML triggers
Designing Programming Objects
Design T-SQL stored procedures.
This objective may include but is not limited to: execution context (EXECUTE AS), table-valued parameters, determining appropriate way to return data, WITH RECOMPILE/OPTION (RECOMPILE), error handling, TRY/CATCH
Design views.
This objective may include but is not limited to: common table expressions, partitioned views, WITH CHECK OPTION, WITH SCHEMABINDING
Design T-SQL table-valued and scalar functions.
This objective may include but is not limited to: inline table-valued functions vs. views, multi-statement table-valued functions, determinism
Design Common Language Runtime (CLR) table-valued and scalar functions.
This objective may include but is not limited to: assembly PERMISSION_SET, CLR vs. T-SQL, ordered vs. non-ordered
Design CLR stored procedures, aggregates, and types.
This objective may include but is not limited to: assembly PERMISSION_SET, CLR vs. T-SQL, ordered vs. non-ordered, executing static methods on user-defined types, multi-parameter aggregations
Evaluate special programming constructs.
This objective may include but is not limited to: dynamic vs. prepared SQL (CREATE PROCEDURE... WITH EXECUTE AS) procedure, protecting against SQL injection
Designing a Transaction and Concurrency Strategy
Design the locking granularity level.
This objective may include but is not limited to: locking hints, memory consumption
Design for implicit and explicit transactions.
This objective may include but is not limited to: nested transactions, savepoints, TRY/CATCH
Design for concurrency.
This objective may include but is not limited to: hints, transaction isolation level, effect of database option READ_COMMITTED_SNAPSHOT, rowversion and timestamp datatypes
Designing an XML Strategy
Design XML storage.
This objective may include but is not limited to: determining when to use XML for storage, untyped vs. typed (XML schema collection)
Design a strategy to query and modify XML data.
This objective may include but is not limited to: when to use appropriate XPath and XQuery expressions, .query vs. .value, XML indexes for performance, typed vs. untyped, .exist, .modify
Design a query strategy by using FOR XML.
This objective may include but is not limited to: views, FOR XML PATH and EXPLICIT, FOR XML... TYPE
Design a strategy to transform XML into relational data.
This objective may include but is not limited to: .nodes, .value, .query, XQuery and XPath
Designing Queries for Performance
Optimize and tune queries.
This objective may include but is not limited to: optimizer hints, common table expressions (CTEs), search conditions, temporary storage, GROUP BY [GROUPING SETS|CUBE|ROLLUP]
Analyze execution plans.
This objective may include but is not limited to: execution order, logical and physical operators, join operators, minimize resource costs, compare query costs
Evaluate the use of row-based operations vs. set-based operations.
This objective may include but is not limited to: row-based logic vs. set-based logic, batching, splitting implicit transactions
Designing a Database for Optimal Performance
Optimize indexing strategies.
This objective may include but is not limited to: table-valued function, views, filtered indexes, indexed views, clustered and non-clustered, unique
Design scalable database solutions.
This objective may include but is not limited to: scale up vs. scale out, federated databases, distributed partitioned views, scalable shared databases, replication, offloading read-only query (database mirroring)
Resolve performance problems by using plan guides.
This objective may include but is not limited to: object plan guides, SQL plan guides, templates plan guides, dynamic management views
Design a table and index compression strategy.
This objective may include but is not limited to: row vs. page, update frequency, page compression implementation, compressing individual partitions
Design a table and index partitioning strategy.
This objective may include but is not limited to: switching partitions, merging, splitting, staging, creating, schemes and functions