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/

Use tags to drill into Azure Enterprise Billing with Power BI

Whilst Power BI's Azure Enterprise Content Pack gives you pre-built dashboards to expose your Azure Enterprise costs, it may not provide the level of detail required to really drill-down into the finer detail of resource costs for your Azure subscriptions.

What if you wanted to tag resources to identify those that belong to your enterprises products or services and report on costs for each. For example, perhaps you'd like to be able to find out how much was spent on Azure Compute resources (Virtual Machines) and storage during the first month of an online service that you've just launched. Your VMs might be spread across Resource Groups, with their storage being held in storage accounts in multiple regions. In order to easily differentiate those resources that are dedicated to the new service from existing resources you could tag those resources as follows:

"service" : "ournewservice"

Using Microsoft's Power BI you can then connect to your Azure Enterprise billing data via the Azure Enterprise (Beta) Data Connection, see here.

The Power BI approach then enables reports and dashboards to be built that display Azure Resource costs per service, by grouping and filtering on resource tags. If you have multiple tag names, then Power BI gives you the ability to split these out into dedicated columns from which tag-specific reports can be built.

PowerShell Pea - Check Disk Usage on Remote Windows Servers using PowerShell

Found this useful script today that (given a list of Windows server names) will check the disk usage on each Windows Server (whose name is supplied in a file) and can also report results back via email.

It demonstrates an alternative to PowerShell remoting, if you want to build reports across a number of servers using a script and need to avoid labour-intensive checks of each server via RDP, for example.

Deploying Azure Virtual Networks with multiple CIDR addresses using ARM templates

I recently had to craft an ARM template in Azure for a Virtual Network that had an address space that wasn't sized as a "to the power of two".

i.e. this would be simple if you're creating a Vnet for an address space of say 256 addresses, such as

10.1.0.0 - 10.1.0.255 = 10.1.0.0/24

You can use a parameter of type "string" in the template, so assuming you arrange your parameters and template into 2 files, the parameters would include:
"vnetAddressPrefix": {
    "value": "10.1.0.0/24",
}
And the template itself would include the parameters and resource definitions for the Vnet as follows:
"vnetAddressPrefix": {
    "type": "string"
}
...
"resources": [ {
    "type": "Microsoft.Network/virtualNetworks",
    "name": "my256Vnet",
    "apiVersion": "2016-03-30",
    "location": "[resourceGroup().location]",
    "properties": {
        "addressSpace": {
            "addressPrefixes": [
                "[parameters('vnetAddressPrefix')]"
            ]
        },
        "subnets": [ {
            "name": "default",
            "properties": {
                "addressPrefix": "10.1.0.0/28"
            }
        }
        ]
    }
}
]

But, lets say you need a Vnet with 384 addresses, such as

10.1.0.0 - 10.1.1.127

Because you can't specify this range in a single CIDR address, you would instead need to create an address space in CIDR notation of

10.1.0.0/24

10.1.1.0/25

The simple "string" parameter used in the first example will not work here, instead you should use a parameter of type "array", modifying the parameter file to include the CIDR ranges as follows:

"vnetAddressPrefixes": {
    "value": [
            "10.1.0.0/24",
            "10.1.1.0/25"
    ]
}
And then changing your template file to include parameters and resource definitions as follows:
"vnetAddressPrefixes": {
    "type": "array"
}
...
"resources": [ {
    "apiVersion": "2016-03-30",
    "type": "Microsoft.Network/virtualNetworks",
    "name": "my384Vnet",
    "location": "[resourceGroup().location]",
    "properties": {
        "addressSpace": {
            "addressPrefixes": 
                "[parameters('vnetAddressPrefixes')]"
        },
        "subnets": [ {
            "name": "default",
            "properties": {
                "addressPrefix": "10.1.0.63/24"
            }
        }
        ]
    }
}
]

That's it! Although it's unlikely that a deploying Vnets in a greenfield environment would require this solution, it still may happen, and is more likely to happen in an environment where address space is already constrained by other Vnets and addressing conventions.

AWS Glue: The future of ETL is nigh

AWS will soon be launching a fully managed ETL as-a-service which promises to automate "data discovery, conversion, mapping, and job scheduling tasks" wherever your data lives, this is not just for your data stored in AWS.

AWS Glue, will provide the ETL layer that links AWS data services (S3, RDS and Redshift etc) to their and analytics services, such as Quicksight.

I for one am fascinated to see what it can do and discover real-world use-cases that can take advantage of AWS Glue, building it into the architecture of Big Data Cloud solutions.

Globally Unique Identifiers - Creating GUIDs in Powershell

Powershell Pea...

New-Guid

I've been working more and more with Powershell, specifically with Azure Automation and scripting and wanted to share a useful Cmdlet for creating GUIDs. This link provides more detail, but ultimately this command will provide you with a unique ID that can be assigned to arbitrary objects that require a unique ID.

Bear in mind that this doesn't protect you from duplicates, but the probability of them occurring is small. So if you need GUIDs for relatively small record/object sets and your application can handle the prospect of duplicates, then this function is a quick way to generate a "unique" ID.

See this link for more details on GUIDs/UUIDs.

Azure Premium Storage Blob Snapshot Error - 409 Conflict

I've recently been working in more detail with Azure blob snapshots and (by trial and error) discovered that Premium storage imposes limitations on the number and frequency of snapshots that can be performed on a single blob.

If you create a snapshot of a Premium storage blob and then another in quick succession (within 60 seconds of each other), then you might get a 409/Conflict error message.

This article suggests that there are two possibilities for this error, either

• SnapshotCountExceeded - You've exceeded the limit of 100 snapshots per blob, or
• SnaphotOperationRateExceeded - You've exceeded the limit of snapshots within a time window (stated as 10 minutes, but I observed this as closer to 1 minute)

Whilst it's unlikely that these limits would cause a problem in practice, it's something to keep in mind when developing/testing solutions that make use of the blob snapshot facility.

Azure Automation Troubles

A recent venture into Azure Automation threw up some unexpected problems.

A simple script, run locally, that called Get-AzureRmVM was working to my subscription, but the same script did not work within an Azure Runbook.

I'd created the AzureRunAsConnection by default with the Azure Automation account and followed the same code as given in the AzureAutomationTutorialScript powershell runbook to login to the subscription and execute Cmdlets.

The problem came when executing the Get-AzureRmVM Cmdlet in the runbook, receiving the error:

Get-AzureRmVM : Run Login-AzureRmAccount to login.
+     $VM = Get-AzureRmVM | ? {$_.Name -eq $VMName}
+           ~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Get-AzureRmVM], PSInvalidOperationException
    + FullyQualifiedErrorId : InvalidOperation,Microsoft.Azure.Commands.Compute.GetAzureVMCommand

After several googles I found this post...
https://feedback.azure.com/forums/246290-automation/suggestions/16590229-get-azurermvm-failing-in-azure-automation

Therefore, if anyone else sees errors when running basic Cmdlets in a runbook (that work fine from PowerShell), try updating all the modules in the Automation account using a script like this one...
https://www.powershellgallery.com/packages/Update-ModulesInAutomationToLatestVersion/1.03/DisplayScript
(Note that the deploy to Azure button didn't work for me, so I had to import the script and run manually.)

Note also that (depending on when you created your Automation Account) the above may, or may not work, and you may have to create a new Automation Account and update the modules before starting to use the account. Microsoft suggest setting the script to run on a schedule in the automation account to keep the modules up to date.