Microsoft and Hackster IoT Bootcamp

In December 2017 I attended this free online IoT bootcamp, presented by Microsoft and Hackster.io.

One of the best courses I've seen to date, with some notable guest speakers, Eben Upton, Raspberry Pi Founder.
If you're considering it, then I'd recommend following the pre-setup exactly in order to minimise issues with the labs., day 1 and 2 labs were good, but day 3 labs are bit buggy (covered in the recorded sessions).

There are 9 hands-on labs covered things like connecting a microcontroller/Raspberry Pi and temperature sensors to Azure IoT Hub, Azure Stream Analytics, PowerBI and Azure Functions.
Introduced me to Windows IoT core (an OS compatible with Raspberry Pi).
The 3rd day labs are code (copy/paste) heavy, focused on Cognitive services (facial recognition), bot frameworks and machine learning.

There's no need to buy the full kit they suggest, the bare minimum is (available elsewhere, but these are adafruit links):
Assembled Adafruit Feather HUZZAH ESP8266 WiFi - https://www.adafruit.com/product/3046
AM2302 (wired DHT22) temperature-humidity sensor - https://www.adafruit.com/product/393
Breadboard - https://www.adafruit.com/product/239
Male/Male Jumper wires - https://www.adafruit.com/product/1956
Female/Male Jumper wires - https://www.adafruit.com/product/1954
1x10mm LED
1x 560 Ohm 5% 1/4W Resistor
8GB Micro SD card - https://www.adafruit.com/product/1294
MicroSD card reader/writer - https://www.adafruit.com/product/939
Raspberry Pi 3 Model B - https://www.adafruit.com/product/3055

Enjoy! I certainly did :-)

Test SQL Server connectivity from a Windows Server without installing another package

Recently I wanted to test whether a Windows 2016 server could connect to a SQL Server endpoint (happened to be an Azure SQL Database), but I was on a customers production web server and I didn't want to install tools (e.g. SSMS) to test, a quick google found this great solution, so I wanted to re-post it, many thanks to steverac for this gem.

https://blogs.msdn.microsoft.com/steverac/2010/12/13/test-remote-sql-connectivity-easily/

Troubles with PowerShell Modules

Working with Azure, I regularly have to run PowerShell CmdLets and scripts to get things done.

Unfortunately this means regularly installing and updating Azure PowerShell modules to make use of the latest and greatest features. Sometimes, too I have to downgrade to earlier module versions just to "get stuff done".

The result can be a nasty mess of my powershell environment, leaving behind modules that cannot be removed. For example, I regularly uninstall the AzureRM module with

Uninstall-Module AzureRM

Which completes without error, but a subsequent

Get-Module -ListAvailable

Still reports that the module and sub-scripts are installed. The situation doesn't improve when you try to uninstall the AzureRM module again, this time you receieve an error stating:

No match was found for the specified search criteria and module names "AzureRM"

So, how do you get rid of modules that "seemingly" can't be uninstalled? One solution I dreamt up is as follows:

Get-Module -ListAvailable | where { $_.Name -like "Azure*" } | Select Name | Write-Output -PipelineVariable modname | ForEach { Uninstall-Module $modname.Name }

This utilises the PipelineVariable common parameter to capture the list of "installed" Azure modules and then forcefully uninstall them one-by-one. Be patient, this can take some time to run, but gets the job done.

Stuck! How to change both the local admin account password and account name for an Azure Windows VM

I recently had to change both the account name and account password for a Windows VM running in Azure. Luckily, help is at hand thanks to some PowerShell and the VM Access Virtual Machine extension. No point in re-inventing the wheel, details are in the articles below...

https://blogs.technet.microsoft.com/askpfeplat/2016/02/08/resetting-the-local-admin-name-and-password-for-azure-arm-virtual-machines-with-powershell/

https://docs.microsoft.com/en-us/powershell/module/azurerm.compute/set-azurermvmaccessextension?view=azurermps-4.2.0

Enterprise Application Migration To Azure

The Azure cloud is moving fast in so many areas. Understandably, migration tools that can assist and automate the process of migrating applications to Azure are of great importance in order to encourage a move away from existing infrastructure.

