Integrating Azure SQL Database with Azure Capabilities

Introduction

Azure SQL Server is a completely managed relational database service supplied by Microsoft Azure. It’s primarily based on the SQL Server database engine and gives the identical capabilities with further cloud-specific options. Azure SQL Server helps numerous deployment choices, together with Azure SQL Database, Azure SQL Managed Occasion, and SQL Server on Azure Digital Machines, catering to totally different wants and workloads.

Key options of Azure SQL Server

1. Absolutely Managed Service: Azure SQL Server handles upkeep, patching, backups, and monitoring, decreasing administrative overhead.
2. Scalability: Helps scaling up or down primarily based on demand with out downtime.
3. Excessive Availability: Supplies built-in excessive availability with automated failover and geo-replication.
4. Safety: Affords superior security measures like encryption, risk detection, and compliance with business requirements.
5. Clever Efficiency: Makes use of machine studying and AI to optimize efficiency robotically.
6. Integration: Seamlessly integrates with different Azure providers and instruments like Azure Knowledge Manufacturing unit, Energy BI, and Azure DevOps.
7. Versatile Deployment Choices: Helps a number of deployment choices to go well with totally different situations and migration paths.

Execs of Azure SQL Server

1. Managed Service: Reduces administrative duties by dealing with upkeep and updates robotically.
2. Scalability: Simply scale sources up or down primarily based on workload necessities.
3. Excessive Availability and Catastrophe Restoration: Constructed-in excessive availability and catastrophe restoration choices guarantee knowledge is all the time accessible.
4. Safety and Compliance: Superior security measures and compliance with main requirements guarantee knowledge safety.
5. Efficiency Optimization: Clever efficiency options improve question efficiency and useful resource utilization.
6. Integration with Azure Ecosystem: Easy integration with different Azure providers enhances productiveness and performance.
7. Migration Instruments: Complete instruments and providers to help with migrating present databases to Azure SQL.

Cons of Azure SQL Server

1. Value: May be costly, particularly for high-performance and high-availability configurations.
2. Complexity in Pricing: Pricing fashions may be complicated and should require cautious planning to optimize prices.
3. Studying Curve: Preliminary studying curve for directors and builders unfamiliar with Azure-specific options.
4. Restricted Management: As a managed service, it gives much less management over the underlying infrastructure in comparison with on-premises deployments.
5. Dependency on Web Connectivity: Requires secure web connectivity to entry the service, which is perhaps a limitation in some situations.

Deployment Choices

Azure SQL Server gives a number of deployment choices to go well with totally different wants:

1. Azure SQL Database: A single database with its personal set of sources managed by way of a logical server. Preferrred for contemporary cloud functions with variable utilization patterns.
2. Azure SQL Managed Occasion: A managed occasion providing close to 100% compatibility with on-premises SQL Server, appropriate for lift-and-shift migrations.
3. SQL Server on Azure Digital Machines: Full management over the SQL Server occasion working on a digital machine, very best for functions requiring OS-level entry or third-party software program.

Integration with Azure Capabilities

Integrating Azure SQL Server with Azure Capabilities entails a number of steps:

Establishing Azure SQL Server

1. Create an Azure SQL Server: Go to the Azure portal and create a brand new Azure SQL Server occasion.
2. Create a Database: Create a brand new database on the Azure SQL Server.
3. Configure Firewall Guidelines: Guarantee your growth machine’s IP tackle is allowed to entry the Azure SQL Server.
4. Get Connection String: Retrieve the connection string from the Azure portal to be used in your software.

Configuring ASP.NET Core MVC mission

• Set up NuGet Packages: Set up the mandatory NuGet packages for SQL Server in your ASP.NET Core MVC mission.

  dotnet add package deal Microsoft.Azure.Capabilities.Employee.Extensions.Sql
  // Utilizing Dapper for Delete operate
  dotnet add package deal Dapper
  

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

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

• Create Knowledge Fashions and DbContext: Outline your knowledge fashions for output and enter binding.

  public class UpsertEmployeeResponse
  {
      [SqlOutput("dbo.Employees", connectionStringSetting: "ConnectionStrings:aeslearn")]
      public Worker Worker { get; set; }
      public HttpResponseData HttpResponse { get; set; }
  }
  public class Worker
  {
      public Guid Id { get; set; }
      public string Title { get; set; }
      public string E mail { get; set; }
  }
  

• Create GetAll HTTPTrigger operate: This operate with use the SQLInput attribute to learn knowledge from a database.

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

• Create HTTPTrigger operate: This operate Creates a brand new file contained in the Worker desk utilizing SQLOutput binding.

  [Function("Create")]
  public async Process<UpsertEmployeeResponse> CreateAsync(
      [HttpTrigger(AuthorizationLevel.Anonymous, "post")] HttpRequestData req,
      [Microsoft.Azure.Functions.Worker.Http.FromBody] Worker mannequin)
  {
      _logger.LogInformation("C# HTTP set off operate processed a request.");
      var response = req.CreateResponse(HttpStatusCode.OK);
      response.Headers.Add("Content material-Sort", "textual content/plain; charset=utf-8");
      await response.WriteStringAsync("Worker created efficiently!");
      // Return a response to each HTTP set off and Azure SQL output binding.
      return new UpsertEmployeeResponse()
      {
          Worker = new Worker
          {
              Id = Guid.NewGuid(),
              Title = mannequin.Title,
              E mail = mannequin.E mail
          },
          HttpResponse = response
      };
  }
  

• Replace HTTPTrigger operate: This operate Upserts knowledge utilizing SQLOutput binding.

  [Function("Update")]
  public async Process<UpsertEmployeeResponse> UpdateAsync([HttpTrigger(AuthorizationLevel.Anonymous,"put")] HttpRequestData req, [Microsoft.Azure.Functions.Worker.Http.FromBody] Worker mannequin)
  {
      _logger.LogInformation("C# HTTP set off operate processed a request.");
      var response = req.CreateResponse(HttpStatusCode.OK);
      response.Headers.Add("Content material-Sort", "textual content/plain; charset=utf-8");
      await response.WriteStringAsync("Worker up to date efficiently!");
      // Return a response to each HTTP set off and Azure SQL output binding.
      return new UpsertEmployeeResponse()
      {
          Worker = new Worker
          {
              Id = mannequin.Id,
              Title = mannequin.Title,
              E mail = mannequin.E mail
          },
          HttpResponse = response
      };
  }

• Delete HTTPTrigger operate: This HTTPTrigger operate deletes worker data from the database. For this operate, I am utilizing Dapper.

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

Conclusion

Azure SQL Server is a robust, totally managed database service that provides scalability, excessive availability, safety, and seamless integration with different Azure providers. It’s well-suited for a variety of functions, from small net functions to massive enterprise options. Integrating it with Azure Capabilities is easy, permitting builders to construct strong and scalable functions with ease.

Supply Code

You may entry the code from my AzureEssentialSeries Github Repo. Please give it a begin should you prefer it.

Video Tutorial

You may watch the Azure Necessities present Episode 4 on CSharpTV for this matter. It’s also possible to watch this video on my LinkedIn.