Certificações Microsoft - Dicas, links e materiais de estudo para as provas de MCSA e MCSE do SQL Server 2016

Visualizações: 16.661 views

Neste artigo

Esse post é a parte 2 de 15 da série Certificação Microsoft

Certificações Microsoft – Material de estudo em português para obter sua primeira certificação (MTA)
Certificações Microsoft – Dicas, links e materiais de estudo para as provas de MCSA e MCSE do SQL Server 2016
Provas de certificação Microsoft: 50% de desconto para estudantes (Graduação, Pós-Graduação, Mestrado, Doutorado)
SQL Server – Como aprender do básico à certificação (Cursos, Virtual Labs, Virtual Academy)
Profissional MCP – Como compartilhar suas certificações e como encontrar profissionais certificados
SQL Server – Material de estudo para a prova de certificação 70-764 Administering a SQL Database
Como foi o Webinar de Certificações Microsoft na área de dados (Data Platform)
Certificação Microsoft – Dicas de como fazer a prova em casa sem dor de cabeça
Minhas impressões e material de estudo da prova DP-300 – Administering Relational Databases on Microsoft Azure (beta)
Minhas impressões e material de estudo da prova de certificação DA-100 – Analyzing Data with Microsoft Power BI (beta)
Simulados de prova de certificação? FUJA DELES!
Exame DP-900 Microsoft Azure Data Fundamentals – Material de estudo gratuito para a prova de certificação
Novo portal Microsoft Certified Fundamentals para quem quer obter sua primeira certificação
Exame AZ-305: Designing Microsoft Azure Infrastructure Solutions – Material de estudo gratuito
Provas de certificação da Microsoft: Simulados oficiais e materiais de estudo

Tempo de Leitura: 78 minutos

Olá pessoal,
Tudo bem com vocês ?

Estou meio ausente aqui no blog, mas em breve vou voltar a postar com bem mais frequência, assim que concluir alguns projetos e consultorias que estou fazendo. Enquanto isso, não poderia deixar de compartilhar com vocês, algumas dicas, materiais e links que podem ajudá-los a se preparar para exames e provas de certificação do MCSA e MCSE do SQL Server 2016, assim como eu venho me preparando nos últimos meses.

Caso você esteja de preparando para a sua primeira prova de certificação e quer começar pela MTA, criei o post Certificações Microsoft – Material de estudo em português para obter sua primeira certificação (MTA) para auxiliar e tirar dúvidas de quem está começando agora nesse mundo de certificação e quer saber mais sobre essa certificação.

Antes de mais nada, vamos conhecer as certificações atuais da Microsoft para a Data Platform e começar a nos familiarizar com as siglas:

MTA (Microsoft Technology Associate): É a primeira prova de certificação da Microsoft e a mais fácil, indicada para pessoas que estão iniciando a carreira e ainda estão se familiarizando com a tecnologia. Ela não é pré-requisito para fazer as provas MCSA e MCSE e fazê-la é opcional.
Como incentivo a fazer a prova do MTA, a Microsoft dá um pequeno desconto na realização desse exame (você paga 77 dólares ao invés dos 100 dólares das outras provas de MCSA e MCSE)
MCSA (Microsoft Certified Solutions Associate): Prova de nível médio/alto, voltada para pessoas que já tenham experiência com as tecnologias envolvidas.
A certificação MCSA é pré-requisito para obter a certificação MCSE.
MCSE (Microsoft Certified Solutions Expert): Último nível de provas de certificação da Microsoft, as provas MCSE costumam ser as mais difíceis dentre as provas Microsoft, e devem ser realizadas por profissionais altamente capacitados e com grande vivência e experiência nas tecnologias em questão.

Por quê se certificar?

O blog BornToLearn, mundialmente conhecido pela comunidade técnica da Microsoft, disponibilizou um post onde eles citam 6 vantagens que profissionais podem obter ao se tornarem certificados (Você pode conferir o post neste link aqui), que se resume na imagem abaixo:

Antes de uma certificação, é um desafio para começar avançando em sua carreira de tecnologia. Após a certificação, você tem um forte impulso para o próximo grande obstáculo. Na verdade, 33% dos profissionais certificados declararam terem conseguido um emprego após a certificação. Além disso, obter a certificação é viciante.
Manter-se atualizando quando você é novo no trabalho é um desafio. A boa notícia é que a certificação fornece a você uma base comprovada de conhecimento especializado e do mundo real para construir, de modo que você estará pronto para subir no trabalho 39% mais rápido do que seus colegas. Se você ainda não está convencido, 38% dos profissionais de TI disseram que a certificação os ajudou a realizar tarefas complicadas com mais confiança. É ciência.
É uma luta para se diferenciar e mostrar que você é a melhor pessoa para o trabalho quando você está começando na indústria de tecnologia. Não é bom saber que 91% dos gerentes de contratação consideram a certificação como parte de seus critérios de contratação? Nós pensamos assim. Ir em entrevistas, sabendo que você está certificado o fará se sentir muito bem.
Mantenha-se atualizado e nunca corra o risco de ter habilidades obsoletas. Quando você mantiver suas certificações, você pode confiar que suas habilidades estão atualizadas. Assim, você pode estar confiante na experiência que você tem ao ganhar novos truques, como novas tecnologias e recursos, quando os padrões da indústria mudarem.
Ficando impaciente para a próxima promoção? A solução é bastante simples. Se você é certificado, você recebe mais aumentos de salário e mais chances de subir em sua carreira. Além disso, você começa direito para fora do portão recebendo 15% mais em média do que seus pares sem certificação.
Talvez o mais importante. Quando você está certificado, você é reconhecido na indústria como um especialista. Empregadores gostam de suas contratações certificadas, tanto que eles costumam mantê-los por perto 15% mais do que outros empregados. Quando você estará certificado??

Vídeo do MVP Rafael Bernares sobre a importância da certificação

Como são as provas de certificação Microsoft?

Caso você nunca tenha feito, gostaria de te preparar bem, pois as provas de certificação Microsoft costumam ser bem difíceis, uma vez que:

Algumas provas tem questões discursivas, onde você precisa escrever consultas SQL seguindo várias regras, conforme o enunciado.
Em algumas questões, a resposta é uma composição de 4 opções, dentre umas 10, onde você deve marcar as alternativas corretas e alinhar na ordem correta de execução. A chance de você acertar uma questão dessas “no chute” é bem pequena.
Em algumas questões, todas as respostas estão corretas, mas você deve escolher a opção que possui uma melhor performance, ou seja a mais barata, financeiramente, por exemplo.
Em algumas etapas da prova, você não tem chance de voltar a questão e pensar melhor na resposta correta: Clicou em avançar, não tem volta.
Geralmente o tempo é curto: 3 mins por questão e algumas possuem um enunciado com mais de 10 linhas de texto.

As provas de certificação Microsoft custam o valor de 100 dólares (Se você for estudante, tem 50% de desconto. Veja como obter o seu desconto AQUI) e você deve tirar uma nota igual ou maior que 700, valendo 1000, para ser aprovado no exame (700 não é 70% da prova).

Esses exames costumam ter versão traduzida para o português (menos as Beta ou exames recém lançados, onde só existe opção do idioma em inglês), mas termos técnicos costumam ser traduzidos também (View = Visão, Trigger = Gatilho, etc), o que pode acabar te confundindo durante a prova. Na minha opinião, um profissional que almeja ser certificado e seguir da carreira de DBA, não pode mais se dar ao luxo de não saber pelo menos, ler textos e artigos em inglês.

AVISO: Se você já atua na área de TI há algum tempo, você já deve ter ouvido falar de algumas provas que são vendidas/distribuídas na internet com as mesmas perguntas que são aplicadas no exame de certificação. Muitos “profissionais” de TI acabam adquirindo esse tipo de prova e, ao invés de estudar e aprender para fazer a prova, acabam decorando as perguntas e respostas (muitas vezes, não sabem nem o que estão decorando) e conseguem ser aprovados nos exames. Fuja deste tipo de “estudo”, pois não adianta nada ter vários certificados no currículo e quando for entrar numa empresa, que te contratou por causa dessas certificações, você acabar “passando vergonha” por não ter todo o conhecimento que aparentava ter. Na comunidade técnica, esse tipo de “estudo” é altamente repudiado e desincentivado.

