Click on the banner to learn about and purchase my database training on Azure

Microsoft Certifications - Tips, Links, and Study Materials for SQL Server 2016 MCSA and MCSE Tests

Views: 16.334 views

In this article

This post is the 2 part of 13 in the series. Microsoft Certification
Reading Time: 78 minutes

Hello people,
All right with you ?

I'm a little absent here on the blog, but soon I'll be posting more often as soon as I finish some projects and consulting I'm doing. In the meantime, I couldn't help but share with you some tips, materials, and links that can help you prepare for your SQL Server 2016 MCSA and MCSE certification exams and exams, just as I've been preparing for the past few months.

If you are preparing for your first certification exam and want to start with MTA, I created the post Microsoft Certifications - Portuguese Study Material for Your First Certification (MTA) to help and clarify who is just starting out in this certification world and wants to know more about this certification.

First of all, let's take a look at Microsoft's current certifications for the Data Platform and start getting familiar with the acronyms:

  • Microsoft Technology Associate (MTA): This is Microsoft's first and easiest certification exam, designed for people just starting their careers and still getting familiar with the technology. She not is a prerequisite for taking the MCSA and MCSE tests and taking it is that is optional.

    As an incentive to take the MTA test, Microsoft gives you a small discount on taking this exam (you pay $ 77 instead of $ 100 for other MCSA and MCSE tests)

  • Microsoft Certified Solutions Associate (MCSA): Medium / high level test, aimed at people who already have experience with the technologies involved.
    MCSA certification is a prerequisite for MCSE certification.
  • Microsoft Certified Solutions Expert (MCSE): Microsoft's latest level of certification exams, MCSE exams are often the most difficult of Microsoft exams, and should be performed by highly-skilled professionals with extensive experience and expertise in the technologies in question.

Why make sure?

The BornToLearn blog, known worldwide by the Microsoft technical community, has posted a post where they cite 6 advantages that professionals can gain by becoming certified (You can check out the post this link here), which is summarized in the image below:

  • Prior to a certification, it is a challenge to start advancing in your technology career. After certification, you have a strong impetus for the next big hurdle. In fact, 33% of certified professionals said they got a job after certification. Also, getting certified is addictive.
  • Keeping up when you're new to work is a challenge. The good news is that certification provides you with a proven foundation of real-world and expert knowledge to build, so you're ready to get up and running 39% faster than your peers. If you are still unconvinced, 38% of IT professionals said that certification helped them perform complicated tasks with more confidence. It is science.
  • It is a struggle to differentiate yourself and show that you are the best person for the job when you are starting in the tech industry. Isn't it good to know that 91% of hiring managers consider certification as part of their hiring criteria? We think so. Going on interviews knowing that you are certified will make you feel great.
  • Stay current and never risk having obsolete skills. When you maintain your certifications, you can be confident that your skills are up to date. So you can be confident in the experience you have in gaining new tricks, such as new technologies and features, as industry standards change.
  • Getting impatient for the next promotion? The solution is quite simple. If you are certified, you get more salary increases and more chances to rise in your career. Plus, you get right out of the gate getting 15% more on average than your uncertified peers.
  • Perhaps the most important. When you are certified, you are recognized in the industry as an expert. Employers like their certified hires so much that they usually keep them around 15% more than other employees. When will you be certified ??

Video of MVP Rafael Bernares about the importance of certification

What do Microsoft certification exams look like?

If you have never done so, I would like to prepare you well, as Microsoft certification exams are often quite difficult as:

  • Some proofs have discursive questions, where you need to write SQL queries following various rules, as stated.
  • In some questions, the answer is a composition of 4 options, among 10, where you must mark the correct alternatives and align in the correct order of execution. The chance that you will hit a question like this "in the kick" is very small.
  • In some questions, all the answers are correct, but you should choose the option that has the best performance, ie the cheapest, financially, for example.
  • In some stages of the test, you have no chance to go back to the question and think better of the correct answer: You clicked forward, there is no turning back.
  • Usually the time is short: 3 mins per question and some have a statement with more than 10 lines of text.

Microsoft certification exams cost $ 100 (If you are a student, you get 50% off. See how to get your discount HERE) and you must get a grade equal to or greater than 700, worth 1000, to pass the exam (700 is not 70% of exam).

These exams are usually translated into Portuguese (minus Beta or newly released exams, where there is only English language option), but technical terms are often translated as well (View = Vision, Trigger = Trigger, etc.), which may end up confusing you during the race. In my opinion, a professional who wants to be certified and pursue a career in DBA can no longer afford not to know at least read English texts and articles.

