Zln treinamentos

Especialize, agilize e atualize o seu negócio

Treinamentos & cursos


FORMAÇÃO MCSA


10774AC – modelagem de dados e consultas Microsoft® SQL Server® 2012

O curso fornece aos alunos as habilidades técnicas necessárias para escrever consultas Transact-SQL básicos para Microsoft SQL Server 2012. Este curso é a base para todas as disciplinas relacionadas no SQL Server, ou seja, Administração de Banco de Dados, Desenvolvimento de Banco de Dados e Business Intelligence. Este curso ajuda as pessoas a se preparar para o exame 70-461.

PÚBLICO-ALVO:

Este curso é destinado a administradores de banco de dados, desenvolvedores de banco de dados a profissionais de Business Intelligence. O curso irá muito provavelmente ser bem atendido por usuários avançados de SQL que não são necessariamente de banco de dados com foco ou plano de fazer o exame, ou seja, o relatório escritores, analistas de negócios e desenvolvedores de aplicativos cliente.

Na conclusão do curso:

Depois de concluir este curso, os alunos serão capazes de:
• Escrever consultas SELECT
• consulta várias tabelas
• Use built-in funções
• Usar subconsultas
• Executar procedimentos armazenados
• Use operadores de conjunto
• Implementar o tratamento de erros
• implementar transações
• Use expressões de tabela
• Ordenar e filtrar dados
• Use classificação janela, offset e funções agregadas
• consulta de metadados do SQL Server
• Programa com T-SQL
• Melhorar o desempenho da consulta

CONTEÚDO PROGRAMÁTICO:

PARTE 1:
Princípios básicos de modelagem de dados
A linguagem de consulta estruturada SQL;
Formas normais (1FN, 2FN e 3FN)
Tabelas e relacionamentos;
Chaves primária, estrangeira, candidata e alternativa;
Cardinalidade

PARTE 2:
consultas Microsoft SQL Server 2012

Module 1: Introdução ao Microsoft SQL 2012
Lessons
Introducing Microsoft SQL Server 2012
Getting Started with SQL Server Management StudioLab : Working with SQL Server 2012 Tools
Working with SQL Server Management Studio
Creating and Organizing T-SQL scripts
Using Books Online

Module 2: Introdução ao SQL Azure
Lessons
Overview of SQL Azure
Working with SQL Azure

Module 3: Introdução à Consultas T-SQL
Lessons
Introducing T-SQL
Understanding Sets
Understanding Predicate Logic
Understanding the Logical Order of Operations in SELECT statements
Lab : Introduction to Transact-SQL Querying
Executing Basic SELECT Statements
Executing queries which filter data using predicates
Executing queries which sort data using ORDER BY

Module 4: Escrevendo consultas de SELECT
Lessons
Writing Simple SELECT Statements
Eliminating Duplicates with DISTINCT
Using Column and Table Aliases
Writing Simple CASE Expressions
Lab : Writing Basic SELECT Statements
Write simple SELECT Statements
Eliminate Duplicates Using Distinct
Use Table and Column Aliases
Use a Simple CASE Expression

Module 5: Querying Multiple Tables
Lessons
Understanding Joins
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins
Lab : Querying Multiple Tables
Writing Queries That Use Inner Joins
Writing Queries That Use Multiple-Table Inner Join
Writing Queries That Use Self Joins
Writing Queries That Use Outer Loins
Writing Queries That Use Cross Join

Module 6: Sorting and Filtering Data
Lessons
Sorting Data
Filtering Data with a WHERE Clause
Filtering with the TOP and OFFSET-FETCH Options
Working with Unknown and Missing Values
Lab : Sorting and Filtering Data
Writing Queries That Filter Data Using a WHERE Clause
Writing Queries That Filter Data Using an ORDER BY Clause
Writing Queries That Filter Data Using the TOP Option
Writing Queries That Filter Data Using the OFFSET-FETCH Clause

Module 7: Working with SQL Server 2012 Data Types
Lessons
Introducing SQL Server 2012 Data Types
Working with Character Data
Working with Date and Time Data
Lab : Working with SQL Server 2012 Data Types
Writing Queries That Return Date and Time Data
Writing Queries That Use Date and Time Functions
Writing Queries That Return Character Data
Writing Queries That Use Character Functions

