Create data types and tables.
• Creating Data Types.
• Creating Tables.
• Creating Partitioned Tables.

Use XML-related features in Microsoft SQL Server 2005.
• Retrieving XML by Using FOR XML.
• Shredding XML by Using OPENXML.
• Using the xml Data Type.
• Mapping Relational Data and XML.
• Storing XML Natively in the Database.

Plan, create, and optimize indexes.
• Planning Indexes.
• Creating Indexes.
• Optimizing Indexes.
• Creating XML Indexes.

Implement data integrity in Microsoft SQL Server 2005 databases by using constraints, triggers, and XML schemas.
• Creating Constraints.
• Creating Triggers.
• Implementing XML Schemas.

Implement views.
• Creating Views.
• Creating Indexed Views.
• Creating Partitioned Views.

Implement stored procedures and functions.
• Creating Stored Procedures.
• Creating Functions.

Implement managed code in the database.
• Importing an Assembly.
• Creating Managed Database Objects.

Use Service Broker to build a messaging-based solution.
• Creating Service Broker Objects.
• Implementing the Initiating Service.
• Implementing the Target Service.

Install and configure SQL Server 2005.
• Preparing to install SQL Server.
• Installing SQL Server.
• Managing a SQL Server 2005 Installation.

Manage database files.
• Planning databases.
• Creating databases.
• Managing databases.

Manage security.
• Overview of SQL Server Security.
• Securing the Server Scope.
• Securing the Database Scope.
• Managing Keys and Certificates in SQL Server.
• Creating Logins.
• Creating and Managing Users.

Perform administrative tasks.
• Configuring the SQL Server Agent.
• Creating Operators and Jobs.
• Creating Alerts.

Automating Administrative Tasks
• Automating Administrative Tasks in SQL Server 2005.
• Configuring the SQL Server Agent.
• Creating Jobs and Operators.
• Creating Alerts.
• Managing Multiple Servers.
• Managing SQL Server Agent Security

Configuring Database Mirroring
• Setting the Recovery Model.
• Backing Up and Restoring the Database.
• Starting Database Mirroring.
• Performing an Automatic and Manual Failover.

Database Backup Recovery
• Back up databases.
• Restore databases.

Monitor SQL Server.
• Viewing Current Activity.
• Using System Monitor.
• Using SQL Server Profiler.
• Using DDL Triggers.
• Using Event Notifications.

Exam 70–443: PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005

Analyzing Capacity Needs
• Estimate disk storage requirements.
• Estimate CPU requirements.
• Estimate memory requirements.
• Estimate network bandwidth requirements.
• Gathering Requirements that Impact or Affect Capacity Needs
• Estimating Capacity Needs

Designing a Strategy for Data Archiving
• Creating a Data Archival Plan
• Designing an Archiving Solution
• Defending Your Archiving Solution
• Determine the structure of archival data.
• Create a data archival plan.

Designing a Strategy for Database Server Consolidation
• Designing a Database Server Consolidation Strategy
• Designing a Consolidation Strategy
• Defending Your Consolidation Strategy

Designing a Strategy for Data Distribution
• Overview of Data Distribution
• Creating a Data Distribution Plan Using Replication
• Designing a Data Distribution Strategy Using Replication
• Designing a Data Distribution Strategy
• Defending Your Data Distribution Strategy

Designing a Database Server Infrastructure
• Designing a Database Server Infrastructure
• Choosing a Database Server Hardware and Software
• Defending Your Database Server Hardware and Software Choices
• Evaluate the current database server infrastructure.
• Gather requirements for changing a database server infrastructure.
• Design modifications to a database server infrastructure.

Designing a Strategy for Data Recovery
Designing a Data Recovery Solution
• Devising a Recovery Strategy
• Sharing Lessons Learned from Disaster Recovery
• Create a backup and restore strategy.
• Create a database disaster recovery plan.

Establishing Database Conventions and Standards
• Establishing Database Conventions and Standards
• Proposing Improved Object Naming Conventions
• Proposing Improved Coding Standards
• Create database naming conventions.
• Define database infrastructure standards

Introduction to Designing SQL Server Security
• Explain the principles of SQL Server security.
• Describe the methodology to design a SQL Server security policy.
• Explain the importance of monitoring SQL Server security.

Designing a SQL Server Systems Infrastructure Security Policy
• Designing a SQL Server Systems Infrastructure Security Policy
• Developing Microsoft Windows Server-Level Security Policies
• Developing a Secure Communication Policy
• Integrating SQL Server Security Within the Active Directory Environment
• Integrating SQL Server Security With Firewall Configurations
• Discussing Systems Infrastructure Security Integration

Creating an Infrastructure Security Inventory
• Auditing the SQL Server Logins
• Auditing the Windows Local Password Policy
• Auditing SQL Server Service Accounts
• Monitoring Security at the Enterprise and Server Levels

Designing Security Policies for Instances and Databases
• Designing an Instance-Level Security Policy
• Designing a Database-Level Security Policy
• Designing an Object-Level Security Policy
• Defining Security Monitoring Standards for Instances and Databases

