Showing posts with label apps. Show all posts
Showing posts with label apps. Show all posts

Reading Notes #261

gummibarchen-359950_960_720Suggestion of the week


Cloud


Programming


Miscellaneous




How to use Azure Function App to crush an SQL Database on a schedule

In a project, I needed to run a task every day to process some data in an Azure SQL Database. I thought Azure Function App would be the perfect candidate for that because we can attach them on a schedule, and I will only get charges when they are running. In this post, I will create a function that will be executed every five minutes. It will read the information from an SQL table Person and write the stats in another SQL table Statistic.

Azure Setup


Let's start by creating the Azure Function App. From the Azure portal (portal.azure.com) click the "+" sign on the top left corner and in the Search textbox type Function App. Fill-up the creation form like usual. Note that it will be a good idea to put your Function Appin the same location to what they will be interacting with; in this case an SQL Database. Once the Function App is created, it will be possible to create a new function. For that you can start with an empty one or use one of the multiple templates available. For this post, the TimeTrigger-CSharp was perfectly indicated.

createAzureFunctionApp

On this page that you can configure the schedule. Here, I set it to 0 */5 * * * * because the task will be running every five minutes. It uses Cron expression, and to learn about it you can (should) refer to the documentation. You can edit that value later by going to the Integrate tab the function. Great, now that we have our function, we need to provide the SQL Database connection string. In this demo, I will use App Settings, but it could also be saved in the Azure Key Vault. Remember Function Apps are part of the Azure Apps ecosystem, thereby they have App Settings. To access it, it's really simple.

GoToAppSettings

In the left panel menu click on the Function app setting options, then in the many choices look for Configure app settings. That will open the usual app setting blade that we use with other Web Apps. Scroll down until you've reached the Connection String section. Add your connection string to your server... And don't forget to save!

saveConnStr

Database Setup


For this demo, I will use two simple tables.
CREATE TABLE [dbo].[Person] (
    PersonID     INT NOT NULL IDENTITY(1,1) PRIMARY key,
    Firstname    VARCHAR(50)  NOT NULL, 
    Lastname     VARCHAR(50)  NOT NULL,
    Age          INT  NOT NULL
)
I will insert manually records, adding people with a random age. The Function App on its side, will read the information from the table Person and calculate the average age and the number of people in the population. For finally inserted it into the table Statistic.
CREATE TABLE [dbo].[Statistic] (
    StatisticID    INT NOT NULL IDENTITY(1,1) PRIMARY key,
    Population     INT NOT NULL, 
    AverageAge     INT  NOT NULL,
    DateTaken      VARCHAR(50)  NOT NULL
)

Coding the Function App


The code of this function is not styled or optimized. It was kept very simple so it was obvious was it was doing. Here is the code.
#r "System.Data"
using System;
using System.Configuration;
using System.Data.SqlClient;
public static async Task Run(TimerInfo myTimer, TraceWriter log)
{
    log.Info($"C# Timer trigger function executed at: {DateTime.Now}");  
    var str = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(str))
    {
        var sum = 0;
        var cnt = 0;
        var avr = 0;
        conn.Open();
        var sqlStr = "SELECT Age FROM [dbo].[Person]";
        using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
        {
            var dataReader = await cmd.ExecuteReaderAsync();
            while(dataReader.Read()){
                sum += dataReader.GetInt32(0);
                cnt++;
            }
            avr = (sum / cnt);
            log.Info($"The average actual population is {avr} .");
        }
        var sqlInsert = $"INSERT INTO [dbo].[Statistic](Population, AverageAge, DateTaken) VALUES ( {cnt} , {avr} , '{DateTime.Now}' )";
        using(SqlCommand cmd  = new SqlCommand(sqlInsert, conn)){
            var rows = cmd.ExecuteNonQuery();
            log.Info($"{rows} rows were inserted");
        }
    }  
}
The first line is to add reference to System.Data. Many libraries are already available to any Function App, and just require a reference. When you need an external library, you will need to create a file project.json and add it to the dependencies. Those will be loaded via Nuget. From there, the code talk by itself. Beginning by getting the connection string from the config, getting all the people and doing his magic, then finally inserting his result into the Statistic table.

Let's Run It


Everything is now in place. It's t to use your favorite SQL tool to insert some record into the Person table. Personally, I've created a little console application in .Net Core to do that. I share all about it at the end of this post. Once it's done, you will see the table Statistic growing every five minutes. Voila! Azure Function App a very useful and effective in this scenario. The code, of course, can be part of your repository, and now that Azure Function Tools have just been released you can even code them from Visual Studio!


Little Bonus


