Just For Fun

Community Speaking Analysis with Power BI

Just a short post.

During the SQL Saturday Cambridge speaker meal on Saturday night I was chatting with various people at the dinner table. During this time I got asked the question by an event sponsor “do you speak at many of these things?”. While the general answer I gave at the time was yes. It occurred to me that I didn’t have any accurate means of answering the question with some richer detail. Being a data/business intelligence person this required some serious self reprimanding. I could of referred to my MVP activity log, but that isn’t easily accessible and contains a list of all community involvement. I therefore decided to create a dataset just for speaking within Excel and visualise it with Power BI. Below is what I created.

Nothing to fancy here, just a simple dataset containing 6 fields:

  • Date
  • Event
  • City
  • Postcode (for the map)
  • Attended
  • Subject

What do you think? Click on it to enlarge.

Generally I spend my time architecting data solutions and developing the services to deliver business intelligence to customers. I confess, I don’t spend much time visualising that data and making things look pretty in tools like Power BI, so go easy with the comments.

I of course then published this to PowerBI.com meaning when I get asked the question next time I can simply load the Power BI app on my phone and answer most questions. Being a good developer (I think) I also created a mobile friendly view of the visuals.

If you’d like access to the published version of the dashboard please use this link to request access: https://app.powerbi.com/groups/me/dashboards/91878616-07f4-4f8b-be54-b4df46a2aab7

Many thanks for reading


Azure Business Intelligence – The Icon Game!

As Azure becomes the new normal for many organisations our architecture diagrams become ever more complicated. Articulating our designs/data flows to management or technical audiences therefore requires a new group of cloud service icons in our pretty pictures. Especially for hybrid solutions. Sadly those icons aren’t yet that familiar for most. So, here’s a very simple blog post to help you recognise what’s in the Azure stack from a Purple Frog business intelligence perspective, in no particular order.

All of the following having been snipped from the Azure portal dashboard so there shouldn’t be any surprises once you start working with these services.

  Azure
    Data Catalogue   Data Factory
  Batch Service   Data Lake Storage
  Data Lake Analytics   Power BI
  Cosmos DB   IoT Hub
  Event Hub   Stream Analytics
  Machine Learning   SQL DB
  SQL DW   Logical SQL Server
  Data Management Gateway         Analysis Services
  Resources   Virtual Machine
  Azure Active Directory   Blob Storage

Happy drawing!


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.

SQL in Seattle – My PASS Summit 2016

passsummitimg_8236This year was my first time at the PASS summit in Seattle. I’d of course been to plenty of other community events and conferences, but this was the big one! Spanning 3x days with 15x tracks to choose from in 4x rounds of sessions per day. Excluding precons.

Really words don’t do the event or the venue justice. It’s massive. You basically need to see it for yourself. Which leads to the question, why write a blog post about it? Well I was fortunate enough to spend Saturday to Saturday in Seattle, doing the tourist thing for the first half of the trip with the future Mrs Andrew. Then at the PASS Summit for the second half. This blog is therefore going to be a shortened account of my experience, but in the form of tips for other first timers, to both the city and the summit.

Tip 1 – Travelling back in time is horrible. But there’s no avoiding it. So, my advice would be to stay awake as long as possible. Watch 3x films on the plane back to back if you have to, I did. We started our journey from the UK at 2am Saturday morning.

  • 1 hour to get up, finish packing and leave the house.
  • 45mins drive to Birmingham airport.
  • 10mins walk to the terminal.
  • 15mins queue to check in and off load bags.
  • 2 hours waiting around and eating breakfast.
  • 1 hour for everybody to board the plane.
  • 2 hour flight to Amsterdam.
  • 1 hour waiting around in Amsterdam airport.
  • 1 hour for everybody to board another plane.
  • 11 hours flying to Seattle.
  • 30mins getting through customs.
  • 30mins waiting around for luggage.
  • 10mins walk to the train.
  • 30mins train ride to down town Seattle.
100px photo-23-10-2016-17-39-52

Tip 2 – Do not get a taxi from the airport to down town Seattle. It’ll cost around $40. The Light Link Rail costs just $3 per person one way. Follow the signs upstairs in the airport terminal arrivals.

  • 10mins walk to the hotel.
  • 15mins to check in.
  • 5mins to find the room.
  • 45mins to unpack.

Now at this point if you add up the above you will have been awake for around 23hours. Which is no thrill. Espically when you discover the local time in Seattle is now only 2pm in the afternoon. This is where you need to power through. Go for a walk. Do anything to keep your zombie like shell of a body awake until the locals sleep.

Tip 3 – Do not lie down on anything once you’ve past the point of being awake for 24hours 🙂

I survived until around 7pm local time before I crashed. It wasn’t pleasant, but it made the adjustment to US time a little easier.

