Tag: SQL

CertificationData Sciencedatabase

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

The 70-776 Performing Big Data Engineering on Microsoft Cloud Services certification exam tests and validates your expertise in designing analytics solutions and building operationalized solutions on Microsoft Azure. This exam covers data engineering topics around Azure SQL Data Warehouse, Azure Data Lake, Azure Data Factory, and Azure Stream Analytics.

Exam Target Audience

The 70-776 Performing Big Data Engineering on Microsoft Cloud Services certification exam is targeted towards Big Data Professionals. This exam is centered around designing analytics solutions and building operationalized solutions on Microsoft Azure. The primary Azure service areas covered on this Big Data exam are: Azure SQL Data Warehouse, Azure Data Lake, Azure Data Factory, and Azure Stream Analytics.

Candidates with experience and familiarity with the capabilities and features of batch data processing, real-time processing, and operationalization technologies are the targeted audience for this exam. These candidates will be able to apply Microsoft cloud technologies to solution designs, and implement big data analytics solutions.

Skills Measured

Here is a list of the skills and objectives measured on this exam. The percentages on the high level objective areas represents the percentage of the exam that is focused on that objective area.

  • Design and Implement Complex Event Processing By Using Azure Stream Analytics (15-20%)
    • Ingest data for real-time processing
      • Select appropriate data ingestion technology based on specific constraints; design partitioning scheme and select mechanism for partitioning; ingest and process data from a Twitter stream; connect to stream processing entities; estimate throughput, latency needs, and job footprint; design reference data streams
    • Design and implement Azure Stream Analytics
      • Configure thresholds, use the Azure Machine Learning UDF, create alerts based on conditions, use a machine learning model for scoring, train a model for continuous learning, use common stream processing scenarios
    • Implement and manage the streaming pipeline
      • Stream data to a live dashboard, archive data as a storage artifact for batch processing, enable consistency between stream processing and batch processing logic
    • Query real-time data by using the Azure Stream Analytics query language
      • Use built-in functions, use data types, identify query language elements, control query windowing by using Time Management, guarantee event delivery
  • Design and Implement Analytics by Using Azure Data Lake (25-30%)
    • Ingest data into Azure Data Lake Store
      • Create an Azure Data Lake Store (ADLS) account, copy data to ADLS, secure data within ADLS by using access control, leverage end-user or service-to-service authentication appropriately, tune the performance of ADLS, access diagnostic logs
    • Manage Azure Data Lake Analytics
      • Create an Azure Data Lake Analytics (ADLA) account, manage users, manage data sources, manage, monitor, and troubleshoot jobs, access diagnostic logs, optimize jobs by using the vertex view, identify historical job information
    • Extract and transform data by using U-SQL
      • Schematize data on read at scale; generate outputter files; use the U-SQL data types, use C# and U-SQL expression language; identify major differences between T-SQL and U-SQL; perform JOINS, PIVOT, UNPIVOT, CROSS APPLY, and Windowing functions in U-SQL; share data and code through U-SQL catalog; define benefits and use of structured data in U-SQL; manage and secure the Catalog
    • Extend U-SQL programmability
      • Use user-defined functions, aggregators, and operators, scale out user-defined operators, call Python, R, and Cognitive capabilities, use U-SQL user-defined types, perform federated queries, share data and code across ADLA and ADLS
    • Integrate Azure Data Lake Analytics with other services
      • Integrate with Azure Data Factory, Azure HDInsight, Azure Data Catalog, and Azure Event Hubs, ingest data from Azure SQL Data Warehouse
  • Design and Implement Azure SQL Data Warehouse Solutions (15-20%)
    • Design tables in Azure SQL Data Warehouse
      • Choose the optimal type of distribution column to optimize workflows, select a table geometry, limit data skew and process skew through the appropriate selection of distributed columns, design columnstore indexes, identify when to scale compute nodes, calculate the number of distributions for a given workload
    • Query data in Azure SQL Data Warehouse
      • Implement query labels, aggregate functions, create and manage statistics in distributed tables, monitor user queries to identify performance issues, change a user resource class
    • Integrate Azure SQL Data Warehouse with other services
      • Ingest data into Azure SQL Data Warehouse by using AZCopy, Polybase, Bulk Copy Program (BCP), Azure Data Factory, SQL Server Integration Services (SSIS), Create-Table-As-Select (CTAS), and Create-External-Table-As-Select (CETAS); export data from Azure SQL Data Warehouse; provide connection information to access Azure SQL Data Warehouse from Azure Machine Learning; leverage Polybase to access a different distributed store; migrate data to Azure SQL Data Warehouse; select the appropriate ingestion method based on business needs
  • Design and Implement Cloud-Based Integration by using Azure Data Factory (15-20%)
    • Implement datasets and linked services
      • Implement availability for the slice, create dataset policies, configure the appropriate linked service based on the activity and the dataset
    • Move, transform, and analyze data by using Azure Data Factory activities
      • Copy data between on-premises and the cloud, create different activity types, extend the data factory by using custom processing steps, move data to and from Azure SQL Data Warehouse
    • Orchestrate data processing by using Azure Data Factory pipelines
      • Identify data dependencies and chain multiple activities, model schedules based on data dependencies, provision and run data pipelines, design a data flow
    • Monitor and manage Azure Data Factory
      • Identify failures and root causes, create alerts for specified conditions, perform a redeploy, use the Microsoft Azure Portal monitoring tool
  • Manage and Maintain Azure SQL Data Warehouse, Azure Data Lake, Azure Data Factory, and Azure Stream Analytics (20-25%)
    • Provision Azure SQL Data Warehouse, Azure Data Lake, Azure Data Factory, and Azure Stream Analytics
      • Provision Azure SQL Data Warehouse, Azure Data Lake, and Azure Data Factory, implement Azure Stream Analytics
    • Implement authentication, authorization, and auditing
      • Integrate services with Azure Active Directory (Azure AD), use the local security model in Azure SQL Data Warehouse, configure firewalls, implement auditing, integrate services with Azure Data Factory
    • Manage data recovery for Azure SQL Data Warehouse, Azure Data Lake, and Azure Data Factory, Azure Stream Analytics
      • Backup and recover services, plan and implement geo-redundancy for Azure Storage, migrate from an on-premises data warehouse to Azure SQL Data Warehouse
    • Monitor Azure SQL Data Warehouse, Azure Data Lake, and Azure Stream Analytics
      • Manage concurrency, manage elastic scale for Azure SQL Data Warehouse, monitor workloads by using Dynamic Management Views (DMVs) for Azure SQL Data Warehouse, troubleshoot Azure Data Lake performance by using the Vertex Execution View
    • Design and implement storage solutions for big data implementations
      • Optimize storage to meet performance needs, select appropriate storage types based on business requirements, use AZCopy, Storage Explorer and Redgate Azure Explorer to migrate data, design cloud solutions that integrate with on-premises data