For a will now, every time I need to generate data, I tend to use a nice framework that my MVPs buddy the ASP.Net Monsters has done called: GenFu. Available on github, GenFu is a library that generates realistic test data. Here the code I used to generate the population in this demo.
using System;
using System.Data.SqlClient;
using GenFu;
namespace ConsoleApplication
{
    public class Program
    {
        public static void Main(string[] args)
        {
            int counter = (args.Length > 0) ? Convert.ToInt32(args[0]) : 5;
            var Users = A.ListOf<Person>(counter);
            var connStr = "Server=YOURSERVERNAME.database.windows.net,1433;Initial Catalog=YOURDATABASENAME;Persist Security Info=False;User ID=YOURUSER;Password=YOURPASSWORD;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                foreach (var u in Users)
                {
                    var sqlStr = $"INSERT INTO [dbo].[Person] (Firstname,Lastname,Age) VALUES ( '{u.Firstname}' , '{u.Lastname}' , {u.Age} )";
                    using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                    {
                        var rows = cmd.ExecuteNonQuery();
                        Console.WriteLine($"{rows} rows were inserted");
                    }
                }
                conn.Close();
            }
        }
    }
    public class Person
    {
        public string Firstname { get; set; }
        public string Lastname { get; set; }
        public int Age { get; set; }
    }
}
You will need to add Genfu reference to the project.json file.
{
"version": "1.0.0-*",
"buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true
},
"dependencies": {
        "Genfu": "1.2.1",
        "System.Data.SqlClient": "4.3.0"
},
"frameworks": {
    "netcoreapp1.0": {
    "dependencies": {
        "Microsoft.NETCore.App": {
        "type": "platform", 
        "version": "1.0.1"
        }
    },
    "imports": "dnxcore50"
    }
},
"runtimes": {
"win-x64":{}
}
}





Happy coding!




Reading Notes #260

shopping-cart-1275482_640Suggestion of the week


Cloud


Programming


Miscellaneous


Lessons learn while trying to deploy a Docker container in Azure

(Ce billet en aussi disponible en fran├žais.)


Since I saw Donovan Brown do his demo during the Connect(); // 2016 Keynote, I really want to have a taste of that left click to turn a project to Docker containers. So last Sunday I wake up early and armed with a big bowl of cafe latte start prepping my VM to try it. But as you could imagine, it didn't work on the first attempt, and that what I want to share to you.

DonovanBrown

Let's get started


I build a new VirtualBox virtual machine (VM) using an ISO of Windows 10, then install all the updates to get the anniversary edition. I afterward went to visualstudio.com to get the Visual Studio 2017 RC and install it with a few components: web, azure, etc. And next when looking for Docker for windows. Same here super easy just need to download and install the MSO from docker.com and voila... or not. Like specified in the Docker documentation the installer noticed that Hyper-V was not present in my environment and suggested installing it and reboot the machine. Until then, everything was going fine, but when Docker try to start, after the reboot, I got an error message:

Error creating machine: Error in driver during machine creation: This computer doesn't have VT-X/AMD-v enabled. Enabling it in the BIOS is mandatory

However, the setting was set, as you can see in the screenshot.

VT-xSetting

After a short investigation, I found that VirtualBox was not supporting nested virtualization, yet. Well, let's try another virtualization platform then.

First Success


I created a new VM under VMware Player this time and repeated all the steps as before. After the reboot, the little white whale in the system didn't prompt any error... Was it working? Let's try to create a Nginx Hello-world container to check. And the answer was YES!

2016-11-22_19-00-45

Docker and Azure


Now let's get serious. It's time to do the left click on the project and add Docker Project Support. Then press F5 to try it locally.

ERROR: for mystuff Cannot create container for service mystuff: C: drive is not shared. Please share it in Docker for Windows Settings Encountered errors while bringing up the project..

I got too excited, and I had skipped some reading... It's written black on white in the documentation that you need to share some drive to make it work.

ShareDrice

After that it when smoothly and in few minutes, I got my website to run in a Docker container accessible via localhost:32768. Great! Now to get it available on Azure, I need to create a registry where all the images will be saved. To do that simply left click again on the project, and select Publish. The guided dialog will popup and help you to create and deploy your things. An interesting point is before clicking the "Create" button you will have the option to export your template as a json file, very useful. But for now click Create, then Publish.

DockerPublishing

After a moment you should have a new browser window that will popup with your App now deployed in Azure.

DockerOnline

Wow! That was really a great experience. It's definitely a very simple process to get started. I really appreciate the Docker for Windows and Kitematic user interface. Of course, all the command-line are still available, but now I also have another option for the day where I feel more for clicking instead of typing.

DockerTools

Let's build it and ship it, from any platform to the cloud!



Reading Notes 193

chaos-monkey-3_480Suggestion of the week


Cloud


Programming


Miscellaneous


Image from  Inside Azure Search: Chaos Engineering

Reading Notes #191

Image by FutUndBeidl / FlickrSuggestion of the week


Cloud


Programming

Miscellaneous




Image by FutUndBeidl / Flickr


Reading Notes #180

Quebec, Canada weatherSuggestion of the week


Cloud


Programming


Miscellaneous


~Frank B.


Reading Notes #179

Apps_2015-03-29_2048Suggestion of the week


Cloud


Programming


~Frank B.