Warning, this is a longer post from me than usual.

The title is a mouthful and so is this post. In the past I have linked to blog posts from Microsoft that say consistency checks for Azure SQL Database is the responsibility of Microsoft. (https://azure.microsoft.com/en-gb/blog/data-integrity-in-azure-sql-database/)

However, Paul Randal got me thinking about his thoughts on it (via his insider email). Forming the core of this post. If you desire to run DBCC CHECKDB against Azure SQL Database (which I know people do) – how can you do this? There are many ways, but for this blog post – Enter Azure functions. There are many moving parts to this, but once setup and coded it is a very satisfying experience. Let’s dig in. I am NOT going to copy and paste every little element of the high-level guide from Microsoft, there is no point in that but I will show you the links that you need to setup the relevant function app project then the tailored bits around CHECKDB forms the bulk of this post.

Step 1 – You need Visual Studio 2019 (community is fine for this) Once downloaded during installation you will need to install Azure development workload. https://visualstudio.microsoft.com/vs/

Step 2- Follow this guide this is a pre-req. https://docs.microsoft.com/en-us/azure/azure-functions/functions-create-your-first-function-visual-studio

Step 3 – Now this is where I tailor the online guides to cater for CHECKDB command.

First thing you need to do is get the connection string details from Azure for the database that you want to run CHECKDB against.

Get the ADO.net string and save it (from highlighted section above).

Assuming you have done STEP 2 load up visual Studio and get your function app project and click publish. (right click on the highlighted section below)

In the publishing screen select Manage Azure App service settings. Here we need a connection to Azure SQL DB.

Click add settings and create a sqldb connection (using the ADO.NET string from before)

Go back to the Azure SQL Database and create a database firewall level rule for Azure function access.

EXECUTE sp_set_database_firewall_rule N’AzureFunc’, ‘104.40.191.175’, ‘104.40.191.175’;

This might be different for you but I found this IP address that was needed via Application Insights Log. In production you will definitely want to lock this down. To find the possible list you can take a different route:

1. Sign in to the Azure Resource Explorer.
2. Select subscriptions > {your subscription} > providers > Microsoft.Web > sites.
3. In the JSON panel, find the site with an id property that ends in the name of your function app.
4. See outboundIpAddresses and possibleOutboundIpAddresses. (Mine matched to the outbound IP)

Now go back to the visual studio project and Open your local function app project then:

Solution explorer > Manage NuGet Packages > Browse tab, search for System.Data.SqlClient and, when found, select it. In the System.Data.SqlClient page, select version 4.5.1 and then click Install.

Add a new function to the project and use the below and select a trigger-based timer.

I used a  timer-based trigger for testing purposes of 1000 seconds – you can see the CHECKDB command. Obviously you will need to change the timings for production services.

Find the function and add the code below.

using System.Data.SqlClient;
using System.Threading.Tasks;
using System;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Extensions.Logging;


namespace FunctionAppToSQL
{
    public static class FuncCall
    {
        [FunctionName("Databasecheckdb")]
        public static async Task Run([TimerTrigger("*/1000 * * * * *")] TimerInfo myTimer, ILogger log)
        {
            // Get the connection string 
            var str = Environment.GetEnvironmentVariable("sqldb");
            using (SqlConnection conn = new SqlConnection(str))
            {
                conn.Open();
                var text = "DBCC CHECKDB('blobdb')";

                using (SqlCommand cmd = new SqlCommand(text, conn))
                {
                    // Execute the command and log the # rows affected.
                    var rows = await cmd.ExecuteNonQueryAsync();
                    log.LogInformation($"{rows} rows were updated");
                }
            }
        }
    }
}

PUBLISH TO AZURE. (right click on the solution)

Login to the Azure Portal. Find the function app.

Click this and you will find the checkdb function we built.

Once enabled lets test and see what happens.

Click the above and find Code / Test.

Something is definitely happening on the SQL Server (this was when I enabled the app).

(NOTE: you should also enable application insights too so you can get some in-depth logs, very useful when debugging errors etc.)

I then connect to the database to grab a SPID and SQL handle.

There we have it. Trigger time based DBCC CHECKDB in Azure SQL DB. Ok, if you have got this far you are probably thinking that this is a lot of effort just for TSQL scheduling/automation in the cloud. Yes, it is but I wanted to share with you that it is a valid possible solution.