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 (http://www.kohera.be) 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 https://github.com/maartenba/WindowsAzureDiskResizer and more info on that here : http://blog.maartenballiauw.be/post/2013/01/07/Tales-from-the-trenches-resizing-a-Windows-Azure-virtual-disk-the-smooth-way.aspx) 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 http://social.technet.microsoft.com/wiki/contents/articles/3402.how-to-build-a-sql-server-2012-hyper-v-virtual-machine-kiwi-build.aspx) 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 : http://msftdbprodsamples.codeplex.com/ (or direct 2012 link here : http://msftdbprodsamples.codeplex.com/releases/view/55330)
AdventureWorks OLTP database for SQL Server 2012 AdventureWorks DW database for SQL Server 2012
steps

  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:

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 :http://msdn.microsoft.com/en-us/library/ee336270.aspx, on partially supported : http://msdn.microsoft.com/en-us/library/ee336267.aspx and on unsupported : http://msdn.microsoft.com/en-us/library/ee336253.aspx 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 :-)

SQL Database Backups Made easy on #WindowsAzure

The last couple of weeks I’ve been doing quite a lot on the Windows Azure SQL Database (still not fully operational on that new name yet, always still want to use SQL Azure …) front, especialliy in delivering presentations on the subject.

One question that always keeps returning on that subject is: Why isn’t there a build in backup solution (or even worse why isn’t there support for backup) in Windows Azure SQL Database Servers …. ?

There’s actually a couple of misinterpretations on that subject:

  1. There are some mechanism available but not as in a straight backup and maintenance plan kind of way.
  2. There’s support for backup but not as you know it (so no in time backup restore / transaction log backups)

That being said, I still got a lot of DBA’s over my head on the topic, so I went on a quest for a suitable backup solution that still can be used with scheduled backups.

During my quest I stumbled upon a tool from one of my fellow Azure Insiders members, Herve Roggero (http://www.twitter.com/hroggero ), named BlueSyntax’s Enzo Cloud Backup.

Cool thing about the tooling is that it works as either a cloud backup system, an on premises backup system, storage backup and even as a migration tool.

What’s in the package ….

When you open the application, after installing, the app asks your Azure account settings, so you can easily connect automatically in the future. It immediately show that the original purpose was just to be a storage backup application.

When reaching the main screen, there’s a classical management console like application, with a treeview-based tasking area, consisting out of 3 parts:

  • A management area
  • A database area
  • An Azure Storage Area

We want to make good use of tool in the PoV of SQL Databases, so therefor it would be wise to register your SQL Database Environments. You can do this by just right clicking the databases node and choosing Register. A pop up will show and ask for the server name, user name and password.

Once done, you’ll be able to access your DB’s through the treeview:

you can now easily take a backup though a simple right click:

Then the magic moment… Once you are in the real backup screen you select either to backup towards a local file or towards Azure Blob Storage.

In a couple of easy steps you can even schedule your backup

Now the cool thing is you can even apply this to your on prem servers and from there out you can easily migrate databases towards Windows Azure.

Still no in time backup solution, but still a nice tooling on scheduling backups without hassle.

Is it feature complete? I believe a plenty of features is foreseen (at least, there could be even more features added for the future, like for instance incorporation of a SQL Data Sync feature to gain an even more close definition of in time db backups.) If you are a DBA or a DB Backup Responsible then this is really a tool to look into even perhaps as an additional solution for storing your on prem backups in the cloud or for even more facilitating your on prem backups to local files.

TFS 2010 + SQL Server 2012 = a no go (for now)

I was setting up a demo environment with SQL Server 2012 , Sharepoint 2010 and TFS 2010 (not DEV11)     To my surprise there seems to be no support for SQL 2012 when it comes to SSAS and SSRS: so no Reporting integration part when doing an advanced setup. Unfortunately I was not able to find an official statement on the matter or even a solution.

So far the system requirements only state SQL 2008 R2 SP1 tops , so implicitely no SQL 2012 yet. http://msdn.microsoft.com/en-us/library/gg265786.aspx

SQL Server 2012 Generally Available

As an addition to previous post, don’t forget boys and girls that SQL Server 2012 is generally available since Tuesday April 3rd 2012 (for MSDN and TechNet lovers it has been around for a little while longer now).

Just my €0.02 ;-)

Reminder to self: installing StreamInsight

Every single time when you install StreamInsight, the installer comes asking for a product key. But what if your installers come from sources like MSDN or TechNEt subscriber downloads? Most of the time your installer come with pre-bidded keys and you don’t find a key on you product keys page.

When you find yourself in this situation then all you have to do is (if you haven’t already) mount the DVD/ISO/other SQL Server Installation Sources and navigate to either the x86 or x64 folder and open the DefaultSetup.ini file. all you then need to do is to locate the PID section and there’s your product key :-)