Category Archives: SQL Azure

Setting Up an HDInsight Cluster (No Scripts Required)

Let me start by saying, I am not a fan of scripting. It definitely has its place and a lot of my peers really like it. It is the easiest way to get functionality out from software vendors such as Microsoft. PowerShell is an incredibly powerful tool which can do just about anything. However, therein lies the problem for me. Scripting solves a lot of problems, however, I just wanted to set up and use a basic HDInsight cluster to create some Power BI demos (posts coming soon). So I started the journey to find the scripts and try to understand the syntax and so on. Then I went to the Azure Portal, here is what I did to set up my cluster and load data with no scripting required. My goal was to go to get a working demo platform up. Would I necessarily recommend this path for production work, not sure yet. But now I can work with HDInsight with considerably less work required to set up the environment.

HDInsight Cluster No Script Setup Requirements

You need an Azure account. You can go to http://azure.microsoft.com to sign up for a free account if you like. If you have an MSDN subscription you should have some time available as well.

HDInsight Cluster No Script Setup

Once you have your account created, you should go to http://portal.azure.com. We will be doing our setup from here. During the process we will be creating a storage account (if this is your first run in azure, you may choose to set up a Resource Group as well) and the HDInsight cluster. Be aware that the cluster has compute costs and the storage has storage costs. At the end we will remove the cluster to save your compute time.

Create the Storage Account

This step can be done during the HDInsight cluster creation, but this limits your ability to share data across clusters. If you are just trying it for fun, you can do this during the cluster set up.

Click the + symbol on the portal, then Data + Storage, then Storage Account. This will open a blade with the set up instructions for a storage account.

image

When you create your account you will have some options to fill in:

  • Name: this name will need to be a unique name, e.g., joescoolhdinsight
  • Pricing tier: The pricing tier is really important if you are using a limited plan or if you plan to keep the data for a long time. If you are planning to use this as a demo, I would select Locally Redundant as that is the lower cost plan.
  • Resource Group: The resource group lets organize your Azure assets. This is for your benefit, so if you want to keep all of the HDInsight components together, you could create a group for that or stick with the default.
  • Subscription: This lets you choose the subscription you want to use.
  • Location: Be sure to select a location close to you that supports HDInsight. Check http://azure.microsoft.com/en-us/regions/ to see what Azure services are supported in each region.
  • Diagnostics: This is optional. If you are looking into the diagnostics or need to prep for production, you will find this useful. In most cases, we would not turn this on for demos.

Click Create and it will create your storage account. This may take a few minutes. The notifications section on the portal will alert you when this has been completed. Once that is complete, we will continue with setting up the cluster.

Create a SQL Database for a Metastore

This is an optional section. If you would like to use Hive or Oozie and want to create a metastore so you can reuse your work when you recreate the cluster you need to create a SQL Database for this.

image

Here are the settings needed to create the database:

  • Name: Something easy for you to track on, e.g. HDInsightMetastore.
  • Server: You can use an existing server if you have it, otherwise you can create a new server. I recommend you create the server in the same location you plan to create your HDInsight cluster.
  • Pricing Tier: The default is S0. If you plan to use this for demos and don’t need the additional features, you can choose Basic.
  • Optional Configuration: no changes.
  • Resource Group: Use the Resource Group you have in place for this example.
  • Subscription: Select your subscription.

Click Create to create your database. You will work with this during the setup of your cluster.

Setting Up the HDInsight Cluster

Click the + symbol on the portal, then Data + Analytics, then HDInsight. This will open a blade with the set up instructions for a storage account.

image

Like with the storage account setup, this will open a blade with options for creating the cluster. Let’s walk through the settings on this.

  • Cluster Name: Like the storage account, this name needs to be unique.
  • Cluster Yype: Select Hadoop for this walk through.
  • Cluster Operating System: Select Windows Server for this walk through.
  • Subscription: Choose the same subscription as your storage account.
  • Resource Group: Choose the same Resource Group as your storage account.
  • Cluster Credentials: Here you select a login name and password for your cluster. You can also choose to enable Remote Desktop, but we are not using that feature for this setup. (Note: be sure to click Select at the bottom when you are done. If you don’t, you will be prompted by IE about unsaved settings.)
  • Data Source: Here is where you select your storage account. If you chose not to create a storage account, you can create a new account here as well.
  • Node Pricing Tiers: This section determines the capability and the associated computing costs of your cluster. By default, 4 worker nodes and 2 head nodes will be created with recommended servers (D12 at the time of this writing). Expand the pricing tier, to change the server type or node count. Unless you are sure you need to change, keep the default settings (you can recreate the cluster later). You will see the current hourly pricing based on your selections. This cost is incurred while the service is running. The only way to stop charges is to delete the cluster, so be sure to do this when you are done if you do not wish to pay for it to keep running.
  • Optional Configuration: You do not need to change any setting here if you choose not to. However, if you plan to delete your cluster and you want to retain the metadata, it is recommended that you set up an External Metastore using the database you created previously.
    • Select the database you want to use for the metadata in each case and update the credentials. You can use the same database for both metastores.