Module 8: Using Built-In Functions
Lessons
Writing Queries with Built-In Functions
Using Conversion Functions
Using Logical Functions
Using Functions to Work with NULL
Lab : Using Built-In Functions
Write queries which use conversion functions
Write queries which use logical functions
Write queries which test for nullability

Module 9: Grouping and Aggregating Data
Lessons
Using Aggregate Functions
Using the GROUP BY Clause
Filtering Groups with HAVING
Lab : Grouping and Aggregating Data
Write queries which use the GROUP BY clause
Write queries which use aggregate functions
Write queries which use distinct aggregate functions
Write queries which filter groups with the HAVING clause

Module 10: Using Subqueries
Lessons
Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries
Lab : Using Subqueries
Write queries which use self-contained subqueries
Write queries which use scalar and multi-result subqueries
Write queries which use correlated subqueries and EXISTS predicate

Module 11: Using Table Expressions
Lessons
Using Derived Tables
Using Common Table Expressions
Using Views
Using Inline Table-Valued Functions
Lab : Using Table Expressions
Write Queries Which Use Views
Write Queries Which Use Derived Tables
Write Queries Which Use Common Table Expressions
Write Queries Which Use Inline Table-Valued Functions

Module 12: Using Set Operators
Lessons
Writing Queries with the UNION Operator
Using EXCEPT and INTERSECT
Using APPLY
Lab : Using Set Operators
Write queries which use UNION set operators and UNION ALL multi-set operators
Write queries which use CROSS APPLY and OUTER APPLY operators
Write queries which use EXCEPT and INTERSECT operators

Module 13: Using Window Ranking, Offset and Aggregate Functions
Lessons
Creating Windows with OVER
Exploring Window Functions
Lab : Using Window Ranking, Offset and Aggregate Functions
Write queries which use ranking functions
Write queries which use offset functions
Write queries which use window aggregate functions

Module 14: Pivoting and Grouping Sets
Lessons
Writing Queries with PIVOT and UNPIVOT
Working with Grouping Sets
Lab : Pivoting and Grouping Sets
Write queries which use the PIVOT operator
Write queries which use the UNPIVOT operator
Write queries which use the GROUPING SETS subclause

Module 15: Querying SQL Server Metadata
Lessons
Querying System Catalog Views and Functions
Executing System Stored Procedures
Querying Dynamic Management Objects
Lab : Querying SQL Server Metadata
Querying System Catalog Views
Querying System Functions
Querying System Dynamic Management Views

Module 16: Executing Stored Procedures
Lessons
Querying Data with Stored Procedures
Passing Parameters to Stored Procedures
Creating Simple Stored Procedures
Working with Dynamic SQL
Lab : Executing Stored Procedures
Use the EXECUTE statement to invoke stored procedures
Pass parameters to stored procedures
Execute system stored procedures

Module 17: Programming with T-SQL
Lessons
T-SQL Programming Elements
Controlling Program Flow
Lab : Programming with T-SQL
Declaring Variables and Delimiting Batches
Using Control-of-Flow Elements
Generating Dynamic SQL
Using Synonyms

Module 18: Implementing Error Handling
Lessons
Using TRY / CATCH Blocks
Working with Error Information
Lab : Implementing Error Handling
Redirecting Errors with TRY / CATCH
Using THROW to Pass an Error Message Back to a Client

Module 19: Implementing Transactions
Lessons
Transactions and the Database Engine
Controlling Transactions
Lab : Implementing Transactions
Controlling transactions with BEGIN, COMMIT, and ROLLBACK
Adding error handling to a CATCH block

Module 20: Improving Query Performance
Lessons
Factors in Query Performance
Displaying Query Performance Data
Lab : Improving Query Performance
Viewing Query Execution Plans
Viewing Index Usage and Using SET STATISTICS Statements

INFORMAÇÕES ADICIONAIS:

Carga Horária: 40H
Idioma:Apenas em Inglês
Material didático: Oficial Microsoft Digital.

OBSERVAÇÃO:

Os treinamentos serão ministrados com 01 aluno por computador, tendo no mínimo 06 (seis) alunos por turma ou no máximo 08 (oito).

A Zillion Treinamentos se reserva no direito de não oferecer o curso, caso não seja preenchido o número mínimo de vagas.




10775AC – Administração Microsoft® SQL Server® 2012 Databases


APRESENTAÇÃO:

This five-day instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2012 database. The course focuses on teaching individuals how to use SQL Server 2012 product features and tools related to maintaining a database. Also this course helps you prepare for the Exam 70-462.