You can also view the full objectives list for the 70-776 Performing Big Data Engineering on Microsoft Cloud Services certification exam on the official 70-776 exam page at Microsoft.com.

Training Material

There are not very many study / training materials designed specifically for the 70-776 Perform Big Data Engineering on Microsoft Cloud Services certification exam. As example, Microsoft Press has not published any Exam Ref or Guide books for this exam yet (at the time of writing this.) However, there is still plenty of material available from various sources, both Free and Paid, that range from official product documentation from Microsoft, other articles and videos from Microsoft, as well as training from companies like Opsgility and their SkillMeUp service.

Another interesting resource to utilize is the following recording of the 70-776 Cert Exam Prep session given by James Herring at Microsoft Ignite 2017:

Happy Studying!!

Certificationdatabase

70-473 Cloud Data Platform Solutions Exam – June 2017 Update

The 70-473 Designing and Implementing Cloud Data Platform Solutions certification exam was first published Oct. 27, 2015. A lot of things with Microsoft Azure have changed in the time since it was first published. For this reason Microsoft does publish updates to the various certification exams, and this past June 2017, Microsoft published an update to the exam to bring it in line with the current state of the Azure data platform. This article outlines the current state of this certification exam. Read More

databasePaaSVideo

Create Azure SQL Database in the Azure Portal

Here’s a short video that shows you how to create an Azure SQL Database in the Azure Portal. It also explains how to connect to the database, and how the relationship between Azure SQL Databases and Azure SQL Servers works. Additionally, a few other features are overviewed such as Geo-Replication, Transparent Data Encryption and others.

