Purple Frog Systems

Wearing Four Hats at SQLBits 2017

I know there are lots of great blog posts already out there from great people sharing their experiences from SQLBits. However, I hope none of them can offer an account quite as unique as mine from this years event. Why? Well, most people go to SQLBits as attendee’s to learn, network and socialise. This certainly applies to me. But ‘attendee’ was just 1 of my 4 hats (roles) at SQLBits 2017. In this post I’d like to share with you details of my other hats. Which alone poses a wardrobe problem… Orange with black writing or black with orange writing!

Firstly, let’s label my hats…

  1. Attendee
  2. Helper
  3. Speaker
  4. Exhibitor

Before I share any more information with you its worth saying from the start that this was not an easy task juggling these 4 different roles! Very long days with little sleep, little nourishment, and little time to pause. That said, I wouldn’t change it for the world and would gladly do it all again. The reward and enjoyable of SQLBits greatly outweighs any negativity.

Hat Number One – Attendee

I’ve already mentioned being an attendee is the main reason for going to SQLBits. But just to add some stats to that statement:

  • 88 expert speakers
  • 290 hours of content
  • 120 sessions
  • 13 tracks
  • 4 event days
  • 7500 square meters of conference space
  • 700 square meters of partying

SQLBits really is an awesome event! If you haven’t been before I strongly recommend it. Even if you just go for the Friday party, maybe not dressed as a Rubik’s cube though!… Sadly for a disco themed party this often takes some explaining (invented in 1974 + BI SSAS cube)  🙂

Hat Number Two – Helper

Helping at SQLBits is so much more than wearing a bright orange tshirt and telling people where the toilets are! Its about being part of something bigger. Being part of the #SQLFamily that makes the conference tick. Those early starts and late nights to ensure attendee’s have the best possible experience are returned by being part of this great team.

To give you an idea of what I mean by family, here is a picture we posed for (I’m in the middle), just to congratulate an absent helper and friend on the birth of his son just days before the conference. Congrats Terry aka @SQLShark.

I’m not going to waffle on about helping, instead I’m just going to direct you to the blog of my bearded friend Mr. Rob Sewell about why we volunteer at SQLBits. Great post. Great pictures!

Hat Number Three – Speaker

This was only my second time at SQLBits but I was honoured to get selected to speak on Saturday. I took along the Raspberry Pi’s and showed the audience how to build an IoT solution end to end within the Microsoft stack using Azure.

For those that asked me for my C# UWP app code that I ran on the Pi this is now available on GitHub here:

https://github.com/mrpaulandrew/IoTPi3FezHatUWPApp

If speaking on Saturday wasn’t enough I was also invited to talk in Chris Testa-O’Neill’s session on Friday about Azure Data Factory. I offered our real world experiences of the service from a recent customer project, building a complete business intelligence solution in Azure.

I’ll certainly treasure those 20mins in the limelight. Thanks again Chris.

Hat Number Four – Exhibitor

This year with SQLBits being held on the home turf of Purple Frog Systems we simply had to sponsor the event and make our mark. This was a very proud moment for the company having never sponsored Europe’s biggest data platform before.

I was charged with the setup and collapse of the stand as well as speaking to all the lovely attendee’s during breaks about what we do. The purple glow sticks and balloons were all my idea. You’re welcome!… Did I mention Purple Frog is also recruiting, please send your CV to Jobs@PurpleFrogSystems.com.

Another little known sponsor called Microsoft 🙂 also embraced the spirit of things by having a very purple exhibitor stand. Seen to the right. Total coincidence, but let’s claim it as a win for the frog massive! Ha.

If you won one of our Raspberry Pi starter kit prizes I’d be keen to hear from you about how you’ve used the device. Please reach out.

That’s it from SQLBits this year. Start counting down the days until next years event.

Many thanks for reading.

My SQL Relay 2016

sqlrelayIn 2015 I attended a SQL Relay day in Birmingham, as a humble attendee with a colleague. I sat on the edge of my seat in every session soaking up all I could from the speakers. I remember being in awe of these technical rock stars that gave their knowledge to the community. A community that was strongly bonded and recognised by many sponsors.

In 2016 the humble attendee was long gone. I was now heavily involved supporting operational elements of the conference. I was now a speaker, giving back my knowledge in every city throughout the week talking about IoT using Raspberry Pi’s and Azure. I believe I completed my initiation into the community (the SQL Family). I accompanied friends and colleagues on the #FunBus each day as we travelled to new locations. I organised other speakers as they arrived at venues and helped with the event in a totally flexible capacity, dealing with whatever was needed. From attending a one day event last year I was now amongst a handful of people that would be at every SQL Relay day throughout the week. As a sponsor, speaker, helper, go to guy.

