Exam 70-431: TS: Microsoft SQL Server 2005: Implementation and Maintenance Curriculum Outline
SQL Server 2005 High Availability and Disaster Recovery
Overview/DescriptionTo implement database mirroring, log shipping, database snapshots, and data replication in SQL Server 2005
Target Audience
Professionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
Prerequisites
This is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration
3 Hours
SQL Server 2005 High Availability and Disaster Recovery
- Recognize the features and functionality of database mirroring
- Configure database mirroring in a given scenario
- Configure database mirroring in SQL Server 2005
- Configure and monitor log shipping in a given scenario
- Configure log shipping options in SQL Server 2005
- Configure database snapshots in a given scenario
- Configure data replication in a given scenario
- Manage replication in SQL Server 2005, given a scenario
- Set up data replication in SQL Server 2005
Back to List
SQL Server 2005 Backup and Restores
Overview/DescriptionTo back up and restore a database in SQL Server 2005
Target Audience
Professionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
Prerequisites
This is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration
1.75 Hours
SQL Server 2005 Backup and Restores
- Determine the appropriate recovery model and backup strategy for a given scenario
- Perform database backups in a given scenario
- Back up a SQL Server database
- Restore a SQL Server backup in a given scenario
- Move a SQL Server database in a given scenario
- Perform a database restore in SQL Server 2005
Back to List
Installing SQL Server 2005
Overview/DescriptionTo install SQL Server 2005 and upgrade to SQL Server 2005 from previous versions
Target Audience
Professionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
Prerequisites
This is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration
3 Hours
Installing SQL Server 2005
- Recognize how SQL Server 2005 can help improve enterprise data management, developer productivity, and business intelligence
- Differentiate between the components and services provided by SQL Server 2005
- Distinguish between SQL Server 2005 management tools
- Identify the requirements and considerations for installing SQL Server 2005
- Configure a SQL Server 2005 installation in a given scenario
- Install SQL Server 2005
- Select the appropriate SQL Server 2005 upgrade path for a given scenario
- Perform the steps to upgrade to SQL Server 2005
- Upgrade to SQL Server 2005
Back to List
Creating and Implementing Database Objects in SQL Server 2005
Overview/DescriptionTo create and implement key database objects in SQL Server 2005
Target Audience
Professionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
Prerequisites
This is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration
3.25 Hours
Creating and Implementing Database Objects in SQL Server 2005
- Create a database table in SQL Server 2005, given a scenario
- Create a database view in a given scenario
- Create a database table and a database view
- Identify recommended guidelines for designing indexes
- Create a database index in a given scenario
- Create an online index in SQL Server 2005
- Create functions and triggers in a given scenario
- Create stored procedures in a given scenario
- Manage stored procedures in a given scenario
- Create and implement a stored procedure
Back to List
SQL Server 2005 Database Performance and Optimization
Overview/DescriptionTo monitor and optimize database performance using SQL Server utilities
Target Audience
Professionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
Prerequisites
This is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration
2.50 Hours
SQL Server 2005 Database Performance and Optimization
- Configure SQL Server Profiler Trace files in a given scenario
- Configure System Monitor to monitor SQL Server performance in a given scenario
- Use SQL Server Profiler and System Monitor to monitor an instance of SQL Server 2005
- Recognize the features and functionality of Database Engine Tuning Advisor
- Use Database Engine Tuning Advisor to tune a SQL Server database in a given scenario
- Tune a SQL Server database using Database Engine Tuning Advisor
- Retrieve SQL Server state information using dynamic management views (DMVs) in a given scenario
- Query dynamic management views and functions
Back to List
Configuring SQL Server 2005
Overview/DescriptionTo configure a SQL Server instance and create a database, configure logins, users, and schemas for a database, and set up a linked server configuration
Target Audience
Professionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
Prerequisites
This is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration
3 Hours
Configuring SQL Server 2005
- Use SQL Server Surface Area Configuration to configure a SQL Server 2005 installation
- Create a SQL Server database in a given scenario
- Create a database in SQL Server 2005
- Recognize the security functionality of the SQL Server 2005 Database Engine
- Create logins and grant permissions in a given scenario
- Create logins, users, and schemas in SQL Server 2005
- Recognize the security mechanisms provided by encryption, the TRUSTWORTHY database property, and execution context in SQL Server 2005
- Recognize how SQL Server 2005 can access data from OLE DB data sources on remote servers using a linked server configuration
- Use SQL Server Management Studio to configure a linked server in a given scenario
- Configure a linked server using SQL Server Management Studio
Back to List
Retrieving Data and Running Queries in SQL Server 2005
Overview/DescriptionTo perform relational data and XML data management tasks, and work with assemblies and transactions in SQL Server 2005
Target Audience
Professionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
Prerequisites
This is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration
3 Hours
Retrieving Data and Running Queries in SQL Server 2005
- Retrieve data from a database in a given scenario
- Modify data from a relational database in a given scenario
- Use queries to retrieve and modify data in a SQL Server database
- Retrieve, insert, and convert XML data in SQL Server 2005
- Create and secure an HTTP endpoint in a given scenario
- Work with XML data in SQL Server 2005
- Create and modify an assembly in a given scenario
- Control SQL Server transactions in a given scenario
- Manage transactions in SQL Server 2005
Back to List
SQL Server 2005 Messaging and Querying Tools
Overview/DescriptionTo implement Service Broker components and use full-text search in SQL Server 2005
Target Audience
Professionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
Prerequisites
This is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration
2 Hours
SQL Server 2005 Messaging and Querying Tools
- Create Service Broker components in a given scenario
- Create a Service Broker service
- Recognize the routing, networking, and security features provided by Security Broker
- Identify the features of Full-Text Searches and recognize considerations for implementing them
- Create and troubleshoot full-text queries in a given scenario
- Use full-text search to query SQL Server 2005
Back to List
Managing and Maintaining SQL Server 2005 Databases
Overview/DescriptionTo manage and maintain SQL Server 2005 databases
Target Audience
Professionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
Prerequisites
This is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration
3.25 Hours
Managing and Maintaining SQL Server 2005 Databases
- Export bulk data in SQL Server 2005 in a given scenario
- Import bulk data into SQL Server 2005 in a given scenario
- Import and export bulk data in SQL Server 2005
- Create and manage jobs using SQL Server Agent
- Configure alerts using SQL Server Agent in a given scenario
- Configure operators using SQL Server Agent in a given scenario
- Perform management and monitoring tasks using the SQL Server Agent service
- Perform database management tasks in SQL Server 2005, given a scenario
- Perform the steps required to shrink a database in a given scenario
- Check and maintain database integrity in SQL Server 2005
Back to List
Monitoring and Diagnosing Database Problems in SQL Server 2005
Overview/DescriptionTo monitor and resolve database blocks, and connect to the server using a dedicated administrator connection (DAC)
Target Audience
Professionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
Prerequisites
This is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration
1.25 Hours
Monitoring and Diagnosing Database Problems in SQL Server 2005
- Recognize how locking is used to secure data integrity in a SQL Server database
- Detect and end deadlocks and blocking connections in a given scenario
- Detect and terminate a transaction lock
- Connect to the server using the dedicated administrator connection (DAC) in a given scenario
- Connect to a non-responsive database using the dedicated administrator connection (DAC)
Back to List
Exam 70-441: PRO: SQL Server 2005: Designing Database Solutions Curriculum Outline
Designing an Application Solution for SQL Server 2005
Overview/Descriptionrecognize how to design and build a database model and a physical database, and how to secure it
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005; individuals looking to pass the 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path.
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types, writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, iImplementing databases at the physical level, and gathering business requirements.
Expected Duration (hours)
3 Hours
Lesson Objectives
Designing an Application Solution for SQL Server 2005
- recognize the conceptual, logical, and implementation phases of building a logical database model.
- recognize how to employ conceptual modeling techniques and implement an Entity Relationship Model in the SQL Server 2005 database.
- recognize the guidelines for normalizing data in a database model.
- recognize how to create and modify a database, based on user requirements, and the procedures defined by SQL Server 2005 for storing data.
- recognize the types of data supported by an RDBMS, and the different data types and their sizes.
- recognize how to implement security in SQL Server 2005 by using roles, login accounts, and encryption keys.
- recognize how to work with schemas and implement synonyms in a database application.
- recognize how to implement execution context in accordance with Object Ownership Chaining in SQL Server 2005.
- determine the design of a SQL Server 2005 database.
Back to List
Designing Database Objects
Overview/Descriptionrecognize how to design database objects according to your requirements
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005; individuals looking to pass the 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path.
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types, writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, iImplementing databases at the physical level, and gathering business requirements.
Expected Duration (hours)
2.5 Hours
Lesson Objectives
Designing Database Objects
- recognize the guidelines laid down by SQL Server 2005 to name objects and select identifiers, and how to create, modify, and decide the constraints that are applicable on tables.
- recognize how to design efficient indexes in SQL Server 2005.
- recognize how to design and delete T-SQL user-defined data types.
- recognize how to design and implement different types of views, based on your requirements.
- recognize how to design, execute, and delete stored procedures in SQL Server 2005.
- recognize how to design scalar and table-valued user-defined functions.
- recognize how to design DDL, DML, and Logon triggers in SQL Server 2005.
- design objects for a SQL Server 2005 database.
Back to List
Programming with SQL Server 2005
Overview/Descriptionrecognize how to implement SQL Server 2005 programming features to work with database data
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005; individuals looking to pass the 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path.
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types, writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, iImplementing databases at the physical level, and gathering business requirements.
Expected Duration (hours)
2.25 Hours
Lesson Objectives
Programming with SQL Server 2005
- recognize how to implement the different levels of isolation and snapshot isolation while working with databases.
- recognize how to access local and external data from SQL Server 2005 by configuring the linked server.
- recognize how to implement exception handling to overcome the errors that arise during execution.
- recognize how to implement Common Table Expressions in SQL Server 2005.
- recognize how to build a crosstab query using the Pivot operator and work with the UnPivot operator.
- recognize how to create a partition and generate an ordered array or table within the result set.
- determine how snapshot isolation works, a solution for linked servers, and how to implement T-SQL statements.
Back to List
Using XML
Overview/Descriptionrecognize how to use, implement, retrieve and manipulate XML data in SQL Server 2005 using XQuery language
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005; individuals looking to pass the 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path.
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types, writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, iImplementing databases at the physical level, and gathering business requirements.
Expected Duration (hours)
2.25 Hours
Lesson Objectives
Using XML
- identify various applications where XML data can be used and recognize XML support in SQL Server 2005.
- recognize how to utilize and implement an XML data type in SQL Server 2005.
- recognize how to retrieve data in XML format from a database by using FOR XML and parse an XML document to produce a rowset by using OPENXML in SQL Server 2005.
- recognize how to utilize and implement the XQuery language in SQL Server 2005.
- recognize how to use the XML data type methods by using XQuery and use the XQuery functions and operators to work with an XML data type.
- recognize how to use the modify() method to manipulate XML data in SQL Server 2005 and the considerations when using XQuery.
- update XML data in a database using XQuery language.
Back to List
CLR Integration
Overview/Descriptionrecognize how to use CLR integration in SQL Server 2005 to create, implement and work with data, and how to execute .NET assemblies to achieve this
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005; individuals looking to pass the 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path.
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types, writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, iImplementing databases at the physical level, and gathering business requirements.
Expected Duration (hours)
3 Hours
Lesson Objectives
CLR Integration
- recognize the necessity of integrating the CLR with SQL Server 2005.
- recognize how to implement and change the security context while working with applications using CLR integration.
- recognize how to create, alter, and delete an assembly, and access the assembly metadata using the catalog views.
- recognize how to create stored procedures and access the database while executing CLR stored procedures.
- recognize how to implement scalar-valued and table-valued functions.
- recognize how to use CLR triggers efficiently.
- recognize how to design CLR data types that will fulfil the requirements of user-defined data types.
- recognize how to create the CLR aggregates fulfilling the requirements of the user-defined aggregate functions.
- determine how to implement coding using CLR integration.
Back to List
Database Testing and Code Management
Overview/Descriptionrecognize how to implement version control, unit testing and performance benchmarking and improvements in SQL Server 2005
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005; individuals looking to pass the 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path.
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types, writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, iImplementing databases at the physical level, and gathering business requirements.
Expected Duration (hours)
2.25 Hours
Lesson Objectives
Database Testing and Code Management
- recognize how different versions of a file are implemented using source control.
- recognize how to manage different versions of a project by using the source control system.
- recognize the concept of unit testing and the process of designing unit tests.
- recognize how to perform unit tests for various parameters on queries executed in SQL server 2005.
- recognize how to identify and formulate a plan to monitor the performance of SQL Server 2005.
- recognize how to monitor and improve the performance of SQL Server 2005.
- determine the design strategy for testing and performance.
Back to List
Developing Applications that Use SQL Server Support Services
Overview/Descriptionrecognize how to develop and deploy SQL Service Support solutions with Notification Services and Service Broker
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005; individuals looking to pass the 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path.
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types, writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, iImplementing databases at the physical level, and gathering business requirements.
Expected Duration (hours)
2.25 Hours
Lesson Objectives
Developing Applications that Use SQL Server Support Services
- recognize the features of Notification Services platform that are used to create notifications for subscribers or applications.
- recognize how to configure Notification Services Applications database, event, subscription, and notification classes.
- recognize how to optimally configure Notification Services applications to provide events, and generate and distribute notifications.
- recognize how to configure Notification Services instances and deploy Notification Services instances to build Notification Services applications.
- recognize the various features and objects of Service Broker.
- recognize how to create a Service Broker application.
- determine how to design and optimize a Notification Services solution and implement Service Broker solutions for applications.
Back to List
Developing Applications that Use SQL Server Business Intelligence Services
Overview/Descriptionrecognize how to implement Reporting Services to generate reports and Integration Services to manipulate data according to your requirements
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005; individuals looking to pass the 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path.
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types, writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, iImplementing databases at the physical level, and gathering business requirements.
Expected Duration (hours)
2.25 Hours
Lesson Objectives
Developing Applications that Use SQL Server Business Intelligence Services
- recognize the various features and components of Reporting Services.
- recognize how to create, modify, and design reports.
- recognize how to use the tools required to administer the Reporting Services application.
- recognize data sources, and execution and scheduling of reports.
- recognize how to build high performance data integration and workflow solutions by using Integration Services tools for building and managing data integration solutions.
- recognize how to create an SSIS Package.
- determine the design of a Reporting Service and Integration Service solution.
Back to List
Database Deployment and Data Distribution
Overview/Descriptionrecognize how to design, deploy and utilize database deployment and database distribution solutions
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005; individuals looking to pass the 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path.
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types, writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, iImplementing databases at the physical level, and gathering business requirements.
Expected Duration (hours)
2.25 Hours
Lesson Objectives
Database Deployment and Data Distribution
- recognize how to deploy a database from one instance of the server to another instance using various deployment techniques.
- recognize how to design scripts for the deployment of a database and its objects.
- recognize how to design SQL Server Agent jobs and define their schedules and alerts.
- recognize how to set up database mail and send emails using database mail.
- recognize how to use web services supported by SQL Server 2005 that utilize HTTP endpoints.
- recognize how to build web services support by using HTTP endpoints for SQL Server 2005.
- determine how to implement database deployment solutions and scripts, and how to design data distribution solutions.
Back to List
Exam 70-442: PRO: SQL Server 2005: Designing and Optimizing Data Access Curriculum Outline
Designing Access to SQL Server Data
Overview/Descriptionrecognize how to design and utilize data access technologies to manage SQL Server and access SQL Server Data
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005. individuals looking to pass the 70-442 PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, implementing databases at the physical level, and gathering business requirements
Expected Duration (hours)
2.25 Hours
Lesson Objectives
Designing Access to SQL Server Data
- recognize the different types of database applications and data-access components, and determine how the components interact with one another.
- recognize how to access SQL Server data by using an appropriate access method.
- distinguish the object models that are used for managing data access.
- recognize the different caching strategies in SQL Server 2005.
- recognize how to design a customized cache to achieve the desired caching solution for SQL Server 2005.
- recognize how to use cursors to retrieve a particular set of rows from the entire result set.
- utilize data access technologies appropriately to access and manage SQL Server data.
Back to List
Designing Efficient Application Access
Overview/Descriptionrecognize how to efficiently utilize client libraries for database access using automation management objects, server Network Interface and MARS for queries and row management
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005. individuals looking to pass the 70-442 PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, implementing databases at the physical level, and gathering business requirements
Expected Duration (hours)
2 Hours
Lesson Objectives
Designing Efficient Application Access
- recognize how to use the Server Management Object to design server applications.
- recognize how to design the Replication Management Objects and the Automation Management Objects.
- recognize how to design the SQL Server Networking Interface (SNI) protocol layer for asynchronous queries, TDS endpoints, and management of endpoints.
- distinguish Multiple Active Results Set (MARS) for queries and the concept of row versioning in MARS.
- recognize how to configure MARS in the SQL Native Client Providers and configure the deadlock monitor to remove MARS deadlocks.
- design and utilize the Replication Management Objects, Automation Management object and Multiple Active Results Set (MARS) for queries and row management.
Back to List
Designing Queries in SQL 2005
Overview/Descriptionrecognize how to design and execute queries in SQL Server 2005, utilizing XQuery and the XML Data Modification Language
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005. individuals looking to pass the 70-442 PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, implementing databases at the physical level, and gathering business requirements
Expected Duration (hours)
2.25 Hours
Lesson Objectives
Designing Queries in SQL 2005
- recognize how to write the SELECT queries and use the operators and functions to aggregate and modify data.
- recognize how to create a partition and generate an ordered array or table within the result set.
- recognize how SQL codes are compiled and create objects that can reuse the query plans.
- recognize how to improve the performance of SQL queries.
- recognize how to create and execute XML data queries by using XQuery in SQL Server 2005.
- recognize how to use the XML Data Modification Language.
- create and execute queries in SQL Server 2005, XQuery and utilize the XML Data Modification Language.
Back to List
Designing a SQL Server 2005 Cursor Strategy
Overview/Descriptionrecognize how to design and implement an appropriate cursor strategy in SQL Server 2005
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005. individuals looking to pass the 70-442 PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, implementing databases at the physical level, and gathering business requirements
Expected Duration (hours)
1 Hour
Lesson Objectives
Designing a SQL Server 2005 Cursor Strategy
- recognize result set-based operations and use cursors to perform row-based operations.
- recognize how to utilize the alternatives to cursors and identify the most effective cursor, depending on the application.
- utilize cursors to perform row-based operations and to distinguish result set-based operations and the appropriate usage of cursors.
Back to List
Designing a SQL Server 2005 Transaction Strategy
Overview/Descriptionrecognize the different levels of isolation and granularity to manage concurrency and execute a transaction strategy in SQL Server 2005
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005. individuals looking to pass the 70-442 PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, implementing databases at the physical level, and gathering business requirements
Expected Duration (hours)
2 Hours
Lesson Objectives
Designing a SQL Server 2005 Transaction Strategy
- recognize how to perform data transactions in SQL Server 2005, by using the most appropriate method.
- recognize different levels of isolation in SQL Server 2005.
- recognize the type of locking hints used at different levels of granularity in SQL Server 2005.
- recognize the transaction modes and the role of MS DTC in the distributed transaction.
- recognize how to perform explicit transactions and distributed transactions in your applications.
- demonstrate the ability to perform data transactions in SQL Server 2005.
Back to List
SQL Server 2005 Error Handling Techniques
Overview/Descriptionrecognize how to design code for validating input, for error handling and for user-defined messages in SQL Server 2005
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005. individuals looking to pass the 70-442 PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, implementing databases at the physical level, and gathering business requirements
Expected Duration (hours)
1.25 Hours
Lesson Objectives
SQL Server 2005 Error Handling Techniques
- recognize how to validate permissions granted to users and inputs provided by users.
- recognize how to design a code for error handling in SQL Server 2005.
- recognize how to design user-defined messages for errors.
- design Error-Handling strategies, user-defined messages and code to validate input.
Back to List
Optimizing Query Performance and Index Strategies
Overview/Descriptionrecognize how to optimize query performance and indexing strategies in SQL Server 2005
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005. individuals looking to pass the 70-442 PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, implementing databases at the physical level, and gathering business requirements
Expected Duration (hours)
2.25 Hours
Lesson Objectives
Optimizing Query Performance and Index Strategies
- recognize how to evaluate the performance of a query and generate the execution plans.
- recognize how to test a query and analyze its performance in SQL Server 2005.
- recognize how to utilize specialized techniques to optimize queries, reduce their execution time and use the system resources efficiently.
- recognize clustered, nonclustered, full-text, and XML indexes, and indexes with computed columns.
- recognize how to create relational, XML and full-text indexes.
- recognize how to optimize the performance of the indexes that are defined on your database table in SQL Server 2005.
- assess query performance in SQL 2005 and determine performance improvement using Indexes and query optimization.
Back to List
Optimizing Storage in SQL Server 2005
Overview/Descriptionrecognize how to optimize database storage and SQL Server performance
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005. individuals looking to pass the 70-442 PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, implementing databases at the physical level, and gathering business requirements
Expected Duration (hours)
1.25
Lesson Objectives
Optimizing Storage in SQL Server 2005
- distinguish the different types of data supported by SQL Server 2005 and the different data types and their sizes.
- recognize how to utilize null and large value data types to improve the performance of the SQL Server.
- determine proper database design for optimal performance, using proper table structure and datatypes.
- recognize how to use various methods, such as row width optimization, denormalization, and variable length columns to improve the performance of your database.
Back to List
Scaling Applications and Resolving Performance Issues
Overview/Descriptionrecognize how to scale out applications to enterprise level and troubleshoot and identify performance issues that result
Target Audience
Developers who use Microsoft Office SQL Server 2005 and consultants providing consulting services for SQL Server 2005. individuals looking to pass the 70-442 PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 certification exam and those individuals targeting toward the Microsoft certification: Microsoft Certified IT Professional (MCITP): Database Developer certification path
Prerequisites
Three or more years database programming experience including expertise in troubleshooting triggers, queries, stored procedures, user-defined functions, and user-defined types writing Transact-SQL queries, performing database performance optimization, designing and troubleshooting the data access layer of the application, designing databases, implementing databases at the physical level, and gathering business requirements
Expected Duration (hours)
2 Hours
Lesson Objectives
Scaling Applications and Resolving Performance Issues
- recognize the various scale-out techniques and be able to create partitions, filegroups, and use linked servers.
- recognize how to implement the scalable shared database and the peer-to-peer replication techniques.
- recognize how to implement distributed partitioned views, data dependent routing, and system-oriented data architecture.
- recognize how to use the various templates provided by the SQL Server Profiler and the default counters provided by the system monitor.
- recognize how to create a performance baseline to identify performance issues related to the server.
- design and implement database scaling techniques such as partitioning, file groups and linked servers.
Back to List
Microsoft MCITP Database Developer Certification
(Microsoft Certified IT Professional)
Microsoft Certified IT Professional (MCITP): Database Developer certification demonstrates that you can design a secure, stable,
enterprise database solution by using Microsoft SQL Server 2005. Earning the MCITP Certification highlights your expertise and
distinguishes you as an IT professional with current skills and proven job-role capabilities to work effectively with a
comprehensive set of Microsoft database technologies.
Database Developers design and implement relational database models (logical and physical) and database storage objects. They also program servers by using user-defined functions, triggers, stored procedures, Transact-SQL, or the CLR. They retrieve or modify data by using SQL queries or tune and optimize queries. Database Developers are typically employed by medium-sized to large organizations.
Benefits of CBT Direct’s Online Microsoft MCITP Certification Training
CBT Direct boasts the most beneficial online certification training on the market. With online training, you have
the flexibility to study on your schedule, and with the speed and reliability of the internet, CBT Direct’s Microsoft MCITP
training course is accessible anywhere you have an internet connection. Convenience finally costs less with CBT Direct –
the most affordable online training solution today. CBT Direct is also a Microsoft Certified Partner.
The unique design of CBT Direct’s Microsoft MCITP certification course incorporates a proven four-step learning process: presentation, demonstration, guidance and independent practice. This four-step proven learning model for CBT Direct’s Microsoft MCITP training course ensures the greatest level of retention to prepare you for your Microsoft MCITP certification exam.
CBT Direct also offers online mentoring for over 100 current major certification exams, including Microsoft MCITP, for IT
professionals and end-users alike. CBT Direct’s mentors have a minimum of 20 certifications each and are available 24/7*.
* Available for most courses.
Who Benefits from CBT Direct’s Microsoft MCITP Training?
This training would be beneficial for individuals looking for IT job positions such as; Microsoft SQL Database Administrators or Microsoft SQL Database Developers.
What Professionals Will Learn from CBT Direct’s Microsoft
MCITP Training
To achieve this certification, you must pass three exams: one Microsoft Certified Technology Specialist (MCTS) prerequisite exam and two Professional Series exams.
Exam 70-431: TS: Microsoft SQL Server 2005: Implementation and Maintenance
This course provides students with the knowledge to install and configure SQL Servers by setting up tables, indexes, columns,
and constraints. Also the ability to use Transact-SQL to implement functions, stored procedures, and triggers. The training also covers
work with relational and XML data and how to monitor, troubleshoot, tune queries and maintain database availability with log
shipping, mirroring, and replication. Click here to see a detailed curriculum outline.
Exam 70-441: PRO: SQL Server 2005: Designing Database Solutions
This course provides students with the knowledge to design database testing and code management procedures and to design an
Application Solution for SQL Server 2005. You will also learn how to design database objects and develop applications that use
SQL Server Support Services. Click here to see a detailed curriculum outline.
Exam 70-442: PRO: SQL Server 2005: Designing and Optimizing Data Access
This course provides students with the knowledge to design efficient access to a SQL Server Service and design a database query
strategy> In this training you will learn how to design error-handling routines, design a transaction strategy and performance tune
a database and a database application. Click here to see a detailed curriculum outline.
Exam#
Certification
Certified Mentoring
Hours
Hours
24x7
Mon-Fri
(9-5 EST)
(9-5 EST)
Tier 3
TestPrep
| 70-431 | Microsoft MCTS: Microsoft SQL Server 2005 - Implementation and Maintenance | ü | ü |
Exam 70-431: TS: Microsoft SQL Server 2005: Implementation and Maintenance
Exam 70-441: PRO: SQL Server 2005: Designing Database Solutions
Exam 70-442: PRO: SQL Server 2005: Designing and Optimizing Data Access