E as provas beta? Como funcionam?

Quando a Microsoft está se preparando para lançar as provas de certificação, ela disponibiliza alguns códigos (vouchers) para profissionais de TI (geralmente 300 vouchers por prova, pro mundo todo), de modo que eles façam essas provas de teste e deem seus feedbacks sobre as questões.

Apesar de serem provas BETA, os seus resultados são computados e valem para o processo de certificação, do mesmo jeito que uma prova paga. Conforme explicado neste link, se você for aprovado na prova beta, você não precisará repetir a prova quando sair a versão final. Se você não passar na prova beta, você não poderá repetir a prova beta novamente e deverá esperar sair a versão final para repetir o exame.

Uma vez que as provas ainda estão em fase de testes (São BETA), você deve ficar atento à possibilidade de erros nas questões. Além disso, geralmente as provas estão disponíveis apenas em Inglês e ao invés de algumas questões escolhidas aleatoriamente, a prova contém bem mais questões (geralmente 59-62 ao invés das 35-40 de uma prova “normal”), então a prova beta costuma ser maior que a prova final.

Outra diferença da versão final das provas para a versão beta, é que na versão final, o resultado é gerado assim que você termina a prova, enquanto nas provas Beta, o resultado só é liberado várias semanas depois (quando a versão final da prova é disponibilizada) – Geralmente uns 4 meses.

Como me tornar um MCSA na Data Plataform?

Como vocês devem saber, o Certification Path de MCSA do SQL Server 2016 mudou bastante em relação ao SQL Server 2012/2014, onde você precisava ser aprovado em 3 provas, sendo 1 de Database Dev, outra de Administração de banco (DBA) e outra de BI, o que não fazia realmente muito sentido, já que são carreiras distintas dentro da área de Data Platform da Microsoft.

Agora no Certification Path de MCSA do SQL Server 2016, você precisa ser aprovado em apenas 2 provas da mesma área de atuação, seja ela DEV, DBA ou BI. Ou seja, se você passar apenas em 1 prova de DEV e outra de DBA, você não terá o título de MCSA.

Entenda melhor visualizando a imagem abaixo:

Como me tornar um MCSE Data Management and Analytics?

Caso você queira se tornar um Microsoft Certified Solutions Expert na Data Platform da Microsoft, você precisará atender aos 2 requisitos abaixo:

Se tornar um MCSA SQL Server 2012/2014 OU MCSA SQL Server 2016 (BI, DEV ou DBA)
Ser aprovado em pelo menos uma das seguintes provas:
Exam 70-464 – Developing Microsoft SQL Server Databases (DEV)
Exam 70-465 – Designing Database Solutions for Microsoft SQL Server (DBA)
Exam 70-466 – Implementing Data Models and Reports with Microsoft SQL Server (BI)
Exam 70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server (BI)
Exam 70-473 – Designing and Implementing Cloud Data Platform Solutions (DBA)
Exam 70-475 – Designing and Implementing Big Data Analytics Solutions (BI)

Novas provas eletivas para MCSE. Para ganhar o título, existem alguns critérios. Não basta fazer a prova e obter o título, igual acontece nas provas acima:
Exam 70-762 – Developing SQL Databases (DEV)
Exam 70-767: Implementing a Data Warehouse using SQL (BI)
Exam 70-768: Developing SQL Data Models (BI)
Exam 70-773: Analyzing Big Data with Microsoft R (BI)
Exam 70-774: Perform Cloud Data Science with Azure Machine Learning (BI)
Exam 70-775: Perform Data Engineering on Microsoft Azure HDInsight (BI)

UPDATE: Em junho de 2017, a Microsoft passou a reconhecer como MCSE os profissionais que possuem 2 ou mais MCSA’s na mesma área, ou seja, se você tem 2 MCSA de Data Platform, você já é um MCSE assim que é aprovado na 2ª prova do seu 2º MCSA.

Observação: Na prova de MCSE, a área de atuação do seu MCSA é indiferente para obter o título. Uma vez que você é MCSA, pode fazer qualquer uma das provas do MCSE para ser reconhecido como um Microsoft Certified Solutions Expert na Data Platform da Microsoft. Ou seja, se você for um MCSA de BI (Passou na 70-767 e 70-768), por exemplo, e passar na 70-464, que é teoricamente voltada para Database Developers, você se tornará um MCSE Data Management and Analytics.

Microsoft Technology Associate (MTA)

Link da Certificação

Exam 98-364: Database Fundamentals (Link do Exame)
Links para Estudo

Understanding Core Database Concepts

Understand how data is stored in tables
- Tables
- Tables, Records, and Fields
Understand relational database concepts
Understand data manipulation language
Understand data definition language (DDL)
- Data Definition Language (DDL) Statements (Transact-SQL)
- Data Definition Language (DDL)

Create Database Objects

Choose data types
Understand tables and how to create them
Create views
Create stored procedures and functions

Manipulate Data

Understand Data Storage

Understand normalization
- Database Normalization Basics
- Database normalization
Understand primary, foreign, and composite keys
Understand indexes

Administer a Database

Understand database security concepts
Understand database backups and restore

MCSA SQL Server 2012/2014

Link da Certificação

Caso você queira se tornar um profissional certificado MCSA do SQL Server 2012/2014 (Sim, é uma certificação para as duas versões), você precisará ser aprovado nas 3 provas abaixo.

Exam 70-461: Querying Microsoft SQL Server 2012/2014 (Link do Exame)
Links para Estudo

Create database objects

Create and alter tables using T-SQL syntax (simple statements)
Create and alter views (simple statements)
Design views
Create and modify constraints (simple statements)
Create and alter DML triggers

Work with data

Modify data

Create and alter stored procedures (simple statements)
Modify data by using INSERT, UPDATE, and DELETE statements
Combine datasets
Work with functions

Troubleshoot and optimize

Optimize queries
- - SQL Server Execution Plans
Graphical Query Plan Tutorial
Manage transactions
- SQL Server Concurrency: Locking, Blocking and Row Versioning
- Introduction to the SQL Server Transaction Log Tutorial
Evaluate the use of row-based operations vs. set-based operations
Implement error handling

Exam 70-462: Administering Microsoft SQL Server 2012/2014 Databases (Link do Exame)
Links para Estudo

Install and configure

Plan installation
Install SQL Server and related services
Implement a migration strategy
Configure additional SQL Server components
Manage SQL Server Agent

Maintain instances and databases

Optimize and troubleshoot

Identify and resolve concurrency problems
Collect and analyze troubleshooting data
Audit SQL Server instances.

Manage Data

Implement security

Implement high availability

Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 (Link do Exame)
Links para Estudo

Design and Implement a Data Warehouse (11%)

Design and implement dimensions

Introduction to Dimensions (Analysis Services – Multidimensional Data) http://msdn.microsoft.com/en-us/library/ms175439.aspx
Attributes and Attribute Hierarchies http://msdn.microsoft.com/en-us/library/ms174760.aspx
Attribute Relationships http://msdn.microsoft.com/en-us/library/ms174557.aspx
SQL Server Analysis Services Interview Questions Part II – Dimensions http://www.mssqltips.com/sqlservertip/2662/sql-server-analysis-services-interview-questions-part-ii–dimensions/
Conformed dimensions http://www.jamesserra.com/archive/2011/11/conformed-dimensions/
Slowly Changing Dimension Transformation http://msdn.microsoft.com/en-us/library/ms141715.aspx
Analysis Services – 11 Star and Snowflake Schemas http://www.youtube.com/watch?v=ewX1nsjX-uA
Defining Dimension Granularity within a Measure Group http://msdn.microsoft.com/en-us/library/ms166573.aspx

Design and implement fact tables

Defining a Fact Relationship http://msdn.microsoft.com/en-us/library/ms167409.aspx
Defining a Many-to-Many Relationship http://msdn.microsoft.com/en-us/library/ms170463.aspx
Columnstore Indexes http://msdn.microsoft.com/en-us/library/gg492088.aspx
Different Fact Tables for Partitions http://msdn.microsoft.com/en-us/library/ms174755.aspx
Configure Measure Properties http://msdn.microsoft.com/en-us/library/ms175623.aspx
Define Semiadditive Behavior http://technet.microsoft.com/en-us/library/ms175356.aspx
Use Aggregate Functions http://technet.microsoft.com/en-us/library/ms365396.aspx