Please, subscribe to get more videos like this all around Microsoft Azure.

This is one of the first videos I’ve published to the Build Azure YouTube Channel where I’m starting to build out video content to accompany this site. Enjoy!

Certificationdatabase

70-762 Developing SQL Databases Certification Exam

Microsoft has been expanding out the certification offerings around Microsoft Azure. The Provisioning SQL Databases (70-765) exam fits right along with this as it measures expertise around SQL Server and Azure SQL Databases. With the ever increasing adoption of the Cloud, the lines between traditional on-premises style systems / applications and cloud applications is being blurred. This exam fits right along with that as it covers Azure SQL Databases and hosting SQL Server within Virtual Machines (VMs).

Certification Target Audience

The focus on the Developing SQL Databases (70-762) certification exam is centered around SQL Server and Azure SQL Database. The exam is designed to target candidates who build and implement databases across organizations and who ensure high levels of data availability. The ideal candidates responsibilities include creating database files, data types, and tables; planning, creating, and optimizing indexes; ensuring data integrity; implementing views, stored procedures, and functions; and managing transactions and locks.

Skills Measured

Here is a high level list of the skills and objectives measured on the Developing SQL Databases (70-762) exam. The percentages next to each of the objectives represent the percentage of the exam questions that will be focus on that specific objective.

  • Design and implement database objects (25-30%)
    • Design and implement a relational database schema
    • Design and implement indexes
    • Design and implement views
    • Implement columnstore indexes
  • Implement programmability objects (20-25%)
    • Ensure data integrity with constraints
    • Create stored procedures
    • Create triggers and user-defined functions
  • Manage database concurrency (25-30%)
    • Implement transactions
    • Manage isolation levels
    • Optimize concurrency and locking behavior
    • Implement memory-optimized tables and native stored procedures
  • Optimize database objects and SQL infrastructure (20-25%)
    • Optimize statistics and indexes
    • Analyze and troubleshoot query plans
    • Manage performance for database instances
    • Monitor and trace SQL Server baseline performance metrics

When studying for this exam, you’ll certainly want to look at the official exam page from Microsoft for the full list of exam objectives covered. You’ll need to study each and every one of the objectives measured on the exam before attempting to take it.

Training Materials

At the time of writing this summary of the 70-762 Developing SQL Databases exam, there is a limited amount of Exam preparation material available. You’ll need to focus mostly on the Microsoft documentation surrounding the technologies and skills measured on this exam for now. However, there an Exam Reference book from Microsoft Press being published soon!

Below is a summary of the exam reference book:

Exam Ref 70-762 Developing SQL Databases

Prepare for Microsoft Exam 70-762, Developing SQL Databases –and help demonstrate your real-world mastery of skills for building and implementing databases across organizations. Designed for database professionals who build and implement databases across organizations and who ensure high levels of data availability, Exam Ref focuses on the critical-thinking and decision-making acumen needed for success at the MCSA level.

Focus on the expertise measured by these objectives:

  • Design and implement database objects
  • Implement programmability objects
  • Manage database concurrency
  • Optimize database objects and SQL infrastructure

This Microsoft Exam Ref:

  • Organizes its coverage by exam objectives
  • Features strategic, what-if scenarios to challenge you
  • Assumes you have working knowledge of Microsoft Windows, Transact-SQL, and relational databases

Happy Studying!

Certificationdatabase

70-768 Developing SQL Data Models Certification Exam

Microsoft has been expanding out the certification offerings around Microsoft Azure. The Developing SQL Data Models (70-768) exam fits right along with this as it measures expertise around SQL Server and Azure SQL Databases. With the ever increasing adoption of the Cloud, the lines between traditional on-premises style systems / applications and cloud applications is being blurred. This exam fits right along with that as it covers topics that apply to both SQL Server and Azure SQL Database.

Certification Target Audience

The focus on the Developing SQL Data Models (70-768) certification exam is centered around SQL Server and Azure SQL Database topics like SSAS, BI, MDX and DAX. The exam is designed to target candidates who are Business Intelligence (BI) developers who are focused on creating BI solutions that require implementing multidimensional data models, implementing and maintaining OLAP cubes, and implementing tabular data models.

Skills Measured