Validating Security Policies for Instances and Databases
• Auditing Existing Server Logins
• Auditing SQL Server Roles Membership
• Analyzing Existing Object Permissions
• Monitoring Security at the Instance and Database Level

Integrating Data Encryption into a Database Security Design
• Secure data by using encryption and certificates.
• Design data encryption policies.
• Determine a key storage method.

Designing a Security Exceptions Policy
• Analyze business and regulatory requirements.
• Determine the exceptions and their impact on security.

Designing a Response Strategy for Threats and Attacks
• Designing a Response Policy for Virus and Worm Attacks
• Designing a Response Policy for Denial-of-Service Attacks
• Designing a Response Policy for Internal and SQL Injection Attacks
• Design a response policy for virus and worm attacks.
• Design a response policy to handle the denial-of-service attacks.
• Design a response policy to prevent internal and SQL injection attacks.

Selecting the Appropriate High-availability Solution
• Identify high-availability requirements and constraints.
• Analyze high-availability requirements.
• Determine appropriate high-availability solutions.

Designing a Clustering Solution
• Designing the Platform for Clustering
• Designing the SQL Server Cluster Implementation
• Designing Recovery Strategies and Test Plans for Database Clustering
• Migrating and Upgrading SQL Server Clusters
• Designing an Operations Plan for Clustering

Designing a Highly Available Database Storage Solution
• Determining Storage Requirements for SQL Server Databases
• Determining Storage Requirements for SQL Server Components and Files
• Designing Storage Solutions for SQL Server Databases
• Designing a Backup and Restore Strategy

Designing a Highly Available Database Storage Solution
• Determining Storage Needs
• Selecting the Storage Solution
• Designing a RAID Solution
• Designing a SAN Solution
• Designing a Backup and Restore Strategy

Designing a Log Shipping Solution
• Introduction to Designing a Log Shipping Solution
• Designing Log Shipping Server Roles and Topology
• Designing a Log Shipping Upgrade Strategy
• Designing an Operations Plan for Log Shipping

Designing a Database Mirroring Solution
• Introduction to Designing a Database Mirroring Solution
• Designing Database Roles and Topology for Database Mirroring
• Converting High-availability Solutions to Database Mirroring
• Designing an Operations Plan for Database Mirroring

Designing a Highly Available Solution Based on Replication
• Introduction to Designing a Replication Solution
• Designing a Replication Solution
• Designing a Replication Upgrade Strategy
• Designing an Operations Plan for Replication

Combining High-availability Technologies
• Evaluating Weaknesses in Each High-availability Technology
• Maximizing Availability by Combining High-availability Technologies

Documenting and Testing a High-availability Strategy
• Documenting High-availability Solutions
• Creating a Test Plan for High-availability Solutions

Exam 70–444: PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005

Managing and Automating Databases and Servers
• Creating Maintenance Plans.
• Creating Database Change Control Procedures.
• Configuring And Using the SQL H2 Tool.
• Plan an automated system for database maintenance.
• Plan an automated system for server maintenance.
• Document administration and automation information in a run book.

Managing SQL Server Supporting Services
• Managing SSIS Packages.
• Managing Replication.
• Managing Reporting Services.
• Moving Packages Between Database Servers.
• Initializing a Transactional Subscription From a Backup.
• Configuring Reporting Services.

Building a Monitoring Solution for SQL Server Performance Issues
• Determining Which Indicators to Monitor
• Implementing a Monitoring Solution
• Auditing Monitoring Results to Identify Problem Areas

Troubleshooting Database and Database Server Performance Issues
• Narrowing Down a Performance Issue to a Database Object
• How Profiler Can Help Narrow a Search to a Specific Issue
• How the SQLdiag Tool Can Be Used to Analyze Outputs
• Analyzing Sysmon and Profiler Traces
• Analyzing a Profiler Trace by Using SQL Server Queries
• Determining Database Server Issues by Using SQLdiag.exe

Optimizing the Query Performance Environment
• Explain the methodology of optimizing the query environment.
• Describe the query performance troubleshooting process.
• Explain how Database Tuning Advisor can be used to troubleshoot the query environment.
• Review an execution plan for clues to poor performance.
• Perform index analysis by using DTA.

Troubleshooting SQL Server Connectivity Issues
• Explain the methodology of troubleshooting connectivity issues.
• Describe the areas to audit for common connectivity issues.
• Explain the SQL Server 2005 endpoints.
• Troubleshoot Server-Not-Found issues.
• Troubleshoot an authentication error message.
• Troubleshoot endpoint issues.

Troubleshooting SQL Server Data Issues
• Troubleshooting and Repairing Torn Pages
• Troubleshooting a Data Issue

Troubleshooting SQL Server Data Concurrency Issues
• Identifying the Objects Involved in a Deadlock
• Identifying the Objects Involved in a Blocking Issue
• Determining Concurrency Issues by Using Latch Wait Type