Extract and Transform Data (23%)

Load Data (27%)

Design control flow

Control Flow http://msdn.microsoft.com/en-us/library/ms137681.aspx
Integration Services (SSIS) Variables http://msdn.microsoft.com/en-us/library/ms141085.aspx
Control Flow vs Data Flow http://msdn.microsoft.com/en-us/library/ms140246.aspx
http://bidn.com/blogs/BradSchacht/ssis/305/control-flow-vs-data-flow
SSIS Designer http://msdn.microsoft.com/en-us/library/ms137973.aspx
Integration Services Tasks http://msdn.microsoft.com/en-us/library/ms139892.aspx
Integration Services (SSIS) Logging http://msdn.microsoft.com/en-us/library/ms140246.aspx

Implement package logic by using SSIS variables and parameters

SSIS 2012: Parameters and Variables, what is the difference? http://www.rafael-salas.com/2011/12/ssis-2012-parameters-and-variables-what.html#!/2011/12/ssis-2012-parameters-and-variables-what.html
Integration Services (SSIS) Parameters http://msdn.microsoft.com/en-us/library/hh213214.aspx
SSIS Basics: Introducing Variables http://www.simple-talk.com/sql/ssis/ssis-basics-introducing-variables/
Package Configurations http://msdn.microsoft.com/en-us/library/ms141682.aspx
Using Parent Package Variables in Package Configurations http://www.sqlis.com/sqlis/post/Using-Parent-Package-Variables-in-Package-Configurations.aspx
Easy Package Configuration http://www.sqlis.com/sqlis/post/Easy-Package-Configuration.aspx
Change Package Properties Using Parameters-SSIS 2012 http://www.sqlservercentral.com/blogs/samvangassql/2012/04/24/change-package-properties-using-parametersssis-2012/
Use Property Expressions in Packages http://msdn.microsoft.com/en-us/library/ms141214.aspx

Implement control flow

Using Sequence Containers in SSIS http://www.bidn.com/blogs/TomLannen/bidn-blog/2620/using-sequence-containers-in-ssis
Restart Packages by Using Checkpoints http://msdn.microsoft.com/en-us/library/ms140226.aspx
Execute SQL Task (Transaction control) http://msbiworld.wordpress.com/2012/02/12/ssis-execute-sql-task-transaction-control/
Debugging Control Flow http://msdn.microsoft.com/en-us/library/ms140274.aspx
Precedence Constraints http://msdn.microsoft.com/en-us/library/ms141261.aspx
Designing Your SSIS Packages for Parallelism http://technet.microsoft.com/en-us/sqlserver/ff686759.aspx
How to pass parameter values to the stored procedure dynamically in ssis http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3973ca4c-195f-498f-ba1d-ee334eccd368
Execute Package Task http://msdn.microsoft.com/en-us/library/ms137609.aspx
Are SSIS Package Template Useful in 2012 http://stackoverflow.com/questions/11347545/are-ssis-package-template-useful-in-2012
Save a Package as a Package Template http://msdn.microsoft.com/en-us/library/ms345191.aspx

Implement data load options

SSIS – Package design pattern for loading a data warehouse http://sqlblog.com/blogs/jorg_klein/archive/2010/01/04/ssis-package-design-pattern-for-loading-a-data-warehouse.aspx
SSIS Design Pattern – Incremental Loads http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis_design-pattern-incremental-loads.aspx
Change Data Capture (SSIS) http://msdn.microsoft.com/en-us/library/bb895315.aspx
Perform an Incremental Load of Multiple Tables http://msdn.microsoft.com/en-us/library/bb895287.aspx
SSIS Design Pattern – ETL Instrumentation, Part 1 http://sqlblog.com/blogs/andy_leonard/archive/2007/08/03/ssis-design-pattern-etl-instrumentation-part-1.aspx
SSIS Design Pattern – ETL Instrumentation, Part 2 http://sqlblog.com/blogs/andy_leonard/archive/2007/11/11/ssis-design-pattern-etl-instrumentation-part-2.aspx
SSIS Design Pattern – ETL Instrumentation, Part 3 http://sqlblog.com/blogs/andy_leonard/archive/2007/11/18/ssis-design-pattern-etl-instrumentation-part-3.aspx

Implement script components in SSIS

Data Warehousing – Slowly Changing Dimensions http://sqlserverpedia.com/wiki/Data_Warehousing_-_Slowly_Changing_Dimensions
Slowly Changing Dimension Transformation http://msdn.microsoft.com/en-us/library/ms141715.aspx
Configure Outputs Using the Slowly Changing Dimension Wizard http://msdn.microsoft.com/en-us/library/ms141662.aspx
Script Component http://msdn.microsoft.com/en-us/library/ms137640.aspx
Developing Specific Types of Script Components http://msdn.microsoft.com/en-us/library/ms345170.aspx
Web Service Task http://msdn.microsoft.com/en-us/library/ms140114.aspx
SSIS Nugget: Get error descriptions http://consultingblogs.emc.com/jamiethomson/archive/2005/08/08/1969.aspx

Configure and Deploy SSIS Solutions (24%)

Troubleshoot data integration issues

Troubleshooting Tools for Package Development http://msdn.microsoft.com/en-us/library/ms137625.aspx
Integration Services (SSIS) Logging http://msdn.microsoft.com/en-us/library/ms140246.aspx
Runtime Debugging Using Data Taps in SSIS 2012 http://blog.gnetgroup.com/bi/2012/11/08/runtime-debugging-using-data-taps-in-ssis-2012/
Debug a Package by Setting Breakpoints on a Task or a Container http://msdn.microsoft.com/en-us/library/ms141754.aspx

Install and maintain SSIS components

Install Integration Services http://msdn.microsoft.com/en-us/library/ms143731.aspx
SQL Server Integration Services 2012 – Project Deployment Model http://www.element61.be/e/resourc-detail.asp?ResourceId=545
Running and Managing Packages Programmatically http://msdn.microsoft.com/en-us/library/aa337077.aspx
Loading and Running a Remote Package Programmatically http://msdn.microsoft.com/en-us/library/ms403355.aspx
SSIS Catalog http://msdn.microsoft.com/en-us/library/hh479588.aspx
Quick Reference: SSIS in 32- and 64-bits http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html#!/2009/10/quick-reference-ssis-in-32-and-64-bits.html

Implement auditing, logging, and event handling

Audit Transformation http://msdn.microsoft.com/en-us/library/ms141150.aspx
Integration Services (SSIS) Logging http://msdn.microsoft.com/en-us/library/ms140246.aspx
Logging and Defining Log Entries in a Data Flow Component http://msdn.microsoft.com/en-us/library/ms345169.aspx
Execute package in SSIS catalog taking advantage of the new project deployment model ,and the logging and reporting feature http://geekswithblogs.net/LifeLongTechie/archive/2012/11/14/time-to-stop-using-ldquoexecute-package-taskrdquondash-a-way-to.aspx
What Events are Included in the SSIS Catalog Log Levels http://www.mattmasson.com/index.php/2011/12/what-events-are-included-in-the-ssis-catalog-log-levels/
SSIS 2012 Logging http://svangasql.wordpress.com/2012/06/12/t-sql-tuesday-31-ssis-2012-logging/
SSIS Catalog: Part 5 – Logging and Execution Reports http://www.rad.pasfu.com/index.php?/archives/78-SSIS-Catalog-Part-5-Logging-and-Execution-Reports.html
Easy Package Execution monitoring in SSIS 2012 hhttp://visakhm.blogspot.com/2012/09/easy-package-execution-monitoring-in.html

Deploy SSIS solutions

Deployment of Projects and Packages http://msdn.microsoft.com/en-us/library/hh213290.aspx
Deploy Packages by Using the Deployment Utility http://msdn.microsoft.com/en-us/library/ms141693.aspx
SSIS 2012 Deployment Models http://www.youtube.com/watch?v=viaMhHKOoEY
SSIS Catalog http://msdn.microsoft.com/en-us/library/hh479588.aspx
Validating SSIS 2012 packages programatically via SSIS Catalog Managed Object Model http://stackoverflow.com/questions/12179934/validating-ssis-2012-packages-programatically-via-ssis-catalog-managed-object-mo
dtutil Utility http://msdn.microsoft.com/en-us/library/ms162820.aspx
dtexec Utility http://msdn.microsoft.com/en-us/library/hh231187.aspx

