It’s all about Data…DocumentDB, Data Analytics using Azure & PowerShell

February 23, 2017 – 33 went

Data Analytics & Data Management Using Azure & PowerShell
·  Quick introduction to Azure and PowerShell and why you should use them for data analytics and data management
·  How to manage and send data queries to an Azure SQL database
·  Creating HTML Reports in PowerShell
·  Automation of third-party reports against a data source with PowerShell
·  Manage Log Analytics (i.e. Operational Insights) data using Azure


Lightning Talks

April 26, 2017 – 23 went

Data Analytics & Data Management Using Azure & PowerShell
• Quick introduction to Azure and PowerShell and why you should use them for data analytics and data management
• How to manage and send data queries to an Azure SQL database
• Creating HTML Reports in PowerShell
• Automation of third-party reports against a data source with PowerShell
• Manage Log Analytics (i.e. Operational Insights) data using Azure


On-Premise Data Synchronization to Azure Blob Storage

Azure Binary Large Objects (i.e. Blobs) Storage is a cloud storage service that is durable, available, and scalable that serves a variety of purposes. Microsoft describes it as massively-scalable object storage for unstructured data on their microsite devoted to Blob storage. There are many reasons why you should consider using Blob storage. Perhaps you want to share files with clients or off-load some of the static content from your web servers to reduce the load on them.

Microsoft has several partners that provide tools and expertise to use Azure Blob Storage effectively. Some of these partners are highlighted on the Microsoft microsite devoted to Blob Storage as displayed below.


Using Microsoft Azure Storage Explorer (MASE)

Microsoft Azure Storage Explorer (MASE) is a standalone app from Microsoft that allows you to easily work locally with Azure Storage data.

This free Azure tool from Microsoft can be installed locally on a client machine or remotely in on-premise or Azure VMs, for access on that machine to Azure Storage Accounts using:

  • the Azure Storage account name and an Azure generated access key
  • or SAS URI for Storage Accounts

This gives access to Azure Blob, Queue, Tables and File Shares Storage containers and contents without having to sign in to the Microsoft Azure Portal.

NOTE: MASE works for ARM and for Classic Storage Accounts; there are MAC, Windows and Linux versions for download.


Enforce HTTPS on Azure Web App

For this example, the Azure web app has:

Enforcing HTTPS will redirect the HTTP, so web app users will always end up at the HTTPS site. This will be done be defining a rewrite rule in the web.config file for the web app.

Bind a custom SSL Certificate to an Azure Web App

For this example of applying an SSL certificate:

  • The web app has a custom domain applied that points to the Azure app. Read here for how to set up a custom domain for an Azure app.
  • The web app is running in Basic 1 Tier (pricing/features)
  • The SSL certificate has been purchased from a trusted certificate authority (CA), and is a wildcard certificate for use on multiple different domain websites on multiple servers, saved in .pfx format.

1.In Azure Portal > specific Web App > Settings > SSL certificates > Upload Certificate


Resizing Azure VMs

An Azure (ARM) VM can conveniently be resized, up or down, while it is running, while stopped or while stopped(deallocated). To resize the OS or Data disk, however, the VM must be be stopped(deallocated).

In the following example, a VM was first resized up to a D2_V2 Standard machine from a Basic A2 machine, while it was Stopped (deallocated)

Then, it was resized back down to a Basic A2 machine. NOTE: This could have been accomplished while the machine was running also.

VM > Overview > Size > Choose a Size > Select


Moving an Azure Web App Between Subscriptions

Sometimes, it may be necessary for management or billing processes, to move an Azure Web App or other Azure Resources between Azure Subscriptions. It is simple process that can be done in Azure Portal.

In this example, we will move the ‘alvarnetwww’ App Service Plan and App Service to a different Subscription and Resource Group ‘AlvarnetWWW’ (in same GeoLocation) .

1. In the Essentials blade of the source Resource Group ‘alvarnetwww’, click on the Subscription name > change.


Publish Website to Azure using One-Drive

1. Sign in to the desired OneDrive account to be used for website deployment in a different browser window than for Azure Portal.

2. Azure Portal > Web App > Deployment Options > Choose OneDrive from various options

3. Authorize and go thru the steps to give permission to Azure Web Services to connect to OneDrive account.

4. This will create a Files > Apps > Azure Web Apps folder in the OneDrive root, and then the new web app goes into a subfolder under that.


Use CloudBerry Drive to create a local mapped network drive to Azure Blob Storage

Use CloudBerry Drive to create a local mapped network drive directly to a Microsoft Azure Blob Storage account. This way, files in the Azure Blob Storage can be directly worked on from Windows Explorer and are accessible by other applications that can read mapped drives. Folders and files can be dragged and dropped to the mapped network drive and instantly saved in the Azure Blob Storage container without having to directly access the Azure Portal.

