SQL Server Course Outlines

SQL Server Administrator Introduction

Course Duration

Three Days

Course Level

Introduction

Course Contents

INTRODUCTION TO MICROSOFT SQL SERVER

  • Exploring the components of SQL Server
  • Identifying SQL Server administration tasks

INSTALLING SQL SERVER

  • Identifying the requirements to install SQL Server
  • Installing SQL Server
  • Exploring the SQL Server installation
  • Troubleshooting the SQL Server installation
  • Configuring SQL Server

DESIGN & CONFIGURE SQL SERVER SECURITY

  • Identifying the security features of SQL Server
  • Implementing Microsoft Windows NT Authentication Mode security
  • Implementing Mixed Authentication Mode security
  • Designing security for a SQL server
  • Assigning login accounts
  • Assigning permissions

OPTIMIZING SQL SERVER’S PERFORMANCE

  • Identifying the factors that affect performance
  • Designing a strategy for monitoring performance
  • Using performance monitoring tools

AUTOMATING SQL SERVER ADMINISTRATION

  • Overview of automating administration
  • Managing alerts
  • Automating tasks across multiple SQL servers
  • Automating the publication of data on the Web
  • Automating maintenance tasks
  • Troubleshooting

BACKING UP SQL SERVER DATABASES

  • Designing a backup strategy
  • Performing a backup
  • Automating backups
  • Implementing hardware fault tolerance

RESTORING SQL SERVER DATABASES

  • Identifying methods to restore databases
  • Restoring databases
  • Recovering system databases
  • Recovering data through a backup SQL server

DESIGN & IMPLEMENT REPLICATION

  • Understanding the components of SQL Server replication
  • Designing a replication strategy
  • Implementing publishing
  • Implementing subscribing
  • Implementing replication in a mixed environment
  • Publishing on the Internet

MANAGING REPLICATION

  • Monitoring replication
  • Troubleshooting replication

SQL Server End User Introduction

Course Duration

Two Days

Course Level

Introduction

Course Contents

INTRODUCTION TO SQL SERVER

  • What is SQL Server
  • What does SQL Server do?
  • Components of SQL Server
  • SQL Server and it’s Databases

SQL SERVER ADMINISTRATION TASKS

  • Common Administrative Components

DESIGNING & IMPLEMENTING DATABASES

  • What is a Relational Database?
  • Developing a Strategy for Implementing a Database
  • Creating a Database

FILEGROUPS

  • Fault Tolerance

MANAGING DATABASES

  • SQL Server & Data Transformation
  • Importing VS Linking
  • DTS Import and Export Wizards
  • Creating a DTS Package: Using a Wizard

CREATING & MANAGING TABLES

  • Table Properties
  • Creating a Table with Enterprise Manager
  • Working with Tables using Query Analyzer

BASIC UNDERSTANDING OF SQL

  • The SELECT Statement
  • Inserting Data
  • Updating Tables
  • Deleting Data

USING DATA STORED BY SQL SERVER VIA MICROSOFT OFFICE PROGRAMS

  • Defining the Data Source
  • Building the Query for Word with MS Query
  • Analysing Numeric Data with Microsoft Excel
  • Working with SQL Server Data via Microsoft Access

LINKING A REMOTE DATABASE TO SQL SERVER AUTOMATING SQL SERVER ADMINISTRATION

  • Registering a SQL Server
  • Web Assistant Wizard
  • The Backup Wizard
  • Maintenance Plan Wizard
  • Troubleshooting SQL Server

SQL Server Reporting Introduction

Course Duration

Two Days

Course Level

Introduction

Course Contents

CORE REPORT SKILLS

  • Exploring SQL server reporting services components
  • Report Designer
  • SQL Server Data Tools Visual Studio
  • Report models
  • The elements of a report
  • Creating a report with report builder or SSDT
  • Grouping table regions on a report
  • Joining data from multiple tables
  • Formatting report elements
  • Using functions in a report
  • Creating expressions
  • Displaying data in a matrix

INTERACTIVITY

  • Filtering data and adding parameters to a report
  • Customizing report parameters
  • Sorting data in a data region
  • Applying interactive sorting
  • Creating a drillthrough action to connect reports

DATA VISUALISATIONS

  • Introduction to charting in Reporting Services
  • Creating a column chart
  • Adding a generated average to a chart
  • Creating a pie chart
  • Using sparklines
  • Adding a sparkline to a drilldown matrix
  • Adding data bars

INDICATORS

  • Adding indicators to a report
  • Using and configuring gauges
  • Using maps in Reporting Services

LARGE REPORTS

  • Creating modular reports with report parts
  • Adding and updating report parts
  • Using subreports and nested regions
  • Configuring headers and footers
  • Printing and exporting reports
  • Using page breaks
  • Creating and using shared data sources
  • Creating and using shared data sets