The follow morning we awoke at a reasonable hour and started our adventure. Having never visited Seattle before we were keen to soak up the sites and sounds.

Tip 4 – Go to the Pike Street Food Market near the water front and sample everything. It’s delicious and a real hive of activity.

Slowly cooked brisket, smoked salmon, clam chowder, Russian pastries to die for. Every colour of fruit and vegetable you can think of. However, what you may not be expecting is the setting. Rather than open air market stalls like you may see in the UK. This market is shoe horned into a rabbit warden of aging industrial water front buildings and spanning several levels. You can easily get disorientated and lost. Its therefore not the most charming place I’ve ever been, but focus on the fresh local produce.

photo-25-10-2016-16-24-17Tip 5 – If you like coffee visit the first ever Starbucks shop and get them to make you a special reserve Pike Place filtered coffee. Lovely!

The shop does get really busy to the point of needing a rope to control the queue down the street so I would suggest getting there early. The strength of the coffee will see you through the rest of the day.

img_8237Tip 6 – Get yourself a City Pass. This is a bulk buy set of vouchers to various attractions. This works out much cheaper than buying tickets separately.

The pass includes the Space Needle during the day, as well as a night time visit. Both provide stunning views of the city scape and surrounding areas. To get to the space needle you can spend 30mins walking or use the monorail on 5th Avenue. It goes back and forth between down town and the needle fairly frequently.photo-23-10-2016-18-19-35cropped

photo-24-10-2016-03-56-39

Tip 7 – The WiFi at the top of the Space Needle is incredible. Ignore the other halve and take your laptop. I measured a 325Mps download speed! #GeekFirst

Once you’ve seen the city from the Space Needle, you’ll also want to see it from a boat cruise around the harbour and pray for good whether.

Tip 8 – Unless you have a particular musical talent or inclination the EMP Museum may seem a little dull. But they do have Super Mario games setup on various consoles to play with.

We also went to the glass art exhibitions in the same area. Not being an arty person, even I found some of the exhibits impressive. Be warned though, even the small pieces retail for around $6k in the gift shop. Other attractions include the aquarium and the zoo.

photo-23-10-2016-16-33-03

Tip 9 – Again if you like coffee visit the Starbucks roastery on Pike Street heading north. It’s a really cool place to hang out and watch the beans being roasted while consuming a beverage.

If you have time make your way north of the city to the Boeing Future of Flight Tour around the factory. This is the worlds largest building in terms of volume. Which you’ll understand when you see full size passenger jets on a production line.

Tip 10 – Book the Boeing factory tour in advance. It does get busy and there are limited places.

For your evening meals you may want to visit the Cheese Cake Factory (yes, like of the Big Bang Theory). Or Daniels Boiler, a steak house just north of down town.

Tip 11 – Get the Uber app setup on your phone. It makes getting a taxi really easy and drivers are everywhere in Seattle.


Enough of the tourist things. Moving on to the PASS Summit.

Tip 12 – Depending on your schedule it’s worth popping into the Washington Convention Centre on Tuesday evening when registration opens to get your badge. This avoids the crowds and gives you chance to socialise with the most enthusiastic attendee’s. As it was my first PASS Summit my levels of enthusiasm were unmatched.

On the first day of the conference make sure you have plenty of sleep and get in nice and early for the breakfast sessions ran by sponsors. Listening to JRJ talk about SQLDW while consuming bacon is a double treat.

Tip 13 – Network, network, network.photo-25-10-2016-22-07-50

Don’t be afraid to talk to people. Everyone it’s friendly and your almost guaranteed to have some professional experience in common.

If speaking to random peeps isn’t for you then try out the SQL Clinic. Normally ran by the Microsoft Cat/Tiger team. Tell them about all your problems and frustrations related to the products you work with and they’ll thank you for the feedback. Seriously, this is the best chance you’ll have to get that bug in Visual Studio that’s been driving you crazy fixed.

Tip 14 – Keep an eye on emails, Twitter and Eventbrite for invitations for evening parties.

Tuesday night, we went to the Amber pub on 3rd Avenue for Karaoke. Hosted by Denny & Cherry Associates.

Wednesday night, we went to the Pike Place Brewery for a private party. Host by Sentry One

Thursday night, we went to 1927 5th Avenue for a party. Hosted by Red Gate.

Tip 15 – Don’t stay out too late and miss the next mornings of content. Especially the keynote talks. You can party anytime. These world class speakers aren’t always around to share their knowledge.

In the community zone make sure you check the schedule for when your country or region is being represented by local chapter leaders and be sure you go along for more networking.

Tip 16 – Take a spare bag with you to carry all the swag the exhibitors will give you. Seriously. T-Shirt, pens, notepadsimg_8238, torches, USB sticks, phone chargers. I even got a 2″ ceramic pig from Solar Winds to remind me about things that may be “hogging” the performance of my infrastructure!

