Integrating Azure SQL Database with Azure Functions

Introduction

Azure SQL Server is a fully managed relational database service provided by Microsoft Azure. It is based on the SQL Server database engine and offers the same capabilities with additional cloud-specific features. Azure SQL Server supports various deployment options, including Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure Virtual Machines, catering to different needs and workloads.

Key features of Azure SQL Server

Fully Managed Service: Azure SQL Server handles maintenance, patching, backups, and monitoring, reducing administrative overhead.
Scalability: Supports scaling up or down based on demand without downtime.
High Availability: Provides built-in high availability with automatic failover and geo-replication.
Security: Offers advanced security features like encryption, threat detection, and compliance with industry standards.
Intelligent Performance: Uses machine learning and AI to optimize performance automatically.
Integration: Seamlessly integrates with other Azure services and tools like Azure Data Factory, Power BI, and Azure DevOps.
Flexible Deployment Options: Supports multiple deployment options to suit different scenarios and migration paths.

Pros of Azure SQL Server

Managed Service: Reduces administrative tasks by handling maintenance and updates automatically.
Scalability: Easily scale resources up or down based on workload requirements.
High Availability and Disaster Recovery: Built-in high availability and disaster recovery options ensure data is always accessible.
Security and Compliance: Advanced security features and compliance with major standards ensure data protection.
Performance Optimization: Intelligent performance features enhance query performance and resource utilization.
Integration with Azure Ecosystem: Smooth integration with other Azure services enhances productivity and functionality.
Migration Tools: Comprehensive tools and services to assist with migrating existing databases to Azure SQL.

Cons of Azure SQL Server

Cost: Can be expensive, especially for high-performance and high-availability configurations.
Complexity in Pricing: Pricing models can be complex and may require careful planning to optimize costs.
Learning Curve: Initial learning curve for administrators and developers unfamiliar with Azure-specific features.
Limited Control: As a managed service, it offers less control over the underlying infrastructure compared to on-premises deployments.
Dependency on Internet Connectivity: Requires stable internet connectivity to access the service, which might be a limitation in some scenarios.

Deployment Options

Azure SQL Server offers several deployment options to suit different needs:

Azure SQL Database: A single database with its own set of resources managed via a logical server. Ideal for modern cloud applications with variable usage patterns.
Azure SQL Managed Instance: A managed instance offering near 100% compatibility with on-premises SQL Server, suitable for lift-and-shift migrations.
SQL Server on Azure Virtual Machines: Full control over the SQL Server instance running on a virtual machine, ideal for applications requiring OS-level access or third-party software.

Integration with Azure Functions

Integrating Azure SQL Server with Azure Functions involves several steps:

Setting up Azure SQL Server

Create an Azure SQL Server: Go to the Azure portal and create a new Azure SQL Server instance.
Create a Database: Create a new database on the Azure SQL Server.
Configure Firewall Rules: Ensure your development machine's IP address is allowed to access the Azure SQL Server.
Get Connection String: Retrieve the connection string from the Azure portal for use in your application.

Configuring ASP.NET Core MVC project

Install NuGet Packages: Install the necessary NuGet packages for SQL Server in your ASP.NET Core MVC project.

dotnet add package Microsoft.Azure.Functions.Worker.Extensions.Sql
// Using Dapper for Delete function
dotnet add package Dapper

Add Configuration: Add the SQL Server connection string to your local.settings.json.

{
  "IsEncrypted": false,
  "Values": {
    "FUNCTIONS_WORKER_RUNTIME": "dotnet-isolated",
    "ConnectionStrings:aeslearn": "Server={server_name};Initial Catalog={database_name};Persist Security Info=False;User ID={user_name};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
  }
}

Create Data Models and DbContext: Define your data models for output and input binding.

public class UpsertEmployeeResponse
{
    [SqlOutput("dbo.Employees", connectionStringSetting: "ConnectionStrings:aeslearn")]
    public Employee Employee { get; set; }
    public HttpResponseData HttpResponse { get; set; }
}
public class Employee
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

Create GetAll HTTPTrigger function: This function with use the SQLInput attribute to read data from a database.

[Function("GetAll")]
public async Task<IActionResult> GetAllAsync(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get")] HttpRequestData req,
    [SqlInput("select * from dbo.Employees", "ConnectionStrings:aeslearn")] IEnumerable<Employee> employees)
{
    return new OkObjectResult(employees);
}

Create HTTPTrigger function: This function Creates a new record inside the Employee table using SQLOutput binding.

[Function("Create")]
public async Task<UpsertEmployeeResponse> CreateAsync(
    [HttpTrigger(AuthorizationLevel.Anonymous, "post")] HttpRequestData req,
    [Microsoft.Azure.Functions.Worker.Http.FromBody] Employee model)
{
    _logger.LogInformation("C# HTTP trigger function processed a request.");
    var response = req.CreateResponse(HttpStatusCode.OK);
    response.Headers.Add("Content-Type", "text/plain; charset=utf-8");
    await response.WriteStringAsync("Employee created successfully!");
    // Return a response to both HTTP trigger and Azure SQL output binding.
    return new UpsertEmployeeResponse()
    {
        Employee = new Employee
        {
            Id = Guid.NewGuid(),
            Name = model.Name,
            Email = model.Email
        },
        HttpResponse = response
    };
}

Update HTTPTrigger function: This function Upserts data using SQLOutput binding.

[Function("Update")]
public async Task<UpsertEmployeeResponse> UpdateAsync([HttpTrigger(AuthorizationLevel.Anonymous,"put")] HttpRequestData req, [Microsoft.Azure.Functions.Worker.Http.FromBody] Employee model)
{
    _logger.LogInformation("C# HTTP trigger function processed a request.");
    var response = req.CreateResponse(HttpStatusCode.OK);
    response.Headers.Add("Content-Type", "text/plain; charset=utf-8");
    await response.WriteStringAsync("Employee updated successfully!");
    // Return a response to both HTTP trigger and Azure SQL output binding.
    return new UpsertEmployeeResponse()
    {
        Employee = new Employee
        {
            Id = model.Id,
            Name = model.Name,
            Email = model.Email
        },
        HttpResponse = response
    };
}

Delete HTTPTrigger function: This HTTPTrigger function deletes employee records from the database. For this function, I'm using Dapper.

[Function("Delete")]
public async Task<IActionResult> DeleteAsync([HttpTrigger(AuthorizationLevel.Anonymous, "delete", Route = "Delete/{id}")] HttpRequest request,Guid id)
{
    var connectionString = _configuration["ConnectionStrings:aeslearn"];
    using var connection = new SqlConnection(connectionString);
    await connection.ExecuteAsync("delete from dbo.Employees where Id=@Id", new { Id = id });
    return new OkObjectResult("Deleted successfully");
}

Conclusion

Azure SQL Server is a powerful, fully managed database service that offers scalability, high availability, security, and seamless integration with other Azure services. It is well-suited for a wide range of applications, from small web applications to large enterprise solutions. Integrating it with Azure Functions is straightforward, allowing developers to build robust and scalable applications with ease.

Source Code

You can access the code from my AzureEssentialSeries Github Repo. Please give it a start if you like it.

Video Tutorial

You can watch the Azure Essentials show Episode 4 on CSharpTV for this topic. You can also watch this video on my LinkedIn.