Here is a high level list of the skills and objectives measured on the Provisioning SQL Databases (70-765) exam. The percentages next to each of the objectives represent the percentage of the exam questions that will be focus on that specific objective.

  • Design a multidimensional business intelligence (BI) semantic model (25-30%)
    • 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 (20-25%)
    • 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) (15-20%)
    • Create basic MDX queries
    • Implement custom MDX solutions
    • Create formulas by using the DAX language
  • Configure and maintain SQL Server Analysis Services (SSAS) (30-35%)
    • Plan and deploy SSAS
    • Monitor and optimize performance
    • Configure and manage processing
    • Create Key Performance Indicators (KPIs) and translations

When studying for this exam, you’ll certainly want to look at the official exam page from Microsoft for the full list of exam objectives covered. You’ll need to study each and every one of the objectives measured on the exam before attempting to take it.

Training Materials

At the time of writing this summary of the Developing SQL Data Models (70-768) exam, there is a limited amount of Exam preparation material available. You’ll need to focus mostly on the Microsoft documentation surrounding the technologies and skills measured on this exam for now. However, there an Exam Reference book from Microsoft Press being published soon!

Below is a summary of the exam reference book:

Exam Ref 70-768 Developing SQL Data Models

Direct from Microsoft, this Exam Ref is the official study guide for the new Microsoft 70-768 Developing SQL Data Models certification exam, the second of two exams required for MCSA: SQL 2016 Business Intelligence Development certification.

Authored by Microsoft Data Platform MVP Stacia Varga, Exam Ref 70-768 Developing SQL Data Models offers professional-level preparation that helps candidates maximize their exam performance and sharpen their skills on the job. It focuses on the specific areas of expertise modern database and business intelligence professionals need to succeed with SQL Server 2016 Analysis Services. Coverage includes:

  • Designing multidimensional BI semantic models
  • Designing tabular BI semantic models
  • Developing queries using MDX and DAX
  • Configuring and maintaining SQL Server Analysis Services

Microsoft Exam Ref publications stand apart from third-party study guides because they:

  • Provide guidance from Microsoft, the creator of Microsoft certification exams
  • Target IT professional-level exam candidates with content focused on their needs, not “one-size-fits-all” content
  • Streamline study by organizing material according to the exam’s objective domain (OD), covering one functional group and its objectives in each chapter
  • Feature Thought Experiments to guide candidates through a set of “what if?” scenarios, and prepare them more effectively for Pro-level style exam questions
  • Explore big picture thinking around the planning and design aspects of the IT pro’s job role

Happy Studying!

Certificationdatabase

70-765 Provisioning SQL Databases Certification Exam

Microsoft has been expanding out the certification offerings around Microsoft Azure. The Provisioning SQL Databases (70-765) exam fits right along with this as it measures expertise around SQL Server and Azure SQL Databases. With the ever increasing adoption of the Cloud, the lines between traditional on-premises style systems / applications and cloud applications is being blurred. This exam fits right along with that as it covers Azure SQL Databases and hosting SQL Server within Virtual Machines (VMs).

Certification Target Audience

The focus on the Provisioning SQL Databases (70-765) certification exam is centered round SQL Server and Azure SQL Database. The exam is designed to target candidates who are Architects, Senior Developers, Infrastructure Specialists, and Development Leads. These candidates should have experience and knowledge of the various cloud service models and service model architectures, data storage options, and data synchronization techniques. It will also be good to have a working knowledge of deployment models, upgrading and migrating databases, and applications and services, in addition to integrating Azure applications with external resources.

Skills Measured

Here is a high level list of the skills and objectives measured on the Provisioning SQL Databases (70-765) exam. The percentages next to each of the objectives represent the percentage of the exam questions that will be focus on that specific objective.

  • Implement SQL in Azure (30-35%)
    • Deploy a Microsoft Azure SQL Database
    • Plan for SQL Server installation
    • Deploy SQL Server instances
  • Manage databases and instances (35-40%)
    • Configure secure access to Microsoft Azure SQL Databases
    • Configure SQL Server performance settings
    • Manage SQL Server instances
  • Deploy and migrate applications (30-35%)
    • Deploy applications to Microsoft Azure SQL Database
    • Deploy applications to SQL Server on Azure Virtual Machines
    • Migrate client applications