Configure SSIS security settings

Security Overview (Integration Services) http://msdn.microsoft.com/en-us/library/ms137833.aspx
Integration Services Roles (SSIS Service) http://msdn.microsoft.com/en-us/library/ms141053.aspx
SSIS Catalog Access Control Tips http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx
Folder Security in SSIS 2012 http://www.biadmin.com/2012/08/folder-security-in-ssis-2012.html

Build Data Quality Solutions (15%)

Install and maintain Data Quality Services

Introducing Data Quality Services http://msdn.microsoft.com/en-us/library/ff877917.aspx
Install Data Quality Services http://msdn.microsoft.com/en-us/library/gg492277.aspx
Run DQSInstaller.exe to Complete Data Quality Server Installation http://msdn.microsoft.com/en-us/library/hh231682.aspx
DQS Security http://msdn.microsoft.com/en-us/library/hh213045.aspx
Manage DQS Users in SSMS http://msdn.microsoft.com/en-us/library/hh510409.aspx
Grant DQS Roles to Users http://msdn.microsoft.com/en-us/library/gg492283.aspx
Data governance hhttp://en.wikipedia.org/wiki/Data_governance

Implement master data management solutions

Install Master Data Services http://msdn.microsoft.com/en-us/library/ee633752.aspx
Master Data Services Overview http://msdn.microsoft.com/en-us/library/ff487003.aspx
Managing Data Warehouse Dimensions with MDS (part 1) http://technet.microsoft.com/en-us/sqlserver/hh709039.aspx
SQL 2012 Master Data Services – Hierarchies and Collections http://technet.microsoft.com/en-us/sqlserver/hh780975.aspx
Deploying Models (Master Data Services) http://msdn.microsoft.com/en-us/library/ff486956.aspx
Administrators (Master Data Services) http://msdn.microsoft.com/en-us/library/ff487051.aspx
Security (Master Data Services) hthttp://msdn.microsoft.com/en-us/library/hh231026.aspx

Create a data quality project to clean data

Create a Data Quality Project http://msdn.microsoft.com/en-us/library/hh510393.aspx
Run the Data Quality Client Application http://msdn.microsoft.com/en-us/library/hh213023.aspx
Data Cleansing http://msdn.microsoft.com/en-us/library/gg524800.aspx
Data Profiling and Notifications in DQS http://msdn.microsoft.com/en-us/library/hh213055.aspx
Creating a knowledge base and cleansing data using Data Quality Services in SQL Server 2012 – Part 3 http://www.mssqltips.com/sqlservertip/2577/creating-a-knowledge-base-and-cleansing-data-using-data-quality-services-in-sql-server-2012–part-3/
Getting Started with Data Quality Services of SQL Server 2012 Using SSIS – Part 4 http://www.mssqltips.com/sqlservertip/2593/getting-started-with-data-quality-services-of-sql-server-2012-using-ssis–part-4/
Demo: Deduplicating Data with SQL Server 2012 Data Quality Services http://channel9.msdn.com/posts/SQL11UPD05-REC-06

Bonus Content

Analysis Services Tutorials (SSAS) http://msdn.microsoft.com/en-us/library/hh231701.aspx
Data Mining Tutorials (Analysis Services) http://msdn.microsoft.com/en-us/library/bb677206.aspx
Reporting Services Tutorials (SSRS) http://msdn.microsoft.com/en-us/library/bb522859.aspx
SSIS Tutorial: Deploying Packages http://msdn.microsoft.com/en-us/library/de18468c-cff3-48f4-99ec-6863610e5886
Introduction to SQL Server Data Tools http://www.mssqltips.com/sqlservertip/2804/introduction-to-sql-server-data-tools/
SQL Server Data Tools Connected Database Development http://www.mssqltips.com/sqlservertip/2832/sql-server-data-tools-connected-database-development/
References SSIS articles by a Microsoft employee, including many on SSIS 2012 http://www.mssqltips.com/sqlserverauthor/30/arshad-ali/
Logging – Level 11 of the Stairway to Integration Services http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/95353/
SQL Server Reporting Services 2012 Drilldown Features http://www.mssqltips.com/sqlservertip/2831/sql-server-reporting-services-2012-drilldown-features/
Graphing the Sine Function in SQL Server Reporting Services http://www.mssqltips.com/sqlservertip/2843/graphing-the-sine-function-in-sql-server-reporting-services/
MDX Guide for SQL Folks: Part I – Navigating The Cube http://www.sqlservercentral.com/articles/MDX/91228/
MDX Guide for SQL Folks: Part II – Hierarchies and Functions http://www.sqlservercentral.com/articles/MDX/91730/

MCSA SQL Server 2016

Link da Certificação

Conforme já explicado acima, para se tornar um MCSA do SQL Server 2016, você precisará ser aprovado em duas provas da mesma área de atuação, seja ela BI, DEV ou DBA.

Caso você já tenha o título de MCSA SQL Server 2012/2014 e deseja atualizar a sua certificação para o SQL Server 2016, basta fazer 1 prova da categoria que você deseja para obter o título de MCSA SQL Server 2016, conforme abaixo.

MCSA SQL Server 2012/2014 + Prova 70-762 (Developing SQL Databases) = MCSA SQL 2016 Database Development
MCSA SQL Server 2012/2014 + Prova 70-765 (Provisioning SQL Databases) = MCSA SQL 2016 Database Administration
MCSA SQL Server 2012/2014 + Prova 70-768 (Developing SQL Data Models) = MCSA SQL 2016 Business Intelligence Development

A prova de upgrade para a certificação do SQL Server 2016 é paga normalmente, como qualquer outro exame (Quando as provas do SQL 2016 começaram, a Microsoft liberou o upgrade grátis, mas a oferta já expirou).

MCSA SQL 2016: Database Development (Developer)

Exam 70-761: Querying Data with Transact-SQL (Link do Exame)
Links para Estudo

Manage data with Transact-SQL

Query data with advanced Transact-SQL components

Query data by using subqueries and APPLY
- SQL Server CROSS APPLY and OUTER APPLY
- FROM (Transact-SQL)
Query data by using table expressions
Group and pivot data by using queries
Query temporal data and non-relational data

Program databases by using Transact-SQL

Create database programmability objects by using Transact-SQL
Implement error handling and transactions
Implement data types and NULLs

Exam 70-762: Developing SQL Databases (Link do Exame)
Links para Estudo

Design and implement database objects

Design and implement a relational database schema
Design and implement indexes
Design and implement views
Implement columnstore indexes

Implement programmability objects

Ensure data integrity with constraints
Create stored procedures
Create triggers and user-defined functions

Manage database concurrency

Implement transactions
Manage isolation levels
Optimize concurrency and locking behavior
Implement memory-optimized tables and native stored procedures

Optimize database objects and SQL infrastructure

Optimize statistics and indexes
Analyze and troubleshoot query plans
Manage performance for database instances
Monitor and trace SQL Server baseline performance metrics

MCSA SQL 2016: Database Administrator (DBA)

Exam 70-764: Administering a SQL Database Infrastructure (Link do Exame)
Links para Estudo

Configure data access and auditing

Manage backup and restore of databases

Manage and monitor SQL Server instances

Monitor database activity
Monitor queries
Manage indexes
Manage statistics
Monitor SQL Server instances
Manage high availability and disaster recovery
Implement failover cluster instances
- Step by Step Installation of SQL Server 2014 on a New Failover Cluster
- Always On Failover Cluster Instances (SQL Server)

Exam 70-765: Provisioning SQL Databases (Link do Exame)
Links para Estudo

Implement SQL in Azure

Manage databases and instances

Deploy and migrate applications

MCSA SQL 2016: Business Intelligence Developer (BI)

Exam 70-767: Implementing a SQL Data Warehouse (Link do Exame)
Links para Estudo

Design and implement a data warehouse

Design and implement dimension tables
Design and implement fact tables
- Use Aggregate Functions
Design and implement indexes for a data warehouse workload
Design storage for a data warehouse
Design and implement partitioned tables and views

