SQL Server Administrator Introduction - 3 Days
Objectives
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 environ- ment
Publishing on the Internet MANAGING REPLICATION Monitoring replication
Troubleshooting replication
SQL Server End User Introduction - 2 Days
Objectives
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 MI- CROSOFT 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 Services Introduction - 2 Days
Objectives
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 Services Introduction - 2 Days
Objectives
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, DataRead- er, Dimension & Partition Processing
Common Transformations: Including Aggregate, Conditional Split, Data Conversion, Derived Column, Lookup, Script Component, Sort, Union All
Other Transformations: Including Audit, Copy Col- umn, 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
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
SQL Server Analytical Services Introduction - 2 Days
Objectives
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 snow- flakes)
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 Di- mensions
Review the three SSAS Action Object Types: Regu- lar, 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 OPTIMIZA- TION
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