Creating a fruitsalad on #WindowsAzure, because I can :-) , part 1 : introducing project “Chinese Gooseberry” (aka “Kiwi”) #SQL

azure new logo_SQL_web120I do some work for a company ( which is known as one of the best SQL Server and Microsoft BI implementers in Belgium (and even the biggest and best of them all afaik :-)). Of course these guys know their stuff so hence they also deliver training on the matter, either from our own training facilities or at the customer site. Most of these guys are even renowned MCT’s and MVP’s. But there’s always a catch on delivering training. they need something like a lab environment, which is as far i am aware one of the more complex that exists. If you want a KIWI to fly then you really need to put some effort in it (a lot of software and storage and a little patience will juts do the trick). This is my 3rd version of Kiwi, now adapted to the latest RTM bits available.

This time i started it with a different approach. In the past I always created these VM’s locally on my laptop. But knowing the amoutn of time is spent on rebooting the VM, being constantly on the move, this is very time consuming. Being an Azure lover gives me the advantage of course on creating this VM infrastructure on the platform and thus enhancing my time spending on creating the KIWI …

I’ve started of easily with the creation of a standard Gallery Windows Server 2012 platform image. This has 2 advantages. For starters i don’t need to install the OS myself and 2 I don’t need to upload the image to a blob storage in Windows Azure (which is very time cosuming). With the aid of the WindowsAzureDiskResizer tool (to be dl’ed here and more info on that here : I managed to resize the OS disk easily online (so with dl’ing, resizing it  and reuploading it back). Because 30Gb is small for a single disk VHD with all the packed goodnes mentioned below. Do note that when resizing your disk, you never expand it to more than 127Gb, due to he fact that the VHD’s used in Windows Azure are IDE based and thus can’t take much more than that.

This environment exists of following components:

  • Windows Server 2012 fully patched including
    • Active Directory Domain Services
    • DNS
    • Application Server
    • Webserver
  • Microsoft SQL Server 2012 SP1 (of course … , duh :-) ), including
    • Analysis Service
      • Tabular Mode instance
      • Multidimensional and Data Mining Mode instance
    • Reporting Services integrated into Sharepoint
    • Powerview
    • Powerpivot
    • Master Data Services
    • Data Quality Services
    • StreamInsight
    •  Semantic Language Statistics database
  • Microsoft Sharepoint Server 2013
    • Performance Point Services
  • Another bunch of necessary applications and extras like:
    • Visual Studio 2012 Ultimate Edition (latest patches)
    • SQL Server Data Tools
    • Office 2013 RTM
    • Windows Azure SDK and tools
    • Adventureworks sample databases
    • MDS Sample models (3in total)

the full installation walkthrough can be found here As an addition i’ve also included sample databases for both normal RDBMS and BI usage. The steps for those are:

Install AdventureWorks databases to be found here : (or direct 2012 link here :
AdventureWorks OLTP database for SQL Server 2012 AdventureWorks DW database for SQL Server 2012

  1. Download AdventureWorks2012 Data File (AdventureWorks2012_Data.mdf) to the SQL Data directory
  2. Attach data file, note that you must remove Log file before attaching so that it is rebuilt
  3. Download AdventureWorksDW2012 Data File (AdventureWorksDW2012_Data.mdf)
  4. Attach data file, note that you must remove Log file before attaching so that it is rebuilt
  5. Download and install Adventure Works 2012 LT database from the 2012 release and install
    Hints: In AdventureWorks LT dir, open instawltdb.sql in SSMS, from main menu Query | SQLCMD Mode, find ‘setvar SqlSamples*’ lines commented out, uncomment and fix as appropriate (SqlSamplesSourceDataPath should be set to parent of folder containing script)

As an extra in Windows Azure you still need to configure endpoints if you want to enjoy having the BI and PowerView/PowerPivot portals available just through the browser: I added the endpoints for both these Sharepoint sites, one for the SQL Server itself (1433) and one for the Sharepoint Management Site.

As you can see this is very time consuming, but a lot of fun on the way creating it :-). Now from this machine we can go forward. Next steps will be in creating a EMU image for testing Always on configurations in SQL Server combined with Windows Azure and its networking capabilities. Afterwards, I’ll try to combine the KIWI machine with a HDInsight for reporting capabilities , just as a proof of concept.

Small note:

yesterday I tried to have something exotic but unfortunately it was not possible (need to take a deeper look to it though): What if your could have MDS and DQS provisioned like that but hooked up to Windows Azue SQL Databases? Well i tried just that but unfortunately when tryin gto set up the DB communication (so during configuration) I get this error:


The bizare thing is that the can communicate with the Windows Azure SQL Database (through SSMS)  but not through the wizard for MDS, so that’s a shame pity. i tried to create a little workaround by installing the DB locally first and then moving that DB to the cloud, by using the SQL Server Export functionality or Enzo Cloudbackup Unfortunately but not completely unexpected the DB can not be migrated yet to an Azure equivalent, due to missing features in the platform (don’t forget Windows Azure SQL Databases are not SQL Servers, but adapted SQL Data Streams, hence there’s some stuff missing .. (see following references for T-SQL references on supported :, on partially supported : and on unsupported : T-SQL features. Also some of these unsupported features are a showstopper for deploying the MDS : Common Language Runtime (CLR), Database file placement, Database mirroring, Distributed queries, Distributed transactions, Filegroup management, Global temporary tables, SQL Server configuration options, SQL Server Service Broker, System tables, Trace Flags).

Next stop : preparation for part 2 : Adding a Zoo , introducing EMU :-)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s