Extract, transform, and load data

Integrate solutions with cloud data and big data

Integrate external data sources with SQL Server by using Polybase
Extract, transform, and load data from SQL Data Warehouse by using Polybase
Design and implement an Azure SQL Data Warehouse
Manage and maintain a SQL Data Warehouse

Build data quality solutions

Exam 70-768: Developing SQL Data Models (Link do Exame)
Links para Estudo

Design and implement a data warehouse

Create a multidimensional database by using Microsoft SQL Server Analysis Services (SSAS)
Design and implement dimensions in a cube
Implement measures and measure groups in a cube

Design a tabular BI semantic model

Design and publish a tabular data model
Configure, manage, and secure a tabular model
Develop a tabular model to access data in near real time

Develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX)

Create basic MDX queries
Implement custom MDX solutions
- SCOPE Statement (MDX)
- MDX Forum
Create formulas by using the DAX language

Configure and maintain SQL Server Analysis Services

MCSA SQL 2016: Machine Learning (BI)

Exam 70-773: Analyzing Big Data with Microsoft R (Link do Exame)
Links para Estudo

Read and explore big data

Process big data

Process data with rxDataStep
- rxDataStep: Data Step for RevoScaleR data sources
- Date-Time Classes

Perform complex transforms that use transform functions

Manage data sets

Process text using RML packages

Build predictive models with ScaleR

Estimate linear models
Build and use partitioning models
Generate predictions and residuals
Evaluate models and tuning parameters
Create additional models using RML packages

Use R Server in different environments

Use different compute contexts to run R Server effectively
Optimize tasks by using local compute contexts
Perform in-database analytics by using SQL Server
Implement analysis workflows in the Hadoop ecosystem and Spark
Deploy predictive models to SQL Server and Azure Machine Learning

Exam 70-774: Perform Cloud Data Science with Azure Machine Learning (Link do Exame)
Links para Estudo

Prepare Data for Analysis in Azure Machine Learning and Export from Azure Machine Learning

Import and export data to and from Azure Machine Learning
Explore and summarize data
Cleanse data for Azure Machine Learning
Perform feature engineering
- Join Data
- Data Transformation / Manipulation
- Feature engineering in data science
- Feature Selection Modules
- Principal Component Analysis
- Edit Metadata

Develop Machine Learning Models

Select an appropriate algorithm or method
Initialize and train appropriate models
Validate models

Operationalize and Manage Azure Machine Learning Services

Deploy models using Azure Machine Learning
Manage Azure Machine Learning projects and workspaces
Consume Azure Machine Learning models
Consume exemplar Cognitive Services APIs

Use Other Services for Machine Learning

Build and use neural networks with the Microsoft Cognitive Toolkit
Streamline development by using existing resources
Perform data sciences at scale by using HDInsights
Perform database analytics by using SQL Server R Services on Azure

MCSA SQL 2016: BI Reporting (BI)

Exam 70-778: Analyzing and Visualizing Data with Microsoft Power BI (Link do Exame)
Links para Estudo

Consume and Transform Data By Using Power BI Desktop

Model and Visualize Data

Configure Dashboards in the Power BI Service

Configure a dashboard
Publish dashboards
Configure security for dashboards
Configure organizational content packs and apps

Exam 70-779: Analyzing and Visualizing Data with Microsoft Excel (Link do Exame)
Links para Estudo

Consume and Transform Data by Using Microsoft Excel Connect to data sources

Model Data

Create and optimize data models
Create calculated columns, measures, and tables
Create performance KPIs
Create hierarchies

Visualize Data

Create and manage PivotTables
Create and manage PivotCharts
Interact with Power BI
- Get data from Excel workbook files
- Import an Excel table into Power BI

MCSA SQL 2016: Data Engineering with Azure (BI)

Exam 70-775: Perform Data Engineering on Microsoft Azure HDInsight (Link do Exame)
Links para Estudo

Administer and Provision HDInsight Clusters

Implement Big Data Batch Processing Solutions

Implement Big Data Interactive Processing Solutions

Implement interactive queries for big data with Spark SQL
Perform exploratory data analysis by using Spark SQL
Implement interactive queries for big data with Interactive Hive
Perform exploratory data analysis by using Hive
Perform interactive processing by using Apache Phoenix on HBase

Implement Big Data Real-Time Processing Solutions

Create Spark streaming applications using DStream API
Create Spark structured streaming applications
Develop big data real-time processing solutions with Apache Storm
Build solutions that use Kafka
Build solutions that use HBase
- HBase Architecture, Use cases & Best practices in HDInsight
- What is HBase in HDInsight: A NoSQL database that provides BigTable-like capabilities for Hadoop
- HBase – Shell
- Get started with an Apache HBase example in HDInsight
- HDInsight HBase: 9 things you must do to get great HBase performance
- Configure HBase cluster replication within virtual networks

Exam 70-776: Perform Big Data Engineering on Microsoft Cloud Services (Link do Exame)

MCSA SQL 2016: Azure Database Development (DEV)

Exam 70-777: Implement NoSQL Solutions with DocumentDB and Azure Search (Link do Exame)
Exam 70-473: Designing and Implementing Cloud Data Platform Solutions (Link do Exame)
Links para Estudo

Design and implement database solutions for Microsoft SQL Server and SQL Database

Manage database management systems (DBMS) security

Design for high availability, disaster recovery, and scalability

Design and implement high availability solutions
Design and implement scalable solutions
Design and implement SQL Database data recovery

Monitor and manage database implementations on Azure

Monitor and troubleshoot SQL Server VMs on Azure
Monitor and troubleshoot SQL Database
Automate and manage database implementations on Azure

MCSE 2016 Data Management and Analytics

Link da certificação

Exam 70-462: Designing Database Solutions for Microsoft SQL Server (Link do Exame)
Links para Estudo

Design a database structure

Design databases and database objects

Design database security

Design an application strategy to support security
Design database, schema, and object security permissions
Design instance-level security configurations

Design a troubleshooting and optimization solution

Design a maintenance strategy for database servers
Troubleshoot and resolve concurrency issues
Design and implement a high availability solution
Design a solution to monitor performance and concurrency
Design a monitoring solution at the instance level

Exam 70-464: Developing Microsoft SQL Server Databases (Link do Exame)
Links para Estudo

Implement database objects

Implement programming objects

Design and implement stored procedures
Design T-SQL table-valued and scalar functions
Create, use, and alter user-defined functions (UDFs)
Create and alter views

Design database objects

Optimize and troubleshoot queries

Optimize and tune queries
Graphical Query Plan Tutorial
Troubleshoot and resolve performance problems
- Monitor Resource Usage (System Monitor)
- Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)
Optimize indexes
Capture and analyze execution plans
Collect performance and system information

Exam 70-465: Designing Database Solutions for Microsoft SQL Server (Link do Exame)
Links para Estudo

Design a database structure

Design databases and database objects

Design for concurrency

Design T-SQL stored procedures

Design a management automation strategy

Design for transactions

Design database security

Design an application strategy to support security
Design database, schema, and object security permissions
Design instance-level security configurations

Design a troubleshooting and optimization solution

Design a maintenance strategy for database servers
Troubleshoot and resolve concurrency issues

Design and implement a high availability solution

Design a solution to monitor performance and concurrency

Design a monitoring solution at the instance level

Exam 70-466: Implementing Data Models and Reports (Link do Exame)
Links para Estudo

Design dimensions and measures

Given a requirement, identify the dimension/measure group relationship that should be selected.
Design patterns for representing business facts and dimensions (many-to-many relationships).
Handle degenerate dimensions in a cube.
Identify the attributes for dimensions.
Identify the measures.
- Measures and measure groups.
- SSAS Interview Questions on Measures, Actions, and Storage.
Aggregation behavior for the measures.
- SQL Server Analysis Services Aggregation Designs.
- Define Semi additive Behavior
Build hierarchies.
- SQL Server Analysis Services Attribute Relationships.
Define granularity of dimension relationships.
- SQL Server Analysis Service Levels of Granularity.

Implement and configure dimensions in a cube