Two such tools that I’ve discovered recently are the Web Apps Migration Assistant, which is detailed here, and the Data Migration Assistant, detailed here.

The Web Apps Migration Assistant will analyse your hosted IIS (and even Linux) web apps, and can migrate them (if compatible) to the Azure App Service as Web Apps.

The Data Migration Assistant targets SQL Servers and analyses them for migration to Azure SQL Database. This tool does not perform the migration, instead it highlights the incompatibilities that exist and suggests remedial action, this could include preparing fixes as Transact-SQL scripts. Tools such as SSMS or SQLPackage.exe can be used to migrate the database to Azure, a good overview of this process is here and a deeper discussion of the process is mentioned in this SQL CAT blog. Of course, you may want to determine if your database is even suitable for running in Azure and tools exist such as this one to help size the database prior to migration.

These two tools could help fast-track your move to the cloud, and whilst some effort will still be required, they remove much of the manual effort that was previously required in shifting your applications and databases to Azure.

Vertica on Azure

For some time now you’ve been able to deploy a Vertica cluster to Azure via a manual process, but now, the Azure marketplace has the HPE Vertica Analytics Platform available to deploy via an ARM template a cluster with up to 5 nodes.

If you’re not familiar with Vertica, then in brief, it’s a column store relational database with rapid data load features (ingesting to memory) and then optimises the storage of that data on disk through different encoding schemes.

Having worked with Vertica for over 5 years I can recommend it for use in particular scenarios, particularly when your data is very tabular, denormalised, compresses/encodes well and is such that you may want to execute analytic workloads (queries) over that data. It works less well in situations where you need to join table data or perform transactional workloads with update or delete operations.

If you’re looking for an alternative to the likes of AWS Redshift or Azure Data Warehouse and are content to consider IaaS alternatives, then why not give Vertica a go, and spin up a cluster in your cloud of choice, it may just give you better performance, have a deeper feature set for your analytics and could work out cheaper than PaaS offerings.

Azure Logic Apps - A blog re-post

For those of you wanting to get started with Azure Logic Apps, I've put together a simple guide to get you started. My example effectively creates an API app in Azure App Service using Logic App callable endpoints...

Azure Logic Apps in Five Steps - an iomart blog

Microsoft Certification 70-475 : Designing and Implementing Big Data Analytics Solutions

Having recently sat and passed Microsoft’s exam 70-475, I thought I’d publish the list of references I built up whilst studying. This is still a relatively new exam, so study materials are hard to come by, just as for exam 70-473. As usual, I also made use of the Mindhub practice exam.

I found it difficult to pin-down specific resources for some of the objective areas, so it’s by no means extensive, but covers a good chunk of the exam content.

I also recommend having some prior knowledge of MS SQL, Hadoop and Azure ecosystems before tackling this exam.

Hope this helps!

1. Design big data batch processing and interactive solutions

• Ingest data for batch and interactive processing

https://docs.microsoft.com/en-us/azure/data-lake-store/
https://docs.microsoft.com/en-us/azure/data-factory/data-factory-copy-activity-performance
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-overview-load

• Ingest from cloud-born or on-premises data,

https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-data-scenarios

• store data in Microsoft Azure Data Lake,

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-azure-datalake-connector#sample-copy-data-from-azure-blob-to-azure-data-lake-store

• store data in Azure BLOB Storage,

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-azure-datalake-connector#sample-copy-data-from-azure-data-lake-store-to-azure-blob

• perform a one-time bulk data transfer,

https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-offline-bulk-data-upload

• perform routine small writes on a continuous basis
• Design and provision compute clusters

https://blogs.msdn.microsoft.com/cindygross/2015/02/26/create-hdinsight-cluster-in-azure-portal/

• Select compute cluster type,

https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-hadoop-introduction#a-nameoverviewaoverview-of-the-hadoop-ecosystem-in-hdinsight
https://www.blue-granite.com/blog/how-to-choose-the-right-hdinsight-cluster

• estimate cluster size based on workload

https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-provision-clusters

• Design for data security
• Protect personally identifiable information (PII) data in Azure
• encrypt and mask data,
• implement role-based security

https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-secure-data

• Design for batch processing

https://docs.microsoft.com/en-us/azure/batch/batch-technical-overview

