5 Days (SQ-DBAGURU-401-EN)

Description
Target Audience
Prerequisites
Course Objectives
Course Summary Outline

Database administrators and developers often need to have a deep understanding of SQL Server and its internals in order to maintain very large and complex environments or perform advanced level troubleshooting. This 5-day very intense seminar is designed for database administrators and advanced developers who have found themselves faced with administrating very large and complex databases and wish to know how SQL Server really interacts with the hardware and how the SQL Server database engine works behind the scenes.

Target Audience

This course is intended for:

• SQL Server database administrators who wish to learn more than just the basics of database administration
• SQL Server DBAs and developers who wish to learn the internals of SQL Server
• SQL Server DBAs and developers who wish to know advanced database administration best practices and tricks

Prerequisites

Before attending this course, it is required that students have the following skills:

• 3-6 years experience with SQL Server database administration against larger databases
• Experience with SQL Server development
• Experience with Transact-SQL

Course Objectives

This course has been designed with the objective of providing very advanced knowledge SQL Server 2005 or SQL Server 2008 database administration. The course attendees will not only learn the topic listed in the course outline, they have the opportunity to learn SQL Server administrative best practices and tips/tricks from some of the most experienced SQL Server database administrators in the world.

Course Summary Outline

Module 01:  Understanding Collations

This module will discuss collations, sort order, and code pages and how they came be configured in SQL Server. The module will cover the following topics:

• Understanding collations
• Understanding sort order
• Understanding code pages
• Database default collations
• Specifying different collations at different database levels
• Working with collations during database operations

Module 02:  Understanding SQL Server Statistics

This module will discuss SQL Server statistics, what they are used for, and how to create and maintain healthy database statistics. The module will cover the following topics:

• Statistics fundamentals
• Statistics metadata
• Maintaining statistics
• Creating test databases with statistics copy

Module 03:  Understanding the SQL Server Query Optimizer

This module will discuss the SQL Server query optimizer and the execution plans that it creates. The module will cover the following topics:

• How the optimizer works
• Optimizer metadata
• Execution Plans

Module 04:  Understanding Concurrency

This module will discuss advanced concurrency related topics. The module will cover the following topics:

• Concurrency metadata
• Controlling concurrency

Module 05:  SQL Server Instance Auditing

This module will discuss advanced SQL Server auditing. The module will cover the following topics:

• DDL triggers and logon triggers
• C2
• Common criteria
• Login failures
• Event notifications

Module 06:  SQL Server Scripting

This module will discuss how database administrators can use scripting in their day-to-day management tasks. The module will cover the following topics:

• Powershell
• Windows Management Instrumentation (WMI)
• SMO

Module 07:  SQL Agent Operators

This module will discuss operators and how they can be configured and used during alert monitoring. The module will cover the following topics:

• Operator schedules
• Fail safe operator
• Adding new operators
• Notification methods

Module 08:  Manage Alerts

This module will discuss advanced alert monitoring. The module will cover the following topics:

• Powershell alerts
• SMO alerts
• Windows Management Instrumentation (WMI) alerts

Module 09:  Advanced SQL Server Memory Management

This module will discuss Memory terminology, best practices, management, and monitoring as it relates Memory usage in SQL Server 2005 and SQL Server 2008. The module will cover the following topics:

• Memory Architecture
• Memory Internals
• Monitoring Memory
• Best Practices  as it relates to memory usage

Module 10:  Advanced Stored Procedures

This module will discuss advanced stored procedure related topics. The module will cover the following topics:

• Table-valued parameters (TVPs)
• Parameter direction (output

Module 11:  Using the SQL Server Performance Studio

This module will discuss using SQL Server 2008’s Performance Studio. The module will cover the following topics:

• Understanding the Performance Studio
• Configuring the Performance Studio
• Customizing the Performance Studio
• Using Performance Studio collected metric data

Module 12:  Advanced SQL Server CPU Management

This module will discuss CPU terminology, best practices, management, and monitoring as it relates CPU usage in SQL Server 2005 and SQL Server 2008. The module will cover the following topics:

• CPU Architecture
• CPU Internals
• Monitoring CPU Usage
• Best Practices  as it relates to CPU usage

Module 13:  XML for the DBA

This module will discuss basic usage of XML for day-to-day database administration. The module will cover the following topics:

• FOR XML
• OPEN XML
• XQUERY
• XPATH
• XML Data Types
• XML indexes
• XML schema collections
• Interacting with XML execution plans

Module 14:  Custom Automation Techniques

This module will discuss how to utilize information obtained in this course to automate many day-to-day management tasks. The module will cover the following topics:

• Automation through Transact-SQL
• Automation through scripting
• Making use of automation for monitoring and capacity planning

Module 15:  Advanced SQL Server Disk IO Management

This module will discuss Disk IO terminology, best practices, management, and monitoring as it relates disk storage for SQL Server 2005 and SQL Server 2008. The module will cover the following topics:

• Disk IO Architecture
• Disk IO Internals
• Monitoring Disk IO
• Best Practices  as it relates to Disk IO usage

Module 16:  Data Compression

This module will discuss SQL Server 2008’s data compression and how it can be used in today’s databases. The module will cover the following topics:

• Page
• Row
• Sparse Columns
• Backup Compression