Syllabus

If primary keys, normalization and SQL queries are your forte, consider becoming a Microsoft Certified Technology Specialist in SQL 2008. The MCTS SQL 2008 course is designed to equip you with the skill sets required for one of three database roles – Database Administrator, Database Developer or Business Intelligence developer.

The certification courses contain various SQL Server 2008 exams, each intended to test your expertise in a particular topic such as Implementation and Maintenance, Database Development or Business Intelligence Development and Maintenance.

Microsoft SQL Server 2008, Implementation and Maintenance

Introduction to SQL Server 2008 and its Toolset

• Introduction to SQL Server Platform
• Working with SQL Server Tools
• Configuring SQL Server Services
• Verify SQL Server Component Installation
• Alter Service Accounts for New Instance
• Enable Named Pipes Protocol for Both Instances
• Create Aliases for AdventureWorks and Proseware
• Ensure SQL Browser is Disabled and Configure a Fixed TCP/IP Port

Preparing Systems for SQL Server 2008

• Describe the SQL Server architecture
• Plan for server resource requirements
• Conduct pre-installation stress testing for SQL Server

Installing and Configuring SQL Server 2008

• Review installation requirements
• Install the SQL Server instance
• Perform Post-installation Setup and Checks
• Configure Server Memory

Working with Databases

• Adjust tempdb configuration
• Create the RateTracking database
• Attach the OldProspects database
• Add multiple files to tempdb
• Describe the role and structure of SQL Server databases
• Work with files and filegroups
• Move database files within servers and between servers

Understanding SQL Server 2008 Recovery Models

• Backup Strategies
• Understanding SQL Server Transaction Logging
• Planning a SQL Server Backup Strategy
• Plan a backup strategy
• Configure recovery models
• Review recovery models and strategy

Backup of SQL Server 2008 Databases

• Back up databases and transaction logs
• Manage database backups
• Work with more advanced backup options

Restoring SQL Server 2008 Databases

• Understand the restore process
• Restore databases
• Work with Point-in-time Recovery
• Restore system databases and individual files

Importing and Exporting Data

• Import the Excel spreadsheet
• Import the CSV file
• Create and test an extraction package
• Compare loading performance

Authenticating and Authorizing Users

• Authenticating Connections to SQL Server
• Authorizing Logins to Access Databases
• Authorization Across Servers
• Create Logins
• Correct an Application Login Issue
• Create Database Users
• Correct Access to Restored Database

Assigning Server and Database Roles

• Work with server roles
• Work with fixed database roles
• Create user-defined database roles

Authorizing Users to Access Resources

• Authorizing User Access to Objects
• Authorizing Users to Execute Code
• Configuring Permissions at the Schema Level

Auditing SQL Server Environments

• Options for Auditing Data Access in SQL Server
• Implementing SQL Server Audit
• Managing SQL Server Audit

Automating SQL Server 2008 Management

• Automating SQL Server Management
• Working with SQL Server Agent
• Managing SQL Server Agent Jobs

Configuring Security for SQL Server Agent

• Explain SQL Server Agent security
• Configure credentials
• Configure Proxy accounts

Monitoring SQL Server 2008 with Alerts and Notifications

• Configure database mail
• Monitor SQL Server errors
• Configure operators, alerts and notifications

Performing Ongoing Database Maintenance

• Ensuring Database Integrity
• Maintaining Indexes
• Automating Routine Database Maintenance
• Check database integrity using DBCC CHECKDB
• Correct index fragmentation
• Create a database maintenance plan
• Investigate table lock performance

Tracing Access to SQL Server 2008

• Capturing Activity using SQL Server Profiler
• Improving Performance with the Database Engine Tuning Advisor
• Working with Tracing Options

Monitoring SQL Server 2008

• Monitoring Activity
• Capturing and Managing Performance Data
• Analyzing Collected Performance Data
• Investigating DMVs
• Configure Management Data Warehouse
• Configure Instances for Data Collection
• Work with Data Collector Reports

Managing Multiple Servers

• Working with Multiple Servers
• Virtualizing SQL Server
• Deploying and Upgrading Data-Tier Applications
• Configure CMS and execute multi-server queries
• Deploy a data-tier application
• Register and extract a data-tier application
• Upgrade a data-tier application

Troubleshooting Common SQL Server 2008 Administrative Issues

• SQL Server Troubleshooting Methodology
• Resolving Service-related Issues
• Resolving Concurrency Issues
• Resolving Login and Connectivity Issues