Querying Microsoft SQL Server 2012 (exam 70-461) 10:30 hours

Microsoft SQL Server 2012: Creating Database Objects

Course: 2 Hours

• Data Types in SQL Server 2012 Creating Tables to Store Data in SQL Server 2012 Implementing
• Data Integrity with Constraints Creating Tables and Constraints in SQL Server 2012

Microsoft SQL Server 2012: Querying Basics and Modifying Data

Course: 1 Hour, 30 Minutes

• Using Basic SELECT Queries to Retrieve Data
• Using Advanced Queries to Retrieve Data
• Retrieving Data using SELECT in SQL Server 2012

Microsoft SQL Server 2012: Manipulate Data Using Operators and Functions

Course: 2 Hours

• Aggregate, Analytic, and Ranking Functions
• Built-in Scalar Functions
• Using Built-in Functions to Manipulate Data

Microsoft SQL Server 2012: Creating Functions and Triggers

Course: 1 Hour

• Transaction Control Statements
• Creating User-Defined Functions
• Creating DML Triggers
• Creating Functions and Triggers in SQL Server 2012

Microsoft SQL Server 2012: Creating Programming Objects and Optimizing Queries

Course: 2 Hours

• Implementing the XML Data Type and Schemas
• Loading and Managing XML Data
• Querying XML Data using XQuery
• Using FOR XML and OPENXML
• Managing XML Data in SQL Server 2012

Microsoft SQL Server 2012: Managing XML Data

Course: 2 Hours

• Implementing the XML Data Type and Schemas
• Loading and Managing XML Data
• Querying XML Data using XQuery
• Using FOR XML and OPENXML
• Managing XML Data in SQL Server 2012

TestPrep 70-461 Querying Microsoft SQL Server 2012

Administering Microsoft SQL Server 2012 Databases (exam 70-462) 19:30 hours

Microsoft SQL Server 2012: Installation and Upgrade

Course: 2 Hours, 30 Minutes

• Planning a SQL Server 2012 Install
• Planning SQL Server 2012 Security Considerations
• Installing SQL Server 2012
• Performing a SQL Server 2012 Installation

Microsoft SQL Server 2012: Instance Configuration and Database Creation

Course: 2 Hours

• Configuring SQL Server 2012 Instances
• Adding Components to SQL Server 2012
• Configuring the Instance and Components

Microsoft SQL Server 2012: Managing Databases and Automating Tasks

Course: 2 Hours

• Managing SQL Server 2012 Databases
• Database Mail Configuration
• Managing Database in SQL Server 2012

Microsoft SQL Server 2012: Security Management

Course: 2 Hours, 30 Minutes

• Resource Security in SQL Server 2012
• Creating Logins, Credentials, and Roles
• Creating Schemas, Users, and Implementing Permissions
• Data Encryption in SQL Server 2012
• Creating Resource Security Objects in SQL Server 2012

Microsoft SQL Server 2012: Managing Database Data

Course: 2 Hours

• Implementing FILESTREAM
• Implementing FileTables
• Implementing Data Compression
• Managing Database Storage Components in SQL Server 2012

Microsoft SQL Server 2012: Managing Indexes, Full-Text Search and Partitions

Course: 2 Hours, 30 Minutes

• Indexes in SQL Server 2012
• Creating Indexes in SQL Server 2012
• Managing Indexes in SQL Server 2012
• Creating and Managing Indexes in SQL Server 2012

Microsoft SQL Server 2012: Backing Up and Restoring Databases

Course: 1 Hour, 30 Minutes

• Recovery Models and Backup Types in SQL Server 2012
• Performing SQL Server 2012 Database Backups
• Restoring Full Database Backups
• Completing File, Page, and Piecemeal Database Restores
• Performing Backups and Restores in SQL Server 2012

Microsoft SQL Server 2012: Database Monitoring and Performance Tuning

Course: 2 Hours, 30 Minutes

• Using SQL Server Profiler to Monitor a Database
• Monitoring the Performance of SQL Server 2012 Instances
• Using Extended Events and Data Collectors
• Resource Management with Resource Governor
• Monitoring and Managing Performance of SQL Server 2012

Microsoft SQL Server 2012: Configuring High Availability

Course: 2 Hours

• Overview of High Availability Solutions
• Configuring Always On Failover Clustering
• Configuring Always On Availability Groups
• Configuring Database Mirroring
• Configuring Replication
• Configuring High Availability Solutions

TestPrep 70-462 Administering Microsoft SQL Server 2012 Databases

Implementing a Data Warehouse with Microsoft SQL Server 2012 (exam 70-463) 20:05 hours

Designing and Implementing Dimensions

Course: 1 Hour, 10 Minutes

• Course Introduction
• The SQL Server Business Intelligence platform
• Changing Dimensions
• Dimension Tables and Attributes8
• Hierarchies
• Schema Options
• Fact Tables
• Auditing and Lineage
• Keys
• Implement Dimensions
• Implement Data Lineage

Designing a Fact Table

Course: 1 Hour, 7 Minutes

• Course Introduction
• Slowly Changing Dimensions Transformation
• Implementing Transformations
• Processing Options
• Identity Mapping
• Deduplication
• Fuzzy Lookup
• Implementing a Fuzzy Lookup
• Erroneous Rows
• Auditing

Connection Managers and Data Sources

Course: 1 Hour, 15 Minutes