Next, you create the cluster. This will take a few minutes. You can track progress in the notifications section on the portal page.

Exploring Your New HDInsight Cluster

Once the cluster has been created, you will see the information page with the settings and other usage information. At the top of that area, you will see some icons. These will help you explore your cluster some more.

image

The gear will open up a settings page and you can review your settings in detail and change some if needed.

The icon with a square and an arrow will open up a dashboard with more options. We will dig into the dashboard more in the next post.

The last three icons are shortcuts to specific actions – remote desktop, scale cluster, and delete.

Once you are done, you should delete your cluster. You can always go through these steps again to recreate your cluster. In my next article we will go through what you can see and do with your cluster using the dashboard.

Intro to Data Factory–Training on the T’s Follow Up Post

PragmaticWorks-LogoThis is a follow up blog post based on the Intro to Data Factory session I gave on the Training on the T’s with Pragmatic Works. Find more free training from the past and upcoming here. I did my session on January 13, 2015.

 Intro To Data Factory

In this session, I gave a simple introduction to new Azure Data Factory using a CopyActivity pipeline between Azure Blob Storage and Azure SQL Database. Below is a diagram illustrating the factory that is created in the demo.

image

I have published my presentation materials here. This includes the sample JSON files, the Movies.csv, and PowerShell scripts.

Q & A

Here are a few questions that were answered during the session.

1. Does Availability refer to when data that has been transferred will be available? Or when the data source is actually available for query?

Availability refers to when the datasets will make a slice available. This is the when the dataset can be consumed as an input or be targeted as an output. This means you can consume data hourly but choose to push it to its final destination on a different cadence to prevent issues on the receiving end.

2. What pre-requisites are must haves?…e.g.(Azure account, HDInsight, Blob Storage Accounts, etc.)

    • An Azure Account is the only real must have. You could use two on premise SQL Server instances.
    • HDInsight if you want to use the HDInsight activitities
    • An Azure Storage account to use blob or table storage

3. How do you decide to use a Factory or Warehouse?

The factory is more of a data movement tool. A warehouse could be a source or target of a factory pipeline.

4. Is this similar to SSIS in SQL Server?

Yes and no. SSIS is definitely more mature and has more tooling available such as data sources and transformations. SSIS also have a good workflow constructor. The focus of the Data Factory initially was to load HDInsight tables from a variety of sources with more flexibility. The other note here is that Data Factory is being built from the ground up to support the scale of the cloud or Azure.

5. Can this be used for Big Data?

Absolutely. I would say that it is one of the primary reasons for the tool. In reference to the previous question, it will likely be the tool of choice for big data operations because it will be able to scale with Azure.

Links to Additional Resources on Data Factory or tools that were used in the presentation:

Azure Data Factory on Azure’s Website

Azure Data Factory Documentation

Azure Data Factory Pricing

Azure Storage Explorer

Azure PowerShell Documentation

Thanks for joining me for this presentation. We look forward to seeing you at the next Free Training on the T’s.

Techfuse, a New Laptop, and How Microsoft Azure Helped Save the Day

On Tuesday, April 22, I had the opportunity to speak at the Techfuse conference in Minneapolis. I was presenting a session on the differences between tabular and multidimensional models with a focus on the developer’s experience. My deck has tenTechFuse_logo  slides including title, references, and bio. The rest of the time is spent in Visual Studio building out equivalent models in using SSAS Tabular and SSAS Multidimensional.

The previous week, I was issued a new laptop from my company, a Dell 7440. This is a very nice machine and I had it all set for the presentation. About 11 AM (I was scheduled to speak at 1:15 PM) it occurred to me that I did not recall seeing a VGA port only HDMI. Next question, did the projectors at the convention center support HDMI? Answer, No. Now I had about an hour and a half to resolve this issue. Simple, I decided to head downtown and get the convertor from Office Depot. This was about 8 blocks away. I could do that and get some exercise in.

I took off at about 11:30. First, I stopped at Target, it was closer. No luck. So on to Office Depot. Keep in mind that Office Depot sells laptops like mine with only HDMI support and it stands to reason that they would have the converter. No such luck. I was able to get the HDMI converted to DVI, but that would not help as I later found out. They directed me to Radio Shack where I promptly picked up a DVI – VGA converter. Now I have three pieces that when strung together should support my needs. I headed back to the convention center and arrived with 30 minutes to spare. Working with the AV guy, we got it all plugged in only to still have it not work. Turns out you need a convertor to convert the digital signal to analog for use in the older projectors. Now what?

