Microsoft Certified Technology Specialist (MCTS) SQL 2008

Microsoft Certified Technology Specialist (MCTS) SQL 2008

• 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

(MCTS) 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.

MCTS 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