• Select appropriate language and tool,
• identify formats,
• define metadata,

Microsoft Azure Batch - slides 46-48

• configure output
• Design interactive queries for big data

https://docs.microsoft.com/en-gb/azure/hdinsight/hdinsight-apache-spark-overview

• Provision Spark cluster,

https://docs.microsoft.com/en-gb/azure/hdinsight/hdinsight-apache-spark-jupyter-spark-sql

• set the right resources in Spark cluster,

https://blogs.msdn.microsoft.com/bigdatasupport/2015/08/19/some-things-to-consider-for-your-spark-on-hdinsight-workload/

• execute queries using Spark SQL,
• select the right data format (Parquet),

http://parquet.apache.org/documentation/latest/

• cache data in memory (make sure cluster is of the right size),
• visualize using business intelligence (BI) tools (for example, Power BI, Tableau),

https://docs.microsoft.com/en-gb/azure/hdinsight/hdinsight-apache-spark-use-bi-tools
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-integrate-power-bi

• select the right tool for business analysis

2. Design big data real-time processing solutions

• Ingest data for real-time processing

https://docs.microsoft.com/en-gb/azure/stream-analytics/stream-analytics-introduction
http://download.microsoft.com/download/6/2/3/623924DE-B083-4561-9624-C1AB62B5F82B/real-time-event-processing-with-microsoft-azure-stream-analytics.pdf
https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-storm-sensor-data-analysis - hands-on tutorial

• Select data ingestion technology,

https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-what-is-event-hubs

• design partitioning scheme,

https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-what-is-event-hubs#partitions

• design row key of event tables in Hbase

https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-hbase-overview
http://www.dummies.com/programming/big-data/hadoop/row-keys-in-the-hbase-data-model/
http://hbase.apache.org/0.94/book/rowkey.design.html

• Design and provision compute resources
• Select streaming technology in Azure,

https://docs.microsoft.com/en-gb/azure/stream-analytics/stream-analytics-comparison-storm

• select real-time event processing technology,

https://docs.microsoft.com/en-us/azure/iot-hub/iot-hub-compare-event-hubs

• select real-time event storage technology,

https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-define-outputs

• select streaming units,

https://azure.microsoft.com/en-us/pricing/details/stream-analytics/#
https://docs.microsoft.com/en-gb/azure/stream-analytics/stream-analytics-scale-jobs

• configure cluster size,

https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-provision-clusters#basic-configuration-options
https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-provision-clusters#cluster-types

• assign appropriate resources for Spark clusters,

https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-apache-spark-resource-manager#what-is-the-optimum-cluster-configuration-to-run-spark-applications
https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-apache-spark-resource-manager#how-do-i-know-if-i-am-running-out-of-resource

• assign appropriate resources for HBase clusters,

https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-hbase-tutorial-get-started#create-hbase-cluster

• utilize Visual Studio to write and debug Storm topologies

https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-storm-develop-csharp-visual-studio-topology

• Design for Lambda architecture

https://blogs.technet.microsoft.com/msuspartner/2016/01/27/azure-partner-community-big-data-advanced-analytics-and-lambda-architecture/
https://social.technet.microsoft.com/wiki/contents/articles/33626.lambda-architecture-implementation-using-microsoft-azure.aspx
http://lambda-architecture.net/

• Identify application of Lambda architecture,
• utilize streaming data to draw business insights in real time,
• utilize streaming data to show trends in data in real time,
• utilize streaming data and convert into batch data to get historical view,
• design such that batch data doesn’t introduce latency,
• utilize batch data for deeper data analysis
• Design for real-time processing

Real-Time Event & Stream Processing on MS Azure

• Design for latency and throughput,
  • design reference data streams,
  • design business logic,
  • design visualization output

 

3. Design Machine Learning solutions

• Create and manage experiments

https://docs.microsoft.com/en-gb/azure/machine-learning/machine-learning-create-experiment
https://docs.microsoft.com/en-gb/azure/machine-learning/machine-learning-studio-overview-diagram

• Create, manage, and share workspaces;

https://docs.microsoft.com/en-gb/azure/machine-learning/machine-learning-walkthrough-1-create-ml-workspace
https://docs.microsoft.com/en-gb/azure/machine-learning/machine-learning-create-workspace