The moderator for my room offered me her laptop to use for the presentation. Which was AWESOME! So now I have a way to give the presentation, all ten slides. However, she did not have Visual Studio with SSDT for BI and SQL Server installed. Which was fine, because I didn’t expect her to.

Here is where Azure comes in. I had created a VM with SQL Server Tabular installed along with Visual Studio 2012 and the SQL Server Data Tools for BI. So, I firedth9CGBMYN6 up the VM right before I gave the presentation. I warned the crowd about what had happened and decided to push the demos to the end of the presentation so everyone could leave if nothing worked and all the material could be covered.

I was able to get into the VM, fire up Visual Studio. Since the demo was a live build of a tabular model and multidimensional model, I used a database I had created in SQL Azure as the data source and we built it the models live. Granted we were not able to do a complete multidimensional model because the database was not formatted as star schema, but it helped highlight the difference between what needs to be done prior to development. Overall it went very well (I think, surveys are forthcoming…). At the end of the day, without the work I had been doing in Azure I would not have been able to demo and it would have been a very short presentation.

Some lessons learned –

  • Be sure to have what you need to support presenting in a variety of scenarios. I should have made sure to have a converter prior to the conference as most convention centers and other facilities haven’t upgraded their projectors yet.
  • I will likely set up Azure VMs to support more demos. Just in case. It is always good to have a backup plan though a wireless connection would have painful to do that on.
  • Roll with it. Don’t give up, try to make the best of a bad situation. People understand things don’t always go perfectly. At the end of the day, I came to talk about multidimensional and tabular model development. I could have opened the floor up for discussion and did Q&A. Make the most of every situation.

Logging into Azure and Office365 with Different Accounts

I have been doing some work on the Modern Apps Live! content that required me to use both an Office365 account and an Azure account.  My Azure work is currently associated with my Microsoft Account (formerly known as my Live ID).  On the other hand, I am working with a Microsoft Office365 account which I have been unable to tie to my current account at this point.

While I was working with Office365, I was trying to open my Azure account to get some storage info that I wanted to use.  It told me I was already signed in with a different user ID and it did not have access to my Azure portal as noted in the image below.

image

Obviously I did not want to sign out, so I started looking for options.  The IT Director at Magenic, Dave Meier, mentioned he was having the same issue with multiple Microsoft accounts. So, we determined this was somehow related to IE so I installed Google Chrome.  By using Chrome I was able to work around the issue.  However, Dave came across the following article regarding this change in behavior starting in IE 8 http://blogs.msdn.com/b/askie/archive/2009/05/08/session-management-within-internet-explorer-8-0.aspx.  Turns out they changed how they manage sessions in that version.

imageThe article brings up a couple of options to work around.  One is using Alt-F-I to change the session.  Kind of annoying as I actually use my mouse (some of you keyboard junkies will like that solution).  So I used the shortcut option and created a shortcut for IE on my Windows 8 desktop which uses the switch –noframemerging.  I added this to my Taskbar, and voila, I have an easy way to open a new session browser which ignores my other session.  Keep in mind that you are essentially running two different sessions in IE which means that you will have two sets of credentials active.  Be sure to keep track of which is which.

Also, as a quick sanity test, Google Chrome works the same way.image  Even when I open a different Chrome instance, it keeps my session state. So, it appears the IE shortcut is a nice way to handle this issue.  Although, it is nice to know I can use a Chrome instance and and IE instance if that is my preferred method.

Add a comment if you try this in FireFox and want to chime in.  I really only want to use one browser, so Chrome is still one more than I want.

UPDATE:  You can also use InPrivate or Incognito modes accomplish the same thing.  Thanks to Rocky Lhotka ( T | B ) for pointing this out.

Join Me at Modern Apps Live! Las Vegas

ModernAppsLive

If you are familiar at all with Visual Studio Live! then you should check out this new conference.  At the Vegas conference, I am participating in this unique three-day event which is a series of sessions that build on each other as the conference progresses called Modern Apps Live! The goal is to do a end-to-end application build during which the presenters build the application as well as focus on Application Lifecycle Management, best practices, tools, and a variety of technologies.

I will be presenting on the database design and business intelligence components of the solution and would love to see you there.  To make this even sweeter, if you register with the following code, MVSPK3, you will qualify for one of the following offers:

  • The 5 day all-access Best Value Conference Package for just $1,595 – a savings of $500.00 off the standard price of $2,095! (*Includes pre and post conference events.)

OR:

  • The 3 day Modern Apps Live! Conference for just $1,295 – a savings of $400.00 off the standard price of $1,695

This is for new registrants only.  Make sure to use the code by February 27, 2013, to take advantage of the full discount.

Also keep in mind that you will be able to take advantage of the Visual Studio Live! sessions as well as this is a cohosted conference.

I hope to see many of you there!