Building an Easy Button: SQL Server and Windows 10 IoT

A not-so-crazy idea

A few months back, I started experimenting with Arduino boards; they are small, inexpensive, open source electronics boards that you can wire up with all manner of electrical components and then write code (ANSI C) that you upload to an on-board microcontroller to do things like light up an LED, control a switch, power a servo motor, or even display an LCD message. I ended up wiring together a “demo” for  a presentation my boss and I did to show the value of automation (or, the perception of it).

While I was learning how to wire circuits and struggling to remember how to code, I was struck with an idea: is it possible to wire up some sort of circuit or device that could interface with SQL Server?

The short answer is: yes, you can. My project was ambitious as it was simple: create a button that, when pressed, backs up all of your databases on a given instance of SQL Server. And I’m going to show you how, one step at a time.

Introducing The “SQL Server Easy Button” project

Over the next few blog posts I’m going to:

1. Talk about the hardware required to get started, including a shopping list of parts if you want to follow along (this entry),
2. The required software and configuration you’ll need to complete on both your IoT device and your local PC,
3. Wiring your device,
4. Writing a middle-tier service to interface between SQL Server and your device, and
5. Writing the application that runs on your device and talks to your service.

If that seems like a lot, it’s actually not. Before we begin though, it helps if you have experience with Visual Studio and C# .NET (it’s okay if you don’t have any experience; I’ll be giving you all the code you need). You won’t need be an electrical engineer either, as I’ll be giving you detailed wiring diagrams too. And no, you won’t need a soldering iron either.

Sound good? Let’s dive in by first talking about the hardware we’re going to be using and why it’s both a blessing and a curse.

The hardware

Back when I started tinkering with these kits, there were two major “players” in the hardware space. You had Arduino which is mostly a pure electronics/logic board product and it’s produced under license by a lot of different vendors. The best thing about Ardunio are that the boards themselves are cheap: Usually between $6 and $30 (depending on if you want a stock board or one with features already wired into it). You can even get kits that come with a board, a USB cable to power and program it, and lots of bits to play with for well under $40.

As fun as Ardunio is to work with, you can’t really consider it an IoT device. Yes, you can hook them up with WiFi or Bluetooth sensors but they lack the modern processing capability of a mobile processor. You can’t access high-level APIs with it easily, either. To really start to network electrical components to allow data exchanging, you need something more substantial. That’s where a devices like a Raspberry Pi comes in.

A Pi board takes the basic concepts of an Arduino board and ratchets it up. Instead of just a microcontroller a Pi is a fully realized computer: a CPU, RAM, built-in WiFI and Bluetooth, USB ports, HDMI output, an an audio controller. All you have to provide is some sort of storage (usually a micro SD card) and a power source (micro USB), load an operating system, and you’re off to the races. Usually for under $50, total. Since it’s a fully realized computer, you can download a Linux distribution designed for a Pi, and SSH you way to nerd heaven.

Sounds great, right? I bet some of you already already thinking “Oh man, I can’t wait to run the Linux version SQL Server on this thing!” There’s just one really big catch: the CPUs on Pi boards are ARM-architecture based. Unlike modern processors in our desktops and laptops, these chips are more akin to what you find in mobile phones or other small devices. It also means programs you run or write on your computer are probably 32 or 64 bit and designed for Intel or AMD processors. ARM is a completely different architecture, so we can’t upload something to it and expect it to run. Programs have to be designed for it.

Furthermore a lot of “stock” Pi operating system images are Linux based so it can be difficult to write code that interfaces with .NET or Windows-based services. Not that you can’t; you can certainly write bash scripts that make wget or curl requests.

Windows 10 IoT Edition

To bridge the gap, Microsoft did something pretty incredible: along with the launch of Windows 10, they released Windows 10 IoT edition. In short, it’s Windows 10 designed to run on ARM-based CPUs. It does this by supporting the Universal Windows Platform (UWP). A UWP app is guaranteed to run on any flavor of Windows 10: your computer, an IoT device, a mobile device… the same code can compile and run on all of them. It’s a series of core APIs common to all devices, and the devices themselves can add or expose additional API support as well.