This blog post is my brief and informal account of SQL Relay 2016 from an insiders’ perspective.

Before Monday

photo-12-10-2016-13-13-20As Purple Frog Systems were a silver sponsor this year and heavily involved in the overall setup of the SQL Relay event, time before the first day with delegates was spent sorting everything out from graphics to sponsor swag, bags, leaflets, transport, hotels and everything in between. Organising a conference in five cities in five days with zero time and little budget takes a phenomenal amount of effort. The picture to the right was the scene in the Purple Frog conference room leading up to Relay.

This little lot had to be sorted, condensed, part sent to Nottingham for the second half of the Relay week and most importantly Tetris’d  into the back of a 17 seat minibus aka the #FunBus. Not just once, but five times in a repeatable way.

Monday 3rd Oct – Birmingham

I was up and out the house for 6:15am, yes, this time does exist! Much to the disgust of my other half who reluctantly gave me a lift to Stafford train station, I bound for Birmingham New Street. Upon arrival I filled up with coffee and met up with Microsoft’s Andrew Fryer, David Postlethwaite and Purple Frog’s Hollie Whittles. We then a short car journey to the Midlands Art Centre (MAC) this was the calm before the storm.

Upon arrival an army of helpers, speakers, sponsors and volunteers unloaded the mini bus and setup for the first day of the conference. Spirits were high and enthusiasm in abundance. The doors opened on time and delegates poured in.

At 12pm I delivered my first talk of the week on building an IoT solution end to end in the MAC’s Hexagon theatre, this went well and I received positive feedback. This was a great start to SQL Relay for me as I am a fairly new community speaker.

photo-04-10-2016-14-12-43 photo-04-10-2016-14-12-31 photo-04-10-2016-14-12-53

Thanks Terry (@SQLShark) for the terrible picture of me 🙂

After the prize giving, a select few loaded up the #FunBus and we set off for Cardiff. Alex Yates from DLM Consultants had turned 30 the day before, so as a SQL Family it was necessary to have a party aboard the #FunBus between Birmingham and Cardiff. This included party hats, balloons, banners, singing and birthday cake! The atmosphere aboard the mini bus was brilliant and perfectly fitting of the SQL Family.

photo-03-10-2016-17-55-01The terrible picture was my fault this time!… But you get the idea.

We arrived in Cardiff, with the first leg of the journey complete. We checked into our respective hotels and met up with the Cardiff massive for a beer or three led my Steph Locke.

Tuesday 4th Oct – Cardiff

photo-05-10-2016-06-31-55A slightly more civilised start today, we met at 7am in the hotel for breakfast before taking a short walk across the road to the Cardiff Motorpoint Arena. The oiled machine of helpers sprung into action again, setting up the event with time to spare before delegates arrived.

The Cardiff peeps really spoilt us at lunch time. We were given lamb tagine, couscous and salad. It was lovely, especially the second time round 😉

The demo gods were once again kind and my IoT talk went without a hitch.

Later in the day we learned that Jean Michel Jarre had also come to the Motorpoint Arena for SQL Relay, providing a great backing track whilst we packed away.

The force was strong in Cardiff so rather than hitting the road straight away we stayed for another SQL Family gathering at Five Guys. It was a great end to the second day. Spirits and enthusiasm were still high.
photo-05-10-2016-06-33-59

Left to right the people I know, sorry if I missed you; David Postlethwaite, Alex Yates, Steph Locke, Ryan Yates, Rob Sewell, Alex Whittles, Richard Douglas, Stuart Moore, Me, John Martin and Andre Melancia. Photo by Andrew Fryer.

After burgers and chips most of us we were aboard the #FunBus again heading for Reading. After getting lost in the Reading traffic, we dropped people off at there hotels and had a relatively early night.

Wednesday 5th Oct – Reading

Sadly there was no breakfast provided in the minimal Reading digs, but I didn’t mind as todays SQL Relay was being hosted at Microsoft’s shiny UK offices at Thames Valley Park, complete with in-house Costa Coffee and Starbucks! SQL Sentry’s John Martin welcomed us into the building and provided the caffeine while we waited in the lobby… It was massive and luxuriously furnished. The Microsoft buildings were superb, even though the SQL Relay conference only occupied a small corner in building 3.

Data_Cul_MFSTHQ