• create training experiment;

https://docs.microsoft.com/en-gb/azure/machine-learning/machine-learning-walkthrough-3-create-new-experiment

• select template experiment from Machine Learning gallery

https://docs.microsoft.com/en-gb/azure/machine-learning/machine-learning-sample-experiments

• Determine when to pre-process or train inside Machine Learning Studio
• Select model type based on desired algorithm,

https://docs.microsoft.com/en-gb/azure/machine-learning/machine-learning-algorithm-choice

• select technique based on data size

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-data-science-prepare-data

• Select input/output types
• Select appropriate SQL parameters,

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-import-data-from-online-sources

• select BLOB storage parameters,

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-import-data-from-online-sources#supported-online-data-sources

• identify data sources,

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-data-science-import-data

• select HiveQL queries

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-data-science-create-features-hive

• Apply custom processing steps with R and Python

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-python-data-access
https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-extend-your-experiment-with-r
https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-custom-r-modules

• Visualize custom graphs,

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-custom-r-modules#elements-in-the-xml-definition-file
https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-execute-python-scripts#working-with-visualizations

• estimate custom algorithms,

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-algorithm-choice
http://download.microsoft.com/download/A/6/1/A613E11E-8F9C-424A-B99D-65344785C288/microsoft-machine-learning-algorithm-cheat-sheet-v6.pdf

• select custom parameters,

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-web-service-parameters
https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-execute-python-scripts#basic-usage-scenarios-in-machine-learning-for-python-scripts

• interact with datasets through notebooks (Jupyter Notebook)

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-gallery-jupyter-notebooks
https://gallery.cortanaintelligence.com/notebooks
https://gallery.cortanaintelligence.com/Notebook/Tutorial-on-Azure-Machine-Learning-Notebook-1

• Publish web services
• Operationalize Azure Machine Learning models,

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-publish-a-machine-learning-web-service

• operationalize Spark models using Azure Machine Learning,

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-data-science-spark-overview
https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-data-science-spark-model-consumption#consume-spark-models-through-a-web-interface

• operationalize custom models

https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-model-progression-experiment-to-web-service
 

4. Operationalize end-to-end cloud analytics solutions

• Create a data factory
• Identify data sources,

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-introduction#data-movement-activities

• identify and provision data processing infrastructure,

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-introduction#data-transformation-activities

• utilize Visual Studio to design and deploy pipelines

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-copy-activity-tutorial-using-visual-studio
https://docs.microsoft.com/en-us/azure/data-factory/data-factory-build-your-first-pipeline-using-vsm
https://docs.microsoft.com/en-us/azure/data-factory/data-factory-build-your-first-pipeline

• Orchestrate data processing activities in a data-driven workflow
• Leverage data-slicing concepts,

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-scheduling-and-execution#time-series-datasets-and-data-slices

• identify data dependencies and chaining multiple activities,

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-scheduling-and-execution#run-activities-in-a-sequence

• model complex schedules based on data dependencies,

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-scheduling-and-execution#data-dependency-deep-dive

• provision and run data pipelines

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-create-pipelines#create-pipelines

• Monitor and manage the data factory
• Identify failures and root causes,

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-monitor-manage-app
https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-monitor-manage-pipelines

• create alerts for specified conditions,

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-monitor-manage-app#creating-alerts
https://docs.microsoft.com/en-us/azure/data-factory/data-factory-monitor-manage-pipelines#create-alerts

• perform a restatement
• Move, transform, and analyze data
• Leverage Pig, Hive, MapReduce for data processing;

https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-pig-activity
https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-hive-activity
https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-map-reduce

• copy data between on-premises and cloud;

https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-move-data-between-onprem-and-cloud
https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-data-management-gateway

• copy data between cloud data sources;

https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-data-movement-activities

• leverage stored procedures;

https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-stored-proc-activity

• leverage Machine Learning batch execution for scoring, retraining, and update resource;

https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-azure-ml-batch-execution-activity

• extend the data factory with custom processing steps;

https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-use-custom-activities

• load data into a relational store

https://docs.microsoft.com/en-gb/azure/data-factory/data-factory-azure-sql-connector

