Azure series : Building an Azure - SQL Server - Analytics box with VirtualBox (revisited)

Introduction

Back in 2012, I wrote a blogpost about building a SQL Server playground with VirtualBox. Now, I want to build another VM with Windows Server 2016, Visual Studio Professional 2017 (and 2015) and SQL Server 2017. So, this blogpost is an upgraded version of the earlier blogpost I wrote. Well, it is not as extensive as I wrote earlier. I'm not building a Domain Controller, etc, but just a simple Development Box for business intelligence, analytics, data science and data management with SQL Server, Visual Studio, Excel and PowerBI. I'll also focus more on Azure and install tools for integration with Azure.

The steps I'll perform and are described in this blogpost are:

1. Create a VirtualBox environment.
2. Install windows Server 2016.
3. Do some additional actions and sysprep the environment.
4. Installation of Visual Studio 2017 (Version 15.5).
• Installation SSDT BI templates for VS 2017.
5. Installation of Visual Studio 2015 Update 3
• Installation SSDT BI templates for VS 2015.
• Installation of Azure Data Factory Visual Studio templates (not supported in VS 2017, yet)
6. Installation of SQL Server 2017.
• Please pay attention for installation of JRE 8 (and not 9).
7. Installation of SQL Server Management Studio (version 17.4).
8. Installation of sample databases like WorldWideImporters samples.
9. Installation of Office 2016.
10. Installation of PowerBI Desktop latest version
11. Installation of R Studio (now version 1.1423).
12. Installation of R Client for Windows (version 3.4.3.0).
13. Installation of R Tools for Visual Studio (version 1.1).
14. Installation of Azure Storage Explorer.
15. Installation of Visual Studio Cloud Explorer (both VS2015 and VS2017).
16. Installation of Dax Studio (now version 2.7.4) and DAX editor for VS.
17. Installation of PowerPivot utilities (version 1.09).
18. Installation of Redgate SQL Search.

Other suggestions are installing the tools BIMLExpress, BIDSHelper and Vertipaq analyzer.

Setting up the VirtualBox

First, install VirtualBox. Next, press on the New button to start the configuration of a new VirtualBox environment.

Give the VM a proper name and set the right properties.

Create a Harddisk for the VM.

I decide to use the VDI image of VirtualBox. In the past I used the vhd for building the harddisk, but the VDI is native supported harddisk type of VirtualBox.

Let's check the Expert modus but nothing really needs a change. 

And choose for Create for building the Virtual Machine.

Note: I've started with 50 GB but I had to expand to 75 GB and now the disk is too small again.

Installation of Windows 2016

I've downloaded Window Server 2016 from my Visual Studio Subscription and used it for the installation of Windows on the VM. 

I decide to use the Desktop experience. 

Some settings are set and the installation proceeds

Additional steps

3) Run Windows Update

• Disable IE ESC.
• Remote desktop enabled for administrator.
• Disable expiration for administrator passwords
• Turn off windows login/logoff sounds
• Update folder options to show all files and extensions
• Add desktop icons for Computer, User files and Recycle bin

1) Install the Guest Additions by choosing the menu apparaten and "Insert Guest additions CD Image" and go to the dvd drive with Windows explorer.

2) After this installation, the VM needs a restart so that's what we do next. The VM is restarted and the next step is to sysprep the VM again. In this way, we can create a BASE VM for future usage. 

And it's processing the Windows intallation with the sysprep tool.

I created a clone of the VM machine. Now, I've always a VM with Windows Server 2016 available for new installations of application software.

Installation of Visual Studio Professional 2017

The next step in the process of building a analytical box is installation of Visual Studio Professional. The newest version is Visual Studio 2017. At first, I started with Visual Studio 2017 but some VS templates are not available on 2017. Therefore, I decided later on that I had to install 2015, too.

The next step is to choose the workloads in the Visual Studio installer. For this installation I choose to install : 