Translations.
- SQL Server Analysis Services Interview Questions on Dimensions, Hierarchies and Properties – What are Dimension Translations?
- Defining and Browsing Translations
Define attribute relationships.
- SQL Server Analysis Services Attribute Relationships.
- Attribute Relationships.
Implement hierarchies.
- Creating a Hierarchy.
Implement SQL Server Analysis Services (SSAS) dimensions and cubes.
- Build a Cube From an Existing Data Source Using SQL Server Analysis Services.
- Multidimensional Modeling (Adventure Works Tutorial).
Create the Attribute Relationships that should be made for a given set of attributes in a dimension.
- Lesson 5: Defining Relationships Between Dimensions and Measure Groups.
- SQL Server Analysis Services Attribute Relationships.
Develop new custom attributes on dimensions.
- Custom Attribute Names in Role Playing Dimensions for SSAS.
- Add an Attribute to a Dimension.
Detect possible design flaws in attribute relationships.
- Analysis Service Performance Guide
Implement time dimensions in cubes.
Manage SSAS parent-child dimensions.
- Parent-Child Dimensions.
- SQL Server Analysis Services Interview Questions Part II – Dimensions.
Dimension types.
- SQL Server Analysis Services Interview Questions Part II – Dimensions.

Design a schema to support cube architecture

Multidimensional modeling starting from a star schema.
- Creating a Star Schema Using a Data Source View.
- Designing the Star Schema Database
Relational modeling for a data source view.
- DWH Schemas
- Introduction to the SQLBI Methodology
Choose or create a topology.
- DWH Schemas
Identify the appropriate data types with correct precision and size.
- Data Types in Analysis Services.
- Tips for Optimizing SQL Server OLAP/Analysis Services: Data Warehouse and Datamart Performance.

Create and configure measures

Logically group measures and configure Measure Group Properties.
- SSAS – Best Practices and Performance Optimization – Part 3 of 4.
- Measures and Measure Groups.
Select appropriate aggregation functions.
- Use Aggregate Functions.
Format measures.
- Configure Measure Properties.
Design the measure group for the correct granularity
- SQL Server Analysis Service Levels of Granularity.
- Defining Dimension Granularity within a Measure Group.

Implement a cube

Use SQL Server Data Tools – Business Intelligence (SSDT-BI) to build the cube.
- Multidimensional Modeling (Adventure Works Tutorial).
- Creating a Sample SSAS Project and Cube
Use SSDT-BI to do non-additive or semi-additive measures in a cube.
- Define Semiadditive Behavior.
Define measures.
- Defining a Cube.
- Creating a Cube using the Cube Wizard.
Specify perspectives.
- Lesson 9: Defining Perspectives and Translations.
- SQL Server Analysis Services Perspectives.
Define dimension usage.
- Dimension Relationships.
Define cube-specific dimension properties.
- Lesson 4: Defining Advanced Attribute and Dimension Properties.
Define measure groups.
- Create Measures and Measure Groups.
Implement reference dimensions.
- Dimension Relationships.
Implement many-to-many relationships.
Implement fact relationships.
- Dimension Relationships.
Implement role-playing relationships.
- Defining Role Playing Dimensions for SQL Server Analysis Services.
Create and manage linked measure groups and linked dimensions.
- Controlling the use of linked objects in SQL Server Analysis Services.
- Define Linked Dimensions
Create actions.
- Lesson 8: Defining Actions.

Create Multidimensional Expressions (MDX) queries

Identify the structures of MDX and the common functions (tuples, sets, TopCount, SCOPE, and more).
Identify which MDX statement would return the required result.
- MDX Function Reference (MDX)
Implement a custom MDX or logical solution for a prepared case task.

Implement custom logic in a data model

Define key performance indicators (KPIs).
- Testing KPIs for SQL Server Analysis Services.
- Lesson 7: Defining Key Performance Indicators (KPIs).
Define calculated members.
- Defining Calculated Members.
Create relative measures (growth, YoY, same period last year).
- Handling Relative Time Periods.
Percentage of total using MDX.
- Using MDX to Calculate Both Values and Percentages for Analysis Services.
- MDX for Percent of Total for a subset of a Hierarchy.
Named sets.
- Create Named Sets.
Implement ranking and percentile.
- Rank (MDX).
- Ranking results from MDX queries.
Define MDX script to import partial PowerPivot model.
- Analysis Services MDX Query Designer (Power Pivot).

Implement storage design in a multidimensional model

Create aggregations.
Create partitions.
Storage modes.
- Partition Storage Modes and Processing.
- How To Implement Proactive Caching in SQL Server Analysis Services SSAS.
Define proactive caching.
- How To Implement Proactive Caching in SQL Server Analysis Services SSAS.
- Proactive Caching (Partitions).
Manage write-back partitions.
- SQL Server Analysis Services Partition Maintenance and Processing, Write enable partition.
- Set Partition Writeback.
Implement linked cubes.
- Controlling the use of linked objects in SQL Server Analysis Services.
- Define Linked Measure Groups.
Implement distributed cubes.
- Create and Manage a Remote Partition (Analysis Services).

Select an appropriate model for data analysis

Select Tabular versus Multidimensional based on scalability needs.
Traditional hierarchical.
- Comparing Tabular and Multidimensional Solutions (SSAS).
Data volume.
- Comparing Tabular and Multidimensional Solutions (SSAS).
Select appropriate organizational BI, such as corporate BI or PowerBI, and team and personal BI needs and data status.
- Comparing Tabular and Multidimensional Solutions (SSAS).

Manage, maintain, and troubleshoot a SQL Server Analysis Services (SSAS) database

Analyze data model performance

Identify performance consequences of data source view design.
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.3).
Optimize performance by changing the design of the cube or dimension.
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.1 and 2.2).
Analyze and optimize performances of an MDX/DAX query.
Optimize queries for huge data sets.
Optimize MDX in the calculations.
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.4).
Performance monitor counters.
- Performance Counters (SSAS).
Select appropriate Dynamic Management Views for Analysis Services.
Analyze and define performance counters
- Performance Counters (SSAS).
- SSAS Operation Guide, chapter 3.
Monitor growth of the cache
- Part 5: An Introduction to SSAS Performance and SQL Sentry Performance Advisor for Analysis Services.
- Performance Counters (SSAS), Cache.
Define and view logging options
- Log operations in Analysis Services.
- SSAS – Best Practices and Performance Optimization – Part 4 of 4.

Process data models

Define processing of tables or partitions for tabular and multidimensional models
Define processing of databases, cubes, and dimensions for multidimensional models.
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 4).
- SQL Server Best Practices Article.
Select full processing versus incremental processing.
Define remote processing;
- Remote Processing (Analysis Services).
Define lazy aggregations.
- SSAS Processing Mode (Lazy Aggregations).
Automate with Analysis Management Objects (AMO) or XML for Analysis (XMLA).
- Create SQL Server Analysis Services Partitions using AMO.
- Developing with Analysis Management Objects (AMO).
Process and manage partitions by using PowerShell.

Troubleshoot data analysis issues

Use SQL Profiler.
- Use SQL Server Profiler to Monitor Analysis Services
Troubleshoot duplicate key dimension processing errors.
- SQL Server Analysis Services SSAS Processing Error Configurations.
- You get “A duplicate attribute key has been found when processing ..” error message when processing a dimension.
Error logs and event viewer logs of SSAS.
Mismatch of data: incorrect relationships or aggregations.
Dynamic security issues.
Validate logic and calculations.

Deploy SSAS databases

Deployment Wizard.
- Using the SQL Server Analysis Services (SSAS) Deployment Wizard.
- Running the Analysis Services Deployment Wizard.
Implement BIDS.
Deploy SSMS.
- Deploy Model Solutions Using XMLA.
Test solution post deployment.
- SQL Server : Unit Testing SQL Server OLAP Cubes Using C#
- SSAS Operation Guide, chapter 4.
Decide whether or not to process.
- Avoid Common SQL Server Analysis Services Problems.
Test different roles.
- SQL Server Analysis Services – Testing Cube Roles.
- Configuring permissions for SQL Server Analysis Services.

Install and maintain an SSAS instance

