MS SQL 2005 Certification
Microsoft Certified IT Professional: Database Administrator
(MCITP: Database Administrator)
The Microsoft Certified IT Professional (MCITP) credential is a premier certification for database designers and developers. By earning this sought after credential, you will demonstrate to your current or prospective employers that you are qualified to design a robust, secure, enterprise database application using Microsoft SQL Server 2005.
This course curriculum covers the following three examinations:
• Microsoft SQL Server 2005 - Implementation and Maintenance
• Designing a Database Server Infrastructure by Using MS SQL Server 2005
• Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005
Along with the pride of obtaining the MCITP certification, it will also demonstrate increased productivity to your employer or customer. You will be able to resolve network support and maintenance problems more efficiently, resolve end-user support issues must faster, and complete routine IT support tasks such as building servers at an alarming pace, making you invaluable to your company. If you are new to IT, this certification will make your entry into this rewarding area much easier.
| Microsoft
SQL Server 2005 MCITP Certification |
£1600 |
 |
| Microsoft SQL Server 2005(MCTS Certification) |
£790 |
|
| Oracle OCP + MS SQL 2005 MCITP |
£2400 |
|
MCITP Certification Exam and Course Highlight |
MCITP Exam 70–431:
Microsoft SQL Server 2005
|
Microsoft SQL Server 2005 - Implementation and Maintenance |
MCITP Exam 70–443: |
Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005
|
MCITP Exam 70–444: |
Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005
|
|
|
|
End 2 End Process
|
Enrolment
Phone or In Person
Course Fee £1600 |
|
|
|
Books and Course
Material Included |
| |
|
|
| |
|
Hands-on instructor
led Training for 8 weeks, Unlimited
Pratice Hours |
| |
|
|
| |
|
Free MCITP
Mock Exam |
| |
|
|
| |
|
Actual MCTS Exam
done @ BCOC
Fee not included |
| |
|
|
| |
|
Certified MCITP |
|
| |
|
| Start Date | Session | Time | Duration | Observations | Enrollment |
| Jun 27 2008 | Friday | 10:00-17:00 | 9 Classes | Once A Week Class & Unlimited Practice Hours |
enrol me
|
| Oct 01 2008 | Mon, Wed | 18:30-21:30 | 18 Classes | Twice a week, Unlimited practice hours |
enrol me
|
| Oct 10 2008 | Friday | 10:00-17:00 | 9 Classes | Once A Week Class & Unlimited Practice Hours |
enrol me
|
| 1 |
|
|
| |
|
|
SQL Server 2005 Course Overview
Whether it is web commerce, accounting, finance,
record keeping, data entry, and marketing or applications
developer occupation, one of the basic tools used
in many organisations in London and globally is
RDBMS (SQL). SQL Server courses can prepare you
for the MCITP certification in database management on highly
diverse level of industries in London and worldwide.
Our courses for Microsoft SQL certification can
also be useful for job encroachment in London
because you can use the skills you gain immediately
to increase your efficiency. Our SQL courses will
give you clear understanding of the structure
and function of a relational database system,
use Microsoft SQL Server to create a relational
database and use good design principles to optimise
data storage and retrieval. Our courses in Microsoft
SQL Server are gaining popularity, which, together
with Microsoft .NET framework courses, promises
to endow you to produce results via sophisticated
database capabilities and information management.
The SQL server course focuses primarily on developing
proficiency with Microsoft SQL Server and on gaining
solid skills for setting up and maintaining a
database in a Microsoft Windows platform.
Exam 70–431: Microsoft SQL Server
2005 - Implementation and Maintenance
Installing and Configuring SQL Server 2005
Install SQL Server 2005.
Verify prerequisites.
Upgrade from an earlier version of SQL Server.
SQL Server 2005 Instance
Create an instance.
Configure SQL Server 2005 instances and databases.
Configure log files and data files.
Configure the SQL Server Database Mail subsystem
for an instance.
Choose a recovery model for the database.
SQL Server 2005 Security
Configure SQL Server security.
Configure server security principals.
Configure database securable.
Configure encryption.
Configure and Manage linked Servers
Configure linked servers by using SQL Server Management
Studio (SSMS).
Identify the external data source.
Identify the characteristics of the data source.
Identify the security model of the data source.
Implementing High Availability and Disaster Recovery
Implement database mirroring.
Prepare databases for database mirroring.
Create endpoints.
Specify database partners.
Specify a witness server.
Configure an operating mode.
Implement log shipping.
Initialize a secondary database.
Configure log shipping options.
Configure a log shipping mode.
Configure monitoring.
Manage database snapshots.
Create a snapshot.
Revert a database from a snapshot.
Supporting Data Consumers
Retrieve data to support ad hoc and recurring queries.
Construct SQL queries to return data.
Format the results of SQL queries.
Identify collation details.
SQL Relational Data
Manipulate relational data.
Insert, update, and delete data.
Handle exceptions and errors.
Manage transactions.
XML Data
Manage XML data, Identify the specific structure
needed by a consumer.
Retrieve XML data, Modify XML data.
Convert between XML data and relational data.
Create an XML index.
Load an XML schema.
HTTP End Point
Implement an HTTP endpoint.
Create an HTTP endpoint.
Secure an HTTP endpoint.
Implement Service Broker components.
Create services, Create queues, Create contracts.
Create conversations, Create message types, Send
messages to a service.
Route a message to a service, Receive messages
from a service.
Import and Export Data
Import and export data from a file.
Set a database to the bulk-logged recovery model
to avoid inflating the transaction log.
Run the bcp utility, Perform a Bulk Insert task.
Import bulk XML data by using the OPENROWSET function.
Copy data from one table to another by using the
SQL Server 2005 Integration Services (SSIS) Import
and Export Wizard.
SQL Server 2005 Database Replication
Manage replication.
Distinguish between replication types.
Configure a publisher, a distributor, and a subscriber.
Configure replication security.
Configure conflict resolution settings for merge
replication.
Monitor replication.
Improve replication performance.
Plan for, stop, and restart recovery procedures.
Maintaining Databases
Implement and maintain SQL Server Agent jobs.
Set a job owner.
Create a job schedule.
Create job steps.
Configure job steps.
Disable a job.
Create a maintenance job.
Set up alerts.
Configure operators.
Modify a job.
Delete a job.
Manage a job.
Manage databases by using Transact-SQL.
Manage index fragmentation.
Manage statistics.
Shrink files.
Perform database integrity checks by using DBCC
CHECKDB.
Back up a database.
Perform a full backup.
Perform a differential backup.
Perform a transaction log backup.
Initialize a media set by using the FORMAT option.
Append or overwrite an existing media set.
Create a backup device.
Back up filegroups.
Restore a database.
Identify which files are needed from the backup
strategy.
Restore a database from a single file and from
multiple files.
Choose an appropriate restore method.
Move a database between servers.
Choose an appropriate method for moving a database.
Monitoring and Troubleshooting SQL Server Performance
Gather performance and optimization data by using
the SQL Server Profiler.
Start a new trace.
Save the trace logs.
Configure SQL Server Profiler trace properties.
Configure a System Monitor counter log.
Correlate a SQL Server Profiler trace with System
Monitor log data.
Gather performance and optimization data by using
the Database Engine Tuning Advisor.
Build a workload file by using the SQL Server
Profiler.
Tune a workload file by using the Database Engine
Tuning Advisor.
Save recommended indexes.
Monitor and resolve blocks and deadlocks.
Identify the cause of a block by using the sys.dm_exec_requests
system view.
Terminate an errant process.
Configure SQL Server Profiler trace properties.
Identify transaction blocks.
Diagnose and resolve database server errors.
Connect to a non-responsive server by using the
dedicated administrator connection (DAC).
Review SQL Server startup logs.
Review error messages in event logs.
Monitor SQL Server Agent job history.
Identify the cause of a failure.
Identify outcome details.
Find out when a job last ran.
Gather performance and optimization data by using
DMVs.
Creating and Implementing Database Objects
Implement a table.
Specify column details.
Specify the filegroup.
Assign permissions to a role for tables.
Specify a partition scheme when creating a table.
Specify a transaction.
Implement a view.
Create an indexed view.
Create an updateable view.
Assign permissions to a role or schema for a view.
Implement triggers.
Create a trigger.
Create DDL triggers for responding to database
structure changes.
Identify recursive triggers.
Identify nested triggers.
Identify transaction triggers.
Implement functions.
Create a function.
Identify deterministic versus nondeterministic
functions.
Implement stored procedures.
Create a stored procedure.
Recompile a stored procedure.
Assign permissions to a role for a stored procedure.
Implement constraints.
Specify the scope of a constraint.
Create a new constraint.
Implement indexes.
Specify the filegroup.
Specify the index type.
Specify relational index options.
Specify columns.
Specify a partition scheme when creating an index.
Disable an index.
Create an online index by using an ONLINE argument.
Create user-defined types.
Create a Transact-SQL user-defined type.
Specify details of the data type.
Create a CLR user-defined type.
Implement a full-text search.
Create a catalog.
Create an index.
Specify a full-text population method.
Implement partitions.
Exam 70–443: Designing a Database Server
Infrastructure by Using Microsoft SQL Server
2005
Designing a Database Server Infrastructure
Design for capacity requirements.
storage requirements, Analyze network requirements,
Analyze CPU requirements.
Analyze the current configuration, Analyze memory
requirements.
Forecast and incorporate anticipated growth
requirements into the capacity requirements.
Specify software versions and hardware configurations.
Choose a version and edition of the operating
system.
Choose a version of SQL Server 2005.
Choose a CPU type.
Choose memory options.
Choose a type of storage.
Design physical storage.
Transaction log storage, Design backup file storage.
SQL Server service executables, Specify the
number and placement of files to create for
each database.
Design instances.
System databases for each instance.
Physical storage for the tempdb database for
each instance.
Number of instances, Naming of instances, Establish
service requirements.
Specify instance configurations.
Design a database consolidation strategy.
Identify potential consolidation problems, Design
a database migration plan for the consolidated
environment.
Designing Security for a Database Server Solution
Analyze business requirements, Integrate database
security with enterprise-level authentication
systems, authentication system to use, Design
Active Directory organizational units (OUs)
to implement server-level security policies.
Develop Microsoft Windows server-level security
policies.
Develop a password policy.
Develop an encryption policy.
Specify server accounts and server account rights.
Specify the interaction of the database server
with antivirus software.
Specify the set of running services and disable
unused services.
Specify the interaction of the database server
with server-level firewalls.
Specify a physically secure environment for
the database server.
Modify the security design based on the impact
of network security policies.
Analyze the risk of attacks to the server environment
and specify mitigations.
Design SQL Server service-level security.
Specify logins, Select SQL Server server roles
for logins, Specify a SQL Server service authentication
mode, Design a secure job role strategy for
the SQL Server Agent Service.
Design database-level security.
Specify database users, Design schema containers
for database objects.
Specify database roles, Define encryption policies,
Design DDL triggers.
Design object-level security.
Design a permissions strategy, Analyze existing
permissions, Design an execution context.
Design column-level encryption, Design security
for CLR objects in the database.
Designing a Physical Database
Modify an existing database design based on
performance and business requirements.
Design tables, Decide if partitioning is appropriate,
Specify primary and foreign keys, Specify column
data types and constraints, Decide whether to
persist computed columns, Specify physical location
of tables, including file groups and a partitioning
scheme.
Design file groups.
file groups for performance, Design file groups
for recoverability, Design file groups for partitioning.
Index usage
Design indexes for faster data access, Design
indexes to improve data modification, Specify
physical placement of indexes.
Manage and Maintain Views
Analyze business requirements, Choose the type
of view, Specify row and column filtering.
Create database conventions and standards.
Database object-naming conventions, consistent
synonyms, database coding standards, Document
database conventions and standards.
Create database change control procedures.
Establish where to store database source code,
Isolate development and test environments from
the production environment.
Define procedures for moving from development
to test.
Define procedures for promoting from test to
production.
Define procedures for rolling back a deployment.
Document the database change control procedures.
Designing a Database Solution for High Availability
Identify potential single points of failure.
Decide how quickly the database solution must
fail over.
Choose automatic or manual failback.
Analyze costs versus benefits of various solutions.
Combine high-availability technologies to improve
availability.
Develop a strategy for migration to a highly
available environment.
Analyze the current environment, Ascertain migration
options, Choose a migration option.
Design a highly available database storage
solution.
Design the RAID solutions for your environment,
Design a SAN solution.
Design a database-clustering solution.
Design a Microsoft Cluster Service (MSCS) implementation,
Design the cluster configuration of the SQL
Server service.
Design database mirroring.
Server roles for database mirroring, Initialization
of database mirroring.
Design a test strategy for planned and unplanned
role changes.
Database Replication.
Specify an appropriate replication solution,
Peer-to-peer replication, application failover
strategy, resolving data conflicts, reconnect
client applications
Design log shipping.
Primary server and secondary server. Switch
server roles, failover strategy, reconnect client
applications
Designing a Data Recovery Solution for a Database
Specify data recovery technologies based on
business requirements.
Analyze how much data the organization can afford
to lose.
Analyze alternative techniques to save redundant
copies of critical business data.
Analyze how long the database system or database
can be unavailable.
Design backup strategies.
Specify the number and location of devices to
be used for backup, Specify what data to back
up, Specify the frequency of backup, Choose
a backup technique, Specify the type of backup,
Choose a recovery model.
Create a disaster recovery plan.
Create a disaster decision tree that includes
restore strategies.
Establish recovery success criteria.
Validate restore strategies.
Designing a Strategy for Data Archiving
Select archiving techniques based on business
requirements.
Gather requirements that affect archiving.
Ascertain data movement requirements for archiving.
Design the format of archival data.
Specify what data to archive.
Specify the level of granularity of an archive.
Specify how long to keep the archives.
Plan for data archival and access.
Specify the destination for archival data.
Specify the frequency of archiving.
Decide if replication is appropriate.
Establish how to access archived data.
Design the topology of replication for archiving
data.
Publications and articles to be published, Distributor
of the publication, Subscriber of the publication.
Design the type of replication for archiving
data.
Exam 70–444: Optimizing and Maintaining
a Database Administration Solution by Using
Microsoft SQL Server 2005
Optimizing the Performance of Database Servers
and Databases
Troubleshoot physical server performance, instance
performance, database performance and maintain
query performance
Poorly performing queries, inefficiencies in
query logic, Maintain and optimize indexes,
stored procedure logging and output, concurrency
issues.
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.
Plan for fault-tolerance.
Recover from a failure of SQL Server 2005.
Recover from a database disaster.
Plan a strategy, Restore a database, Configure
logins, Recover lost data.
Maintain server and database scripts for recoverability.
Salvage good data from a damaged database by
using restoration techniques.
Designing a Strategy to Monitor and Maintain
a Database Solution
Define and implement monitoring standards for
a physical server.
Establish the thresholds for performance, baselines
for performance, monitor on the physical server
Define traces, Set alerts, Set notifications,
Create a job dependency diagram.
Manage the maintenance of database servers,
manage Reporting Services
Designing a Database Data Management Strategy
Design and manage SQL Server Integration Services
(SSIS) packages.
Construct complex SSIS packages.
Design security for accessing packages.
Restart failed packages.
Deploy and move packages.
Schedule package execution.
Move packages to different servers.
Enforce data quality according to business
requirements.
Establish the business requirements for quality.
Create queries to inspect the data.
Use checksum.
Clean the data.
Design data integrity.
Reconcile data conflicts.
Make implicit constraints explicit.
Assign data types to control characteristics
of data stored in a column.
Design a strategy to manage data across linked
servers.
Set up and manage linked servers.
Design a strategy to manage replication.
Alerts, monitor health, latency, and failures
Verify replication, resolve replication conflicts,
modify agent profiles Tune replication configuration
Designing a Strategy to Manage and Maintain
Database Security
Perform a security audit of the existing security
infrastructure based on the security plan.
Analyze the physical server security.
Compare the existing security infrastructure
to business and regulatory requirements.
Identify variations from the security design,
Maintain a server-level security strategy.
Design a strategy to audit Windows account permissions.
Design a strategy to audit SQL Server service
access.
Maintain a strategy to assign the appropriate
minimum level of privileges.
Maintain an encryption strategy that meets business
requirements.
Design a strategy to apply service packs and
security updates.
Configure the surface area.
Maintain a user-level security strategy.
Verify the existence and enforcement of account
policies.
Verify SQL Server login authentication.
Verify permissions on SQL Server roles and accounts.
Prepare for respond to threats and attacks.
Prepare for and respond to SQL Server injection
attacks.
Prepare for and respond to denial-of-service
attacks that are specific to SQL Server.
Prepare for and respond to virus and worm attacks
that are specific to SQL Server.
Prepare for and respond to internal attacks
that are specific to SQL Server.
|
