Objective Maps
The following three tables
list all of the objectives for each of the three tests in the MCITP: Database
Administrator certification (70-431, 70-443 and 70-444). Additionally, each
objective is mapped to the specific chapter and section (or sections) where
that topic is covered. When preparing for any of the tests, you can use these
objective maps to remind you of the topics you need to know and specifically
where to look to fill in any gaps in your knowledge.
Exam 70-431 Objective Map
Installing and Configuring SQL Server 2005
|
|
Install SQL Server 2005
|
Chapter 1, Installing SQL Server 2005
|
Verify prerequisites
|
Chapter 1, Installing SQL Server 2005
|
Upgrade from an earlier version of SQL Server
|
Chapter 1, Installing SQL Server 2005
|
Create an instance
|
Chapter 1, Installing SQL Server 2005
|
Configure SQL Server 2005 instances and databases
|
Chapter 1, Configuring SQL Server 2005
|
Configure log files and data files
|
Chapter 2, Creating a Database
Chapter 7, Filegroups
|
Configure the SQL Server Database Mail subsystem for an
instance
|
Chapter 13, SQL Server Agent,
|
Choose a recovery model for the database
|
Chapter 11, Recovery Models
|
Configure SQL Server security
|
Chapter 8, Chapter 9
|
Configure server security principals
|
Chapter 8, Security Principals
|
Configure database securables
|
Chapter 8, Database Securables
|
Configure encryption
|
Chapter 8, Encryption
|
Configure linked servers by using SQL Server Management
Studio (SSMS)
|
Chapter 6, Linked Servers
|
Identify the external data source
|
Chapter 6, Linked Servers
|
Identify the characteristics of the data source
|
Chapter 6, Linked Servers
|
Identify the security model of the data source
|
Chapter 6, Linked Servers
|
Implementing High Availability and Disaster Recovery |
|
Implement database mirroring
|
Chapter 10, Database Mirroring
|
Prepare databases for database mirroring
|
Chapter 10, Database Mirroring
|
Create endpoints
|
Chapter 10, Database Mirroring
|
Specify database partners
|
Chapter 10, Database Mirroring
|
Specify a witness server
|
Chapter 10, Database Mirroring
|
Configure an operating mode
|
Chapter 10, Database Mirroring
|
Implement log shipping
|
Chapter 10, Log Shipping
|
Initialize a secondary database
|
Chapter 10, Log Shipping
|
Configure log shipping options
|
Chapter 10, Log Shipping
|
Configure a log shipping mode
|
Chapter 10, Log Shipping
|
Configure monitoring
|
Chapter 10, Log Shipping
|
Manage database snapshots
|
Chapter 10, Database Snapshots
|
Create a snapshot
|
Chapter 10, Database Snapshots
|
Revert a database from a snapshot
|
Chapter 10, Database Snapshots
|
Supporting Data Consumers
Retrieve data to support ad hoc and recurring queries
|
Chapter 4, SELECT
|
Construct SQL queries to return data
|
Chapter 4, SELECT
|
Format the results of SQL queries
|
Chapter 4, SELECT
|
Identify collation details
|
Chapter 4, SELECT
|
Manipulate relational data
|
Chapter 4, SELECT; Other DML Statements
|
Insert, update, and delete data
|
Chapter 4, SELECT; Other DML Statements
|
Handle exceptions and errors
|
Chapter 4, Transactions
|
Manage transactions
|
Chapter 4, Transactions
|
Manage XML data
|
Chapter 4, XML
|
Identify the specific structure needed by a consumer
|
Chapter 4, XML
|
Retrieve XML data
|
Chapter 4, XML
|
Modify XML data
|
Chapter 4, XML
|
Convert between XML data and relational data
|
Chapter 4, XML
|
Create an XML index
|
Chapter 4, XML
|
Load an XML schema
|
Chapter 4, XML
|
Implement an HTTP endpoint
|
Chapter 12, Web Services and HTTP Endpoints
|
Create an HTTP endpoint
|
Chapter 12, Web Services and HTTP Endpoints
|
Secure an HTTP endpoint
|
Chapter 12, Web Services and HTTP Endpoints
|
Implement Service Broker components
|
Chapter 12, Service Broker
|
Create services
|
Chapter 12, Service Broker
|
Create queues
|
Chapter 12, Service Broker
|
Create contracts
|
Chapter 12, Service Broker
|
Create conversations
|
Chapter 12, Service Broker
|
Create message types
|
Chapter 12, Service Broker
|
Send messages to a service
|
Chapter 12, Service Broker
|
Route a message to a service
|
Chapter 12, Service Broker
|
Receive messages from a service
|
Chapter 12, Service Broker
|
Import and export data from a file
|
Chapter 4, XML
|
Chapter 11, Recovery Models
|
|
Chapter 12, Bulk Imports and Exports
|
|
Set a database to the bulk-logged recovery model to avoid
inflating the transaction log
|
Chapter 11, Recovery Models
|
Run the bcp utility
|
Chapter 12, Bulk Imports and Exports
|
Perform a Bulk Insert task
|
Chapter 12, Bulk Imports and Exports
|
Import bulk XML data by using the OPENROWSET function
|
Chapter 4, XML
|
Copy data from one table to another by using the SQL
Server 2005 Integration Services (SSIS) Import and Export Wizard
|
Chapter 12, Bulk Imports and Exports
|
Manage replication
|
Chapter 10, Replication
|
Distinguish between replication types
|
Chapter 10, Replication
|
Configure a publisher, a distributor, and a subscriber
|
Chapter 10, Replication
|
Configure replication security
|
Chapter 10, Replication
|
Configure conflict resolution settings for merge
replication
|
Chapter 10, Replication
|
Monitor replication
|
Chapter 10, Replication
|
Improve replication performance
|
Chapter 10, Replication
|
Plan for, stop, and restart recovery procedures
|
Chapter 10, Replication
|
Maintaining Databases |
|
Implement and maintain SQL Server Agent jobs
|
Chapter 13, SQL Server Agent
|
Set a job owner
|
Chapter 13, SQL Server Agent
|
Create a job schedule
|
Chapter 13, SQL Server Agent
|
Create job steps
|
Chapter 13, SQL Server Agent
|
Configure job steps
|
Chapter 13, SQL Server Agent
|
Disable a job
|
Chapter 13, SQL Server Agent
|
Create a maintenance job
|
Chapter 13, Maintenance Plans
|
Set up alerts
|
Chapter 13, SQL Server Agent
|
Configure operators
|
Chapter 13, SQL Server Agent
|
Modify a job
|
Chapter 13, SQL Server Agent
|
Delete a job
|
Chapter 13, SQL Server Agent
|
Manage a job
|
Chapter 13, SQL Server Agent
|
Manage databases by using Transact-SQL
|
Chapter 2, Creating a Database
|
Chapter 7, Index Maintenance, Statistics
|
|
Manage index fragmentation
|
Chapter 7, Index Maintenance
|
Manage statistics
|
Chapter 7, Statistics
|
Shrink files
|
Chapter 2, Creating a Database
|
Perform database integrity checks by using DBCC CHECKDB
|
Chapter 7
|
Back up a database
|
Chapter 11, Database Backups
|
Perform a full backup
|
Chapter 11, Database Backups
|
Perform a differential backup
|
Chapter 11, Database Backups
|
Perform a transaction log backup
|
Chapter 11, Database Backups
|
Initialize a media set by using the FORMAT option
|
Chapter 11, Database Backups
|
Append or overwrite an existing media set
|
Chapter 11, Database Backups
|
Create a backup device
|
Chapter 11, Database Backups
|
Back up filegroups
|
Chapter 11, Database Backups; Restores
|
Restore a database
|
Chapter 11, Restores
|
Identify which files are needed from the backup strategy
|
Chapter 11, Restores
|
Restore a database from a single file and from multiple
files
|
Chapter 11, Restores
|
Choose an appropriate restore method
|
Chapter 11, Restores
|
Move a database between servers
|
Chapter 11, Detach and Attach
|
Chapter 11, Database Backups; Restores
|
|
Choose an appropriate method for moving a database
|
Chapter 11, Detach and Attach
Chapter 11, Database Backups; Restores
|
Monitoring and Troubleshooting SQL Server Performance |
|
Gather performance and optimization data by using the SQL
Server Profiler
|
Chapter 13, SQL Server Profiler
|
Start a new trace
|
Chapter 13, SQL Server Profiler
|
Save the trace logs
|
Chapter 13, SQL Server Profiler
|
Configure SQL Server Profiler trace properties
|
Chapter 13, SQL Server Profiler
|
Configure a System Monitor counter log
|
Chapter 13, SQL Server Profiler
|
Correlate an SQL Server Profiler trace with System
Monitor log data
|
Chapter 13, SQL Server Profiler
|
Gather performance and optimization data by using the
Database Engine Tuning Advisor
|
Chapter 7, Database Engine Tuning Advisor (DTA)
|
Build a workload file by using the SQL Server Profiler
|
Chapter 13, SQL Server Profiler
|
Tune a workload file by using the Database Engine Tuning
Advisor
|
Chapter 7, Database Engine Tuning Advisor (DTA)
|
Save recommended indexes
|
Chapter 7, Database Engine Tuning Advisor (DTA)
|
Monitor and resolve blocks and deadlocks
|
Chapter 15, Locks and Deadlocks
|
Identify the cause of a block by using the
sys.dm_exec_requests system view
|
Chapter 15, Locks and Deadlocks
|
Terminate an errant process
|
Chapter 15, Locks and Deadlocks
|
Configure SQL Server Profiler trace properties
|
Chapter 13, SQL Server Profiler
Chapter 15, Locks and Deadlocks
|
Identify transaction blocks
|
Chapter 15, Locks and Deadlocks
|
Diagnose and resolve database server errors
|
Chapter 1, Troubleshooting the Installation or Operation
|
Connect to a nonresponsive server by using the dedicated
administrator connection (DAC)
|
Chapter 1, Connecting to SQL Server 2005
|
Review SQL Server startup logs
|
Chapter 1, Troubleshooting the Installation or Operation
|
Review error messages in event logs
|
Chapter 1, Troubleshooting the Installation or Operation
|
Monitor SQL Server Agent job history
|
Chapter 13, SQL Server Agent
|
Identify the cause of a failure
|
Chapter 13, SQL Server Agent
|
Identify outcome details
|
Chapter 13, SQL Server Agent
|
Find out when a job last ran
|
Chapter 13, SQL Server Agent
|
Gather performance and optimization data by using DMVs
|
Chapter 7, Index Maintenance
|
Chapter 13, Dynamic Management Views and Functions
|
|
Chapter 15, Locks and Deadlocks
|
|
Creating and Implementing Database Objects |
|
Implement a table
|
Chapter 2, Tables
|
Specify column details
|
Chapter 2, Tables
|
Specify the filegroup
|
Chapter 3, Partitioning Tables
Chapter 7, Filegroups
|
Assign permissions to a role for tables
|
Chapter 8, Security Principals; Database Securables
|
Specify a partition scheme when creating a table
|
Chapter 3, Partitioning Tables
|
Specify a transaction
|
Chapter 4, Transactions
|
Implement a view
|
Chapter 2, Views
|
Create an indexed view
|
Chapter 2, Views
Chapter 7, Index Design
|
Create an updateable view
|
Chapter 2, Views
Chapter 5, Triggers
|
Assign permissions to a role or schema for a view
|
Chapter 8, Security Principals; Database Securables
|
Implement triggers
|
Chapter 5, Triggers
|
Create a trigger
|
Chapter 5, Triggers
|
Create DDL triggers for responding to database structure
changes
|
Chapter 5, Triggers
|
Identify recursive triggers
|
Chapter 5, Triggers
|
Identify nested triggers
|
Chapter 5, Triggers
|
Identify transaction triggers
|
Chapter 5, Triggers
|
Implement functions
|
Chapter 5, Functions
|
Create a function
|
Chapter 5, Functions
|
Identify deterministic versus nondeterministic functions
|
Chapter 5, Functions
|
Implement stored procedures
|
Chapter 5, Stored Procedures
|
Create a stored procedure
|
Chapter 5, Stored Procedures
|
Recompile a stored procedure
|
Chapter 5, Stored Procedures
|
Assign permissions to a role for a stored procedure
|
Chapter 8, Security Principals; Database Securables
|
Implement constraints
|
Chapter 3, Constraints
|
Specify the scope of a constraint
|
Chapter 3, Constraints
|
Create a new constraint
|
Chapter 3, Constraints
|
Implement indexes
|
Chapter 7, Indexes; Index Design
|
Specify the filegroup
|
Chapter 7, Index Design
|
Specify the index type
|
Chapter 7, Indexes; Index Design
|
Specify relational index options
|
Chapter 7, Index Design
|
Specify columns
|
Chapter 7, Index Design
|
Specify a partition scheme when creating an index
|
Chapter 7, Index Design
|
Disable an index
|
Chapter 7, Index Design
|
Create an online index by using an ONLINE argument
|
Chapter 7, Index Design; Database Engine Tuning Advisor (DTA)
|
Create user-defined types
|
Chapter 2, Tables
|
Create a Transact-SQL user-defined type
|
Chapter 2, Tables
|
Specify details of the data type
|
Chapter 2, Tables
|
Create a CLR user-defined type
|
Chapter 2, Tables
|
Implement a full-text search
|
Chapter 7, Full-Text Index
|
Create a catalog
|
Chapter 7, Full-Text Index
|
Create an index
|
Chapter 7, Full-Text Index
|
Specify a full-text population method
|
Chapter 7, Full-Text Index
|
Implement partitions
|
Chapter 3, Partitioning Tables
|
Exam 70-433 Objective Map
Designing a Database Server Infrastructure
|
|
Design for capacity requirements
|
Chapter 6, Server Resources; System Monitor; Server Design Considerations
|
Analyze storage requirements
|
Chapter 6, Server Resources; System Monitor; Server Design Considerations
|
Analyze network requirements
|
Chapter 6, Server Resources; System Monitor; Server Design Considerations
|
Analyze CPU requirements
|
Chapter 6, Server Resources; System Monitor; Server Design Considerations
|
Analyze the current configuration
|
Chapter 6, Server Resources; System Monitor; Server Design Considerations
|
Analyze memory requirements
|
Chapter 6, Server Resources; System Monitor; Server Design Considerations
|
Forecast and incorporate anticipated growth requirements
into the capacity requirements
|
Chapter 6, Server Resources; System Monitor; Server Design Considerations
|
Specify software versions and hardware configurations
|
Chapter 1, SQL Server Editions; Installing SQL Server 2005
|
Choose a version and edition of the operating system
|
Chapter 1, SQL Server Editions; Installing SQL Server 2005
|
Choose a version of SQL Server 2005
|
Chapter 1, SQL Server Editions
|
Choose a CPU type
|
Chapter 1, SQL Server Editions; Installing SQL Server 2005
Chapter 6, Server Resources; Server Design Considerations
|
Choose memory options
|
Chapter 1, SQL Server Editions; Installing SQL Server 2005
Chapter 6, Server Resources; Server Design Considerations
|
Choose a type of storage
|
Chapter 1, SQL Server Editions; Installing SQL Server 2005
Chapter 6, Server Resources; Server Design Considerations
|
Design physical storage
|
Chapter 6, Server Resources; Server Design Considerations
|
Design transaction log storage
|
Chapter 6, Server Resources; Server Design Considerations
|
Design backup file storage
|
Chapter 6, Server Resources; Server Design Considerations
|
Decide where to install the operating system
|
Chapter 1, Installing SQL Server 2005
|
Decide where to place SQL Server service executables
|
Chapter 1, SQL Server Editions; Installing SQL Server 2005
|
Specify the number and placement of files to create for
each database
|
Chapter 7, Filegroups
|
Design instances
|
Chapter 1, SQL Server Editions; Installing SQL Server 2005
|
Decide how many databases to create
|
Chapter 1, SQL Server Editions
Chapter 2, Creating a Database
|
Decide on the placement of system databases for each
instance
|
Chapter 1, SQL Server Editions
Chapter 7, Filegroups
|
Decide on the physical storage for the tempdb database
for each instance
|
Chapter 7, Index Design
|
Decide on the number of instances
|
Chapter 1, SQL Server Editions
|
Decide on the naming of instances
|
Chapter 1, SQL Server Editions
|
Decide how many physical servers are needed for instances
|
Chapter 1, SQL Server Editions
|
Establish service requirements
|
Chapter 1, Configuring SQL Server 2005
|
Specify instance configurations
|
Chapter 1, Configuring SQL Server 2005
|
Design a database consolidation strategy
|
Chapter 9, Server Security Basics; Security Strategy
|
Gather information to analyze the dispersed environment
|
Chapter 1, SQL Server Editions
|
Identify potential consolidation problems
|
Chapter 1, SQL Server Editions
Chapter 9, Server Security Basics; Security Strategy
|
Create a specification to consolidate SQL Server
databases
|
Chapter 1, SQL Server Editions
Chapter 9, Server Security Basics; Security Strategy
|
Design a database migration plan for the consolidated
environment
|
Chapter 2, Database Control
|
Test existing applications against the consolidated
environment
|
Chapter 2, Database Control
|
Designing Security for a Database Server Solution |
|
Analyze business requirements
|
Chapter 9, Server Security Basics; Security Strategy
|
Gather business and regulatory requirements
|
Chapter 9, Server Security Basics; Security Strategy
|
Decide how requirements will impact choices at various
security levels
|
Chapter 9, Server Security Basics; Security Strategy
|
Evaluate costs and benefits of security choices
|
Chapter 9, Server Security Basics; Security Strategy
|
Decide on appropriate security recommendations
|
Chapter 9, Server Security Basics; Security Strategy
|
Inform business decision-makers about security
recommendations and their impact
|
Chapter 9, Server Security Basics; Security Strategy
|
Incorporate feedback from business decision-makers into a
design
|
Chapter 9, Server Security Basics; Security Strategy
|
Integrate database security with enterprise-level
authentication systems
|
Chapter 9, Server Security Basics; Working in a Domain; Network Security; Security Strategy
|
Decide which authentication system to use
|
Chapter 9, Server Security Basics
|
Design Active Directory organizational units (OUs) to implement
server-level security policies
|
Chapter 9, Working in a Domain
|
Ascertain the impact of authentication on a
high-availability solution
|
Chapter 9, Server Security Basics
|
Establish the consumption of enterprise authentication
|
Chapter 9, Server Security Basics; Working in a Domain
|
Ascertain the impact of enterprise authentication on
service uptime requirements
|
Chapter 9, Server Security Basics; Working in a Domain; Network Security; Security Strategy
|
Develop Microsoft Windows server-level security policies
|
Chapter 9, Server Security Basics; Security Strategy
|
Develop a password policy
|
Chapter 9, Server Security Basics
|
Develop an encryption policy
|
Chapter 8, Encryption
|
Specify server accounts and server account rights
|
Chapter 9, Server Security Basics
|
Specify the interaction of the database server with
antivirus software
|
Chapter 9, Security Strategy
|
Specify the set of running services and disable unused
services
|
Chapter 1, Configuring SQL Server 2005
Chapter 9, Security Strategy
|
Specify the interaction of the database server with
server-level firewalls
|
Chapter 9, Network Security
|
Specify a physically secure environment for the database
server
|
Chapter 9, Security Strategy
|
Modify the security design based on the impact of network
security policies
|
Chapter 9, Network Security; Working in a Domain
|
Analyze the risk of attacks to the server environment and
specify mitigations
|
Chapter 9, Network Security; Security Strategy
|
Design SQL Server service-level security
|
Chapter 1, Configuring SQL Server 2005
|
Chapter 9, Security Strategy
|
|
Specify logins
|
Chapter 8, Security Principals
|
Select SQL Server server roles for logins
|
Chapter 8, Security Principals
|
Specify an SQL Server service authentication mode
|
Chapter 9, Server Security Basics
|
Design a secure HTTP endpoint strategy
|
Chapter 12, Web Services and HTTP Endpoints
|
Design a secure job role strategy for the SQL Server
Agent service
|
Chapter 1, Configuring SQL Server 2005
Chapter 13, SQL Server Agent
|
Specify a policy for .NET assemblies
|
Chapter 9, Security Strategy
|
Design database-level security
|
Chapter 8, Security Principals; Database Securables
|
Specify database users
|
Chapter 8, Security Principals
|
Design schema containers for database objects
|
Chapter 8, Security Principals
|
Specify database roles
|
Chapter 8, Security Principals
|
Define encryption policies
|
Chapter 8, Encryption
|
Design DDL triggers
|
Chapter 5, Triggers
|
Design object-level security
|
Chapter 8, Database Securables
|
Design a permissions strategy
|
Chapter 8, Database Securables
|
Analyze existing permissions
|
Chapter 8, Database Securables
|
Design an execution context
|
Chapter 8, Database Securables
|
Design column-level encryption
|
Chapter 8, Encryption
|
Design security for CLR objects in the database
|
Chapter 8, Database Securables
|
Designing a Physical Database |
|
Modify an existing database design based on performance
and business requirements
|
Chapter 2, Creating a Database
Chapter 3, Normalization and Normal Forms
|
Ensure that a database is normalized
|
Chapter 3, Normalization and Normal Forms
|
Allow selected denormalization for performance purposes
|
Chapter 3, Normalization and Normal Forms
|
Ensure that the database is documented and diagrammed
|
Chapter 2, Database Control
|
Design tables
|
Chapter 2, Tables
|
Decide if partitioning is appropriate
|
Chapter 3, Partitioning Tables
|
Specify primary and foreign keys
|
Chapter 3, Data Integrity
|
Specify column data types and constraints
|
Chapter 2, Tables
Chapter 3, Constraints
|
Decide whether to persist computed columns
|
Chapter 2, Tables
|
Specify physical location of tables, including filegroups
and a partitioning scheme
|
Chapter 3, Partitioning Tables
Chapter 7, Filegroups
|
Design filegroups
|
|
Design filegroups for performance
|
Chapter 3, Partitioning Tables
Chapter 7, Filegroups
|
Design filegroups for recoverability
|
|
Design filegroups for partitioning
|
Chapter 7, Filegroups
Chapter 11, Restores
|
Design index usage
|
Chapter 7, Indexes, Index Design
|
Design indexes for faster data access
|
Chapter 7, Indexes; Index Design
|
Design indexes to improve data modification
|
Chapter 7, Indexes; Index Design
|
Specify physical placement of indexes
|
Chapter 7, Indexes; Index Design; Filegroups
|
Design views
|
Chapter 2, Views
|
Analyze business requirements
|
Chapter 2, Views
|
Choose the type of view
|
Chapter 2, Views
Chapter 7, Index Design
|
Specify row and column filtering
|
Chapter 2, Views
|
Create database conventions and standards
|
Chapter 2, Data Quality; Database Control
|
Define database object-naming conventions
|
Chapter 2, Data Quality; Database Control
|
Define consistent synonyms
|
Chapter 2, Data Quality; Database Control
|
Define database coding standards
|
Chapter 2, Data Quality; Database Control
|
Document database conventions and standards
|
Chapter 2, Data Quality; Database Control
|
Create database change control procedures
|
Chapter 2, Database Control
|
Establish where to store database source code
|
Chapter 2, Database Control
|
Isolate development and test environments from the
production environment
|
Chapter 2, Database Control
|
Define procedures for moving from development to test
|
Chapter 2, Database Control
|
Define procedures for promoting from test to production
|
Chapter 2, Database Control
|
Define procedures for rolling back a deployment
|
Chapter 2, Database Control
|
Document the database change control procedures
|
Chapter 2, Database Control
|
Designing a Database Solution for High Availability |
|
Select high-availability technologies based on business
requirements
|
Chapter 10, High-Availability Comparisons
|
Analyze availability requirements
|
Chapter 10, High-Availability Comparisons
|
Analyze potential availability barriers
|
Chapter 10, High-Availability Comparisons
|
Analyze environmental issues
|
Chapter 10, High-Availability Comparisons
|
Analyze potential problems related to processes and staff
|
Chapter 10, High-Availability Comparisons
|
Identify potential single points of failure
|
Chapter 10, High-Availability Comparisons
|
Decide how quickly the database solution must failover
|
Chapter 10, High-Availability Comparisons; Clustering; Database Mirroring; Log Shipping
|
Choose automatic or manual failover
|
Chapter 10, High-Availability Comparisons; Clustering; Database Mirroring; Log Shipping
|
Analyze costs versus benefits of various solutions
|
Chapter 10, High-Availability Comparisons
|
Combine high-availability technologies to improve
availability
|
Chapter 10, High-Availability Comparisons; Database Mirroring; Database Snapshots; Log Shipping
|
Develop a strategy for migration to a highly available
environment
|
Chapter 10, all
Chapter 2, Database Control
|
Analyze the current environment
|
Chapter 10, High-Availability Comparisons
|
Ascertain migration options
|
Chapter 10, High-Availability Comparisons
Chapter 2, Database Control
|
Choose a migration option
|
Chapter 10, High-Availability Comparisons
Chapter 2, Database Control
|
Design a highly available database storage solution
|
Chapter 6, Server Resources
|
Design the RAID solutions for your environment
|
Chapter 6, Server Resources
|
Design a SAN solution
|
Chapter 6, Server Resources
|
Design a database-clustering solution
|
Chapter 10, Clustering
|
Design a Microsoft Cluster Service (MSCS) implementation
|
Chapter 10, Clustering
|
Design the cluster configuration of the SQL Server
service
|
Chapter 10, Clustering
|
Design database mirroring
|
Chapter 10, Database Mirroring
|
Design server roles for database mirroring
|
Chapter 10, Database Mirroring
|
Design the initialization of database mirroring
|
Chapter 10, Database Mirroring
|
Design a test strategy for planned and unplanned role
changes
|
Chapter 10, Database Mirroring
|
Design a high-availability solution that is based on
replication
|
Chapter 10, Replication
|
Specify an appropriate replication solution
|
Chapter 10, Replication
|
Choose servers for peer-to-peer replication
|
Chapter 10, Replication
|
Establish a strategy for resolving data conflicts
|
Chapter 10, Replication
|
Design an application failover strategy
|
Chapter 10, Replication
|
Design a strategy to reconnect client applications
|
Chapter 10, Replication
|
Design log shipping
|
Chapter 10, Log Shipping
|
Specify the primary server and secondary server
|
Chapter 10, Log Shipping
|
Switch server roles
|
Chapter 10, Log Shipping
|
Design an application failover strategy
|
Chapter 10, Log Shipping; Database Snapshots; Database Mirroring; Clustering; High-Availability Comparisons
|
Design a strategy to reconnect client applications
|
Chapter 10, Log Shipping; Database Snapshots; Database Mirroring; Clustering; High-Availability Comparisons
|
Designing a Data Recovery Solution for a Database |
|
Specify data recovery technologies based on business requirements
|
Chapter 11, Recovery Models; Database Backups; Restores; Protecting System Databases and Scripts
|
Analyze how much data the organization can afford to lose
|
Chapter 11, Recovery Models; Database Backups; Restores; Protecting System Databases and Scripts
|
Analyze alternative techniques to save redundant copies
of critical business data
|
Chapter 11, Recovery Models; Database Backups; Restores; Protecting System Databases and Scripts
|
Analyze how long the database system or database can be
unavailable
|
Chapter 11, Recovery Models; Restores
|
Design backup strategies
|
Chapter 11, Database Backups
|
Specify the number and location of devices to be used for
backup
|
Chapter 11, Database Backups
|
Specify what data to back up
|
Chapter 11, Database Backups
|
Specify the frequency of backup
|
Chapter 11, Database Backups
|
Choose a backup technique
|
Chapter 11, Database Backups
|
Specify the type of backup
|
Chapter 11, Database Backups
|
Choose a recovery model
|
Chapter 11, Recovery Models
|
Create a disaster recovery plan
|
Chapter 11, Recovery Models; Database Backups; Restores
|
Document the sequence of possible events
|
Chapter 11, Recovery Models; Database Backups; Restores
|
Create a disaster decision tree that includes restore
strategies
|
Chapter 11, Recovery Models; Database Backups; Restores
|
Establish recovery success criteria
|
Chapter 11, Recovery Models; Database Backups; Restores
|
Validate restore strategies
|
Chapter 11, Restores
|
Designing a Strategy for Data Archiving |
|
Select archiving techniques based on business
requirements
|
Chapter 11, Database Backups; Restores; Protecting System Databases and Scripts
|
Gather requirements that affect archiving
|
Chapter 11, Database Backups; Restores; Protecting System Databases and Scripts
|
Ascertain data movement requirements for archiving
|
Chapter 11, Database Backups; Restores; Protecting System Databases and Scripts
|
Design the format of archival data
|
Chapter 11, Database Backups; Restores;
|
Specify what data to archive
|
Chapter 11, Database Backups; Restores; Protecting System Databases and Scripts
|
Specify the level of granularity of an archive
|
Chapter 11, Database Backups; Restores
|
Specify how long to keep the archives
|
Chapter 11, Database Backups; Restores
|
Plan for data archival and access
|
Chapter 11, Database Backups; Restores
|
Specify the destination for archival data
|
Chapter 11, Database Backups; Restores
|
Specify the frequency of archiving
|
Chapter 11, Database Backups; Restores
|
Decide if replication is appropriate
|
Chapter 11, Database Backups; Restores
Chapter 10, Replication
|
Establish how to access archived data
|
Chapter 11, Database Backups; Restores
|
Design the topology of replication for archiving data
|
Chapter 10, Replication
|
Specify the publications and articles to be published
|
Chapter 10, Replication
|
Specify the distributor of the publication
|
Chapter 10, Replication
|
Specify the subscriber of the publication
|
Chapter 10, Replication
|
Design the type of replication for archiving data
|
Chapter 10, Replication
|
Exam 70-444 Objective Map
Optimizing the Performance of Database Servers and
Databases
|
|
Troubleshoot physical server performance
|
Chapter 6, System Monitor
|
Troubleshoot instance performance
|
Chapter 6, System Monitor
|
Troubleshoot database performance
|
Chapter 6, System Monitor
|
Chapter 13, SQL Server Profiler; Dynamic Management Views and Functions
|
|
Troubleshoot and maintain query performance
|
Chapter 4, Hints
Chapter 7, Index Design
|
Chapter 13, SQL Server Profiler; Dynamic Management Views and Functions
|
|
Identify poorly performing queries
|
Chapter 13, SQL Server Profiler
|
Analyze a query plan to detect inefficiencies in query
logic
|
Chapter 4, Hints
Chapter 7, Index Design
|
Maintain and optimize indexes
|
Chapter 7, Index Design
|
Enforce appropriate stored procedure logging and output
|
Chapter 5, Stored Procedures
Chapter 13, SQL Server Profiler; Dynamic Management Views and Functions
|
Troubleshoot concurrency issues
|
Chapter 15, Locks and Deadlocks; Transaction Isolation Levels
|
Optimizing and Implementing a Data Recovery Plan for a Database |
|
Diagnose causes of failures. Failure types include
database failures, physical server failures, and SQL Server service failures.
|
Chapter 1, Troubleshooting the Installation or Operation; Configuring SQL Server 2005
|
Chapter 6, System Monitor
|
|
Chapter 10, all
|
|
Chapter 11, Recovery Models; Database Backups; Restores
|
|
Plan for fault tolerance
|
Chapter 6, Server Resources
|
Chapter 10, all
|
|
Recover from a failure of SQL Server 2005
|
Chapter 1, Troubleshooting the Installation or Operation; Configuring SQL Server 2005
|
Chapter 10, all
|
|
Chapter 11, Recovery Models; Database Backups; Restores
|
|
Recover from a database disaster
|
Chapter 11, Recovery Models; Database Backups; Restores
|
Plan a strategy
|
Chapter 11, Recovery Models; Database Backups; Restores
|
Restore a database
|
Chapter 11, Recovery Models; Database Backups; Restores
|
Configure logins
|
Chapter 8, Security Principals
|
Recover lost data
|
Chapter 11, Recovery Models; Database Backups; Restores
|
Maintain server and database scripts for recoverability
|
Chapter 11, Protecting System Databases and Scripts
|
Salvage good data from a damaged database by using
restoration techniques
|
Chapter 10, Log Shipping; Database Snapshots; Database Mirroring; Replication; Clustering
|
Chapter 11, Recovery Models; Database Backups; Restores
|
|
Designing a Strategy to Monitor and Maintain a
Database Solution
|
|
Define and implement monitoring standards for a physical
server
|
Chapter 6, System Monitor
|
Establish the thresholds for performance
|
Chapter 6, System Monitor
|
Establish the baselines for performance
|
Chapter 6, System Monitor
|
Define which types of information to monitor on the
physical server
|
Chapter 6, System Monitor
|
Define traces
|
Chapter 13, SQL Server Profiler
|
Set alerts
|
Chapter 6, System Monitor
Chapter 13, SQL Server Agent
|
Set notifications
|
Chapter 6, System Monitor
Chapter 13, SQL Server Agent
|
Choose the appropriate information to monitor
|
Chapter 6, System Monitor
|
Chapter 13, SQL Server Profiler
|
|
Create and implement a maintenance strategy for database
servers
|
Chapter 13, Maintenance Plans
|
Create a job dependency diagram
|
Chapter 13, SQL Server Agent
|
Manage the maintenance of database servers
|
Chapter 13, SQL Server Agent; Maintenance Plans
|
Design a database maintenance plan
|
Chapter 13, Maintenance Plans
|
Design a strategy to manage Reporting Services
|
Chapter 14, SQL Server Reporting Services (SSRS)
|
Designing a Database Data Management Strategy |
|
Design and manage SQL Server Integration Services (SSIS)
packages
|
Chapter 14, SQL Server Integration Services (SSIS)
|
Construct complex SSIS packages
|
Chapter 14, SQL Server Integration Services (SSIS)
|
Design security for accessing packages
|
Chapter 14, SQL Server Integration Services (SSIS)
|
Restart failed packages
|
Chapter 14, SQL Server Integration Services (SSIS)
|
Troubleshoot or debug packages
|
Chapter 14, SQL Server Integration Services (SSIS)
|
Deploy and move packages
|
Chapter 14, SQL Server Integration Services (SSIS)
|
Schedule package execution
|
Chapter 14, SQL Server Integration Services (SSIS)
|
Move packages to different servers
|
Chapter 14, SQL Server Integration Services (SSIS)
|
Enforce data quality according to business requirements
|
Chapter 2, Data Quality
Chapter 3, Data Integrity; Constraints
|
Chapter 14, SQL Server Integration Services (SSIS)
|
|
Establish the business requirements for quality
|
Chapter 2, Data Quality
|
Create queries to inspect the data
|
Chapter 2, Data Quality
|
Use checksum
|
Chapter 11, Database Backups
|
Clean the data
|
Chapter 14, SQL Server Integration Services (SSIS)
|
Design data integrity
|
Chapter 2, Data Quality
|
Chapter 3, Data Integrity; Constraints
|
|
Chapter 14, SQL Server Integration Services (SSIS)
|
|
Reconcile data conflicts
|
Chapter 2, Data Quality
Chapter 3, Data Integrity; Constraints
Chapter 14, SQL Server Integration Services (SSIS)
|
Make implicit constraints explicit
|
Chapter 3, Constraints
|
Assign data types to control characteristics of data
stored in a column
|
Chapter 2, Tables; Data Quality
Chapter 3, Constraints
|
Design a strategy to manage data across linked servers
|
Chapter 6, Linked Servers
|
Set up and manage linked servers
|
Chapter 6, Linked Servers
|
Design a strategy to manage replication
|
Chapter 10, Replication
|
Design alerts
|
Chapter 6, System Monitor
Chapter 13, SQL Server Agent
|
Design a maintenance plan to monitor health, latency, and
failures
|
Chapter 10, Replication
Chapter 13, Maintenance Plans; SQL Server Agent
|
Verify replication
|
Chapter 10, Replication
|
Design a plan to resolve replication conflicts
|
Chapter 10, Replication
|
Design a plan to modify agent profiles
|
Chapter 10, Replication
Chapter 13, SQL Server Agent
|
Tune replication configuration
|
Chapter 10, Replication
|
Optimize a database control strategy to meet business
requirements
|
Chapter 2, Database Control
|
Verify that database change control procedures are being
followed
|
Chapter 2, Database Control
|
Identify all database objects related to a particular
deployment
|
Chapter 2, Chapter 3
Chapter 10, Replication
|
Designing a Strategy to Manage and Maintain Database Security |
|
Perform a security audit of the existing security
infrastructure based on the security plan
|
Chapter 8, Security Principals; Database Securables
|
Chapter 9, Server Security Basics; Security Strategy
|
|
Analyze the physical server security
|
Chapter 9, Security Strategy
|
Compare the existing security infrastructure to business
and regulatory requirements
|
Chapter 9, Working in a Domain; Security Strategy
|
Identify variations from the security design
|
Chapter 9, Working in a Domain; Security Strategy
|
Maintain a server-level security strategy
|
Chapter 9, Working in a Domain; Security Strategy
|
Design a strategy to audit Windows account permissions
|
Chapter 8, Database Securables; Security Principals
Chapter 9, Working in a Domain; Server Security Basics; Security Strategy
|
Design a strategy to audit SQL Server service access
|
Chapter 8, Database Securables; Security Principals
Chapter 9, Server Security Basics; Security Strategy
|
Maintain a strategy to assign the appropriate minimum
level of privileges
|
Chapter 8, Database Securables; Security Principals
Chapter 9, Server Security Basics; Security Strategy
|
Maintain an encryption strategy that meets business requirements
|
Chapter 8, Encryption
|
Design a strategy to apply service packs and security
updates
|
Chapter 6, Server Design Considerations
|
Configure the surface area
|
Chapter 1, Configuring SQL Server 2005
|
Maintain a user-level security strategy
|
Chapter 8, Security Principals
|
Chapter 9, Server Security Basics; Security Strategy; Working in a Domain
|
|
Verify the existence and enforcement of account policies
|
Chapter 9, Server Security Basics; Security Strategy; Working in a Domain
|
Verify SQL Server login authentication
|
Chapter 9, Server Security Basics
|
Verify permissions on SQL Server roles and accounts
|
Chapter 8, Security Principals
Chapter 9, Server Security Basics
|
Prepare for and respond to threats and attacks
|
Chapter 9, Network Security; Security Strategy
|
Prepare for and respond to SQL Server injection attacks
|
Chapter 5, Stored Procedures
|
Prepare for and respond to denial-of-service attacks that
are specific to SQL Server
|
Chapter 9, Network Security; Security Strategy
|
Prepare for and respond to virus and worm attacks that
are specific to SQL Server
|
Chapter 9, Network Security; Security Strategy
|
Prepare for and respond to internal attacks that are
specific to SQL Server
|
Chapter 5, Stored Procedures
Chapter 9, Working in a Domain; Network Security; Security Strategy
|
0 comments
Post a Comment