PÚBLICO-ALVO:

The primary audience for this course is individuals who administer and maintain SQL Server databases. These individuals perform database administration and maintenance as their primary area of responsibility, or work in environments where databases play a key role in their primary job.The secondary audiences for this course are individuals who develop applications that deliver content from SQL Server databases.

Pré-requisito:

In addition to their professional experience, students who attend this training should already have the following technical knowledge:
Basic knowledge of the Microsoft Windows operating system and its core functionality.
Working knowledge of Transact-SQL.
Working knowledge of relational databases.
Some experience with database design.
Students who attend this training can meet the prerequisites by attending the following courses, or obtaining equivalent knowledge and skills:
10774A: Writing T-SQL Queries for Microsoft SQL Server 2012

Na conclusão do curso:

Plan and install SQL Server.
Describes the system databases, the physical structure of databases and the most common configuration options related to them.
Explain the concept of the transaction log and SQL Server recovery models and implement different backup strategies available with SQL Server.
Create SQL Server Backups.
Restore SQL Server databases.
Use the import/export wizards and explain how they relate to SSIS.
Work with SQL Server security models, logins and users.
Work with fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.
Work with permissions and the assignment of permissions.
Work with SQL Server Audit.
Work with SQL Server Agent, jobs and job history.
Implement SQL Server agent security, proxy accounts and credentials.
Configure database mail, alerts and notifications.
Create database maintenance plans.
Work with SQL Profiler and SQL Trace stored procedures.
Introduce DMVs and the configuration of data collection.
Work with Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
Troubleshoot SQL Server databases.

CONTEÚDO PROGRAMÁTICO

