SQL Server® 2005 Database Administration: Hands-On

You Will Learn How To

Administer SQL Server 2005 with SQL Server Management Studio and Transact-SQL
Create and manage databases, transaction logs and database snapshots
Implement login and database security and manage permissions
Develop a backup and recovery strategy
Manage complex multistep jobs with SQL Server Agent
Deploy and manage transactional replication

Course Benefits

Administration of your organisation's SQL Server databases requires taking advantage of the scalability, availability and security features of SQL Server 2005. This course offers extensive experience using SQL Server 2005 tools to install SQL Server, create and modify storage structures, back up and recover databases, and manage security.

Who Should Attend

Those interested in SQL Server administration. Course 131, "SQL Server 2005 Comprehensive Introduction", or equivalent experience is assumed, along with a working knowledge of computers and a familiarity with Windows at the level of Course 595, "Windows Server 2003 Comprehensive Introduction".

Hands-On Training

Throughout this course, a series of extensive hands-on exercises provides you with practical experience administering SQL Server 2005. Exercises include:

Administering SQL Server 2005 with graphical tools and Transact-SQL
Creating databases
Implementing security for logins, databases and objects
Backing up and restoring databases
Recovering the master database
Maintaining a hot standby server with mirroring
Configuring Database Mail
Managing transactional replication

Course Content

Getting Started with SQL Server 2005

Components

Services
Editions
System and user databases

Essential tools

SQL Server Management Studio
Transact-SQL
Surface Area Configuration
SQL Server Configuration Manager

Installing SQL Server

SQL Server 2005 installation

Choosing installation options
Employing multiple instances
Upgrading from previous versions
Applying a Service Pack

Managing Databases

Disc structures

Examining storage structures
Creating databases and transaction logs

Space management strategies

Handling dynamic database growth
Reclaiming unused space

Moving databases

Detaching and attaching databases
Copying databases with the Copy Database Wizard

Controlling Server and Database Security

Login security

Contrasting Windows and SQL Server authentications
Authorising logins
Making logins members of server roles
Enforcing password policy

Database security

Designing schemas
Adding users
Defining NEW roles
Delegating privileges with predefined roles
Assigning users to roles

Permissions

Granting server, database, schema-scoped and object permissions
Interaction of roles and permissions

Migrating Data

SQL Server Integration Services

Packages
Control flows
Data flows
Connections
Business Intelligence Development Studio

Building simple packages

Importing data from Access
Exporting a table to Excel

Backup and Recovery

Planning for and recovering from disasters

Transaction log architecture
Choosing a recovery model
Performing full, log and differential backups
Recovering system and user databases

Implementing database mirroring

Selecting a mirroring architecture
Configuring the mirror and witness servers
Monitoring database mirroring
Taking a snapshot of the mirror

Automating Tasks with Jobs and Alerts

The SQL Server Agent

Configuring the Agent
Setting up Database Mail

Multistep jobs

Defining jobs to handle routine tasks
Creating alerts and operators
Associating alerts with jobs

Deploying Replication

Replication concepts

Publish/subscribe metaphor
Replication types
Replication agents

Managing transactional replication

Configuring the Publisher and Distributor
Creating publications
Subscribing to publications

Monitoring SQL Server

Identifying problems with Activity Monitor
Querying Dynamic Management Objects
Deploying the Performance Dashboard
Analysing trends with the SQL Server Health and History tool (SQLH2)

<< Back to SQL Server

Related Courses

SQL Server is a registered trademark of Microsoft Corporation.