SQL Server 2008 : MCITP - Designing, Optimising and Maintaining a Database Server Infrastructure (SQL-DBA2)
We use this book as our prescribed book for the course and you will receive a copy.
Bookings:
You can download the course registration form on our home page or by clicking here
Duration and pricing:
5 days full-time (R7995 excl VAT)
5 weeks part-time (2 nights per week, 3 hour sessions) (R9995 excl Vat)
Schedule
On the calender on this page below
Intended Audience:
Prerequisites:
70-432 TS
Further Training:
Introducing SQL Server 2008
1. Understanding SQL Server’s Role
What Is Information Technology?
The Importance of IT
The Components of IT
Introduction to Databases
Types of Databases
Weighing the Benefits of Using a Local or
Server-Based Database
Important Database Terms
Database Servers and Applications
Database Application Types
SQL Server’s Role
New Features Since SQL Server 2005
New Features Since SQL Server 2000
Core Features of SQL Server
SQL Server Roles
2 Installing SQL Server 2008
Installation Planning
SQL Server 2008 Architecture
Installation Planning Process
Managing Permissions
Installing a Default Instance
SQL Server Installation Center Tasks
Installation
Installing Named Instances
Installing to a Cluster
Installing Extra Features
Upgrading from Previous Versions
Handling Upgrades
Understanding Migrations
3. Working with the Administration Tools
SQL Server Configuration Manager
Overview of the SSCM
Performing Common SSCM Tasks
SQL Server Management Studio (SSMS)
Overview of the SSMS
Performing Common SSMS Tasks
SQL Server Business Intelligence Development Studio
Overview of BIDS
Performing Common BIDS Tasks
Books Online
Windows Server Administration for the DBA
User Account Management
File System Management
Network Configuration Administration
4. SQL Server Command-Line Administration
Introducing the Command Prompt
General Command-Prompt Options
Font Settings
Screen Layout
Color Choices
Directory and Folder Navigation
Directory and Folder Listing
Screen Management
Displaying Information
Redirecting Output
Administrative Commands
Using Logic (IF and GOTO)
Passing Data
Including Comments
Mastering SQLCMD
Introducing Windows PowerShell
Using SQL Server PowerShell Extensions
Windows Scripting Host
Designing Database Solutions
5. Database Concepts and Terminology
Relational Database Theory
Data
Information
Tables
Table Components
Relationship Types
Database Design Processes
Systems Development Lifecycle (SDLC)
Database Lifecycle (DBLC)
Business, User, Model (BUM)
Project Management for the DBA
The Define Phase
The Design Phase
The Deliver Phase
The Determine Phase
6. ERD and Capacity Planning
Planning a Database
User Surveys
Evaluating Business Processes
Developing Use Cases
Understanding Entity Relationship Diagramming
Building an ERD
Creating an ERD in Visio
Creating an ERD in OSA
7. Normalization: Enough Is Enough
Normalization Defined
Normal Forms
First Normal Form
Second Normal Form
Third Normal Form
Really, Really Normal Forms
Building a 1NF Table from the Excel Worksheet
Ensuring Data Is in 2NF and 3NF
Designing for Performance
Designing for Availability
Designing for Security
Implementing Database Solutions
8. Creating SQL Server Databases
SQL Server Databases
System Databases
User Databases
Database Data Files
Database Filegroups
Transaction Logs
Database Options and Properties
Autogrowth
Recovery Model
Compatibility Level
Auto Shrink
Restrict Access
More Database Properties
Creating Databases in the GUI
Creating Databases with T-SQL
Creating Databases with PowerShell
Attaching and Detaching Databases
Database Snapshots
Creating Snapshots
Reverting to Snapshots
9. Creating Tables
Data Types
Configuring Server Instance Collations
Configuring Database Collations
Configuring Column Collations
Creating Tables
Creating Tables with the Table Designer
Creating Tables with T-SQL
Data Partitioning
Vertical and Horizontal Partitioning
Data Partitioning with Functions and Schemes
10. Indexes and Views
Understanding Indexes
Indexes Defined
Index Types
Creating a Clustered Index
Creating a Nonclustered Index
Creating Advanced Indexes
Creating a Covering Index
Creating a Filtered Index
Dropping an Index
Disabling and Enabling Indexes
Understanding Index Fragmentation
Understanding Views
Creating Views
11. Triggers and Stored Procedures
Triggers Defined
How Triggers Differ from Stored Procedures
Types of Triggers
Recursive and Nested Triggers
Using Triggers
Creating Triggers
Understanding Stored Procedures
Creating Stored Procedures
12. Implementing Advanced Features
Understanding and Installing Analysis Services
Analysis Services Tools
Analysis Services Optional Components
Installing and Configuring Analysis Services
Understanding Integration Services
Installing and Configuring Integration Services
Creating a Basic Integration Services Package
Troubleshooting and Debugging an SSIS Package
Scheduling Your Package to Run Automatically
Security Protection Levels
Understanding and Installing Reporting Services
Implementing Database Mail
Configuring Full-Text Indexing
Administration and Maintenance
13. Creating Jobs, Operators, and Alerts
Standardize, Automate, and Update
Understanding SQL Server Jobs
Job Steps
Job Configuration Properties
Typical Jobs
Creating T-SQL Jobs
Creating SSIS Jobs
Creating Windows Command Jobs
Creating and Using Operators
Creating and Using Alerts
Using WSUS for SQL Server 2008
14. Performance Monitoring and Tuning
Performance Tuning Principles
Why Performance Tuning Matters
Common Performance Tuning Myths
Performance and Troubleshooting Tools
Blocks, Locks, and Deadlocks
Understanding Locks
Lock Types
Granularity of Locks
Lock Isolation Levels
Blocks and Deadlocks
SQL Server Profiler
Database Engine Tuning Advisor
Creating a DTA Workload File
Analyzing Your Workload File
Applying DTA Recommendations
Performance Monitoring with System Monitor
Installing the System Monitor
Viewing Live Performance Data
Logging Counters in Windows Server 2003
Data Collection in Windows Server 2008
Using the New Resource Governor
Performance Studio
Advanced Monitoring Tools
Dynamic Management Views (DMVs)
DBCC
Resource Monitor
15. Policy-Based Management
Policy-Based Management (PBM)
PBM Components
Creating Conditions
Creating Policies
Evaluating Policies
Centralized Server Management
Major Benefits and Requirements
Creating a Centralized Management Server
Registering Subscriber Servers
Standardizing with PBM and CMS
Standardizing
Automating
Updating
16. Backup and Restoration
Backing Up a Database
Creating a Backup Plan
Choosing a Recovery Model
Using the Different Backup Types
Working with Backup Devices and Files
Compressing Backups
Performing File and Filegroup Backups
Back Up System Databases
Restore a Database
Choosing a Restore Method
Restoring to a Point in Time
Restoring to the Point of Failure
Restoring System Databases
Back Up the Environment
Built-In Backup Tools
Imaging and Third-Party Tools
SQL Server Security
17. Security Threats and Principles
Security Defined
How to Classify Data for Security Purposes
Security in Theory
Security in the Real World
Threats, Vulnerabilities, and Exploits Defined
Attack Point One: Windows Server
Attack Point Two: SQL Server
Attack Point Three: the Network
Attack Point Four: the Client
Cracking Examples
Security Principles
Start with Good Design
Trust, but Monitor
Defense-in-Depth
Least Privilege
18. Authentication and Encryption
Understanding Authentication
Credentials
Common Authentication Methods
Regulatory Compliance
SQL Server Authentication Methods
Logins, Users, and Roles
Configuring the Authentication Mode
Creating and Managing Principals and Roles
Creating Database Users
Understanding Encryption
SQL Server Encryption Solutions
Implementing Application-Level Encryption
Implementing Transparent Encryption
19. Security Best Practices
Establishing Baselines
Working with Security Templates
Analyzing a Server’s Security Settings
Using the Security Configuration Wizard
Implementing Least Privilege
Permissions and Authorization
Ownership Chains
Credentials
Auditing SQL Server Activity
Using Audits
Notifications
DDL Triggers
Logon Triggers
Configuring the Surface Area
SP_Configure for Surface Area Management
Policy-Based Management Surface Area Options
Understanding Common Criteria and C2
GRC
C2 Compliance
Common Criteria
High Availability and Data Distribution
20. SQL Server Failover Clustering
Understanding Windows Failover Clustering Service
Implementing a Windows Cluster
Failover Clustering Components
The Failover Clustering Installation Process
Installing SQL Server 2008 to a Cluster
Monitoring and Managing a SQL Server Cluster
Service Packs and Update Management
Failover Management
Cluster Troubleshooting
21. Database Mirroring and Snapshots
RAID-Based Data Redundancy
Using Database Mirroring
Database Mirroring Components
Understanding Mirror Operating Modes
Planning for Role Switching
Implementing Database Mirroring
Understanding Log Shipping
Inside the Log-Shipping Process
Configuring Log Shipping and Monitoring
Implementing Database Snapshots
Database Snapshot Functionality
Implementing a Snapshot
Querying a Snapshot
Reverting to a Snapshot
22. Implementing Replication
SQL Server Replication
SQL Server Replication Roles and Concepts
Replication Types
Replication Models
Configuring a Publisher and Distributor
Configuring a Subscriber
Monitoring Replication
Replication Performance
Replication Recovery
Importing and Exporting Data
Using BCP
Bulk Insert Commands
Using SQL Server Integration Services (SSIS)