“Using CloudBerry Drive, you can mount your Microsoft Azure account as a network drive to your Windows computer and use it just like any other hard drive.” –

These instructions assume that a Microsoft Azure Blob Storage Account has already been created. Retrieve the Azure Blob Storage Acccount name and an access key, for use in configuring CloudBerry Drive on a local client machine. We found this very simple to install and it worked seamlessly with Azure Blob Storage.


Cloud Database: Creating an Azure SQL Logical Server

To setup Azure SQL Database, first there needs to be a logical server created that the database(s) will reside on. Then from within SQL Server Management Studio (SSMS), either from within the Azure Portal or from a remote machine with SSMS and a registered IP address, the database(s) can be created.

Create a SQL server (logical server)

1.In Marketplace, add ‘SQL Server (logical’ into the search bar and select beginning the setup process that Azure leads through.


VM Stopped but still incurring Compute Charges in Azure

This warning will be shown on the Azure portal for the Azure VM, if the VM was only shut down from within the VM via the RDP session. The second step to totally shut down the VM, is to go to the Azure portal and choose the Stop option at the top of the Essentials blade or use PowerShell to stop the machine. This will deallocate the VM, and incur no more compute charges.


Using Azure File Storage and FileVista (web-based file manager)

The client company is in the process of moving fully to Azure from their physical servers hosted in datacentres. Their favoured web-based file manager GleamTech’s FileVista cannot yet be hosted as an Azure App Service. Until it can be hosted as an Azure App Service, an Azure VM is required to host FileVista. FileVista, ver 7.6+, is able to connect to mapped networks drive via SMB 3.0 to root folders of company data sitting in Azure File Storage. (FileVista does also connect easily to Azure SQL database, but in this case, an Azure WS2016 VM with SQL Server Express and IIS installed is the hosting plan.)

In a previous blog, we noted that remote users of the company were unable to connect from their W10 laptops, into Azure File Storage services, because their ISPs were blocking Port 445, required for SMB 3.0 transmission. FileVista web-based file manager to the rescue!


  1. Create an Azure WS2016 VM with SQL Express (and SSMS) with 1 additional attached data disk; with a Static IP address & Network Security Group Inbound Rules for HTTP & HTTPS.  RDP into the VM to add IIS Role and features; add Inbound & Outbound Port 80 & 443 in Windows Firewall.
  2. Install, configure & customize FileVista, after creating the new website.


Connect to Azure File Storage – Error 80070043 – Port 445 – Network name cannot be found


While trying to create a mapped network drive on a local Windows 10 (W10) client machine to Azure File Storage service, we got the ‘Error 8007003 – Network cannot be found‘ consistently when using Windows “Map network drive…” functionality. When using the net use command in an elevated Command Prompt, the ‘System Error 67 has occurred. The network name cannot be found‘ error occurred.


Provision an Azure 2016 Webserver (Single Tier)

‘Single Tier webserver’ is a VM with both IIS and SQL Server on the same machine, which is adequate for a development environment.

Azure provides Windows images with the latest updated versions of SQL Server or SQL Express and SQL Server Maintenance Studio already installed. These pre-installations are a great time saver, to not have to download the SQL Server software and then go through the extensive installation and update process. The VM will also have an additional attached data drive installed with folders linked for SQL user databases. All that is required is to enable the sys admin (sa) account (optional).

  1. Build Azure VM
  2. Setup SSMS
  3. Install IIS and Firewall Rule for HTTP, HTTPS
  4. Import and install SSL certificate

1. Build VM in Azure Portal:

Marketplace > type in SQL Server Express to find the image options available and select the one with SQL Server  SP1 and WS2016


Backing Up Azure File Storage to Azure Blob Storage

We are using Azure File Storage for keeping day to day company data and wanted to have that data backed up to an Azure Blob Storage as a safe repository. We are currently backing up Azure File Storage Data to Azure Blob Storage Data using a 3rd Party file backup and synchronization tool, GoodSync.

While Good Sync cannot yet connect directly to Azure File Storage, it does connect quickly and securely to Azure Blob Storage. GoodSync also connects easily to mapped network drives – and Azure File Storage containers can be set up as local SMB3 file shares on a machine that can access the Azure File Storage account.


Transitioning from Private Cloud to Azure Cloud

In transitioning from using physical hosts with Windows 2012R2 Hypervisor located in co-location datacentres, to using Azure cloud only, we were required to look for Microsoft & 3rd party tools to make the transition: more cost efficient, easy to continue established work flow with only minor adjustments for users, and have minimal infrastructure responsibilities for IT. While many of the following pages of how-to’s can also be done more efficiently with PowerShell and Automation, the focus of this series of articles is on using the Azure Portal (ARM) GUI, for ease of client use.