Skills measured by Exam 70-431Course 2778ECourse 2779Course 2780Installing and Configuring SQL Server 2005

Install SQL Server 2005.

• Verify prerequisites.

• Upgrade from an earlier version of SQL Server.

• Create an instance.

Configure SQL Server 2005 instances and databases.

• Configure log files and data files.

• Configure the SQL Server DatabaseMail subsystem for an instance.

• Choose a recovery model for the database.

Configure SQL Server security.

• Configure server security principals.

• Configure database securables.

• Configure encryption.

Configure linked servers by using SQL Server Management Studio (SSMS).

• Identify the external data source.

• Identify the characteristics of the data source.

• Identify the security model of the data source.

Implementing High Availability and Disaster Recovery

Implement database mirroring.

• Prepare databases for database mirroring.

• Create endpoints.

• Specify database partners.

• Specify a witness server.

• Configure an operating mode.

Implement log shipping.

• Initialize a secondary database.

• Configure log shipping options.

• Configure a log shipping mode.

• Configure monitoring.

Manage database snapshots.

• Create a snapshot.

• Revert a database from a snapshot.

Supporting Data Consumers

Retrieve data to support ad hoc and recurring queries.

• Construct SQL queries to return data.

• Format the results of SQL queries.

• Identify collation details.

Manipulate relational data.

• Insert, update, and delete data.

• Handle exceptions and errors.

• Manage transactions.

Manage XML data.

• Identify the specific structure needed by a consumer.

• Retrieve XML data.

• Modify XML data.

• Convert between XML data and relational data.

• Create an XML index.

• Load an XML schema.

Implement an HTTP endpoint.

• Create an HTTP endpoint.

• Secure an HTTP endpoint.

Implement Service Broker components.

• Create services.

• Create queues.

• Create contracts.

• Create conversations.

• Create message types.

• Send messages to a service.

• Route a message to a service.

• Receive messages from a service.

Import and export data from a file.

• Set a database to the bulk-logged recovery model to avoid inflating the transaction log.

• Run the bcp utility.

• Perform a Bulk Insert task.

• Import bulk XML data by using the OPENROWSET function.

• Copy data from one table to another by using the SQL Server 2005 Integration Services (SSIS) Import and Export Wizard.

Manage replication.

• Distinguish between replication types.

• Configure a publisher, a distributor, and a subscriber.

• Configure replication security.

• Configure conflict resolution settings for merge replication.

• Monitor replication.

• Improve replication performance.

• Plan for, stop, and restart recovery procedures.

Maintaining Databases

Implement and maintain SQL Server Agent jobs.

• Set a job owner.

• Create a job schedule.

• Create job steps.

• Configure job steps.

• Disable a job.

• Create a maintenance job.

• Set up alerts.

• Configure operators.

• Modify a job.

• Delete a job.

• Manage a job.

Manage databases by using Transact-SQL.

• Manage index fragmentation.

• Manage statistics.

• Shrink files.

• Perform database integrity checks by using DBCC CHECKDB.

Back up a database.

• Perform a full backup.

• Perform a differential backup.

• Perform a transaction log backup.

• Initialize a media set by using the FORMAT option.

• Append or overwrite an existing media set.

• Create a backup device.

• Back up filegroups.

Restore a database.

• Identify which files are needed from the backup strategy.

• Restore a database from a single file and from multiple files.

• Choose an appropriate restore method.

Move a database between servers.

• Choose an appropriate method for moving a database.

Monitoring and Troubleshooting SQL Server Performance

Gather performance and optimization data by using the SQL Server Profiler.

• Start a new trace.

• Save the trace logs.

• Configure SQL Server Profiler trace properties.

• Configure a System Monitor counter log.

• Correlate a SQL Server Profiler trace with System Monitor log data.

Gather performance and optimization data by using the Database Engine Tuning Advisor.

• Build a workload file by using the SQL Server Profiler.

• Tune a workload file by using the Database Engine Tuning Advisor.

• Save recommended indexes.

Monitor and resolve blocks and deadlocks.

• Identify the cause of a block by using the sys.dm_exec_requests system view.

• Terminate an errant process.

• Configure SQL Server Profiler trace properties.

• Identify transaction blocks.

Diagnose and resolve database server errors.

• Connect to a non-responsive server by using the dedicated administrator connection (DAC).

• Review SQL Server startup logs.

• Review error messages in event logs.

Monitor SQL Server Agent job history.

• Identify the cause of a failure.

• Identify outcome details.

• Find out when a job last ran.

Gather performance and optimization data by using DMVs.

Creating and Implementing Database Objects

Implement a table.

• Specify column details.

• Specify the filegroup.

• Assign permissions to a role for tables.

• Specify a partition scheme when creating a table.

• Specify a transaction.

Implement a view.

• Create an indexed view.

• Create an updateable view.

• Assign permissions to a role or schema for a view.

Implement triggers.

• Create a trigger.

• Create DDL triggers for responding to database structure changes.

• Identify recursive triggers.

• Identify nested triggers.

• Identify transaction triggers.

Implement functions.

• Create a function.

• Identify deterministic versus nondeterministic functions.

Implement stored procedures.

• Create a stored procedure.

• Recompile a stored procedure.

• Assign permissions to a role for a stored procedure.

Implement constraints.

• Specify the scope of a constraint.

• Create a new constraint.

Implement indexes.

• Specify the filegroup.

• Specify the index type.

• Specify relational index options.

• Specify columns.

• Specify a partition scheme when creating an index.

• Disable an index.

• Create an online index by using an ONLINE argument.

Create user-defined types.

• Create a Transact-SQL user-defined type.

• Specify details of the data type.

• Create a CLR user-defined type.

Implement a full-text search.

• Create a catalog.

• Create an index.

• Specify a full-text population method.