Install SSAS.
- Install Analysis Services in Tabular Mode.
- Install SQL Server 2012 from the Installation Wizard (Setup).
Install development tools.
- SQL Server Data Tools (SSDT) is Missing After Installing SQL Server 2014.
- Download SQL Server Data Tools (SSDT or SSDT-BI).
Identify development and production installation considerations.
- Deploying Analysis Services Development and Production Systems
Upgrade SSAS instances.
- Upgrade Analysis Services
Define data file and program file location.
- SSAS Operation Guide, chapter 2.5.
- Analysis Services Configuration – Data Directories.
Plan for Administrator accounts.
- SSAS Operation Guide, chapter 5.4
Define server and database level security.
- SSAS Operation Guide, chapter 5.
Support scale-out read-only.
- SSAS Operation Guide, chapter 7.5
Update SSAS (service packs).
- How to obtain the latest service pack for SQL Server 2012.
- How to obtain the latest service pack for SQL Server 2014.
- KB2755533 How to obtain the latest SQL Server 2012 service pack
- KB2527041 How to obtain the latest SQL Server 2008 R2 service pack
- Microsoft SQL Server support lifecycle
- KB321185 How to determine the version and edition of SQL Server and its components
- KB957826 The builds for all SQL Server versions
- KB822499 Naming schema and Fix area descriptions for SQL Server software update packages
- KB824684 Description of the standard terminology that is used to describe Microsoft software updates
Install and maintain each instance type of Analysis Services including PowerPivot.
Restore and import PowerPivot.
- Power Pivot and SSAS (Tabular) Restore
Back up and restore by using PowerShell.

Build a tabular data model

Configure permissions and roles in a tabular model

Configure server roles.
- Installing Analysis Services 2012 – Tabular Model.
Configure SSAS database roles.
- Roles and Permissions (Analysis Services).
- Securing the Tabular BI Semantic Model, Managing roles and permissions
Implement dynamic security (custom security approaches).
- Implement Dynamic Security by Using Row Filters.
- Securing the Tabular BI Semantic Model, Dynamic security
Role-based access.
- Roles (SSAS Tabular)
- Securing the Tabular BI Semantic Model, Managing roles and permissions
Test security permissions.
- Securing the Tabular BI Semantic Model
Implement cell-level permissions.
- Roles (SSAS Tabular).

Implement a tabular data model

Define tables, import data.
- Getting started with Tabular Model in SQL Server 2012 – Part 1
- Lesson 2: Add Data
Define calculated columns.
- Lesson 6: Create Calculated Columns.
Define relationships.
- Lesson 5: Create Relationships.
Define hierarchies and perspectives.
Manage visibility of columns and tables.
- Getting started with the SQL Server 2012 Tabular Model – Part 2
Embed links.
- Getting started with the SQL Server 2012 Tabular Model – Part 2
Optimize BISM for Power View.
- Optimizing High Cardinality Columns in VertiPaq
- Power View: Explore, visualize, and present your data
Mark a date table.
- Getting started with the SQL Server 2012 Tabular Model – Part 2
- Lesson 4: Mark as Date Table.
Sort a column by another column.
- Sorting Dates Columns in PowerPivot v2 and BISM Tabular.

Implement business logic in a tabular data model

Implement measures and KPIs.
- Getting started with the SQL Server 2012 Tabular Model – Part 2
- Lesson 8: Create Key Performance Indicators
Implement Data Analysis Expressions (DAX).
Define relationship navigation.
- Relationships (SSAS Tabular)
- Many-to-Many Relationships in DAX.
Implement time intelligence.
- SQL Server Analysis Service DAX Time Intelligence.
- Time Intelligence Functions (DAX)
Implement context modification.
- Understanding the SQL Server 2012 BI Semantic Model (BISM)
- Filter Functions (DAX)

Implement data access for a tabular data model

Manage partitions.
- Getting started with the SQL Server 2012 Tabular Model – Part 2
- Lesson 11: Create Partitions
Processing.
- Processing tabular models 101
Select xVelocity versus DirectQuery for data access.
- Tabular query modes: DirectQuery vs. In-Memory.

Build a report with SQL Server Reporting Services (SSRS)

Design a report

Select report components (crosstab report, Tablix).
- Tablix Data Region (Report Builder and SSRS).
- SQL Server 2012 Tutorials: Reporting Services.
Design chart.
- SQL Server Reporting Services Charts Tips.
Data visualization components.
- Create a Simple Report with the Wizard.
Design report templates (Report Definition Language).
- Report Definition Language (SSRS).
Identify the data source and parameters.
- SQL Server Reporting Services Parameters Tips.
- Data Connections, Data Sources, and Connection Strings (SSRS).
Design a grouping structure.
- Understanding Groups (Reporting Services).
Drill-down reports.
Drill-through reports.
- SQL Server Reporting Services Drill Through Tips.
- Drillthrough Reports (Report Builder and SSRS).
Determine if any expressions are required to display data that is not coming directly from the data source.
- Data Connections, Data Sources, and Connection Strings (SSRS), Expression based connection strings.

Implement a report layout

Formatting.
- Lesson 5: Formatting a Report (Reporting Services)
- SSRS Tutorial videos.
Apply conditional formatting.
- SQL Server Reporting Services Expressions Tips and Tricks.
Page configuration.
- Pagination in Reporting Services (Report Builder and SSRS)
Implement headers and footers.
- Add or Remove a Page Header or Footer (Report Builder and SSRS).
Implement matrixes, table, chart, images, list, indicators, maps, and groupings in reports.
Use Report Builder to implement a report layout.
- Page Layout and Rendering (Report Builder and SSRS).
Create a range of reports using different data regions.
- Data Regions and Maps (Report Builder and SSRS).
Define custom fields (implementing different parts of the report).
- Dataset Fields Collection (Report Builder and SSRS).
Implement collections (global collections).
- Built-in Globals and Users References (Report Builder and SSRS).
Define expressions.
- Expressions (Report Builder and SSRS).
Implement data visualization components.
- Tables, Matrices, and Lists (Report Builder and SSRS).
Identify report parts.
- Report Design View (Report Builder)
implement group variables and report variables.
- Report and Group Variables Collections References (Report Builder and SSRS).
Design for multiple delivery extension formats.
- Interactive Functionality for Different Report Rendering Extensions (Report Builder and SSRS).

Configure authentication and authorization for a reporting solution

Configure server-level and item-level role-based security.
- SQL Server Reporting Services 2012 Permissions.
- Security and Protection (SSRS).
Configure reporting service security (setup or addition of role).
- SQL Server Reporting Services 2012 Permissions.
Authenticate against data source.
- Specify Credential and Connection Information for Report Data Sources.
Store credential information.
- Specify Credential and Connection Information for Report Data Sources.
Describe Report Server security architecture and site level security.
- OVERVIEW OF SQL SERVER REPORTING SERVICES 2012 ARCHITECTURE, FEATURES, AND TOOLS.
- Authorization in Reporting Services.
Create system level roles, item level security.
- Predefined Roles.
- SQL Server Reporting Services 2012 Permissions.
Create a new role assignment.
- SQL Server Reporting Services 2012 Permissions.
Assign Windows users to roles.
- SQL Server Reporting Services 2012 Permissions.
Secure reports using roles.
- SQL Server Reporting Services 2012 Permissions.
Configure SharePoint groups and permissions.
- Set Permissions for Report Server Items on a SharePoint Site (Reporting Services in SharePoint Integrated Mode)
Define varying content for different role memberships.
- SQL Server Reporting Services 2012 Permissions.

Implement interactivity in a report

Drilldown.
Drillthrough.
- Drillthrough Reports (Report Builder and SSRS).
Interactive sorting.
- Interactive Sorting for a SQL Server Reporting Services Report.
Parameters: (databound parameters, multi-value parameters).
- Working With Multi-Select Parameters for SSRS Reports.
Create dynamic reports in SSRS using parameters.
- Creating Dynamic Report Filters in SQL Server Reporting Services SSRS.
Implement show/hide property.
- Hide an Item (Report Builder and SSRS).
Actions (jump to report).
- Add a Bookmark to a Report (Report Builder and SSRS)
Filters.
- Creating Dynamic Report Filters in SQL Server Reporting Services SSRS.
Parameter list.
- SQL Server Reporting Services Using Multi-value Parameters.
Fixed headers.
- SQL Server Reporting Services Report Manager Tips and Tricks
Document map.
- SQL Server Reporting Services Bookmarks and Document Maps.
Embedded HTML.
- Add HTML into a Report (Report Builder and SSRS)

