MCITP: Database Administrator SQL Server 2008 Microsoft Certified IT Professional (MCITP) certification

MCITP: Database Administrator SQL Server 2008
Microsoft Certified IT Professional (MCITP) certification

• Installing and Configuring SQL Server 2008
• Configure SQL Server 2008 instances and databases
• Maintaining SQL Server Instances and Configuration
• Managing SQL Server Security
• Maintaining a SQL Server Database
• High Availability and Disaster Recovery, database mirroring
• Performing Data Management Tasks
• Implement log shipping, database snapshots, relational data 
• XML, HTTP endpoint, Serive Broker, Export, Import, Replication 
• Backup, Restore, Move Database between Servers, Performance 
• Table, Views, Storedprocedure, Triggers, Functions, Constrains, Indexes 
• Security Logins and Data Encryptions

Microsoft Certified IT Professional (MCITP) certification SQL 2008 
Course Description

The Microsoft SQL Server 2008 provides Failover clustering and database mirroring technology and enable enterprises to deliver highly reliable, available applications to employees, customers, and partners. SQL 2008 students with learn both theoretical and practical instruction in the areas of an overview to SQL Server, Database Management and the Transact-SQL language, and SQL Server Databases. It will teach students how to work with SQL Server data objects. Students will learn how to create and manage view, stored procedures, user-defined functions, and triggers, and SQL Server indexes, how to plan for index creation, how to create and manage indexes, as well as learn how to optimize query performance and how to analyze queries. This curriculum teaches SQL Server 2008 transactions and locks, and how to import and export data, and how to transform data with DTS. Additionally, users will learn how to manage distributed queries and linked servers, create transactions, manage locks, and how to troubleshoot transactions, locking, XML, HTTP endpoint, Serive Broker, Export, Import, Replication and database backup and restore.

MCITP certification Exam 70-432
Microsoft SQL Server 2008 - Implementation and Maintenance

Installing and Configuring SQL Server 2008 
• Install SQL Server 2008 and related services, file locations; default paths; service accounts 
• Configure SQL Server instances, sp_configure 
• 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 
• Implement database mail, set up and configure 
• Configure full-text indexing, enable/disable, index population

Maintaining SQL Server Instances 
• 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

Managing SQL Server Security 
• Manage logins and server roles, create logins; disable/enable logins; security model (authentication mode); password policy enforcement; fixed server roles; alter logins 
• Manage users and database roles, user mapping; user-defined roles; fixed roles; guest, public, dbo; creating and deleting user roles 
• Manage SQL Server instance permissions, logon triggers; permissions vs. fixed role membership; cross-database ownership chaining; impersonation 
• Manage database permissions, impersonation; cross-database ownership chaining 
• Manage schema permissions and object permissions, manage schema ownership 
• Audit SQL Server instances, use DDL triggers and logon triggers; C2; common criteria; login failures; event notifications 
• Manage transparent data encryption, impact of transparent data encryption on backups 
• Configure surface area, sp_configure

Maintaining a SQL Server Database 
• Back up databases, full backups; differential backups; transaction log; compressed backups; file and filegroup backups; verifying backup 
• Restore databases, online restores; full restores; differential restores; transaction log; file and filegroup restores; verifying restore; tail of the transaction log 
• 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 
• Maintain a database by using maintenance plans, Maintenance Plan Wizard; Maintenance Plan Designer

Performing Data Management Tasks
• Import and export data, BCP; BULK INSERT; OPENROWSET; GUI tools 
• Manage data partitions, switching data from one partition to another; add a filegroup; alter a partition function; alter a partition scheme 
• Implement data compression, sparse columns; page/row 
• 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 
• Manage collations, column collation; database collation; instance collation

Monitoring and Troubleshooting SQL Server
• 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

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, monitoring, configuring, failover 
• Implement a SQL Server clustered instance, monitoring, configuring, failover 
• Implement log shipping, monitoring, configuring, failover 
• Implement replication, monitoring, configuring, failover

MCIPT certification Exam 70-450
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.
• RAID, calculating table size, IO throughput, transaction per second, data compression, non-uniform memory access (NUMA), tempdb capacity
• Design SQL Server instances. 
• Instance configuration, surface area configuration, CPU affinity, memory allocation, max degree of parallelism (MAXDOP), collation
• Design physical database and object placement.
• 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. 
• 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 
• Choosing authentication type, logon triggers, regulatory requirements
• Design instance-level security configurations 
• Windowsservice accounts, filestream, proxy, credentials, instance-level permissions, certificate and key management, endpoint security, using SSL certificates, TCP ports
• Design database, schema, and object security paramaters 
• Users, roles, certificate and key management, Service broker, Common Language Runtime (CLR), ownership chains
• Design a security policy and an audit plan 
• 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 
• Cluster resource group, cluster setup considerations, number of nodes, service accounts
• Design database mirroring 
• Design a high-availability solution that is based on replication 
• Different replication types, topologies, recover from replication failure, synchronization, health monitoring
• Design a high-availability solution that is based on log shipping 
• 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 
• Failover clustering, database mirroring, log shipping, replication

Designing a Backup and Recovery Solution 
• Design a backup strategy 
• 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 
• 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 
• 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 
• 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 
• Instance, database and object monitoring, data collection, event notifications, dynamic management objects, analyze results
• Design a solution to monitor performance and concurrency 
• 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 
• Design a solution to govern resources 
• Resource Governor (CPU, memory, number of requests per second; resource pools, resource groups), query governor
• Design policies by using Policy-Based Management 
• 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 
• SQL Server PowerShell, Windows Management Instrumentation (WMI), SQL Server Agent, event notifications, DDL triggers

Designing a Strategy for Data Distribution
• Administer SQL Server Integration Services (SSIS) packages 
• 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 
• Design a replication strategy for data distribution 
• Selecting replication types, conflict resolution, health monitoring, horizontal and vertical partitioning