12pm came around again and I presented my talk in a state of the art theatre. The venue made the buzz of speaking even more exhilarating.

photo-06-10-2016-06-57-24 photo-06-10-2016-06-57-32

In the afternoon we were greeted by Claire Smyth, Microsoft’s MVP Programme Organiser. Claire spent a lot of time learning about the wider community and brought us another round of coffee, while we discussed how else Microsoft could help support local user groups.

Sadly, the day came to a close and we were kicked out of the pretty office. But we were over the hump of the week.

We boarded the #FunBus bound for Nottingham this time. This was the longest leg of our journey and travelling up the motorway, limited to 62mph drained a lot of my positivity. The new co-pilot/DJ didn’t help either :-). Fortunately, we had more cake on-board and conversation flowed freely which helped pass the time. Also a new game was created to get me more twitter followers, this offered much amusement discovering how many twitter accounts one person can have!

Upon arrival in Nottingham we made our usual rounds of local hotel drop offs and arranged to meet up at The Round House Pub at 9:30pm. This later resulted in 10 of us all going for curry at The Cumin. The restaurant owner was surprised and happy to accommodate our party on this Wednesday evening. With full stomachs SQL Relay paid the bill (thank you!) and we headed off to count sheep.

1
SELECT COUNT(0) FROM [Brain] WHERE [Thoughts] = 'Sheep'

Thursday 6th Oct – Nottingham

Another early start, the SQL Family met for breakfast at 7am. The St James Hotel was great, but the coffee machine was broken! Let’s just take a moment to reflect of the gravity of this issue. Two third party coffee vendors in house at the Microsoft offices to instant coffee today! Trip advisor shall hear about this! 🙂

Reflection over. Today we were at the Albert Hall! Not Thee Albert Hall, but good enough if anyone asks. An aged building with a beautiful main hall, complete with a massive pipe organ. Sadly, our conference suite was downstairs.

By day four, things were pretty automatic with Relay setup and the day flew by. My IoT talk went well and I even went the extra mile to setup the demo again in the venue lobby for delegates who missed my session.

photo-06-10-2016-14-30-45 photo-06-10-2016-09-44-10

Purple Frog’s Alex Whittles presenting on the right, as nobody took my picture today, well not that I’m aware of.

photo-06-10-2016-22-49-16We packed up and loaded the #FunBus heading for Leeds. We arrived to be greeted by a city light show with music and stunning visuals projected onto the side of our hotel. In addition, Microsoft’s Mike Boswell had booked us a private room at the local Fazenda – a Brazilian restaurant where huge amounts of meat get delivered to your table by gentlemen holding swords and very sharp knives. Yum yum! Wine flowed and the variety of meat were gorgeous.

By 10:30pm I was struggling to keep my eyes open so admitted defeat and headed back to the hotel. I was gone as soon as my head hit the pillow.

Friday 7th Oct – Leeds

The 7am breakfast routine continued, but none of us really had an appetite after the meat feast from the night before.

thankrayToday we were at the Thackray Medical Museum for the last day of SQL Relay. The venue was in a lovely old hospital building complete with exhibitions on how leg amputation used to be performed!

The local SQL community helpers sprang into action once more providing fresh hands to make light work of the conference setup. I confess by day 5 I wasn’t feeling very fresh or enthusiastic. Despite this I was honoured with a midway clap from the audience in my IoT session after the first Raspberry Pi demo worked like a dream, feeding its sensor values into Azure in real-time. It was a proud moment. I may have even cracked a smile myself, which a member of the audience caught on camera 🙂 … because I’m not a grumpy person *cough*

photo-07-10-2016-14-26-13

The last prize giving was done and SQL Relay was closed to delegates for another year.

After Friday

Now it’s back to normality for most people however the SQL Relay drum beats on.

This morning was spent sorting out the Purple Frog conference room again after all the SQL Relay stuff was unloaded on Saturday. Then it’s a case of finishing this blog post and sorting out sponsor attendee slips before we can get things closed off.

It’s been brilliant being part of SQL Relay this year. The people that give their time to make the event run are gods amongst men. In each city, having that fresh wave of helpers really made the difference. Thank you all.

In short; it was an incredible, but exhausting week. One which I hope I’ll be lucky enough to repeat next year.

Many thanks for reading.

Edited by the future Mrs Andrew.


Using Hyper-V and PowerShell to Create the Perfect Developer Workstation

