SolidQ Australia Pty Ltd > COURSE-DB-ADMIN-BOOTCAMP

Microsoft SQL Server Administration Bootcamp

5-Day (COURSE-DB-ADMIN-BOOTCAMP)

Description

This 5 day course covers maintaining a Microsoft SQL Server 2008 R2 Database. It covers core database administration skills required for SQL Server. While based on SQL Server 2008 R2, this course is equally useful for attendees working with either SQL Server 2008 or SQL Server 2005.

Written and delivered by industry experts, this course is targeted at practical skills and features a large number of hands-on labs. While not targeted specifically at students aiming for certification, it would be an excellent resource for students aiming for Exam 70-432: TS: Microsoft SQL Server 2008, Installation and Maintenance and for Exam 70-450: PRO: Designing, Optimizing and Maintaining a Database Server Infrastructure using Microsoft SQL Server 2008.

Attendees will work through an extensive set of detailed and practical hands-on labs that reinforce the material presented.

For details on booking courses or seminars, please visit our booking page.

Audience

The presentations and labs have been designed specifically to give professional DBAs—those who work for an ISV, SI, or enterprise customer—the information they need to deliver SQL Server solutions on the Microsoft data platform.

This course is also relevant and useful for those interested to understand and evaluate Microsoft’s SQL Server offerings.

NOTE: This course is also very useful for those currently working with the SQL Server 2005 series of technologies. Differences between the releases are identified.

Prerequisites

Attendees should have a basic understanding of SQL Server and relational database technology.

Course Objectives

Through interactive presentations and concrete hands-on labs, attendees will learn:

Understand how to use SQL Server Tools
Will be able to install and Configure SQL Server
Understand how to work with Database Files and Filegroups
Understand how to prepare Systems for SQL Server
Understand how to secure Servers and Databases
Understand how to maintain SQL Server Indexes
Understand how to backup and restore of SQL Server Databases
Understand how to import and export data
Understand how to configure email
Understand how to automate and monitor SQL Server Management and Performance
Understand multi-server Management
Understand Declarative Management of SQL Server
Understand Resource Governance for SQL Server
Understand how to secure Data Through Encryption
Understand SQL Server High Availability Options
Understand how to troubleshoot common issues

Technologies

The workshop will empower developers to exploit the BI functionality in the following technologies:

SQL Server 2008 Database Services

While based on SQL Server 2008 R2 versions of SQL Server, this course is highly relevant for those working on SQL Server 2005 versions as well.

Course Outline

Day 1

Course Introduction

Module 1 Understanding SQL Server and Working with the SQL Server Tools

Introduction to SQL Server
Connecting to SQL Server
Working with SQL Server tools
LAB: Working with SQL Server Tools

Module 2 – Installing and Configuring SQL Server

Planning for a SQL Server Installation
Installing SQL Server
Configuring SQL Server
LAB: Installing and Configuring SQL Server

Module 3 – Working with Database Files and Filegroups

SQL Server System Databases
Database Structures
Creating and Configuring User Databases
LAB: Working with Database Files and Filegroups

Module 4 – Preparing Systems for SQL Server

SQL Server Requirements
SQL Server I/O Requirements
Pretesting I/O Subsystems with SQLIO, SQLIOSim and IOMeter
LAB: Preparing Systems for SQL Server

Day 2

Module 5 – Securing Servers and Databases

Logons and Database Users
Server and Database Roles
Permission-based Security for Objects and Schemas
LAB: Securing Servers and Databases

Module 6 – Maintaining SQL Server Indexes

SQL Server Table Structures
Non-clustered Indexes
Index Maintenance
LAB:Maintaining SQL Server Indexes

Module 7 – Backup and Restore of SQL Server Databases

SQL Server Recovery Models
Database and Log Backup
Database and Log Restore
LAB: Backup and Restore of SQL Server Databases

Module 8 – Data Import and Export and Email Configuration

Import and Export Wizards
BULK INSERT and BCP
Configuring Email
LAB: Data Import and Export and Email Configuration

Day 3

Module 9 – Automating SQL Server Management

SQL Server Agent Subsystem
Agent Security with Proxy Accounts and Credentials
Database Maintenance Plans
LAB: Automating SQL Server Management

Module 10 – Monitoring SQL Server

Alerts and Notifications
Activity Monitor
Dynamic Management Views
LAB: Monitoring SQL Server

Module 11 – Tracing Access to SQL Server

SQL Server Profiler
Analysis of Profiler Traces
SQL Trace Stored Procedures
LAB:Tracing Access to SQL Server

Module 12 – Multi-server Management

Central Management Servers
Multi-Server Queries
SQL Server Utility Control Points
LAB: Multi-server Management

Day 4

Module 13 – Monitoring Server Performance

SQL Server Performance
Performance Data Collection
Management Data Warehouse Reports
LAB: Monitoring Server Performance

Module 14 – Declarative Management of SQL Server

DDL Triggers and Logon Triggers
Configuring Policy Based Management
LAB: Declarative Management of SQL Server

Module 15 – Resource Governance for SQL Server

Techniques for Governing SQL Server Resources
Configuring Resource Governor
Troubleshooting Classifier Function Failures
LAB: Resource Governance for SQL Server

Module 16 – Securing Data Through Encryption

Introduction to Encryption Technologies
Column-based Encryption
Transparent Database Encryption
LAB: Securing Data Through Encryption

Day 5

Module 17 – SQL Server Audit

Techniques for Auditing SQL Server Access
Audit via Triggers
Configuring SQL Server Audit
LAB: SQL Server Audit

Module 18 – Introduction to SQL Server High Availability Options

Comparison of SQL Server High Availability Options
Failover Clustering
Database Mirroring
LAB: Database Mirroring

Module 19 – Introduction to SQL Server Replication

Comparison of SQL Server Replication Technologies
Transactional Replication and Peer to Peer Replication
Merge Replication
LAB: Peer to Peer Replication

Module 20 – Troubleshooting Common Issues

Correcting Connection Issues
Correcting SID-related Issues
Identifying Blocking Issues
LAB: Troubleshooting Common Issues