CORE ADMIN TASKS

  • Organizing reports in Report Manager
  • Adding users and configuring report security
  • Configuring subscriptions
  • Creating a linked report
  • Using Report Designer in SQL Server Data Tools

SQL Server Integration Introduction

Course Duration

Two Days

Course Level

Introduction

Course Contents

SSIS TASKS

  • Task Editor & Expressions
  • Looping & Sequence Tasks
  • Analysis Services Tasks
  • Data Flow Tasks

THE DATA FLOW

  • Understanding the Data Flow
  • Data Viewers
  • Data Sources: OLE DB, Excel, Flat File, Raw, XML, ADO.NET
  • Data Destinations: Excel, Flat File, OLE DB, Raw, Recordset, Data Mining Model Training, DataReader, Dimension & Partition Processing
  • Common Transformations: Including Aggregate, Conditional Split, Data Conversion, Derived Column,Lookup, Script Component, Sort, Union All
  • Other Transformations: Including Audit, Copy Column, DQS Cleansing, Export & Import Column, Merge, Merge Join, Multicast
  • Data Flow Example

USING VARIABLES, PARAMETERS & EXPRESSIONS

  • Data Types
  • Using Variables & Parameters
  • Expression Builder

CONTAINERS

  • Task Host Containers
  • Sequence Containers
  • Groups
  • For Loop Container
  • Foreach Loop Container

JOINING DATA

  • Lookup Transformation
  • Merge Join Transformation
  • Contrasting SSIS & Relational Join

DATA CLEANSING PACKAGE

  • Creating Connections
  • Creating Control Flow
  • Creating Data Flow
  • Handling Dirty Data
  • Finalizing Data
  • Making the Package Dynamic

ADVANCED DATA CLEANSING

  • Advanced Derived Column
  • Advanced Fuzzy Lookup & Fuzzy Grouping
  • DQS Cleansing if applicable
  • Master Data Management

RELIABILITY & SCALABILITY

  • Control flow, containers, checkpoints
  • Single transaction, multiple transactions, multiple packages
  • Error Outputs
  • Scaling Out
  • Memory, Staging Data, Parallel Loading

ERROR & EVENT HANDLING

  • Precedence Constraints
  • Event Handling
  • Breakpoints
  • Error Rows
  • Logging

SQL Server Analytical Introduction

Course Duration

Two Days

Course Level

Introduction

Course Contents

WHAT IS MICROSOFT BI?

  • Define Business Intelligence
  • Understand the Cube Structure
  • Deploy and View a Sample Cube
  • View a Cube by using Excel
  • View a Cube by using SQL Reporting Services

OLAP MODELING

  • Understand Basic OLAP Modeling (star schema)
  • Understand Dimensional Modeling (stars and snowflakes)
  • Understand Measure (fact) and Cube Modeling

INTRODUCTION TO SSDT

  • Create Data Sources
  • Create Data Source Views
  • Create Cubes by using the Cube Wizard
  • Understand the Development Environment

INTERMEDIATE SSAS

  • Learn how to Create Key Performance Indicators
  • (KPIs)
  • Discover how to Create Perspectives
  • See how to Create Translations for Cubes and Dimensions
  • Review the three SSAS Action Object Types: Regular,
  • Drillthrough, and Reporting

ADVANCED SSAS

  • Work with Multiple Fact Tables and the Dimension
  • Usage Subtab in BIDS
  • Explore Advanced Dimension Types
  • Learn how to use the Business Intelligence Wizard
  • Understand Writeback in Dimensions
  • Review Semi-Additive Measures in OLAP Cubes

CUBE STORAGE AND AGGREGATION

  • View Aggregation Designs
  • Customize Aggregation Designs
  • Implement Proactive Caching
  • Use Relational and SSAS Partitions
  • Customize Cube and Dimension Processing

INTRODUCTION TO MDX QUERIES

  • Understand Basic MDX Syntax
  • Use the MDX Query Editor in SSMS
  • Understand Common MDX Functions and Tasks

INTRODUCTION TO DATA MINING

  • Understand Data Mining Concepts
  • Review the Algorithms that SSAS Includes
  • Consider Data Mining Clients
  • Understand Mining Structure Processing

SSAS ADMINISTRATION

  • Implement SSAS Security
  • Deploy and Synchronize Databases
  • Understand SSAS Database Backup and Restore

ADVANCED ADMINISTRATION AND OPTIMIZATION

  • Implement SSIS to Manage SSAS Databases
  • Explore Clustering
  • Explore Scalability Options
  • Understand Performance Tuning and Optimization

INTRODUCTION TO SSAS CLIENTS

  • Design Reports using Reporting Services
  • Design Reports by using Report Builder
  • Implement Excel Pivot Tables and Charts
  • Use Excel as a Data Mining Client
  • Review Microsoft Office SharePoint Server