Sound good? Well it is, but there’s some big catches. UWP applications will run on a lot of devices, but they do so by sacrificing a lot of common libraries and functionality that we depend on as we use our desktops everyday, whether we realize it or not. Unfortunately, one piece of functionality that as a DBA I rely on every single day, the System.Data library, isn’t supported. It’s a dependency that all the SMO objects that Management Studio, the SQL Server PowerShell provider, even SQL Server SDKs all rely on.

Oh Crap.

Architecting a Solution

There’s still a way we can do this, we just need a bit of re-architecting. It turns out that UWP applications not having access to System.Data libraries is by design. It makes sense if you think about it: you wouldn’t want someone walking off with your device and having access to your databases or database servers. Instead, Microsoft wants these devices to talk to web services, either on-premises or in Azure. My solution ends up looking something like this:

How does it work? Well, first we’re going to wire up our device and upload a UWP application that detects things like state changes in buttons and changes active LED lights based on what the application is doing. Our UWP application does have the ability to call web services, so we’ll have it make a call to a service (that we’re going to write) that lives on a web server. In my example, we’re going to configure our local machine to run IIS. Since this machine will be a full-fledged Windows installation, our service can rely on libraries and dependencies that the UWP app cannot, so that’s where we’ll write our code that will interface with SQL Server.

Make sense? With all of that out of the way, it’s time to get to work. To get started, I’ve prepared a little shopping list of what you’ll need to have on hand to build this solution.

Requirements

Before you bust out your wallet there are some prerequisites you’ll need to be aware of:

• You need to be running Windows 10 version 10.0.10240 or better.
• You need to have some sort of version of Visual Studio 2015. If you have Enterprise or Professional, you may need some additional packages. If not, you can just use Visual Studio 2015 community. More details can be found here: https://developer.microsoft.com/en-us/windows/iot/Docs/GetStarted/rpi3/sdcard/stable/getstartedstep3
• A local instance of SQL Server. You can even use Express Edition if you want, just make sure you install the client SDK’s when you do your install!
• Make sure you install some version of management studio as well.
• IIS should be installed on your local machine. You can add it via “Programs and Features” from your settings menu, then clicking “Turn Windows features on or off.” For reference, you’ll need to select the following items:
  

Shopping list

Now that your home PC is ready to go, you can go shopping! To make this as easy as possible, I’ve prepared a list for you. The prices I am providing are based on my local MicroCenter, you very well may be able to get them cheaper online.

• Raspberry Pi 3 Model B: $29.99
• Breadboard: $7.99
• 5mm Assorted LEDs: $7.99 (note: you’ll need 3 colors; I went with blue, yellow, and red ones that were clear).
• Assortment of tactile push buttons: $12.00 (note: I’m linking to Amazon here, because I can’t find the ones I bought at MicroCenter. If you don’t want a lot, just buy one and save some bucks).
• Assortment of 1/4 Watt Resistors: $7.99 (note: what you’re really after here are 470-Ohm resistors, but you can get a ton for cheap if you buy in bulk).
• 5V Power Supply: $10.99
• Female to female jumper wires: $7.99
• Single Row Headers: $5.00
• 16GB MicroSD Card: $8.00

Total cost: ~$98. Your cost (and tax/shipping) may vary, but it should be close. If you can make it even cheaper, let me know.

Those are the essentials, but there a couple nice-to-haves you might want to consider:

• A case for your Pi of some kind. Find something you like, but you’ll want one with an open top or at least a removable top so you can access the pins for the connections.
• An extra network cable. Your Pi does have wireless, but you can save a lot of headaches if you hardware it in to your network. Trust me.
• The bigger the breadboard the better. The one I linked above should be plenty big, but don’t get too small of one or you might run out of lanes.

Ready… set…

That should be all you need! Once you have those items, we can move to part two of this series of blog posts where we install Windows 10 IoT and wire up our circuit! Follow me on twitter or subscribe for alerts on here for when the next part is available.