By the end of the week if you’ve done everything right; Your brain will be mashed with knowledge. Your feet will be killing from walking round. You’ll have forgotten all the names of the people you meant. You’ll have about 30 more twitter followers. Finally, you’ll be exhausted and ready for home. But, you’ll still want to come back and do it all again next year.

Many thanks for reading.

Note; all the photos in this post are my own.


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.


SQL Battleships

When Battleships Met SQL Server, like Bailys meeting Coffee, but better.

Do you have an equal love of the classic two player board game Battleship and also SQL Server database development? If the answer is yes (which it obviously should be) then this is the blog post for you!

The next question that I’m more than happy to answer for you is; how can I combine these 2x loves?… Well my friend, please continue reading to find out.

SQL BattleShips

So, let’s create ourselves a SQL Server database to play a game of Battleships using tables as our grid and ship layout, then let’s use stored procedures as our weapons in which to fire on your opponent (executing statements against their tables). The other unique advantage of playing SQL BattleShips is that both players don’t need to be in the same room verbally querying each other’s grid references and board. In other words, why use English to query this data from a person when you can use SQL to query a database table.

Getting Setup

If you haven’t already done so, please download the zip file from the Downloads page in the database backups section labelled BattleShips.zip. It contains a SQL Server 2014 database backup file of the database called BattleShips. Please restore the database to a suitable SQL instance (and please check the assumption section below). I say a suitable SQL instance because apparently my previous employer didn’t think we needed it on every production server in the organisation!

A Few Assumptions

  • You or the person creating the new game, which doesn’t have to be one of the players has sysadmin access to the SQL Server instance where the Battleships database has been restored

Why? The new game procedure alters various database objects and setups up SQL logins for each player.

  • The SQL instance has database mail setup and is working.

Why? For ease of automation and sending each player details of what to do next. Including login details.

  • The SQL instance is setup for mixed mode authentication.

Why? Each player will have a SQL authenticated login created on the SQL instance in order to be able to connect to the BattleShips database and play with their respective objects. The database users then have a set of permissions defined which prevents each player for seeing each other’s tables (ship board).

  • You and your opponent are aware of how to edit/query database table data, plus views and execute stored procedures with required parameters in SQL Server Management Studio (SSMS).

Why? Because this is SQL BattleShips and it can’t be played using English queries.

Creating A New Game

To create a new game, execute the following procedure adding an email address for each player as required.

1
2
3
4
5
6
USE [BattleShips]
GO
 
EXEC [dbo].[NewGame]
	@Player1EmailAddress = N'bobby.tables@somewhere.com',
	@Player2EmailAddress = N'chuck.norris@somewhere.com'

Using database mail players will then receive details instructions on what to do and where to connect to using a set of SQL credentials provided. For information the body of this email is sorted in the [dbo].[EmailText].

To isolate players from each other as part of the game SQL authenticated user accounts are created for each player which only grants the necessary permissions to the respective player’s databases objects. This is handled using database schema’s and execute only permissions to certain procedures. Players will not have access to any other database on the SQL instance.

If you are a sysadmin you can of course very easily cheat, but this is not in the spirit of the game and it is expected that each player only uses their SQL account provided in the email sent out.

How To Play

Following the instructions provided by email, once you’ve connected to the BattleShips database in SSMS, edit your ‘board’ table. [playerX].[board]. Add all your available ships and close the table editor once complete.

BattleShipsBoardSetupWhen you are happy with the strategic planning (layout) execute the following stored procedure. This will validate your board layout and provide feedback if a ship is the incorrect length or has been missed. If everything is correct your status will be set to ready.

1
EXEC [playerX].[ImReadyToPlay]

Once both players have completed this operation can battle commence! Query the table players if you are unsure of the other player’s status.

1
SELECT * FROM [dbo].[Players]

They may also be ready to play or still setting up their board. If you’re playing in the same room feel free to speak to each other, it’s not against the rules, just not in the sprite of SQL BattleShips.

When both players are ready start taking shots at each other’s ship grids in turn, with player 1 going first. This can be done by executing the following procedure. You’ll of course want to update the gird reference parameter value once you’ve eliminated A01.

1
2
EXEC [player1].[TakeShot]
	@GridReference = 'A01'

Following execution feedback will be returned about the success of your shot and the states of both your board and a grid of which coordinates you have already tried. If you use the same grid reference twice you’ll be warned so your shot isn’t wasted.
BattleShipsShots
After a winner has been established by an opponent sinking all of their rival’s ships (or updating all of their ships with an X for hit). The players table will be BattleShipsPlayersupdated with the winner’s status and the loser will no longer be able to take any more shot.

Happy hunting my friends and please give me your feedback on any epic battles, and maybe the coding!

Many thanks for reading.


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