Are you ready for Microsoft Azure or Not?

With so many exciting features that Azure offers such as Azure Stream Analytics and Azure Machine Learning, many friends and colleagues asked me about how to get started with Azure so that they can having some feelings about Azure. I highly suggest them reading "Azure Readiness Playbook", the official guidelines that came out in October 2018 to help you configure Azure environment. You may know that currently Microsoft offers $200 credit for first 30 day trial of Azure subscription. $200 will be gone in just a few days if you are not aware of costs associated with different subscriptions. You need to have an clear idea on how to track resource costs and control costs, or whether to start services before you are fully ready for Azure deployment. So

How to get access to official Azure Readiness Playbook?

Step 1: Sign in your Azure Portal.
Step 2: Search "Quickstart" in search box. You will find the "Quickstart Center (Preview)".

Step 3: Click "Quickstart Center" and you will see two options: "Create an Azure service" and "Set up your Azure environment".

Step 4: Select "Set up your Azure environment", you will see the launch page of Playbook.

Read through it or choose the topic that you are most interested in. Ensure that you have a general idea about what roles you can assign to in order to manage the access to your resources in Azure.

Below provides a brief summary about the topic.

Four management levels of access to resources in Azure 

As shown above, four management levels of access in Azure are: Management Group -> Subscriptions -> Resource Groups -> Resources, by the order of control levels from highest to lowest. Management groups have highest control while the rightmost resources have lowest level of management.  It is suggested to apply access and application controls at possible highest level because lower levels inherit settings from higher levels, such as security, policy in governance, and compliance strategy.

It is suggested to apply project-specific requirements at resources/services in Azure, including virtual machines, SQL databases, storage accounts, etc. Since a resource group is a logical container that group Azure resources such as SQL Data Warehouses, Data Factories, storage accounts, it is a good practice to assign one project with the same resource group.

Azure Policy and Azure Security Center

Make sure to try out Azure security center to see how to enforce and automate your governance decisions across hybrid cloud workloads.

Manage Cost and Billing

As for pricing, you can estimate costs with Pricing Calculator or check Azure Pricing. Be aware of services charged by hours, such as Azure SQL Database and Azure VM. Find out what are services charged by storage. Play around different settings to see how costs are affected. For example, for Azure SQL database, 8 core SQL DB usually costs $1476 per month while the one with only 1 core costs about $184. Remember, Azure SQL database is not able to be paused as for now (Nov. 2018).

 

Another way to monitor cost is to use "Cost Management + Billing" in Azure Portal as shown below:

If you would like to explore more detailed charges, click the cost on the far right. It will bring "Costs by service" page. You can check costs by subscriptions, resource types, resource group associated with, and at which time period by selecting related items at the top section.

If you want to drill down to check charges by dates, click charges that you are interested in, it will give you cost history by dates. Note that there is warning message stating that "There is a delay between the time when a resource is used and the time when the usage reaches the billing system. Due to this, costs reported here may be delayed. Amounts displayed are estimates, and may not reflect some recent usage. Taxes are not included."

After reading this article, I hope that you know whether you are ready to kick the tires of Azure or not. Good luck!

Reference: Azure Readiness Playbook Overview

How to use Azure Storage Emulator for Testing

Problem: During development of SSIS packages for data transferring between Azure Storage, you want it to be fully tested on your local first before any data transfers to your Azure account. You also want to avoid unnecessary charges on your Azure account for testing. How to achieve this?

Solution: Using Azure storage emulator

We know that Azure storage is charged based on volume of data stored every month, types of operations performed, and amount of data transfers involved base on Azure Storage pricing.

In order to avoid unnecessary charges during development and testing, we can leverage Azure storage emulator to test and debug Azure cloud services locally before real data transfer occurs between on-premise and Azure account.

First, you can download and install Azure storage emulator here. During installation, you need to take note of installation paths of Azure emulators. Here mine is installed by default at: 

C:\Program Files (x86)\Microsoft SDKs\Azure\Storage Emulator

After installation, start the storage emulator by searching "Azure Emulator" after clicking windows Start button. 

If it is executed for the first time, it will do initialization first by creating a database in LocalDB and granting database access for current user.

You can also check the database created by Storage Emulator as shown below:

How to set connection to Azure storage emulator?

Here we use upload files to Azure Blob storage by SSIS as an example. (As for how to set up Azure environments for SSIS, please refer to the steps in "How to Upload Data Files to Azure Blob Storage". )

At connection manager pane in SSIS, choose "New Connection", then choose "AzureStorage" as shown below.

Next, choose "Use local developer account" instead of "Use Azure account". Then click "Test Connection" button to ensure connection with storage emulator is successful. Ensure that you have storage emulator running. You can check the status of emulator by running the following command on command console:

C:\Program Files (x86)\Microsoft SDKs\Azure\Storage Emulator>AzureStorageEmulator.exe status

You can rename the connection to the name you want. Here I rename it to "azure-emulator" for future use.

To use it with Azure related task, the main change is the connection to be the Azure emulator one you just created. Below shows the configuration for Azure Blob Upload Task. Now you are good to go to debug and test your package on your local!

Help Command in Storage Emulator

You can use help command to quickly locate commands for operations on storage emulator.

Note that storage emulator is an emulated environment running on a local SQL instance, there are Differences between the storage emulator and Azure Storage, in aspects of Blob Storage and table storage.

How to Upload Data Files to Azure Blob Storage?

Problem: You have text files in csv or AVRO formats and you would like to upload them to Blob storage as intermediate steps of big data processing in Azure. How to upload them to Azure Blob storage?

Solution: 

Use "Azure Blob Upload Task" or  "Azure Blob Destination" in Data Flow Task in Integration Service in SQL Server (SSIS).

Before we open new Integration Service project in Visual Studio 2015, you need to do preparations to get our environments setup:

1. Sign up Azure account.
2. Install Azure feature pack for SQL Server and ensure that Azure tools appear in Visual Studio. If Azure tools are grey out, please refer to the article to resolve issues.
3. Install Microsoft Azure Storage Explorer and create a storage account.

     After you create a storage account, you should see four types of storage appearing under your storage account at Azure Storage Explorer:

        Then you can create a Blob container to contain the file to be uploaded. You can easily do it by using Microsoft Azure Storage Explore shown below. Here the Blob Container called "ssisdemoblob" is created and under this a Blob Directory "data" is also created. The summary of naming rules for Azure can be referred as this article.

Now you can go ahead to open visual studio and create a new SSIS project. 

Step 1: Create Azure Storage Connection.

            - Choose "AzureStorage" as Connection manager type. 

            - For Account Key in "Azure Storage Connection" editor, copy "Access Key" in your Azure account. 

            - Click "Test Connection" to make sure it connects successfully to your Azure account.

Below shows how to get access key for your storage account from Azure Portal.

Note that you can create Azure Storage Connection under project level connections as shown below so every package under this project can share the same Azure Storage connection.

Step 2: Configure the Azure Blob Upload Task in SSIS as shown below:

To make this upload more dynamic, here we use variable @[User::srcFolder] for local directory. You can use expression for other properties such as BlobContainer, BlobDirectory, Disable.

Moreover, you can use wildcard for FileName as in other SSIS solutions.

After executing this task, you can see the related file appears under the Blob Directory specified. Now you have your first file uploaded to Azure Blob Storage!

Note that you can also use "Azure Blob Destination" in Data Flow Task to achieve this.
Azure Blob Upload Task can be easily used to upload one more files to azure Blob storage by using wildcards for FileName. While for "Azure Blob Destination" in Data Flow Task, you have to use Foreach Loop Container together with Azure Blob Destination to load multiple files to Blob Storage.

Why "Azure" related tools are grey out in SSIS after installation of Azure feature pack?

Problem: After installation of Azure feature pack for SSIS, Azure related toolboxes are still grey out?

Solution: 

If Azure related toolboxes are grey out in SSIS, that means that the version of SQL server is different from the version of Azure feature pack. You need to change the TargetServerVersion in visual studio to the same version of Azure feature pack you installed.

For example, if you download and install Microsoft SQL Server 2016 Integration Services Feature Pack for Azure for SQL Server 2016, you need to set TargetServerVersion in visual studio to be SQL Server 2016 as shown below:

Now your project version will show: SQL Server 2016 and Azure related tools will show up in your SSIS toolbox!