• visualize using Power BI

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-integrate-power-bi
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-get-started-visualize-with-power-bi

• Design a deployment strategy for an end-to-end solution
• Leverage PowerShell for deployment,

https://docs.microsoft.com/en-us/powershell/resourcemanager/azurerm.datafactories/v2.3.0/azurerm.datafactories

• automate deployment programmatically

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-create-data-factories-programmatically
https://msdn.microsoft.com/library/mt415893.aspx
https://msdn.microsoft.com/library/dn906738.aspx

Copying an Azure Blob snapshot to another storage account using PowerShell

I've been working with Azure Blobs and Snapshots recently. One gripe is that the Azure portal and the various storage explorers don't give you the power to copy a blob snapshot. Blobs, yes, but their snapshots, no.

Depending on your requirements there are many approaches. The PowerShell below gives an example where the snapshot timestamp is extracted from the snapshot URI and then used to obtain an object to the specific snapshot before copying it with the Start-AzureStorageBlobCopy cmdlet.

# Define the source snapshot blob URI
$SrcBlobURI="https://<SOURCE_STORAGE_ACCOUNT>.blob.core.windows.net/vhds/<SOURCE_VHD_PREFIX>.vhd?snapshot=2017-01-24T21:08:36.9371576Z"

# Define the destination storage account and context.
$DestStorageAccountName = "<DESTINATION_STORAGE_ACCOUNT>"
$DestStorageAccountKey = "<DESTINATION_STORAGE_ACCOUNT_KEY>"
$DestContainerName = "<DESTINATION_CONTAINER_NAME>"
$DestContext = New-AzureStorageContext -StorageAccountName $DestStorageAccountName -StorageAccountKey $DestStorageAccountKey

# Determine source snapshot blob container name and context
$SrcDiskInfo = "" | Select StorageAccountName, VHDName, ContainerName
$SrcDiskInfo.StorageAccountName = ($SrcBlobURI -split "https://")[1].Split(".")[0]
$SrcDiskInfo.VHDName = $SrcBlobURI.Split("/")[-1]
$SrcDiskInfo.ContainerName = $SrcBlobURI.Split("/")[3]
$SrcContainerName = $SrcDiskInfo.ContainerName
$SrcStorageAccount = Find-AzureRmResource `
    -ResourceNameContains $SrcDiskInfo.StorageAccountName `
    -WarningAction Ignore
$SrcStorageKey = Get-AzureRmStorageAccountKey `
    -Name $SrcStorageAccount.Name `
    -ResourceGroupName $SrcStorageAccount.ResourceGroupName
$SrcContext = New-AzureStorageContext `
    -StorageAccountName $SrcStorageAccount.Name `
    -StorageAccountKey $SrcStorageKey[0].Value

# Extract timestamp from the Snapshot filename and convert to a datetime
$pre, $post = $SrcBlobURI.split("=")
$snapdt = [uri]::UnescapeDataString($post)
$snapdt = [datetime]::ParseExact( `
    $snapdt,'yyyy-MM-dd"T"HH:mm:ss"."fffffff"Z"',$null `
)