• Azure Development with Azure Data Lake and Stream analytics tools, Cloud services tools, etc
• Python Development. Off course this includes Python support, but also Azure Cloud Services Core Tools.
• Data and Storage processing. The following optionals are installed : F#, Pyhon and R support, etc.
• Data Science and Analytical Applications. This includes tools like Python language support, Microsoft R client ((3.3.2) and R Language support.

And now press on Install and the installation process is started:

Now let's see what kind of templates are installed in Visual Studio:

• SQL Server
• SQL Server Database Project
• R
• R Project
• Azure Data Lake
• HIVE projects
• PIG projects
• Storm projects
• U-SQL projects
• Stream analytics
• Stream analytics projects
• Python
• All kind of projects like, Web projects, Django projects, IronProject, etc

Next step in this VM building process is the installation of SQL Server Data Tools for Visual Studio 2017. 

Regarding Data Lake and Data lake analytics please note that after installation:

• The Server Explorer > Azure node contains a Data Lake Analytics node.
• The Tools menu has a Data Lake item.

Installation of Visual Studio 2015

During the installation of Visual Studio 2017 I noticed that the Azure Data Factory V1 templates are not supported and I wanted to use these for working with Azure Data Factory for the course Orchestrating Big Data with Azure Data Factory on Edx. Therefore I installed the VS2015 also. Alhough it is not recommended to install VS2015 after VS2017, in my case I did not have choice, otherwise to deinstall VS2017 but that was something I could do anyway.

And now the installation of the Azure Data Factory Visual Studio 2015 Templates is possible.

And now I can create Azure Data Factory projects for Azure Data Factory V1.

In the meantime I installed also the BI templates for Visual studio 2015, so now I can develop the SSIS, SSAS and SSRS in VS2015 as well as in VS2017.

Installation of SQL Server 2017 Developer edition

The next step in the processbuilding a new playground VM is the installation of SQL Server 2017. So I downloaded SQL Server 2017 Developer edition and selected all the options.

And because the PolyBase connector is selected in the feature selection the following error occurs.

So, let's install the latest version of the Java engine (v9) and all problems are solved...Well, that is not the case. The error keeps coming and this seems a bug in the installation process. You have to install version 8. And so I did and the error is disappears after the installation of Java update 8.

And after a couple of times Next, Next, Accept en Next the installation process continues.

Installation of SQL Server Management Studio 17.4

In order to work with the Database engine and other featrues of SQL Server you have install the SSMS too. So, I downloaded the latest version of SSMS.

Check regularly for new updates of extensions and VS templates.

Installation of Samples databases

Next step was the installation of the sample databases like WorldWideImporters and AdventureWorks, both OLTP and DW. Support for Adventureworks is stopped, but examples of queries on MSDN are still based on AdventureWorks. That's why it is good practice to install AdventureWorks too.

Installation of Office 365

Now, an important item for data analysis is (still) Excel and that is available in the Office365 subscription. I decide to install a version of Office 365 in my VM. This way it is easy to browse the Tabular models in my VM and the Tabular models I develop in Azure Analysis Services.

Installation of PowerBI Desktop

Yet another tool that is indispensable nowadays is PowerBI Desktop. For this reason a Microsoft analytical VM box without PowerBI Desktop is not an Analytical Box;-)

Installation of  R Studio

Although Visual Studio also support R now, I do find it convenient to use R Studio for building R scripts. Most of the time I build, test and run R scripts in R Studio and there is a lot of support for other tooling like Swirl, R Markdown, R presenter, Shiny apps. I don't think that these are supported in Visual Studio. May be that is for future blogposts!

Microsoft R Client on Windows

It can be handy to use the Microsoft R Client on Windows for analysis. Therefore I install this client too. The current version is 3.4.3.0.

It will take some time to install.

Installation of R Tools for Visual Studio

Next is using R in a IDE like Visual Studio. Download R for Visual Studio and install the software. Using R in Visual Studio happens by creating a R project.

Installation of Azure Storage Explorer

For exploring, uploading, downloading, and to manage blobs, files, queues, tables, and Cosmos DB entities it is a good idea to install the Azure Storage Explorer. Current version, at the moment of writing, is 0.9.4. (january 2018).

Installation of cloud explorer

The next thing that useful is the ability to browse the Azure environment with the cloud explorer in Visual Studio 2015 and 2017. Download these from the marketplace: VS2015 cloud explorer  and VS2017 cloud explorer.

Installation happens with a vsix file and double clicking is enough. At first glance, it seems that the installation was only on VS2017, but the Cloud explorer is installed both in VS2015 and VS2017. The Cloud Explorer vsix for 2017 seems not necessary (!).

Installation of Dax Studio and DAX editor for Visual Studio

Another useful tool is DAX Studio. This tool will help you build and analyze DAX expression. With DAX Studio it is also possible to connect to Azure Analysis Services. Please verify during the installation of DAX Studio to check the option "Ensure that the pre-requisite for analysis Services Azure are installed"

Connecting to Azure Analysis Services works!

The installations of VS DAX template happens with the known, so called vsix files. Double clicking the file will easily install the editor in Visual Studio 2015 (2017 is not yet supported).

So, that is finished up too and now up to the next tool, PowerPivot utilities.

Installation of PowerPivot utilities

The installation of PP utilities is easy. In the accompanied README file the instructions are well explained. Copy the file to a desired location and integrate this in Excel with the menu Options and Add ins.

Important note is that PowerPivot is not included in all versions of Excel 2016. For instance, Excel Home Edition does not support PowerPivot.

Installation of SQL Search

A useful tool to search your database is SQL Search from Red Gate. This is a free tool and there are also other tools available.

The current version doesn't work with SQL Data warehouse, unfortunately.

Conclusion

At the moment of writing, the Azure Data Factory V1 Templates are not compatible with Visual Studio 2017. Only VS2015 is supported. Now, most of the times you want the latest version of software. But, because Azure Data Factory is only supported in VS2015, I should have installed VS2015 first (as adviced by Microsoft) and then VS2017. Installing an older version after a newer version of Visual Studio is not really a good practice. So, my advice for building VM's with Visual studio would be to install always the older version first and then the newest version of Visual Studio.

Ath the moment of writing Azure Data Factory V2 is out now but the ADF templates in VS do not work with ADF V2. It's not clear to me if Microsoft will continue supporting ADF Templates in visual Studio.

Greetz,

Hennie