So the second challenge I faced after unboxing and plugging in my new workstation at Purple Frog Systems was software. Having customers using all versions of SQL Server from 2005 onwards I didn’t want to tie myself to a particular version locally and I also didn’t want the hassle of running lots of different SQL Server services all on the same host operating system. Especially if I wanted to use Windows 10 as my host, which as we know would have compatibility issues with SQL Server 2005 and our old friend BIDS.

WindowsFeaturesHyperV

Enter Microsoft Hyper-V

In Windows 8 onwards Hyper-V is available out of the box as a feature which can simply be switched on from Control Panel > Programs and Features > Turn Windows Features On or Off. Apologies in advance if the home editions don’t support this.

Note: be sure to select the Hyper-V Module for Windows PowerShell.

Now with my Windows 10 host now running Hyper-V management services I set about creating a bunch of virtual machines to serve up every version of SQL Server required.

Whenever starting the Hyper-V manager always do so as an administrator. The user interface throws some odd errors without the elevated permissions which could lead you down a rabbit hole of Google searches. Trust me!

Creating a Virtual Switch

For those of you new to Hyper-V creating a virtual switch for your guess virtual machines is one of the most important things to get right and sort out first. It’s very simple to do, but without it your network admins and DHCP police might start questioning what on earth your physical network connection is doing. Again, trust me on this!

HyperVCreateSwitchFrom your Hyper-V Manager toolbar go to Action > Virtual Switch Manager. In the dialogue window that appears in the right hand panel choose the switch type of External and click Create Virtual Switch. Give the switch a name and select the External Network: that the new switch will basically impersonate. This will be a sort list if you only have 1x physical network connection.

 

With an external virtual switch in place any guess machine setup to use it as a network resource will appear on your network in the same way your physical host does. It’s own MAC address, IP address, hostname etc. Also be mindful that Hyper-V will take over how your network connections appear in your host operating system and a CMD ipconfig /all will start to look a little more complex, but this is nothing to worry about and perfectly normal.

Creating the Guest Virtual Machines

To offer some repeatability when creating my guest virtual machines I broke out the PowerShell, hence including the module at install time. With a few parameterised cmdlets I was able to setup several empty virtual machines ready for my guest operating systems. Example below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#Create hyperv machine
New-VM `
    -Name $VMName `
    -Path $VMLocation `
    -MemoryStartupBytes $RAM `
    -NewVHDPath $VHDOSFile `
    -NewVHDSizeBytes $VHDSizes `
    -SwitchName $NetworkSwitch `
    -Generation $Generation
 
#Set Number of CPUs
Set-VMProcessor `
    -VMName $VMName `
    -Count $CPUs
 
#Create data disk
New-VHD `
    –Path $VHDDataFile -Dynamic `
    –SizeBytes $VHDSizes `

Download the full script I used here.

Note: I’m not a PowerShell expert, just a big fan of it’s uses.

You’ll see in the full script that I give my virtual machines 2x hard drive files each on different controllers (IDE and SCSI), which are in turn are created on different host hard drives, mainly because my host has 3x physical disks. My original intention was to have all operating systems on mechanical disks and all data on solid state disks. So for guess SQL Server installs the virtual machine data disk would be used to house databases attached to the instance. In practice this didn’t really pay off but then this is just another advantage of using Hyper-V, that the underlying resources can be moved around as required.

If you have time I would recommend having you ISO’s on a separate physical disk to the guess hard drive files. This greatly speeds up installation. You could even run the guess OS hard drive files on your solid state drive just for installation, then move them to mechanical disks afterwards.

The Guests

Assuming you now have a bunch of virtual machine shells in place I’d recommend the following operating systems vs versions of SQL Server. Mainly to avoid any compatibility issues.

  • SQL Server 2005 – Windows Server 2003
  • SQL Server 2008 – Windows Server 2008
  • SQL Server 2012 – Windows Server 2012
  • SQL Server 2014 – Windows Server 2012
  • SQL Server 2016 – Windows Server 2016 (assuming the OS is released in time)

I’m not working to talk about installing Windows and SQL Server on each guest. I’m assuming your familiar with that Next, Next, Next process (if you want the defaults!). However I would say that for SQL Server 2012 onwards I did this twice for Analysis services to give me both Tabular and Multi Dimensional services in my guess virtual machine.

The Goal

If you’ve worked through this post and setup your host in the same way I have you’ll now be able to enjoy the advantages and flexibility of running all versions of SQL Server at the same time with all SQL services in guess virtual machines on your developer worksation.

HyperVVMList

My virtual machines are also setup with dynamic RAM:

  • Initial value: 2048 MB
  • Min value: 512 MB
  • Max value: 8192 MB