# Get a reference to blobs in the source container.
$blob = Get-AzureStorageBlob -Container $SrcContainerName `
    -Context $SrcContext `
    | Where-Object {`
        $_.ICloudBlob.IsSnapshot `
        -and $_.SnapshotTime -eq $snapdt `
    }

# Copy blobs from one container to another.
$blob | Start-AzureStorageBlobCopy `
    -DestContainer $DestContainerName `
    -DestContext $DestContext `
    -DestBlob "acopy_$($blob.Name)"

Microsoft Certification 70-473 : Designing and Implementing Cloud Data Platform Solutions

I recently passed the Microsoft exam 70-473 "Designing and Implementing Cloud Data Platform Solutions", here's my insight into the certification for anyone else thinking of studying and sitting this exam. I've also included a selection of my most useful links I found whilst looking for relevant online content.

Generally, if you have experience with SQL Server (2008+) then focus your study on hybrid scenarios, HA/DR, auditing, monitoring, security and the differences between SQL Server (on-prem Vs Azure) and Azure SQL Database.

Having some Azure certs already will also give you a small head-start. Also, knowing the different MS tools available and when to use them was key.

With the absence of any MS Exam References and Certification videos my preparation material was based on MSDN, Azure and Technet articles, so lots and lots of online reading. I did find various online videos that dealt with specific aspects of the exam, but in the end I stuck to the docs and took the Mindhub practice exam I had as part of the (no longer available) Microsoft Exam Booster Pack.

I hope these prove useful (Note: not all objective areas have a link, if you find a good one then let me know)...

1. Design and implement database solutions for Microsoft SQL Server and SQL Database (20–25%)

·         Design a hybrid SQL Server solution

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-paas-vs-sql-server-iaas/#microsofts-data-platform

o    Design Geo/DR topology,

https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-high-availability-dr/#hybrid-it-disaster-recovery-solutions

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-business-continuity/

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-designing-cloud-solutions-for-disaster-recovery/

o    design a data storage architecture,

o    design a security architecture,

o    design a data load strategy

·         Implement SQL Server on Azure Virtual Machines (VMs)

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-sql-server-iaas-overview/

https://azure.microsoft.com/en-gb/services/virtual-machines/sql-server/

https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-performance/

o    Provision SQL Server in an Azure VM,

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-portal-sql-server-provision/

o    configure firewall rules,

https://technet.microsoft.com/en-GB/library/ms175043(v=sql.110).aspx

https://technet.microsoft.com/en-gb/library/cc646023(v=sql.110).aspx

o    configure and optimise storage,

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-sql-server-storage-configuration/

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-sql-performance/#storage-guidance

o    migrate an on-premises database to Microsoft Azure,

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-migrate-sql/

o    configure and optimise VM sizes by workload

https://azure.microsoft.com/en-us/documentation/articles/storage-premium-storage/#ds-dsv2-and-gs-series-vms

·         Design a SQL Database solution

o    Design a solution architecture,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-paas-vs-sql-server-iaas/

o    design Geo/DR topology,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-business-continuity/

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-designing-cloud-solutions-for-disaster-recovery/

o    design a security architecture,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-security-guidelines/

https://msdn.microsoft.com/library/ff929188.aspx

o    design a data load strategy,

o    determine the appropriate service tier

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-service-tiers/

·         Implement SQL Database

o    Provision SQL Database,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-get-started/

o    configure firewall rules,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-firewall-configure/

o    configure active geo-replication,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-geo-replication-overview/

o    migrate an on-premises database to SQL Database,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-cloud-migrate/

o    configure for scale and performance

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-elastic-scale-introduction/

·         Design and implement data warehousing on Azure

https://azure.microsoft.com/en-gb/documentation/articles/sql-data-warehouse-index-all-articles/

o    Design a data warehousing solution on Azure,

https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-overview-develop/

https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-tables-overview/

o    design a data load strategy and topology,

https://blogs.msdn.microsoft.com/sqlcat/2016/02/06/azure-sql-data-warehouse-loading-patterns-and-strategies/

o    configure SQL Data Warehouse,

https://azure.microsoft.com/en-gb/documentation/articles/sql-data-warehouse-overview-manage/

o    migrate an on-premises database to SQL Data Warehouse

https://azure.microsoft.com/en-gb/documentation/articles/sql-data-warehouse-migrate-data/

2. Manage database management systems (DBMS) security (25–30%)

·         Design and implement SQL Server Database security

https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-security/

o    Configure firewalls;

https://msdn.microsoft.com/en-us/library/cc646023.aspx

o    manage logins, users and roles;

http://www.top-password.com/blog/difference-between-sql-server-logins-and-database-users/

https://msdn.microsoft.com/en-us/library/aa337552.aspx

https://msdn.microsoft.com/en-us/library/bb669065(v=vs.110).aspx

https://msdn.microsoft.com/en-us/library/ms181127.aspx

o    assign permissions;

https://msdn.microsoft.com/en-us/library/ms191465.aspx

o    configure auditing;

https://msdn.microsoft.com/en-us/library/cc280386.aspx

o    configure transparent database encryption

https://msdn.microsoft.com/en-GB/library/bb934049.aspx

·         Implement Azure SQL Database security

o    Configure firewalls;

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-firewall-configure/

o    manage logins, users, and roles;

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-manage-logins/

o    assign permissions;

https://msdn.microsoft.com/en-us/library/mt667986.aspx

o    configure auditing;

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-auditing-get-started/

o    configure row-level security;

https://msdn.microsoft.com/library/dn765131

o    configure data encryption;

https://msdn.microsoft.com/library/dn948096.aspx

o    configure data masking;

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-dynamic-data-masking-get-started/

o    configure Always Encrypted

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-always-encrypted-azure-key-vault/

3. Design for high availability, disaster recovery and scalability (25–30%)

·         Design and implement high availability solutions

https://msdn.microsoft.com/library/hh781257.aspx

o    Design a high availability solution topology,

https://blogs.technet.microsoft.com/dataplatforminsider/2014/06/19/sql-server-alwayson-availability-groups-supported-between-microsoft-azure-regions/

https://msdn.microsoft.com/library/hh510230.aspx

o    implement high availability solutions between on-premises and Azure,

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-sql-high-availability-dr/

o    design cloud-based backup solutions,

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-sql-automated-backup/

https://msdn.microsoft.com/library/dn449496.aspx

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-use-storage-sql-server-backup-restore/

https://msdn.microsoft.com/library/jj919148.aspx

https://msdn.microsoft.com/library/dn435916.aspx

o    implement backup and recovery strategies

https://msdn.microsoft.com/en-us/library/ms187048.aspx

https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-backup-recovery/

·         Design and implement scalable solutions

o    Design a scale-out solution,

https://msdn.microsoft.com/en-us/library/aa479364.aspx

o    implement multi-master scenarios with database replication,

https://msdn.microsoft.com/en-us/library/ms152531.aspx

https://msdn.microsoft.com/en-us/library/ms151832.aspx - Snapshot

https://msdn.microsoft.com/en-us/library/ms151176.aspx - Transactional

https://msdn.microsoft.com/en-us/library/ms152746.aspx - Merge

https://technet.microsoft.com/en-us/library/ms151196.aspx - Peer-to-peer

http://devproconnections.com/database-development/sql-server-high-availability-developers-part-4

o    implement elastic scale for SQL Database

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-elastic-scale-introduction/

·         Design and implement SQL Database data recovery

o    Design a backup solution for SQL Database,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-business-continuity/

o    implement self-service restore,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-recovery-using-backups/

o    copy and export databases

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-copy/

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-export/

4. Monitor and manage database implementations on Azure (25–30%)

·         Monitor and troubleshoot SQL Server VMs on Azure

o    Monitor database and instance activity,

https://msdn.microsoft.com/en-us/library/ms179428.aspx

https://msdn.microsoft.com/en-us/library/ms189081.aspx

https://msdn.microsoft.com/en-us/library/hh213686.aspx

o    monitor using dynamic management views (DMVs) and dynamic management functions (DMFs),

https://msdn.microsoft.com/en-us/library/ms188754.aspx

o    monitor performance and scalability

https://msdn.microsoft.com/en-us/library/mt643740.aspx

https://msdn.microsoft.com/en-us/library/dn817826.aspx

·         Monitor and troubleshoot SQL Database

o    Monitor and troubleshoot SQL Database,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-troubleshoot-common-connection-issues/

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-troubleshoot-performance/

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-troubleshoot-permissions/

o    monitor database activity,

https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-advisor

o    monitor using DMVs and DMFs,

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-monitoring-with-dmvs/

https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-performance-guidance

o    monitor performance and scalability

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-performance/

·         Automate and manage database implementations on Azure

o    Manage SQL Server in Azure VMs with PowerShell,

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-ps-sql-create/

o    manage Azure SQL Database with PowerShell,

https://docs.microsoft.com/en-us/powershell/resourcemanager/azurerm.sql/v2.3.0/azurerm.sql

o    configure Automation and Runbooks

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-manage-automation/

Related Videos

Ignite Exam Prep Session - Channel 9

https://channel9.msdn.com/Events/Ignite/2016/BRK3258

Exam Boot Camp - MAPA

https://www.the-mapa.com/PastEventDetails.aspx?SeriesId=fnAAcxXXKPg=&SeriesName=Boot%20Camp%20for%20Exam%2070-473

1.1/3.1 SQL Server HA/DR with Azure

https://mva.microsoft.com/en-US/training-courses/platform-for-hybrid-cloud-with-sql-server-2014-jump-start-8256?l=mHFOkXKy_504984382

1.2 Migrate SQL Server to Azure

https://mva.microsoft.com/en-US/training-courses/migrating-sql-server-databases-to-azure-16594

1.2 SQL Server in Azure VMs

https://mva.microsoft.com/en-US/training-courses/sql-server-in-azure-virtual-machines-developer-jump-start-14240

1.4/4.2 Hybrid Cloud workloads SQL

https://mva.microsoft.com/en-US/training-courses/hybrid-cloud-workloads-sql-8326?l=JEG6OOay_6304984382

1.5 Elastic Data Warehouse as-a-service

https://mva.microsoft.com/en-US/training-courses/deliver-an-elastic-data-warehouse-as-a-service-14005?l=vQOw1qcrB_1305192797

2.2 Securing data in SQL Database

https://mva.microsoft.com/en-US/training-courses/securing-your-data-in-microsoft-azure-sql-database-16076

3.1 Design a scale-out solution

https://www.pluralsight.com/courses/scaling-sqlserver2012-2014-part2

3.2 Elastic Database with Azure SQL Database

https://mva.microsoft.com/en-US/training-courses/elastic-database-capabilities-with-azure-sql-db-16554

3.3 Implementing SQL Database Data Recovery

https://mva.microsoft.com/en-US/training-courses/implementing-azure-sql-database-data-recovery-10341

4.1 Monitor and Troubleshoot SQL Server

https://mva.microsoft.com/en-US/training-courses/administering-microsoft-sql-server-2012-jump-start-8259?l=70lJ0hKy_7704984382

4.3 SQL Server and SQL Database Powershell

https://mva.microsoft.com/en-US/training-courses/powershell-for-sql-data-professionals-16532?l=XgA5w0PgC_8805121157

Other Links

General Introduction

https://mva.microsoft.com/en-US/training-courses/design-and-implement-cloud-data-platform-solutions-15711?l=jbCdW0j1B_3005244527

https://blogs.technet.microsoft.com/msuspartner/2016/01/22/azure-partner-community-sql-server-and-azure-sql-database/

https://www.microsoft.com/en-us/cloud-platform/cloud-data

SQL Server on Azure VMs Learning Path

https://azure.microsoft.com/en-us/documentation/learning-paths/sql-azure-vm/

SQL Database Intro

https://mva.microsoft.com/en-US/training-courses/windows-azure-sql-database-8280?l=eOml92Wy_3004984382

TechNet SQL Server Reference

https://technet.microsoft.com/en-us/library/mt590198(v=sql.1).aspx

MSDN SQL Server Reference

https://msdn.microsoft.com/library/mt590198.aspx

SQL Server (2008) Data Loading White Paper

https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

SQL Server Customer Advisory Team (SQLCAT)

https://blogs.msdn.microsoft.com/sqlcat/

SQL Server Solution Design

https://mva.microsoft.com/en-US/training-courses/designing-solutions-for-sql-server-8484

Hybrid SQL Server Scenarios

http://www.biztalk360.com/azure-hybrid-integration-day/hybrid-cloud-scenarios-sql-server/

https://channel9.msdn.com/Events/Ignite/2015/BRK2554

https://community.dynamics.com/partner/b/msuspartner/archive/2016/05/10/data-amp-analytics-partner-community-hybrid-scenarios-using-sql-server-2016

http://www.imgroup.com/introduction-microsoft-sql-server-2016-part-two/

https://goo.gl/581D4u

http://download.microsoft.com/download/8/7/F/87F5D41A-6252-4B5E-B532-EA3E3706216F/SQL_Server_2016_Hyperscale_Cloud_White_Paper.pdf

Migrate On-prem to Azure VMs

http://www.jamesserra.com/archive/2016/05/migrate-from-on-prem-sql-server-to-azure-vm-iaas/

Training Course

http://itmasterclasses.com/training/courses/design-and-implement-cloud-data-platform-solutions/