• Course Introduction
• Data Flow Components
• Using Data Flows
• Debugging Data Flow
• SQL/SSIS Transformations and Packages
• SSIS Lookups
• Data Sources and Destinations
• Extracting, Transforming, and Loading Data
• Performance Tuning, Optimization, and Integrity

Designing Data Flow

Course: 1 Hour, 5 Minutes

• Course Introduction
• Designing a Package Execution Strategy
• Scheduling Package Execution
• Implementing Package Execution
• Executing Packages Using SSMS
• Monitoring Execution Using Management Studio
• Using DTEXEC and DTEXECUI
• Executing Packages Using PowerShell
• ETL Restartability

Data Flow Implementation

Course: 1 Hour, 6 Minutes

• Course Introduction
• Control Flow
• Containers and Tasks
• Precedence Constraints
• SSIS Package Transaction Strategy
• Event Handlers, Variables, Parameters
• Connection Managers
• Custom Tasks
• Logging a Package

SSIS Package Execution and Script Tasks

Course: 1 Hour, 19 Minutes

• Course Introduction
• Checkpoints
• Debug Control Flow
• Implement Control Flow
• Data Profiling
• Sequence and Loop Containers
• Transactions in SSIS Packages
• Parallelism
• Controlling Task Execution Sequences
• Package Templates
• Execute Package Tasks

Designing Control Flow

Course: 1 Hour, 4 Minutes

• Course Introduction
• Designing a Package Execution Strategy
• Scheduling Package Execution
• Implementing Package Execution
• Executing Packages Using SSMS
• Monitoring Execution Using Management Studio
• Using DTEXEC and DTEXECUI
• Executing Packages Using PowerShell
• ETL Restartability

Implementing Control Flow

Course: 1 Hour, 12 Minutes

• Course Introduction
• User Variables
• Variable Scope
• Data Types
• Parameterization of Properties
• Precedence Constraints
• SSIS System Variables

SSIS Variables

Course: 39 Minutes

• Course Introduction
• Checkpoints
• Debug Control Flow
• Implement Control Flow
• Data Profiling
• Sequence and Loop Containers
• Transactions in SSIS Packages
• Parallelism
• Controlling Task Execution Sequences
• Package Templates
• Execute Package Tasks

SSIS Packages

Course: 1 Hour, 41 Minutes

• Course Introduction
• Designing SSIS Packages
• Package Configurations for Files
• Package Configurations for SQL tables
• Expressions
• Package and Project Parameters
• Project Level Connection Managers
• Expressions for Connection Managers
• Package Environments
• Package Configurations
• Package and Project Level Parameters
• Project Connection Managers
• Property Expressions

Data Load Options

Course: 1 Hour

• Data Loading
• Incremental Data Load Strategy
• Full Data Load Strategy
• Data Mart
• Data Mart: Incremental Updates
• Indexed Tables
• Bulk Load Options
• Load Technique: SSIS Destination
• Load Technique: T-SQL
• Load Partitioned Tables

Script Components

Course: 47 Minutes

• Course Introduction
• SCD Changes
• Script Components in SSIS
• Source Components
• Transformation Components
• Destination Components
• Web Service Source and Destination
  Error Messages

SSIS Components

Course: 1 Hour, 40 Minutes

• Course Introduction
• SSIS Components
• Preparing to Install Integration Services
• Integration Services Configuration Options
• Installing Integration Services
• SSIS Designer
• Development Box and Server
• Remote Package Execution
• 32 & 64-bit Installation
• Upgrading
• Provisioning Accounts
• Creating Catalogs
• Creating Object Identifiers
• Catalog Configuration

Auditing, Logging, and Event Handling

Course: 51 Minutes

• Course Introduction
• Auditing Using System Variables
• Propagating Events
• Log Providers
• Logging SSIS Execution
• Alerts and Notification Mechanisms
• Event Handlers
• Custom Logging

Troubleshooting Data Integration

Course: 1 Hour

• Course Introduction
• Performance Issues
• Connectivity Issues
• Troubleshooting Tasks
• Logic Issues
• SSIS Logging Infrastructure
• SSIS Package Failure
• Break Points
• Data Viewers
• Profiling Data

SSIS Solutions

Course: 50 Minutes

• Course Introduction
• The SSIS Catalog
• Backing Up and Restoring the SSIS Catalog
• Upgrading the SSIS Catalog
• Creating a Deployment Utility
• Deploy Packages by Using the Deployment Utility
• SSIS Packages: SQL or File System Locations
• Deploying Packages on Multiple Servers
• Validating Packages
• Installing Custom Components and Tasks
• DTUTIL

SSIS Security

Course: 1 Hour, 4 Minutes

• Course Introduction
• Database Roles
• Parameters and Package Deployment Model
• Parameters and Project Deployment Model
• Package Protection Levels
• Package and Project Protection Level Dialog Box
• Secure Deployments
• SSIS Packages Deployed at the File System
• Create Sensitive Parameters
• Set Parameter Values Post Deployment

Data Quality Services (DQS) and Master Data Services (MDS)

Course: 1 Hour, 15 Minutes

• Course Introduction
• Installation Prerequisites
• Installing Data Quality Services
• DQ Roles
• Identity Analysis

Data Quality Projects

Course: 50 Minutes

• Course Introduction
• Data Quality Knowledge Base Management
• Data Quality Client
• Data Quality Knowledge Base Domains
• Data Quality Projects
• Cleansing Data
• Deduplication
• History
• Managing Knowledge Base Domains

TestPrep 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012TestPrep 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012