SQL Server Administrator Introduction

Course Duration

Three Days

Course Level

Introduction

Course Contents

INTRODUCTION TO MICROSOFT SQL SERVER

  • Exploring the components of SQL Server
  • Identifying SQL Server administration tasks

INSTALLING SQL SERVER

  • Identifying the requirements to install SQL Server
  • Installing SQL Server
  • Exploring the SQL Server installation
  • Troubleshooting the SQL Server installation
  • Configuring SQL Server

DESIGN & CONFIGURE SQL SERVER SECURITY

  • Identifying the security features of SQL Server
  • Implementing Microsoft Windows NT Authentication Mode security
  • Implementing Mixed Authentication Mode security
  • Designing security for a SQL server
  • Assigning login accounts
  • Assigning permissions

OPTIMIZING SQL SERVER’S PERFORMANCE

  • Identifying the factors that affect performance
  • Designing a strategy for monitoring performance
  • Using performance monitoring tools

AUTOMATING SQL SERVER ADMINISTRATION

  • Overview of automating administration
  • Managing alerts
  • Automating tasks across multiple SQL servers
  • Automating the publication of data on the Web
  • Automating maintenance tasks
  • Troubleshooting

BACKING UP SQL SERVER DATABASES

  • Designing a backup strategy
  • Performing a backup
  • Automating backups
  • Implementing hardware fault tolerance

RESTORING SQL SERVER DATABASES

  • Identifying methods to restore databases
  • Restoring databases
  • Recovering system databases
  • Recovering data through a backup SQL server

DESIGN & IMPLEMENT REPLICATION

  • Understanding the components of SQL Server replication
  • Designing a replication strategy
  • Implementing publishing
  • Implementing subscribing
  • Implementing replication in a mixed environment
  • Publishing on the Internet

MANAGING REPLICATION

  • Monitoring replication
  • Troubleshooting replication

SQL Server End User Introduction

Course Duration

Two Days

Course Level

Introduction

Course Contents

INTRODUCTION TO SQL SERVER

  • What is SQL Server
  • What does SQL Server do?
  • Components of SQL Server
  • SQL Server and it’s Databases

SQL SERVER ADMINISTRATION TASKS

  • Common Administrative Components

DESIGNING & IMPLEMENTING DATABASES

  • What is a Relational Database?
  • Developing a Strategy for Implementing a Database
  • Creating a Database

FILEGROUPS

  • Fault Tolerance

MANAGING DATABASES

  • SQL Server & Data Transformation
  • Importing VS Linking
  • DTS Import and Export Wizards
  • Creating a DTS Package: Using a Wizard

CREATING & MANAGING TABLES

  • Table Properties
  • Creating a Table with Enterprise Manager
  • Working with Tables using Query Analyzer

BASIC UNDERSTANDING OF SQL

  • The SELECT Statement
  • Inserting Data
  • Updating Tables
  • Deleting Data

USING DATA STORED BY SQL SERVER VIA MICROSOFT OFFICE PROGRAMS

  • Defining the Data Source
  • Building the Query for Word with MS Query
  • Analysing Numeric Data with Microsoft Excel
  • Working with SQL Server Data via Microsoft Access

LINKING A REMOTE DATABASE TO SQL SERVER AUTOMATING SQL SERVER ADMINISTRATION

  • Registering a SQL Server
  • Web Assistant Wizard
  • The Backup Wizard
  • Maintenance Plan Wizard
  • Troubleshooting SQL Server

SQL Server Reporting Introduction

Course Duration

Two Days

Course Level

Introduction

Course Contents

CORE REPORT SKILLS

  • Exploring SQL server reporting services components
  • Report Designer
  • SQL Server Data Tools Visual Studio
  • Report models
  • The elements of a report
  • Creating a report with report builder or SSDT
  • Grouping table regions on a report
  • Joining data from multiple tables
  • Formatting report elements
  • Using functions in a report
  • Creating expressions
  • Displaying data in a matrix

INTERACTIVITY

  • Filtering data and adding parameters to a report
  • Customizing report parameters
  • Sorting data in a data region
  • Applying interactive sorting
  • Creating a drillthrough action to connect reports

DATA VISUALISATIONS

  • Introduction to charting in Reporting Services
  • Creating a column chart
  • Adding a generated average to a chart
  • Creating a pie chart
  • Using sparklines
  • Adding a sparkline to a drilldown matrix
  • Adding data bars

INDICATORS

  • Adding indicators to a report
  • Using and configuring gauges
  • Using maps in Reporting Services

LARGE REPORTS

  • Creating modular reports with report parts
  • Adding and updating report parts
  • Using subreports and nested regions
  • Configuring headers and footers
  • Printing and exporting reports
  • Using page breaks
  • Creating and using shared data sources
  • Creating and using shared data sets

