Microsoft SQL Server 2008: Database Administration

Course Overview

SQL Server 2008 provides a robust platform that enables organisations to run their most demanding, mission-critical applications. It is essential that database administrators know how to manage this complex platform correctly. This hands-on course provides you with the skills you need to install SQL Server, create and modify storage structures, manage security and backup and recover databases.

On completion of the Microsoft training course, Microsoft users will be well equipped to use SQL Server

Related Courses

• Microsoft SQL Server 2008: Business Intelligence End to End with SQL Server 2008 R2 and SharePoint 2010
• Microsoft SQL Server 2008: Audit & Security Course
• Microsoft SQL Server 2008: Implementing and Maintaining a Microsoft SQL Server 2008 Database
• Microsoft SQL Server 2008: Integration Services
• Microsoft SQL Server 2008 New Features: Hands-On
• Microsoft SQL Server 2008 Comprehensive Introduction: Hands-On
• Microsoft SQL Server 2008: New Features for SQL Server 2005 Administrators and Developers
• Microsoft SQL Server 2008: Administration
• Microsoft SQL Server 2008: Integration Services
• Microsoft SQL Server 2008: Querying and Programming (also suitable for 2000 and 2005)

Audience

Those responsible for administering SQL Server 2008. A basic understanding of SQL Server 2008 or experience at the level of Course "SQL Server 2008 Comprehensive Introduction", is assumed. Experienced SQL Server 2005 DBAs should take Course "SQL Server 2008 Skills Upgrade".

Skills Gained

You Will Learn How To

• Administer SQL Server 2008 with SQL Server Management Studio and Transact-SQL
• Build databases and upgrade from prior versions
• Secure the server through logins and permissions management
• Automate administrative activities with SQL Server Agent multistep jobs
• Perform routine maintenance to correct fragmentation and database corruption
• Identify problems and monitor server health

Course Outline

Hands-On Training

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

• Administering SQL Server 2008 with graphical tools and Transact-SQL
• Creating databases
• Implementing security for logins, databases and objects
• Backing up and restoring databases
• Managing transaction logs
• Configuring Database Mail
• Carrying out routine database maintenance
• Monitoring server health

SQL Server 2008 Installation and Features

Installing SQL Server

• Choosing installation options
• Upgrading from previous versions
• Applying a service pack

Essential tools

• SQL Server Management Studio
• Transact-SQL
• SQL Server Configuration Manager
• Policy-based Management

Creating and Managing Databases

Storage structures

• Examining disc structures
• Creating databases and transaction logs
• Defining file groups

Managing database space

• Permitting automatic database growth
• Adding database files to expand databases
• Specifying database options

Moving databases

• Scripting objects and moving data with Transact-SQL
• Detaching and attaching databases

Implementing Server and Database Security

Creating logins

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

Authorising database access

• Adding users
• Defining new roles
• Delegating privileges with predefined roles
• Assigning users to roles
• Handling mismapped logins

Managing Permissions

Granting database-scoped privileges

• Permitting object creation
• Granting blanket permissions

Schemas

• Designing schemas
• Assigning a default schema

Handling object-level permissions

• Limiting object access
• Meeting complex permission requirements with roles
• Examining permission hierarchies

Recovering from Disasters

Backing up databases

• Choosing a recovery model
• Transaction log architecture
• Performing full, log and differential backups
• Reclaiming transaction log space

Restoring databases

• Recovering user databases
• Testing recovery scenarios

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

Performing Database Maintenance

Fragmentation and database corruption

• Identifying and curing fragmentation
• Finding and repairing database corruption

Database Maintenance Plan Wizard

• Choosing maintenance tasks
• Scheduling plan execution

Monitoring SQL Server

Ad hoc monitoring

• Identifying problems with Activity Monitor
• Querying Dynamic Management Objects

The Management Data Warehouse

• Configuring data collection
• Reviewing the reports