The Memory Weight slider is great if you want to keep all your guess virtual machines running at the same time like I do. If doing some development work on SQL Server 2005 I simply increase that guests RAM priority which dynamically adjusts the less used virtual machines so the host doesn’t get overwhelmed. Plus you’ll only be developing on one version of SQL Server at once right?!

HyperVRAMWeight

The last thing you’ll probably want to do is install some SQL Server client tools on your Windows 10 host. I went for SQL Server 2012, plus Visual Studio 2012. Then SQL Server 2014, plus Visual Studio 2015. Installed in that order.

Final Words

Please be aware that the above was only possible with a Microsoft MSDN subscription which provided licencing for the guest operating systems and older versions of SQL Server.

During this setup I also had the advantage of being a domain admin, which meant creating things like service accounts for all my virtual machines was easy, adding my virtual machines to the domain and accessing SQL Server services from my host using AD authentication made easy. If you not a DA SQL authentication and local workgroups is ok, but has its limits for SSAS.

RDCMAddMachine

You may want to try using Remote Desktop Connection Manager to access your guess operating systems. In the latest version a VM ID can be including giving console style access without needing to connect to each guess from the Hyper-V manager.

VM IDs can be found with the following bit of PowerShell.

1
Get-VMMemory -VMName "PF*" | Select VMName, ID | Sort-Object VMName

Very last thing, I mentioned my host machine had 3x physical hard drives. The third of which is a huge 4 TB block. To keep my guest operating systems fairly customer independent you’ll have seen I only gave the standard virtual hard drives in PowerShell 100GB of space each. What I then do is give a guest an additional virtual disk much larger in size which resides on the host big data volume. Work is done here. Then post project completion this customer specific virtual data disk can just be dropped/archives/moved away and the guest machine ready for the next work item. It’s of course a management overhead, but helps keep things clean.

Many thanks for reading.


My Introduction to the SQL Server Community

Once I was blind, but now I see!

In the early part of 2015 (I think) a work colleague and friend suggested that on Thursday evening I attend the SQL Server Midlands User Group. The event was completely new to me, I’d neverSQLBitsXVPic1 before ventured outside my professional comfort zone, or even had any appreciation that there was such a gathering about SQL Server locally. Anyway, I registered and got some more details. The event was currently ran by some guy called Alex Whittles.

Thursday evening came around and I made the journey from Stafford to Birmingham and the venue for the user group, the Midlands Art Centre (MAC). Little did I know that this event was to become the catalyst for my career and involvement in the SQL Server community. Needless to say the event was a massive eye opener to how close the SQL Server user community was, not just socially, but in terms of knowledge sharing and skills transfer. Family is the best way to describe it, a massive SQL Family.

Time passed and I proceeded to attend the next few Midlands User Groups, meeting new people and listening to the rich variety of speakers that gave up there time to attend each session. In short I was hooked. I might compare myself to the robot Johnny 5 from the 1986 film Short Circuit needing “more input”. Well the webinars started to flow and I quickly realised that I needed to shed my current skin and public sector employer and engage more with the SQL Server Community with a view to expanding my horizons.

Next I attended the Azure User Group, SQL Relay, SQL Saturday Exeter and then the big one SQL Bits XV (which was also the technical launch for SQL Server 2016). I was honoured to be an official helper at my first ever SQL Bits which felt incredible to be part of this huge event. Meeting people that are as passionate about what they do as I am was truly inspiring.

During this time my ambitions were rewarded and through the SQL Server Midlands User Group that guy I mentioned above, Alex Whittles, became my boss! I joined Purple Frog Systems as a Business Intelligence Developer in March 2016.

SQLBitsXVPic2

So what next? Well my friend, keep an eye on this blog and lets find out.

#SQLFamily

Thanks Rob for the pictures.

 


 

 

Paul’s Frog Blog

Paul is a Microsoft Data Platform MVP with 10+ years’ experience working with the complete on premises SQL Server stack in a variety of roles and industries. Now as the Business Intelligence Consultant at Purple Frog Systems has turned his keyboard to big data solutions in the Microsoft cloud. Specialising in Azure Data Lake Analytics, Azure Data Factory, Azure Stream Analytics, Event Hubs and IoT. Paul is also a STEM Ambassador for the networking education in schools’ programme, PASS chapter leader for the Microsoft Data Platform Group – Birmingham, SQL Bits, SQL Relay, SQL Saturday speaker and helper. Currently the Stack Overflow top user for Azure Data Factory. As well as very active member of the technical community.
Thanks for visiting.
@mrpaulandrew