NOTICE: If you have been in the IT field for some time, you may have heard of some tests that are sold / distributed on the internet with the same questions that are applied in the certification exam. Many IT “professionals” end up acquiring this type of test and, instead of studying and learning to take the test, they end up memorizing the questions and answers (they often do not even know what they are memorizing) and manage to pass the exams. Avoid this type of "study", because it is useless to have several certificates in the curriculum and when you join a company, which hired you because of these certifications, you end up "feeling ashamed" for not having all the knowledge that you seemed to have. In the technical community, this type of “study” is highly repudiated and discouraged.

And the beta tests? How do they work?

When Microsoft is preparing to launch certification exams, it makes some vouchers available to IT professionals (usually 300 proof vouchers, worldwide) so that they take these test exams and give their feedback on the questions.

Although they are BETA tests, their results are computed and are valid for the certification process, just as a paid test. As explained on this link, if you pass the beta test, you do not need to repeat the test when the final version comes out. If you do not pass the beta test, you will not be able to repeat the beta test again and you should wait for the final version to exit to repeat the exam.

Since the tests are still in the testing phase (São BETA), you should be aware of the possibility of errors in the questions. In addition, tests are generally available in English only and instead of some questions chosen at random, the test contains a lot more questions (usually 59-62 instead of 35-40 for a “normal” test), so the beta test usually be greater than the final exam.

Another difference from the final version of the tests to the beta version, is that in the final version, the result is generated as soon as you finish the test, while in Beta tests, the result is only released several weeks later (when the final version of the test is available) - Usually about 4 months.

How do I become an MCSA on Data Plataform?

As you may know, the SQL Server 2016 MCSA Certification Path has changed a lot compared to SQL Server 2012 / 2014, where you needed to pass 3 proofs, 1 from Database Dev, another from Bank Administration (DBA), and another BI, which didn't really make much sense, as they are distinct careers within Microsoft's Data Platform area.

Now in SQL Server 2016 MCSA Certification Path, you need to pass only 2 exams from the same area of ​​expertise, be it DEV, DBA or BI. That is, if you pass only 1 proof of DEV and another of DBA, you not will have the title of MCSA.

Understand better by viewing the image below:

How do I become an MCSE Data Management and Analytics?

If you want to become a Microsoft Certified Solutions Expert on Microsoft Data Platform, you will need to meet the 2 requirements below:

UPDATE: In June 2017, Microsoft started to recognize as MCSE professionals who have 2 or more MCSA's in the same area, that is, if you have 2 MCSA of Data Platform, you are already an MCSE as soon as you pass the 2nd test of your 2nd MCSA.

Note: In MCSE testing, your MCSA's area of ​​expertise is indifferent to obtaining the title. Since you are MCSA, you can take any of the MCSE exams to be recognized as a Microsoft Certified Solutions Expert on the Microsoft Data Platform. That is, if you are a BI MCSA (Passed on 70-767 and 70-768), for example, and pass on 70-464, which is theoretically focused on Database Developers, you will become MCSE Data Management and Analytics.

Microsoft Technology Associate (MTA)

Certification Link

98-364 Exam: Database Fundamentals (Exam Link)
Study Links

Understanding Core Database Concepts

Create Database Objects

Manipulate Data

Understand Data Storage

Administer a Database

MCSA SQL Server 2012 / 2014

Certification Link

If you want to become a SQL Server MCN Certified 2012 / 2014 Professional (Yes, it is a certification for both versions), you will need to pass the 3 exams below.

70-461 Exam: Querying Microsoft SQL Server 2012 / 2014 (Exam Link)
Study Links

Create database objects

Work with data

Modify data

Troubleshoot and optimize

70-462 Exam: Administering Microsoft SQL Server 2012 / 2014 Databases (Exam Link)
Study Links

Install and configure

Maintain instances and databases

Optimize and troubleshoot

Manage Data

Implement security

Implement high availability

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

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 quality data project to clean data

Bonus Content

MCSA SQL Server 2016

Certification Link

As explained above, to become a SQL Server 2016 MCSA you will need to pass two exams from the same area of ​​expertise, be it BI, DEV or DBA.

If you already have the MCSA SQL Server 2012 / 2014 title and would like to upgrade your certification to SQL Server 2016, simply make 1 proof of the category you want to obtain the MCSA SQL Server 2016 title as below.

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

The upgrade exam for SQL Server 2016 certification is paid as normal as any other exam (When the SQL 2016 exams began, Microsoft released the free upgrade, but the offer has already expired).

MCSA SQL 2016: Database Development (Developer)

70-761 Exam: Querying Data with Transact-SQL (Exam Link)
Study Links

Manage data with Transact-SQL

Query data with advanced Transact-SQL components

Program databases by using Transact-SQL

70-762 Exam: Developing SQL Databases (Exam Link)
Study Links

Design and implement database objects

Implement programmability objects

Manage database concurrency

Optimize database objects and SQL infrastructure

MCSA SQL 2016: Database Administrator (DBA)

70-764 Exam: Administering a SQL Database Infrastructure (Exam Link)
Study Links