Module 1: Introduction to SQL Server 2012 and its Toolset
This module introduces the entire SQL Server platform and its major tools. It covers editions, versions, basics of network listeners, and concepts of services and service accounts.
Lessons
Introduction to the SQL Server Platform
Working with SQL Server Tools
Configuring SQL Server Services
Lab : Introduction to SQL Server and its Toolset
Verifying SQL Server Component Installation
Altering Service Accounts for New Instance
Enabling Named Pipes Protocol for Both Instances
Creating an Alias for AdvDev
Ensuring SQL Browser is Disabled and Configure a Fixed TCP/IP Port (Only if time permits


Module 2: Preparing Systems for SQL Server 2012
This module covers planning for an installation related to SQL Server I/O requirements, 32 bit vs 64 bit, memory configuration options and I/O subsystem pre-installation checks using SQLIOSim and SQLIO.
Lessons
Overview of SQL Server Architecture
Planning Server Resource Requirements
Pre-installation Testing for SQL Server
Lab : Preparing Systems for SQL Server
Adjust memory configuration
Pre-installation Stress Testing
Check Specific I/O Operations

Module 3: Installing and Configuring SQL Server 2012
This module details installing and configuring SQL Server.
Lessons
Preparing to Install SQL Server
Installing SQL Server
Upgrading and Automating Installation
Lab : Installing and Configuring SQL Server
Review installation requirements
Install the SQL Server instance
Perform Post-installation Setup and Checks
Configure Server Memory

Module 4: Working with Databases
This module describes how data is stored in databases, how to create databases, and how to move databases either within a server or between servers.
Lessons
Overview of SQL Server Databases
Working with Files and Filegroups
Moving Database Files
Lab : Working with Databases
Adjust tempdb configuration
Create the RateTracking database
Attach the OldProspects database
Add multiple files to tempdb

Module 5: Understanding SQL Server 2012 Recovery Models
This module describes the concept of the transaction log and SQL Server recovery models. It introduces the different backup strategies available with SQL Server.
Lessons
Backup Strategies
Understanding SQL Server Transaction Logging
Planning a SQL Server Backup Strategy
Lab : Understanding SQL Server Recovery Models
Plan a backup strategy
Configure Recovery Models
Review recovery models and strategy

Module 6: Backup of SQL Server 2012 Databases
This module describes SQL Server Backup and the backup types.
Lessons
Backing up Databases and Transaction Logs
Managing Database Backups
Working with Backup Options
Lab : Backup of SQL Server Databases
Investigate backup compression
Transaction log backup
Differential backup
Copy-only backup
Partial backup

Module 7: Restoring SQL Server 2012 Databases
This module describes the restoration of databases.
Lessons
Understanding the Restore Process
Restoring Databases
Working with Point-in-time recovery
Restoring System Databases and Individual Files
Lab : Restoring SQL Server 2012 Databases
Determine a restore strategy
Restore the database
Using STANDBY mode

Module 8: Importing and Exporting Data
This module covers the use of the import/export wizards and explains how they relate to SSIS. Also introduces BCP.
Lessons
Transferring Data To/From SQL Server
Importing and Exporting Table Data
Inserting Data in Bulk
Lab : Importing and Exporting Data
Import the Excel spreadsheet
Import the CSV file
Create and test an extraction package
Compare loading performance

Module 9: Authenticating and Authorizing Users
This module covers SQL Server security models, logins and users.
Lessons
Authenticating Connections to SQL Server
Authorizing Logins to Access Databases
Authorization Across Servers
Lab : Authenticating and Authorizing Users
Create Logins
Correct an Application Login Issue
Create Database Users
Correct Access to Restored

Module 10: Assigning Server and Database Roles
This module covers fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.
Lessons
Working with Server Roles
Working with Fixed Database Roles
Creating User-defined Database Roles
Lab : Assigning Server and Database Roles
Assign Server Roles
Assign Fixed Database Roles
Create and Assign User-defined Database Roles
Check Role Assignments

Module 11: Authorizing Users to Access Resources
This module covers permissions and the assignment of permissions.
Lessons
Authorizing User Access to Objects
Authorizing Users to Execute Code
Configuring Permissions at the Schema Level
Lab : Authorizing Users to Access Resources
Assign Schema-level Permissions
Assign Object-level Permissions
Test Permissions

Module 12: Auditing SQL Server Environments
This module covers SQL Server Audit.
Lessons
Options for Auditing Data Access in SQL
Implementing SQL Server Audit
Managing SQL Server Audit
Lab : Auditing SQL Server Environments
Determine audit configuration and create audit
Create server audit specifications
Create database audit specifications
Test audit functionality

Module 13: Automating SQL Server 2012 Management
This module covers SQL Server Agent, jobs and job history.
Lessons
Automating SQL Server Management
Working with SQL Server Agent
Managing SQL Server Agent Jobs
Lab : Automating SQL Server Management
Create a Data Extraction Job
Schedule the Data Extraction Job
Troubleshoot a Failing Job

Module 14: Configuring Security for SQL Server Agent
This module covers SQL Server agent security, proxy accounts and credentials.
Lessons
Understanding SQL Server Agent Security
Configuring Credentials
Configuring Proxy Accounts
Lab : Configuring Security for SQL Server Agent
Troubleshoot job execution failure
Resolve the security issue
Perform further troubleshooting

Module 15: Monitoring SQL Server 2012 with Alerts and Notifications
This module covers the configuration of database mail, alerts and notifications.
Lessons
Configuration of Database Mail
Monitoring SQL Server Errors
Configuring Operators, Alerts and Notifications
Lab : Monitoring SQL Agent Jobs with Alerts and Notifications
Configure Database Mail
Implement Notifications
Implement Alerts

Module 16: Performing Ongoing Database Maintenance
This module covers database maintenance plans.
Lessons
Ensuring Database Integrity
Maintaining Indexes
Automating Routine Database Maintenance
Lab : Performing Ongoing Database Maintenance
Check database integrity using DBCC CHECKDB
Correct index fragmentation
Create a database maintenance plan
Investigate table lock performance

Module 17: Tracing Access to SQL Server 2012
This module covers SQL Profiler and SQL Trace stored procedures.
Lessons
Capturing Activity using SQL Server Profiler
Improving Performance with the Database Engine Tuning Advisor
Working with Tracing Options
Lab : Tracing Access to SQL Server 2012
Capture a trace using SQL Server Profiler
Analyze a trace using Database Engine Tuning Advisor
Configure SQL Trace

Module 18: Monitoring SQL Server 2012
This module introduces DMVs and the configuration of data collection.
Lessons
Monitoring Activity
Capturing and Managing Performance Data
Analyzing Collected Performance Data
Lab : Monitoring SQL Server 2012
Investigating DMVs
Configure Management Data Warehouse
Configure Instances for Data Collection
Work with Data Collector Reports

Module 19: Managing Multiple Servers
This module covers Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
Lessons
Working with Multiple Servers
Virtualizing SQL Server
Deploying and Upgrading Data-Tier Applications
Lab : Managing Multiple Servers
Configure CMS and execute multi-server queries
Deploy a data-tier application
Register and extract a data-tier application
Upgrade a data-tier application

Module 20: Troubleshooting Common SQL Server 2012 Administrative Issues
This module covers common issues that require troubleshooting and gives guidance on where to start looking for solutions.
Lessons
SQL Server Troubleshooting Methodology
Resolving Service-related Issues
Resolving Concurrency Issues
Resolving Login and Connectivity Issues
Lab : Troubleshooting Common Issues
Troubleshoot and resolve SQL Server administrative issues

INFORMAÇÕES ADICIONAIS:

Carga Horária: 40H
Idioma: Apenas em Inglês
Material didático: Oficial Microsoft Digital.

OBSERVAÇÃO:

Os treinamentos serão ministrados com 01 aluno por computador, tendo no mínimo 06 (seis) alunos por turma ou no máximo 08 (oito).

A Zillion Treinamentos se reserva no direito de não oferecer o curso, caso não seja preenchido o número mínimo de vagas.




10777AC – Implementing a Data Warehouse with Microsoft® SQL Server® 2012


APRESENTAÇÃO:

Course focuses on teaching individuals how to create a data warehouse with SQL Server 2012, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services. This course helps people prepare for exam 70-463.

PÚBLICO-ALVO:

This course is intended for database professionals who need to fulfill a Business Intelligence Developer role. They will need to focus on hands-on work creating BI solutions including Data Warehouse implementation, ETL, and data cleansing. Primary responsibilities include:
Implementing a data warehouse.
Developing SQL Server Integration Services (SSIS) packages for data extraction, transformation, and loading (ETL).
Enforcing data integrity by using Master Data Services.
Cleansing data by using Data Quality Services

Pré-requisito:

Before attending this course, student should have:At least 2 years’ experience of working with relational databases, including:
Designing a normalized database.
Creating tables and relationships.
Querying with Transact-SQL.Some exposure to basic programming constructs (such as looping and branching).
An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.

Na conclusão do curso:

After completing this course, students will be able to:
Describe data warehouse concepts and architecture considerations.
Select an appropriate hardware platform for a data warehouse.
Design and implement a data warehouse.
Implement Data Flow in an SSIS Package.
Implement Control Flow in an SSIS Package.
Debug and Troubleshoot SSIS packages.
Implement an SSIS solution that supports incremental data warehouse loads and changing data.
Integrate cloud data into a data warehouse ecosystem infrastructure.
Implement data cleansing by using Microsoft Data Quality Services.
Implement Master Data Services to enforce data integrity.
Extend SSIS with custom scripts and components.
Deploy and Configure SSIS packages.
Describe how information workers can consume data from the data warehouse.

CONTEÚDO PROGRAMÁTICO

Module 1: Introduction to Data Warehousing
This module provides an introduction to the key components of a data warehousing solution and the high-level considerations you must take into account when starting a data warehousing project.
Lessons
Overview of Data Warehousing
Considerations for a Data Warehouse Solution
Lab : Exploring a Data Warehousing Solution
Exploring data sources
Exploring an ETL solution
Exploring a data warehouse

Module 2: Data Warehouse Hardware
This module describes the characteristics of typical data warehouse workloads, and explains how you can use reference architectures and data warehouse appliances to ensure you build the system that is right for your organization.
Lessons
Considerations for Building a Data Warehouse
Data Warehouse Reference Architectures and Appliances

Module 3: Designing and Implementing a Data Warehouse
In this module, you will learn how to implement the logical and physical architecture of a data warehouse based on industry-proven design principles.
Lessons
Logical Design for a Data Warehouse
Physical Design for a Data Warehouse
Lab : Implementing a Data Warehouse Schema
Implementing a Star Schema
Implementing a Snowflake Schema
Implementing a Time Dimension Table

Module 4: Creating an ETL Solution with SSIS
This module discusses considerations for implementing an ETL process, and then focuses on SQL Server Integration Services (SSIS) as a platform for building ETL solutions.
Lessons
Introduction to ETL with SSIS
Exploring Source Data
Implementing Data Flow
Lab : Implementing Data Flow in a SSIS Package
Exploring Source Data
Transferring Data by Using a Data Flow Task
Using Transformations in a Data Flow

Module 5: Implementing Control Flow in an SSIS Package
Control flow in SQL Server Integration Services packages enables you to implement complex ETL solutions that combine multiple tasks and workflow logic. This module covers how to implement control flow, and design robust ETL processes for a data warehousing solution that coordinate data flow operations with other automated tasks.
Lessons
Introduction to Control Flow
Creating Dynamic Packages
Using Containers
Managing Consistency
Lab : Implementing Control Flow in an SSIS Package
Using Tasks and Precedence in a Control Flow
Using Variables and Parameters
Using Containers
Lab : Using Transactions and Checkpoints
Using Transactions
Using Checkpoints

Module 6: Debugging and Troubleshooting SSIS Packages
This module describes how you can debug SQL Server Integration Services (SSIS) packages to find the cause of errors that occur during execution. Then module then covers the logging functionality built into SSIS you can use to log events for troubleshooting purposes. Finally, the module describes common approaches for handling errors in control flow and data flow.
Lessons
Debugging an SSIS Package
Logging SSIS Package Events
Handling Errors in an SSIS Package
Lab : Debugging and Troubleshooting an SSIS Package
Debugging an SSIS Package
Logging SSIS Package Execution
Implementing an Event Handler
Handling Errors in a Data Flow

Module 7: Implementing an Incremental ETL Process
This module describes the techniques you can use to implement an incremental data warehouse refresh process.
Lessons
Introduction to Incremental ETL
Extracting Modified Data
Loading Modified Data
Lab : Extracting Modified Data
Using a DateTime Column to Incrementally Extract Data
Using a Change Data Capture
Using Change Tracking
Lab : Loading Incremental Changes
Using a Lookup Transformation to Insert Dimension Data
Using a Lookup Transformation to Insert or Update Dimension Data
Implementing a Slowly Changing Dimension
Using a MERGE Statement to Load Fact Data

Module 8: Incorporating Data from the Cloud into a Data Warehouse
In this module, you will learn about how you can use cloud computing in your data warehouse infrastructure and learn about the tools and services available from the Microsoft Azure Marketplace.
Lessons
Overview of Cloud Data Sources
SQL Server Database
The Windows Azure Marketplace
Lab : Using Cloud Data in a Data Warehouse Solution
Creating a SQL Azure Database
Extracting Data from a SQL Azure Database
Obtaining Data from the Windows Azure Marketplace

Module 9: Enforcing Data Quality
Ensuring the high quality of data is essential if the results of data analysis are to be trusted. This module explains how to use the SQL Server 2012 Data Quality Services (DQS) to provide a computer assisted process for cleansing data values and identifying and removing duplicate data entities.
Lessons
Introduction to Data Quality
Using Data Quality Services to Cleanse Data
Using Data Quality Services to Match Data
Lab : Cleansing Data
Creating a DQS Knowledge Base
Using a DQS Project to Cleanse Data
Using DQS in an SSIS Package
Lab : Deduplicating Data
Creating a Matching Policy
Using a DQS Project to Match Data

Module 10: Using Master Data Services
This module introduces Master Data Services and explains the benefits of using it in a data warehousing context. The module also describes the key configuration options for Master Data Services, and explains how to import and export data. Finally, the module explains how to apply rules that help to preserve data integrity, and introduces the new Master Data Services Add-in for Excel.
Lessons
Introduction to Master Data Services
Implementing a Master Data Services Model
Using the Master Data Services Add-in for Excel
Lab : Implementing Master Data Services
Creating a Basic Model
Editing a Model by Using the Master Data Services Add-in for Excel
Loading Data into a Model
Enforcing Business Rules
Consuming Master Data Services Data

Module 11: Extending SQL Server Integration Services
This module describes the techniques you can use to extend SQL Server Integration Services (SSIS). The module is not designed to be a comprehensive guide to developing custom SSIS solutions, but to provide an awareness of the fundamental steps required to use custom components and scripts in an ETL process that is based on SSIS.
Lessons
Using Custom Components in SSIS
Using Scripts in SSIS
Lab : Using Custom Components and Scripts
Using a Custom Component
Using a Script Task

Module 12: Deploying and Configuring SSIS Packages
SQL Server Integration Services provides tools that make it easy to deploy packages to another computer. The deployment tools also manage any dependencies, such as configurations and files that the package needs. In this module, you will learn how to use these tools to install packages and their dependencies on a target computer.
Lessons
Overview of SSIS Deployment
Deploying SSIS Projects
Planning SSIS Package Execution

Lab : Deploying and Configuring SSIS Packages
Create a SSIS Catalog
Deploy an SSIS Project
Create Environments for an SSIS Solution
Running an SSIS Package in SQL Server Management Studio
Scheduling SSIS Packages with SQL Server Agent

Module 13: Consuming Data in a Data Warehouse
This module introduces Business Intelligence (BI), describes the components of SQL Server that you can use to create a BI solution, and the client tools that users can use to create reports and analyze data.
Lessons
Introduction to Business Intelligence
Introduction to Reporting
Introduction to Data Analysis
Lab : Using Business Intelligence Tools
Exploring a Reporting Services Report
Exploring a PowerPivot Workbook
Exploring a Power View Report

INFORMAÇÕES ADICIONAIS:

Carga Horária: 40H
Idioma: Apenas em Inglês
Material didático: Oficial Microsoft Digital.

OBSERVAÇÃO:

Os treinamentos serão ministrados com 01 aluno por computador, tendo no mínimo 06 (seis) alunos por turma ou no máximo 08 (oito).

A Zillion Treinamentos se reserva no direito de não oferecer o curso, caso não seja preenchido o número mínimo de vagas.




FORMAÇÃO MCSE


10776AC – Developing Microsoft® SQL Server® 2012 Databases

Este curso fornece aos alunos os conhecimentos e habilidades necessárias para implementar um banco de dados no Microsoft SQL Server. O curso apresentará aos alunos as novas características do produto Microsoft SQL Server 2012 e descreve o desenho lógico de tabelas, indexação e planos de consulta. O curso também mostra a criação de objetos do banco de dados, incluindo views, stored procedures, assim como parâmetros e funções. Demais aspectos de codificação de procedures tais como transações, concorrência, tratamento de erros, triggers e SQL CLR também são cobertos pelo conteúdo do curso. Este curso prepara o aluno para o Exame 70-464.

PÚBLICO-ALVO:

Profissionais que querem se tornar hábil em características de produto do SQL Server 2012 e tecnologias para a implementação de um banco de dados.

Pré-requisito:

Além da sua experiência profissional, os alunos que frequentam este treinamento já devem ter os seguintes conhecimentos técnicos: Conhecimento de escrever consultas T-SQL. Conhecimento de conceitos básicos de banco de dados relacional. Os alunos que frequentam este treinamento podem atender aos pré-requisitos frequentando os seguintes cursos, ou obtendo habilidades e conhecimentos equivalentes: 10774A: escrever consultas T-SQL para Microsoft SQL Server 2012

CONTEÚDO PROGRAMÁTICO

Module 1: Introduction to SQL Server 2012 and its Toolset
Lessons
Introduction to the SQL Server Platform
Working with SQL Server Tools
Configuring SQL Server Services

Module 2: Working with Data Types
Lessons
Using Data Types
Working with Character Data
Converting Data Types
Specialized Data Types

Module 3: Designing and Implementing Tables
Lessons
Designing Tables
Working with Schemas
Creating and Altering Tables

Module 4: Ensuring Data Integrity through Constraints
Lessons
Enforcing Data Integrity
Implementing Domain Integrity
Implementing Entity and Referential Integrity

Module 5: Planning for SQL Server 2012 Indexing
Lessons
Core Indexing Concepts
Data Types and Indexes
Single Column and Composite Indexes

Module 6: Implementing Table Structures in SQL Server 2012
Lessons
SQL Server Table Structures
Working with Clustered Indexes
Designing Effective Clustered Indexes

Module 7: Reading SQL Server 2012 Execution Plans
Lessons
Execution Plan Core Concepts
Common Execution Plan Elements
Working with Execution Plans

Module 8: Improving Performance through Nonclustered Indexes
Lessons
Designing Effective Nonclustered Indexes
Implementing Nonclustered Indexes
Using the Database Engine Tuning Advisor

Module 9: Designing and Implementing Views
Lessons
Introduction to Views
Creating and Managing Views
Performance Considerations for Views

Module 10: Designing and Implementing Stored Procedures
Lessons
Introduction to Stored Procedures
Working With Stored Procedures
Implementing Parameterized Stored Procedures
Controlling Execution Context

Module 11: Merging Data and Passing Tables
Lessons
Using the MERGE Statement
Implementing Table Types
Using TABLE Types As Parameters

Module 12: Designing and Implementing User-Defined Functions
Lessons
Overview of Functions
Designing and Implementing Scalar Functions
Designing and Implementing Table-Valued Functions
Implementation Considerations for Functions
Alternatives to Functions

Module 13: Creating Highly Concurrent SQL Server 2012 Applications
Lessons
Introduction to Transactions
Introduction to Locks
Management of Locking
Transaction Isolation Levels

Module 14: Handling Errors in T-SQL Code
Lessons
Understanding T-SQL Error Handling
Implementing T-SQL Error Handling
Implementing Structured Exception Handling

Module 15: Responding to Data Manipulation via Triggers
Lessons
Designing DML Triggers
Implementing DML Triggers
Advanced Trigger Concepts

Module 16: Implementing Managed Code in SQL Server 2012
Lessons
Introduction to SQL CLR Integration
Importing and Configuring Assemblies
Implementing SQL CLR Integration

Module 17: Storing XML Data in SQL Server 2012
Lessons
Introduction to XML and XML Schemas
Storing XML Data and Schemas in SQL Server
Implementing the XML Data Type

Module 18: Querying XML Data in SQL Server
Lessons
Using the T-SQL FOR XML Statement
Getting Started with XQuery
Shredding XML

Module 19: Working with SQL Server 2012 Spatial Data
Lessons
Introduction to Spatial Data
Working with SQL Server Spatial Data Types
Using Spatial Data in Applications

Module 20: Working with Full-Text Indexes and Queries
Lessons
Introduction to Full-Text Indexing
Implementing Full-Text Indexes in SQL Server
Working with Full-Text Queries

INFORMAÇÕES ADICIONAIS:

Carga Horária: 40H
Idioma: Apenas em Inglês
Material didático: Oficial Microsoft Digital.


20465CC – Designing a Data Solution With Microsoft® SQLServer® 2014


SOBRE ESTE CURSO

The focus of this five-day instructor-led course is on planning and implementing enterprise database infrastructure solutions by using SQL Server 2014 and other Microsoft technologies. It describes how to consolidate SQL Server workloads, work with both on-premises and Microsoft Azure cloud-based solutions, and how to plan and implement high availability and disaster recovery solutions.
Note: This course is designed for customers who are interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features in SQL Server 2014, but also the important capabilities across the SQL Server data platform.

PERFIL DO PÚBLICO

This course is intended for database professionals who need who plan, implement, and manage database solutions. Primary responsibilities include:
Planning and implementing database infrastructure.
Planning and implementing consolidation strategies.
Implementing SQL Server in on-premises, cloud, and hybrid IT scenarios.
Planning and implementing high availability solutions.
Planning and implementing disaster recovery solutions.

NO FINAL DO CURSO

After completing this course, students will be able to:
Assess an existing enterprise environment.
Plan and implement Policy-Based Management.
Describe the considerations for consolidating workloads with SQL Server 2014.
Describe considerations for including SQL Server 2014 in a private cloud.
Use Microsoft Azure storage with SQL Server 2014.
Implement and configure databases in Microsoft Azure SQL Database.
Implement and configure databases in Microsoft Azure virtual machines.
Describe high availability technologies in SQL Server 2014 and implement log shipping.
Describe Windows Server Failover Clustering and Implement an AlwaysOn Failover Cluster
Instance.
Implement an Always On Availability Group.
Plan high availability and disaster recovery solutions.
Plan and implement database replication.

CONTEÚDO PROGRAMÁTICO

Module 1: Introduction to Enterprise Data Architecture
Module 2: Multi-Server Configuration Management
Module 3: Monitoring SQL Server 2014 Health
Module 4: Consolidating Database Workloads with SQL Server 2014
Module 5: Introduction to Cloud Data Solutions
Module 6: Introduction to Microsoft Azure™
Module 7: Microsoft Azure SQL Database
Module 8: SQL Server in Microsoft Azure Virtual Machines
Module 9: Introduction to High Availability in SQL Server 2014
Module 10: Clustering with Windows Server and SQL Server 2014
Module 11: AlwaysOn Availability Groups
Module 12: Planning High Availability and Disaster Recovery
Module 13: Replicating Data

INFORMAÇÕES ADICIONAIS:

Carga Horária: 40H
Idioma: Apenas em Inglês
Material didático: Oficial Microsoft Digital.