SQL Server

At the end of this course you will be able to:

• Install Microsoft SQL Server 2005 and the associated bundled tools
• Understand the many tools that facilitate effective Database Administration
• Monitor the smooth running of the Database Server
• Maintain the Database and Indexes
• Setup and control Database Security
• Cope effectively with Database Disaster Recovery
• Implement High Availability and Replication
• Use relevant TSQL commands
• Understand the basics of Integration Services
• Implement SQL Server Full-text search

SQL Server 2005 Database Administration Training

• Availability and Scalability Features
• Security Features
• Data Management Features
• Administration and Maintenance Features
• Development Features
• To enable SQLiMail Testing mail delivery

• Installing Microsoft SQL Server
• System Requirements
• Preparing for a SQL 2005 Installation
• Installation Checklist
• Setup Features
• Using the System Consistency Checker
• Unattended Installation
• Installation Sequence
• Installation Maintenance
• Upgrading from previous versions of SQL Server
• To verify the Installation
• Administrative Tools

• Management Studio Windows
• Registered Servers
• Object Explorer
• Review Database Objects
• Modify Database Properties
• Create Database Scripts
• The Maintenance Plans Node
• Solution Explorer
• Query Editor
• Template Explorer
• Dynamic Help
• SQL Server 2005 Services node
• SQL Server 2005 Network Configuration node
• SQL Server 2005 Native Client Configuration node
• Dedicated Administrative Connection
• SQL Server Management Studio SQLCMD Mode
• Using SQL Management Objects
• SMO Object Model

• Managing Metadata Views
• Metadata Storage
• System Views
• Information Schema
• Catalog Views
• Sys All
• Dynamic Management Views
• System Stored Procedures

• Index Management New Index Features
• Creating Indexes
• Online Index Maintenance
• Lock Options
• Included Columns in Indexes
• Partitioned Tables and Indexes
• XML Index Support
• Altering an Index
• Rebuilding an Index
• Reorganising an Index
• Disabling an Index Dropping an Index
• Index Fragmentation
• Using the Database Tuning Advisor

• Implementing Securables
• New Security Features
• Principals Securables
• Permissions
• Permission Types Managing Logins
• Managing Users
• Managing Schemas Schema Definition
• Using Schemas
• Managing Execution Context
• Managing Permissions
• Manage Server Permissions
• Manage Server-Scope Securable Permissions
• Manage Database Permissions
• Manage Database-Scope Securable Permissions
• Managing Certificates

• Disaster Recovery
• SQL 2005 Disaster Recovery Feature
• Enhanced Features Database Snapshots Creating a Snapshot
• Common Snapshot Applications
• Undeleting table rows Undoing a table update
• Recovering a dropped object
• Backup and Restore
• Backup Changes Restore Changes
• Media Integrity Changes
• Recovering the MASTER Database
• Create a mirrored backup
• Restore from the backup mirror
• Create a snapshot
• Restore the snapshot data
• Automate the creation of daily snapshots

• SQL 2005 Data Availability
• Database Mirroring
• The Witness Server and Automatic Failover
• Configuring Database Mirroring
• Transaction Safety Level
• Operating Modes
• Mirroring Security
• Monitoring Mirroring
• Database States for Database Mirroring
• Prepare the Servers Configure Mirroring
• Verify Principal and Mirror Access
• Execute the Mirror Failover
• Verify Mirror and Witness Status
• Terminating the Mirror Session

• Replication Enhancements
• Stand-alone Replication Monitor
• HTTP Synchronisation for Merge Replication
• Subscriber Initialisation
• Replicating DDL Statements
• Modified User Interface
• Replication Management Objects
• To Configure Publishing and Distribution
• Configuring Peer-to-Peer Replication
• Create a Publication
• Create a Subscription with RMO

• Creating the Database Objects
• Creating a Package Using Business Intelligence Studio
• Integration Services Project Template
• OLE DB Command object
• Column Mappings
• Adding a Data Viewer

• SQL Server Profiler
• Optimal Data Storage
• Diagnostic Tools Query Analyzer
• Database Engine Tuning Advisor
• Index Optimisation
• Federated Database Servers
• Partitioned Indexes
• Lock optimsation