SQL Server and Windows 10 IoT Part 3: From Button Press to Service to Backup
Back in part 2 of this series, we did a lot of things. Since there was so much going on I didn’t really get a chance to go over just what the code we uploaded to our IoT device actually did. Before we can finish this series and get into modifying our code to call a web service, we should at least talk a little bit about the objects, methods, and properties of the IoT libraries we’ll be working with. Let’s revisit the code we used in the previous post.
Initializing the GPIO
The first part of our application has two crucial parts: a series of constants we’ll use to identity pin numbers and some code to set the initial state of these pins on the board. What this code does is assign integer numbers to some variable names.
private const int BlueLED_PIN = 6; private const int YellowLED_PIN = 22; private const int RedLED_PIN = 27; private const int Button_PIN = 5; private GpioPin BlueLEDPin; private GpioPin YellowLEDPin; private GpioPin RedLEDPin; private GpioPin buttonPin;
Next, we need to initialize the pins. Our InitGPIO function takes care of this. Note the methods .OpenPin() and .SetDriveMode() . OpenPin() actually turns on the pins we set constants for (the integers being the pin numbers) and SetDriveMode() sets the pins to either write mode or read mode. For our LEDs, we want to “write” to them. They can have two values: Low or High. Our initial configuration lights up the blue LED while leaving the yellow and red ones off. For our button, we set the mode to be Input (or InputPullUp, if the board supports it). More about that in a second. Then we set event for the button press. Thanks to the magic of the Windows 10 IoT libraries, this is all very readable and very to set up.
private void InitGPIO()
{
var gpio = GpioController.GetDefault();
buttonPin = gpio.OpenPin(Button_PIN);
BlueLEDPin = gpio.OpenPin(BlueLED_PIN);
YellowLEDPin = gpio.OpenPin(YellowLED_PIN);
RedLEDPin = gpio.OpenPin(RedLED_PIN);
BlueLEDPin.Write(GpioPinValue.Low);
BlueLEDPin.SetDriveMode(GpioPinDriveMode.Output);
YellowLEDPin.Write(GpioPinValue.High);
YellowLEDPin.SetDriveMode(GpioPinDriveMode.Output);
RedLEDPin.Write(GpioPinValue.High);
RedLEDPin.SetDriveMode(GpioPinDriveMode.Output);
if (buttonPin.IsDriveModeSupported(GpioPinDriveMode.InputPullUp))
buttonPin.SetDriveMode(GpioPinDriveMode.InputPullUp);
else
buttonPin.SetDriveMode(GpioPinDriveMode.Input);
buttonPin.DebounceTimeout = TimeSpan.FromMilliseconds(50);
buttonPin.ValueChanged += buttonPin_ValueChanged;
}
Detecting Button Presses
The trickiest part of wiring a circuit like this is detecting a button press. Most logic boards don’t know if an input circuit should poll at high or low levels. That’s where pull-ups come in. Above, you can see we set one of the pins for the button to be a pull-up (or an input if we were using another board). That means it will pull the current and look for impedance. The other important thing is our debounce. With circuits, one button press can actually turn into lots because as soon as the switch completes (or interrupts) the circuit, it starts sending signals. A debounce is like a referee saying “only look for a signal for this long” and it will filter out extra “presses” based on current that might linger on a press.
Once we detect our button press, we’re calling the function below. All it does is read the current LED pin values, and looks to see which one is currently lit, and then lights the next one.
private void buttonPin_ValueChanged(GpioPin sender, GpioPinValueChangedEventArgs e)
{
if (e.Edge == GpioPinEdge.FallingEdge)
{
if (BlueLEDPin.Read() == GpioPinValue.Low)
{
BlueLEDPin.Write(GpioPinValue.High);
YellowLEDPin.Write(GpioPinValue.Low);
RedLEDPin.Write(GpioPinValue.High);
} else if (YellowLEDPin.Read() == GpioPinValue.Low)
{
BlueLEDPin.Write(GpioPinValue.High);
YellowLEDPin.Write(GpioPinValue.High);
RedLEDPin.Write(GpioPinValue.Low);
} else if (RedLEDPin.Read() == GpioPinValue.Low)
{
BlueLEDPin.Write(GpioPinValue.Low);
YellowLEDPin.Write(GpioPinValue.High);
RedLEDPin.Write(GpioPinValue.High);
}
}
}
One thing you might be wondering though, is why we’re testing for a “low” value. Wouldn’t a lit LED be a high value? Remember when I said we were wiring our circuit in an active low configuration? That means if a pin is set to “Low” it’s actually the circuit that’s open.
Got all that? Cool, because we’re going to be modifying this same solution later on.
The web service
Since our device can’t run SQL Server itself, and since it can’t talk to SQL Server directly, we need a way to bridge the gap. Back in part one, we made sure we installed SQL Server and set up IIS. With those in place, now we can write a web service to bridge the gap. Before we begin, make sure you installed all the required software from part 1 and configured visual studio correctly in part 2. If you forgot a step, you may get errors as we move through this.
Developer trigger warning: The type of service we’re going to write here (ASMX) will probably make any developers you’re working with roll their eyes or just outright laugh at you. This isn’t how you’d normally write a web service. If you plan on ever doing production-level problem solving with an IoT device like this that leverages a service, talk to a developer and they’ll help you write a much more modern version (like WebAPI or anything else on .NET 4.5 and later) but this will work fine for now, and requires the absolute least amount of mucking about in Windows, IIS, and Visual Studio to make it work. Otherwise, this would easily be a 60,000 word blog post.
To get started, shut down Visual Studio (if it’s running) and then relaunch it as an administrator. Also, make sure IIS is running on your machine. Then, in the File menu, select new Web Site.
In the above dialog box, change the .NET Framework drop-down to .NET 3.5. Then, in the templates menu select “Visual C#” and then click “ASP.NET Web Service.” At the bottom of your dialog, click the “Browse..” button.
You should then see a window similar to what is shown above. Click the little application with a star icon in the upper left to create a new web application on your local IIS (not IIS Express Sites) and name it SQLServices. Then click Open. You’ll be taken back to your New Web Site dialog and then you’ll click okay. If all goes well, you should see a new solution that looks like this:
With that in place, we can start to write our service. First things first, we’ll need to add some SQL Server references. Double-click on the Web.config file to open it. Find the section in there that is labeled “assemblies” and you’ll want it to look like this (replace any other references that are there, we won’t need them).
<assemblies>
<add assembly="Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>
<add assembly="Microsoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>
<add assembly="Microsoft.SqlServer.ConnectionInfo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>
<add assembly="Microsoft.SqlServer.Management.Sdk.Sfc, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>
</assemblies>
NOTE: If you’re not using SQL Server 2016, your version numbers of your references won’t be 13.0.0.0. In that case, you can still add the references by right-clicking “References” in your solution explorer, then clicking “Extensions” on the left hand side. Scroll down and you should see all the references. Just check the box next to each one and click okay; it should add the required lines to your Web.config.
Save the file, and then open up the service.cs file in the solution (which is located in the App_Code folder) by double-clicking it. Copy and paste the following code (again, replacing whatever is there)
using System.Collections.Generic;
using System.Web.Services;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{
private const string filepath = "C:\\temp";
private const string sqlservername = "localhost";
public Service()
{
}
[WebMethod]
public string HelloWorld()
{
return "Hello World";
}
[WebMethod]
public List<string> ListDatabases()
{
Server smo = new Microsoft.SqlServer.Management.Smo.Server(sqlservername);
List<string> DatabaseList = new List<string>();
List<string> excludedDatabases = new List<string>() { "tempdb" };
foreach (Database database in (smo.Databases))
{
if (!excludedDatabases.Contains(database.Name.ToString()))
{
DatabaseList.Add(database.Name.ToString());
}
}
return DatabaseList;
}
[WebMethod]
public List<string> BackupDatabases()
{
Server smo = new Microsoft.SqlServer.Management.Smo.Server(sqlservername);
List<string> DatabaseList = new List<string>();
List<string> excludedDatabases = new List<string>() { "tempdb" };
foreach (Database database in (smo.Databases))
{
if (!excludedDatabases.Contains(database.Name.ToString()))
{
Backup BackupObject = new Microsoft.SqlServer.Management.Smo.Backup();
BackupObject.Action = BackupActionType.Database;
BackupObject.BackupSetDescription = "Full database backup of " + database.Name.ToString();
BackupObject.BackupSetName = database.Name.ToString() + " backup";
BackupObject.Database = database.Name.ToString();
BackupObject.Devices.AddDevice(filepath + "\\" + database.Name.ToString() + "_FullBackup.bak", DeviceType.File);
BackupObject.SqlBackup(smo);
DatabaseList.Add(database.Name.ToString());
}
}
return DatabaseList;
}
}
What does this do? We’ve written three methods. The first is a basic “Hello World” example to just write out a string. The next one returns a list of databases on our instance in a list object, and the final one is using SMO automation to connect to SQL Server and backup every database but tempdb. If you’ve seen code like this before, it’s how I do my backups in SQL Server with PowerShell today; we’re using the same objects and almost the same code, line for line!
Save all your files, it’s time for the moment of truth. At the top of your Visual Studio window, there should be a green arrow next to the “Any CPU” drop down. It probably has the name of your default browser. Click it to start our debugging process. If all goes well, your solution should build and you should get a web browser to open that looks like this:
Those links at the top are the methods we incorporated into our service. As a quick test, let’s click on HelloWorld. You’ll be taken to a new page that has an “invoke” button. Click it. You should get an XML response that has a string that says “Hello World.” Congrats, you just wrote a web service!
Now, let’s test our SQL Server integration. Close that window/tab that the invoke button opened and go back to your main service page. Now, click on the “ListDatabases” link. You’ll get another service page, and then click “Invoke” again. This time though, you’re not going to get any XML. No, you’re going to get an error:
Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server localhost. ---> System.Data.SqlClient.SqlException: Login failed for user 'IIS APPPOOL\.NET v2.0'.
What’s this error about? Well, we’re trying to connect to SQL, but our application can’t do it. So that’s a perfect segue into talking about your web service, your IoT device, and security.
Securing your device and your web service
In case you missed it, it turns out every internet enabled device is open to being compromised. The same goes for remote services that these devices work with. If you’re going to explore this technology in earnest, my advice to you is put security at the forefront of everything you do. Microsoft has a great article on how to build secure IoT devices that you can read here. There are a lot of considerations that go into an IoT infrastructure and you can get in over your head real fast. These are real world problems, and I get out of my depth real fast the minute anyone starts talking about things like TPM. Seek expert advice; not just for your peace of mind, but any potential lawsuits.
But we need to do more than just secure the device. For our sample service, we only want registered devices phoning the service. We want to deny any other request. One easy way to do that is to set up IP filtering. When you installed IIS earlier, there was another piece you might want to have installed (if it wasn’t already selected). To check it out, open Programs and Features again, and select “Turns Windows Features On or Off.” In the pop up, navigate to and select (if they aren’t already) the options I show below:
Click OK and let the process complete. Next, close IIS manager if it’s open, then re-open it. Navigate to your “Default Web Site” and you should have a new option now: IP Address and Domain Restrictions. Double-click that icon to bring up the IP filtering options. I would do 3 things here:
- Add an “Allow” rule for 127.0.0.1 (so you can test the service from your machine as localhost)
- Add an “Allow” rule for your machine’s IP address (you’ll need this to configure the service in your code later)
- Add an “Allow” rule for the IP address if your IoT device. You can get this from the IoT dashboard or by logging into your device like we did in part 2.
- Edit the feature setting to deny everything else: click “Edit feature settings” on the right-hand side, and in the first drop-down box select “Deny.”
Is this a fool-proof way to secure your service? Of course not. In the real world you’d most likely work with network engineers to filter traffic on a firewall or restrict things based on domain names, and you’d probably NOT being using http for everything; you’d be using SSL and https most likely. This is just meant to get you thinking about security (and if you show this off to anyone, you can be sure they’ll try to get to your service just to be snarky; nip that in the bud right now).
Secure devices and services are one thing, but what about the error message that sent us here in the first place: a failed login. You don’t need to be a developer to see what’s going on there: some process is trying to log into our local instance of SQL Server with an account we never set up. In order to set up this access, we need to do two things. First, we need to create a local user account that our web service can run under. On your local machine, open up computer management (hit the “windows” and “x” keys at the same time and select “computer management”) and then click “local users and groups” and click “users.” Go ahead and create a local user (I used “WEB_SVC”) and give it a password (it helps to write it down).
Once that’s done, close computer management and head back to IIS. On the left-hand side, click on “Application Pools.” We’re going to now assign that local user as the user ID of the application pool our service uses. If you’ve been following this blog step-by-step, your service should be running on the .NET 2.0 app pool by default (you can verify the app pool it’s using by expanding the default web site, right-clicking on SQLServices, then selecting manage application, and advanced settings… in the dialog box, look for “Application Pool”). Right click on the .NET 2.0 entry, and click “Advanced Settings.” In the “Process Model” section, look for “Identity.” It probably says ApplicationPoolIdentity. Highlight it, and you should see some ellipses you can click on. Click that, then click on “Custom account” and click set.
In the pop-up, enter your local account you just created (you need to type it in like you would a domain account, but instead of your domain, type your computer name, i.e. <computer name>\<account name>). Then, type and re-type the password. Mine looks like this:
Now that we know what account our service is going to use, all we have to do is add it to our instance of SQL Server. Since our device is designed to connect and backup databases, there’s an easy database role for that. Connect to your local instance of SQL Server in management studio, and add the local user. Again, you’ll need to type it in as <computer name>\<account name>. Then in user mapping, select every database (except tempdb) and add this user as a member of db_backupoperator role.
Let’s retest the service now with the proper permissions in place. To make sure our changes stick, in IIS right click-click the Default Web Site, then click “Manage Website…” and then click “Restart.” Then right click the .NET 2.0 app pool and click “Recycle.” Once that is done, let’s try debugging our web app again. Go back to Visual Studio and click the green arrow again to bring up a web page with our service. Now click the “ListDatabases” link and click “invoke.”
Et voilà! Our service works! You can test the other method too, which should back up our databases to the C:\temp folder (if it exists… see the next section), but delete the backups when you’re done. With this all in place, we just have one final step: To make sure our device can call it.
Calling the service on a button press
Before we go any further, take a second and create a “temp” folder on your C: drive (if you don’t already have one; our code might break if it’s not there). Go ahead and close your service solution in Visual Studio, and re-open your IoTTestApplication solution. In order to have our code call this service, we need to add it as a service reference. To do so, in your solution explorer right click on the “Service References” folder and click “Add service reference.” You’ll get a pop-up asking for the service address. If you’ve been using the same names of things as I have, you’ll put http://<your machine IP>/SQLServices/Service.asmx in that box. Don’t use localhost as your address; this code will live on your device, so it needs the IP of the IIS instance hosting your service.
Then click “Go.” Visual Studio will look at the service that’s running and return some info about it. In the namespace box at the bottom of the pop-up, name it “SQLServiceReference.” Then click “OK.” You should now see that reference listed under your service references.
And now, we’re going to replace our code that we originally wrote. Inf your MainPage.xaml.cs page, replace all your code with the listing below:
using System;
using Windows.Devices.Gpio;
using Windows.UI.Xaml.Controls;
namespace IoTTestApplication
{
public sealed partial class MainPage : Page
{
public MainPage()
{
InitializeComponent();
InitGPIO();
}
private void InitGPIO()
{
var gpio = GpioController.GetDefault();
buttonPin = gpio.OpenPin(Button_PIN);
BlueLEDPin = gpio.OpenPin(BlueLED_PIN);
YellowLEDPin = gpio.OpenPin(YellowLED_PIN);
RedLEDPin = gpio.OpenPin(RedLED_PIN);
BlueLEDPin.Write(GpioPinValue.Low);
BlueLEDPin.SetDriveMode(GpioPinDriveMode.Output);
YellowLEDPin.Write(GpioPinValue.High);
YellowLEDPin.SetDriveMode(GpioPinDriveMode.Output);
RedLEDPin.Write(GpioPinValue.High);
RedLEDPin.SetDriveMode(GpioPinDriveMode.Output);
if (buttonPin.IsDriveModeSupported(GpioPinDriveMode.InputPullUp))
buttonPin.SetDriveMode(GpioPinDriveMode.InputPullUp);
else
buttonPin.SetDriveMode(GpioPinDriveMode.Input);
buttonPin.DebounceTimeout = TimeSpan.FromMilliseconds(50);
buttonPin.ValueChanged += buttonPin_ValueChanged;
}
private void buttonPin_ValueChanged(GpioPin sender, GpioPinValueChangedEventArgs e)
{
if (e.Edge == GpioPinEdge.FallingEdge)
{
if (BlueLEDPin.Read() == GpioPinValue.Low)
{
BlueLEDPin.Write(GpioPinValue.High);
RedLEDPin.Write(GpioPinValue.Low);
YellowLEDPin.Write(GpioPinValue.High);
try
{
var client = new SQLServiceReference.ServiceSoapClient();
client.BackupDatabasesAsync().Wait();
}
catch
{
BlueLEDPin.Write(GpioPinValue.Low);
YellowLEDPin.Write(GpioPinValue.Low);
RedLEDPin.Write(GpioPinValue.High);
}
BlueLEDPin.Write(GpioPinValue.Low);
RedLEDPin.Write(GpioPinValue.High);
}
}
}
private const int BlueLED_PIN = 6;
private const int YellowLED_PIN = 27;
private const int RedLED_PIN = 22;
private const int Button_PIN = 5;
private GpioPin BlueLEDPin;
private GpioPin YellowLEDPin;
private GpioPin RedLEDPin;
private GpioPin buttonPin;
}
}
The biggest change you’ll notice here are on lines 52 and 53. When a service reference is added to your project, Visual Studio takes care of wiring up everything it needs work, leaving us with two really simple lines of code. One of them initializes the object calls the backup databases service. We’re also making this call synchronous by using the .Wait() method (so our LED’s will stay lit before moving on).
Save all your files, and then click the “Remote Machine” button to publish your code to your device. While it’s published, browse to your C: drive; if you already have a directory named “temp” go ahead and open it up. If not, one should be created when we run our code. After you app loads, your blue LED should be lit. Go ahead and press your button. If everything works correctly, your yellow LED will light up and stay lit while your backups run. You should also start seeing .bak files for every database on your local instance appear in the C:\temp folder on your machine. When the process finishes, the yellow LED light will turn off, and the blue LED will be lit, indicating the process completed successfully!
If something doesn’t work, you’ll have the red and blue LED lights switch on. This means you can press it again, but your last attempt was unsuccessful. There could be a lot of reasons this happens. It might be a code issue, or a service issue, or a SQL Server issue. Try isolating your error; access the service directly in a browser and try invoking the services manually. Did those work? If they did, then it must be your code. Verify your service reference was set correctly. Still stuck? Reach out to me and we’ll walk though the code together; maybe I have a typo (but I did step through each one of these processes to make sure I didn’t leave anything out; that doesn’t mean I still didn’t make a mistake).
Once you get everything running, as long as you have IIS and SQL Server running, you won’t need to use Visual Studio to run your example code. When you debugged it last time, the code is already uploaded to your device. If you log into your device’s web portal, and then click on “Apps” you should see your IoTTestApplication app in the list.
If it’s running, you can click the square to stop it, or click the play button to run it again. Isn’t that neat?
Lessons learned
Whew! What a process. Hopefully you’ve been able to follow along with your own device and recreate what I’ve done here. My goal wasn’t just to show you how to backup databases but I’m more hoping that you’ll get some gears turning about other fun projects you can work on. I also hope that if you’re a DBA, you have a better appreciation and understanding of processes developers go through to make things like web services talk to SQL Server (and the sometimes hoops they have to jump through with Visual Studio). But most of all, I just like sharing. This project wasn’t born out of someone asking me to do it; I just had a desire to learn more about IoT devices and because I’m a DBA, I wanted to see what I could make that might help me.
If you enjoyed this series and you want to learn more about IoT devices, then there’s plenty of resources linked throughout the blog series. Here’s some of the more helpful guides that I used as inspiration while working on this project:
If nothing else, just keep on learning, folks. Thanks for reading! If you are attempting to create this device and discover any errors I’ve made or have questions, I’m always available to help.
Pingback: Windows 10 IoT Code To Back Up Databases – Curated SQL