Troubleshoot reporting services issues

Query the ReportServer database.
- Retrieve SSRS report server database information.
View Reporting Services log files.
- Reporting Services Logging.
- SQL Server Reporting Services (SSRS) Log Files for Troubleshooting.
Use Windows Reliability and Performance monitor data for troubleshooting.
- Using PerfMon for SQL Server Reporting Services Performance Management.
- Performance Counters for the ReportServer:Service and ReportServerSharePoint:Service Performance Objects.
Use the ReportServer: define service and web service objects.
- Report Server Web Service.
Monitor for long-running reports.
Rendering, and connectivity issues.
- SQL Server Reporting Services 2012 SP2 CU3 Report Rendering Issues.
- Troubleshooting Reports: Report Performance.
Use SQL Profiler.
- Using SQL Profiler for SSRS Reports – Missing main dataset SQL Query in trace.
Perform data reconciliation for incorrect relationships or aggregations.
- TESTING SSRS REPORTS
Detect dynamic security issues.
- SQL Server Reporting Services Security Tips
Validate logic and calculations.
- SQL Server Reporting Services Best Practices Tips.

Manage a report environment

Manage subscriptions and subscription settings.
- Subscriptions and Delivery (Reporting Services).
Define data-driven subscriptions.
- Data-Driven Subscriptions.
Manage data sources.
- Data Sources Supported by Reporting Services (SSRS).
Integrate SharePoint Server.
- Install Reporting Services SharePoint Mode for SharePoint 2013.
Define email delivery settings.
- Configure a Report Server for E-Mail Delivery (Reporting Services).
Manage the number of snapshots.
- Working with Report Snapshots in SQL Server Reporting Services SSRS.
- Creating, Modifying, and Deleting Snapshots in Report History.
Manage schedules.
- Schedules.
Running jobs, and report server logs.
- Manage a Running Process.
- Report Server Execution Log and the ExecutionLog3 View.
Manage report server databases.
- Administer a Report Server Database.
Manage the encryption keys.
- SQL Server Reporting Services (SSRS) Encryption Key.
- Configure and Manage Encryption Keys.
Set up the execution log reporting.
- SQL Server Report Server 2008 R2 Execution Log Reports.
- Report Server Execution Log and the ExecutionLog3 View.
Review the reports.
- SQL Server Reporting Services Best Practices Tips.
Configure site-level settings.
- Site Settings Page (Report Manager)
Design report lifecycle.
- Report Life Cycle: Creating, Managing, and Delivering Reports.
Automate management of reporting services.
Create a report organization structure.
- Reporting Services Concepts (SSRS)
Install and configure reporting services.
- Install Reporting Services Native Mode Report Server (SSRS)
Deploy custom assemblies.
- SQL Server Reporting Services Custom Code Assemblies.
- Deploying a Custom Assembly.

Configure report data sources and datasets

Select appropriate query types (stored procedure versus table versus text only).
- Pros and Cons: Stored Procedures–vs.- Embedded Queries–vs.- Views in an SSRS Dataset
Configure parameterized connection strings (dynamic connection strings).
- Data Connections, Data Sources, and Connection Strings (SSRS), Expression based connection strings.
Define filter location (dataset versus query).
- MSBI(SSRS) Training –SSRS Report Parameters, Filters, Query Parameters.
- Add a Filter to a Dataset (Report Builder and SSRS).
Configure data source options, for example, extract and connect to multiple data sources.
- Data Connections, Data Sources, and Connection Strings (SSRS)
Shared and embedded data sources and datasets.
- Create, Modify, and Delete Shared Data Sources (SSRS)
- Embedded and Shared Datasets (Report Builder and SSRS)
Use custom expressions in data sources.
- Data Connections, Data Sources, and Connection Strings (SSRS), Expression based connection strings.
Connect to Microsoft Azure SQL database.
- SQL Azure Connection Type (SSRS)
Connect to Microsoft Azure Marketplace.
- SSRS Report using dataset.
- Microsoft Azure Marketplace Language.
Implement DAX and MDX queries to retrieve appropriate data sets.
Work with non-relational data sources, such as XML or SharePoint lists.
- SharePoint List Connection Type (SSRS)
- XML Connection Type (SSRS).
Connect to HDInsight Server.
- SQL Server Reporting Services

Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server (Link do Exame)
Links para Estudo

Plan business intelligence (BI) infrastructure

Design BI infrastructure

Design a reporting solution

Design an ETL solution

Design SSIS package execution
Plan deploy SSIS solutions
Design package configurations for SSIS packages
- Create Package Configurations

Exam 70-475: Designing and Implementing Big Data Analytics Solutions (Link do Exame)
Links para Estudo

Design big data batch processing and interactive solutions

Design big data real-time processing solutions

Operationalize end-to-end cloud analytics solutions

Caso vocês queiram participar de um grupo do Whatsapp voltado para certificações Microsoft, acessem o link https://chat.whatsapp.com/invite/0pnHXak46QCHOMfGXOD2OC (Whatsapp) ou https://t.me/certificacao (Telegram) pelo seu celular e aproveitem o conteúdo e as dicas que são enviadas lá!

Quer saber TUDO sobre as provas de certificação da área de dados? Assista ao Webinar sobre Certificações de Data Platform, que participei junto com o Caio Amante, Danilo Cardoso, Maruan Aawar e Meirieli Ribeiro:

Seguem alguns links que podem ajudá-los nos seus estudos para as provas de certificação:

Um abraço e até o próximo post.

Por quê se certificar?

Como são as provas de certificação Microsoft?

E as provas beta? Como funcionam?

Como me tornar um MCSA na Data Plataform?

Como me tornar um MCSE Data Management and Analytics?

Microsoft Technology Associate (MTA)

MCSA SQL Server 2012/2014

Create database objects

Work with data

Modify data

Troubleshoot and optimize

Install and configure

Maintain instances and databases

Optimize and troubleshoot

Manage Data

Implement security

Implement high availability

Design and Implement a Data Warehouse (11%)

Design and implement dimensions

Design and implement fact tables

Extract and Transform Data (23%)

Define connection managers

Design data flow

Implement data flow

Manage SSIS package execution

Implement script tasks in SSIS

Load Data (27%)

Design control flow

Implement package logic by using SSIS variables and parameters

Implement control flow

Implement data load options

Implement script components in SSIS

Configure and Deploy SSIS Solutions (24%)

Troubleshoot data integration issues

Install and maintain SSIS components

Implement auditing, logging, and event handling

Deploy SSIS solutions

Configure SSIS security settings

Build Data Quality Solutions (15%)

Install and maintain Data Quality Services

Implement master data management solutions

Create a data quality project to clean data

Bonus Content

MCSA SQL Server 2016

MCSA SQL 2016: Database Development (Developer)

MCSA SQL 2016: Database Administrator (DBA)

MCSA SQL 2016: Business Intelligence Developer (BI)

MCSA SQL 2016: Machine Learning (BI)

Read and explore big data

Process big data

Build predictive models with ScaleR

Use R Server in different environments

Prepare Data for Analysis in Azure Machine Learning and Export from Azure Machine Learning

Develop Machine Learning Models

Operationalize and Manage Azure Machine Learning Services

Use Other Services for Machine Learning

MCSA SQL 2016: BI Reporting (BI)

Consume and Transform Data By Using Power BI Desktop

Model and Visualize Data

Configure Dashboards in the Power BI Service

Consume and Transform Data by Using Microsoft Excel Connect to data sources

Model Data

Visualize Data

MCSA SQL 2016: Data Engineering with Azure (BI)

Administer and Provision HDInsight Clusters

Implement Big Data Batch Processing Solutions

Implement Big Data Interactive Processing Solutions

Implement Big Data Real-Time Processing Solutions

MCSA SQL 2016: Azure Database Development (DEV)

Design and implement database solutions for Microsoft SQL Server and SQL Database

Manage database management systems (DBMS) security

Design for high availability, disaster recovery, and scalability

Monitor and manage database implementations on Azure

MCSE 2016 Data Management and Analytics

Design a database structure

Design databases and database objects

Design database security

Design a troubleshooting and optimization solution

Implement database objects

Implement programming objects