CORE ADMIN TASKS

  • Organizing reports in Report Manager
  • Adding users and configuring report security
  • Configuring subscriptions
  • Creating a linked report
  • Using Report Designer in SQL Server Data Tools

SQL Server Integration Introduction

Course Duration

Two Days

Course Level

Introduction

Course Contents

SSIS TASKS

  • Task Editor & Expressions
  • Looping & Sequence Tasks
  • Analysis Services Tasks
  • Data Flow Tasks

THE DATA FLOW

  • Understanding the Data Flow
  • Data Viewers
  • Data Sources: OLE DB, Excel, Flat File, Raw, XML, ADO.NET
  • Data Destinations: Excel, Flat File, OLE DB, Raw, Recordset, Data Mining Model Training, DataReader, Dimension & Partition Processing
  • Common Transformations: Including Aggregate, Conditional Split, Data Conversion, Derived Column,Lookup, Script Component, Sort, Union All
  • Other Transformations: Including Audit, Copy Column, DQS Cleansing, Export & Import Column, Merge, Merge Join, Multicast
  • Data Flow Example

USING VARIABLES, PARAMETERS & EXPRESSIONS

  • Data Types
  • Using Variables & Parameters
  • Expression Builder

CONTAINERS

  • Task Host Containers
  • Sequence Containers
  • Groups
  • For Loop Container
  • Foreach Loop Container

JOINING DATA

  • Lookup Transformation
  • Merge Join Transformation
  • Contrasting SSIS & Relational Join

DATA CLEANSING PACKAGE

  • Creating Connections
  • Creating Control Flow
  • Creating Data Flow
  • Handling Dirty Data
  • Finalizing Data
  • Making the Package Dynamic

ADVANCED DATA CLEANSING

  • Advanced Derived Column
  • Advanced Fuzzy Lookup & Fuzzy Grouping
  • DQS Cleansing if applicable
  • Master Data Management

RELIABILITY & SCALABILITY

  • Control flow, containers, checkpoints
  • Single transaction, multiple transactions, multiple packages
  • Error Outputs
  • Scaling Out
  • Memory, Staging Data, Parallel Loading

ERROR & EVENT HANDLING

  • Precedence Constraints
  • Event Handling
  • Breakpoints
  • Error Rows
  • Logging

SQL Server Analytical Introduction

Course Duration

Two Days

Course Level

Introduction

Course Contents

WHAT IS MICROSOFT BI?

  • Define Business Intelligence
  • Understand the Cube Structure
  • Deploy and View a Sample Cube
  • View a Cube by using Excel
  • View a Cube by using SQL Reporting Services

OLAP MODELING

  • Understand Basic OLAP Modeling (star schema)
  • Understand Dimensional Modeling (stars and snowflakes)
  • Understand Measure (fact) and Cube Modeling

INTRODUCTION TO SSDT

  • Create Data Sources
  • Create Data Source Views
  • Create Cubes by using the Cube Wizard
  • Understand the Development Environment

INTERMEDIATE SSAS

  • Learn how to Create Key Performance Indicators
  • (KPIs)
  • Discover how to Create Perspectives
  • See how to Create Translations for Cubes and Dimensions
  • Review the three SSAS Action Object Types: Regular,
  • Drillthrough, and Reporting

ADVANCED SSAS

  • Work with Multiple Fact Tables and the Dimension
  • Usage Subtab in BIDS
  • Explore Advanced Dimension Types
  • Learn how to use the Business Intelligence Wizard
  • Understand Writeback in Dimensions
  • Review Semi-Additive Measures in OLAP Cubes

CUBE STORAGE AND AGGREGATION

  • View Aggregation Designs
  • Customize Aggregation Designs
  • Implement Proactive Caching
  • Use Relational and SSAS Partitions
  • Customize Cube and Dimension Processing

INTRODUCTION TO MDX QUERIES

  • Understand Basic MDX Syntax
  • Use the MDX Query Editor in SSMS
  • Understand Common MDX Functions and Tasks

INTRODUCTION TO DATA MINING

  • Understand Data Mining Concepts
  • Review the Algorithms that SSAS Includes
  • Consider Data Mining Clients
  • Understand Mining Structure Processing

SSAS ADMINISTRATION

  • Implement SSAS Security
  • Deploy and Synchronize Databases
  • Understand SSAS Database Backup and Restore

ADVANCED ADMINISTRATION AND OPTIMIZATION

  • Implement SSIS to Manage SSAS Databases
  • Explore Clustering
  • Explore Scalability Options
  • Understand Performance Tuning and Optimization

INTRODUCTION TO SSAS CLIENTS

  • Design Reports using Reporting Services
  • Design Reports by using Report Builder
  • Implement Excel Pivot Tables and Charts
  • Use Excel as a Data Mining Client
  • Review Microsoft Office SharePoint Server