Configure data access and auditing

Manage backup and restore of databases

Manage and monitor SQL Server instances

70-765 Exam: Provisioning SQL Databases (Exam Link)
Study Links

Implement SQL in Azure

Manage databases and instances

Deploy and migrate applications

MCSA SQL 2016: Business Intelligence Developer (BI)

70-767 Exam: Implementing a SQL Data Warehouse (Exam Link)
Study Links

Design and implement a data warehouse

Extract, transform, and load data

Integrate solutions with cloud data and big data

Build data quality solutions

70-768 Exam: Developing SQL Data Models (Exam Link)
Study Links

Design and implement a data warehouse

Design to tabular BI semantic model

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

Configure and maintain SQL Server Analysis Services

MCSA SQL 2016: Machine Learning (BI)

70-773 Exam: Analyzing Big Data with Microsoft R (Exam Link)
Study Links

Read and explore big data

Process big data

  • Manage data sets
  • Process text using RML packages
  • Build predictive models with ScaleR

    Use R Server in different environments

    70-774 Exam: Perform Cloud Data Science with Azure Machine Learning (Exam Link)
    Study Links

    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)

    70-778 Exam: Analyzing and Visualizing Data with Microsoft Power BI (Exam Link)
    Study Links

    Consume and Transform Data By Using Power BI Desktop

    Model and Visualize Data

    Configure Dashboards in the Power BI Service

    70-779 Exam: Analyzing and Visualizing Data with Microsoft Excel (Exam Link)
    Study Links

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

    Model Data

    View Date

    MCSA SQL 2016: Data Engineering with Azure (BI)

    70-775 Exam: Perform Data Engineering on Microsoft Azure HDInsight (Exam Link)
    Study Links

    Administer and Provision HDInsight Clusters

    Implement Big Data Batch Processing Solutions

    Implement Big Data Interactive Processing Solutions

    Implement Big Data Real-Time Processing Solutions

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

    MCSA SQL 2016: Azure Database Development (DEV)

    70-777 Exam: Implementing NoSQL Solutions with DocumentDB and Azure Search (Exam Link)
    70-473 Exam: Designing and Implementing Cloud Data Platform Solutions (Exam Link)
    Study Links

    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

    Certification Link

    70-462 Exam: Designing Database Solutions for Microsoft SQL Server (Exam Link)
    Study Links

    Design a database structure

    Design databases and database objects

    Design database security

    Design a troubleshooting and optimization solution

    70-464 Exam: Developing Microsoft SQL Server Databases (Exam Link)
    Study Links

    Implement database objects

    Implement programming objects

    Design database objects

    Optimize and troubleshoot queries

    70-465 Exam: Designing Database Solutions for Microsoft SQL Server (Exam Link)
    Study Links

    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 a troubleshooting and optimization solution

  • Design and implement a high availability solution
  • Design a solution to monitor performance and concurrency
  • Design a monitoring solution at the instance level
  • 70-466 Exam: Implementing Data Models and Reports (Exam Link)
    Study Links

    Design dimensions and measures

    Implement and configure dimensions in a cube

    Design a schema to support cube architecture

    Create and configure measures

    Implement a cube

    Create Multidimensional Expressions (MDX) Queries

    Implement custom logic in a data model

    Implement storage design in a multidimensional model

    Select an appropriate model for data analysis

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

    Analyze data model performance

    Process data models

    Troubleshoot data analysis issues

    Deploy SSAS databases

    Install and maintain an SSAS instance

    Build a tabular data model

    Configure permissions and roles in a tabular model

    Implement a tabular data model

    Implement business logic in a tabular data model

    Implement data access for a tabular data model

    Build a report with SQL Server Reporting Services (SSRS)

    Design a report

    Implement a report layout

    Configure authentication and authorization for a reporting solution

    Implement interactivity in a report

    Troubleshoot reporting services issues

    Manage a report environment

    Configure report data sources and datasets

    70-467 Exam: Designing Business Intelligence Solutions with Microsoft SQL Server (Exam Link)
    Study Links

    Plan business intelligence (BI) infrastructure

    BI infrastructure design

    Design a reporting solution

    Design an ETL solution

    70-475 Exam: Designing and Implementing Big Data Analytics Solutions (Exam Link)
    Study Links

    Design big data batch processing and interactive solutions

    Big data design real-time processing solutions

    Operationalize end-to-end cloud analytics solutions

    If you would like to join a Whatsapp group focused on Microsoft certifications, please visit the link (Whatsapp) or (Telegram) on your mobile and enjoy the content and tips that are sent there!

    Want to know ALL about data area certification exams? Watch the Data Platform Certifications Webinar, which I participated with Caio Lover, Danilo Cardoso, Maruan Aawar and Meirieli Ribeiro:

    Here are some links that can help you in your studies for certification exams:

    Regards and see you next post.