Contenido del examen

Installing 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.
*Create 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.
*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

Pegado de <http://www.microsoft.com/learning/en/us/exams/70-431.aspx>