When studying for this exam, you’ll certainly want to look at the official exam page from Microsoft for the full list of exam objectives covered. You’ll need to study each and every one of the objectives measured on the exam before attempting to take it.

Training Materials

At the time of writing this summary of the 70-765 Provisioning SQL Databases exam, there is a limited amount of Exam specific training material available. You’ll need to rely fairly heavily on the Microsoft documentation when studying for this exam. No exam guide or prep books are available, however there is a Practice Test available.

Happy studying!

Big DataCertification

70-773 Analyzing Big Data with Microsoft R Certification Exam

In the effort of expanding out the breadth of certification offerings, Microsoft has added the Analyzing Big Data with Microsoft R (70-773) exam. This exam focuses on using Microsoft R Server and SQL R Services for analyzing Big Data.

Certification Target Audience

The focus on the Analyzing Big Data with Microsoft R (70-773) exam is centered around Microsoft R Server and SQL R Services. The exam is designed to target candidates who are Data Scientists or Analysts who are processing and analyzing large data sets using R. This exam will test your familiarity with data structures, as well as basic programming concepts (such as control flow and scope), and your ability to write and debug R code functions.

Skills Measured

Here is a high level list of the skills and objectives measured on this exam:

  • Read and Explore Big Data
    • Read data with R Server
    • Summarize data
    • Visualize data
  • Process Big Data
    • Process data with rxDataStep
    • 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

When studying for this exam, you’ll certainly want to look at the official exam page from Microsoft for the full list of exam objectives. You’ll need to be sure to study every one of them that will be measured on the exam.

Training Materials

At the time of writing this summary of the 70-773 Analyzing Big Data with Microsoft R, the exam is still in Beta as it was just recently published. This means there aren’t any exam guide books, or practice exams available yet. To study for this exam, you’ll need to rely mostly on the Microsoft documentation for the different technologies covered on this exam.

Happy studying!

Certificationdatabase

MCSE: Data Management and Analytics Certification

mcse_datamanagementandanalyticsMicrosoft continues to expand out their certification program to better fit the newly emerging “cloud-based” world of IT. As a result, the existing Microsoft Azure exams as well as new exams being published are being integrated into the broad spectrum of Microsoft certifications; such as the MCSE. The MCSE: Data Management and Analytics certification has recently been introduced as part of a new redesign and restructuring of the MCSE certifications. This article explains some of the details around how you can go about earning the new MCSE: Data Management and Analytics certification.

On the path to earning the MCSE: Data Management and Analytics an MCSA certification will first be earned.

The MCSA Foundation

On the path to earning the MCSE: Data Management and Analytics certification an MCSA certification will first be earned. It’s really good to have a milestone along the way, instead of having to take a tone of exams before claiming any single certification title / credential. Additionally, instead of just a single MCSA choice to earn, there are actually multiple tracks to choose from based on your unique combination of expertise and interests.

Here’s the list of the MCSA certification tracks, along with the exams you’ll need to pass to achieve each of them:

As you can see the MCSA foundations mostly require 2 exams to be passed; these are the newer MCSA’s. The older MCSA for SQL Server 2012/2014 requires 3 exams to be passed to earn. All these MCSA’s fill provide a solid foundation of SQL Server in the specific track that you choose.

MCSE: Data Management and Analytics

Once a qualifying MCSA certification (as listed above) has been earned, the next path to the MCSE: Data Management and Analytics certification is to pass only 1 more exam. Also, best of all, you get to choose which exam you would like to take from a list of elective exams.

Here’s the list of elective exams to choose from to build on top of the MCSA foundation when achieving the MCSE: Data Management and Analytics certification:

Even if you choose to focus on SQL Server 2012/2014 or SQL Server 2016 with the MCSA you earned, it’s up to you if you want to mix some Azure / Cloud expertise into  your certification for the MCSE, Or, you can still with SQL Server for your choice elective exam as well.

Once a qualifying MCSA certification (as listed above) is earned, the next path is to pass 1 more exam to earn the full MCSE: Data Management and Analytics certification!

Happy studying!

Updated May 27, 2017: Microsoft announced a renaming of a few of the MCSA certifications and the addition of more elective exams for the MCSE: Data Management and Analytics certification.

Updated Apr 13, 2017: Added info about MCSA Data Science certification.