Dynamics 365 Business Central + Azure Cosmos DB for globally distributed integrations

Today’s post wants to give an overview of a successfully deployed scenario of a geo-distributed integration between Dynamics 365 Business Central and some local B2B applications and it wants to leave a message to all: enlarge your vision to other Azure services if you want to create globally distributed architectures that rocks!

The scenario:

The headquarter of a large commercial company is located on West Europe and it’s using Dynamics 365 Business Central SaaS as the main ERP system. This company creates items and when these items are ready for selling worldwide, they have the need to distribute their database (product catalog) to every point of selling in the world (on many countries on different continents in the globe). The point of selling (shops) have locally installed apps that reads this product catalog to immediately know what items can be sold, price, availability and so on.

The first solution:

The fist solution was to use Dynamics 365 Business Central APIs. The geo-distributed local apps call custom D365BC API pages and retrieves the data. This solution had two main “problems”:

• some contries experiences long latency on retrieving data
• too much API requests are forwarded to the D365BC tenant during the day

The second solution:

The second solution was to use Azure SQL as an “intermediate layer”: D365BC sends items records to an Azure SQL database by calling an Azure Function and then the local apps calls other Azure Functions for retrieving the data from this “centralized” Azure SQL database instance. Pros and cons of this solution:

• The D365BC tenant does not have to handle too much requests for data
• some contries experiences long latency on retrieving data
• Cost increased

The main problem to handle for having a good architecture for this scenario was to avoid big latencies on some countries. For reaching this goal, data should be as near as possible to the local application (so on every contries where the company has a shop). For this business case, data has no strictly the need to have a relational database under the hood, but we can use also a NoSQL database.

Final solution:

The solution that reached all the goals is described in the following schema:

For this solution we’re using an instance of Azure Cosmos DB for storing item’s data. Azure Cosmos DB is Microsoft’s globally distributed, low-latency, high throughput, always on, multi-model database service. Azure Cosmos DB is a no-sql database and one of the key benefits of Azure Cosmos DB is that it transparently replicates your data wherever your users are, so your users can interact with a replica of the data that is closest to them.

Azure Cosmos DB allows you to add or remove any of the Azure regions to your Cosmos account at any time, with a click of a button.

In our scenario, we have an instance of Azure Cosmos DB in West Europe. On the same region we have an Azure Function that is called from Dynamics 365 Business Central for storing item’s data into an Azure Cosmos DB document collection. Items are stored as JSON documents. Then, the Azure Cosmos DB database is geo-replicated into N different Azure regions we need for our business.

The local applications rerieve the item’s data by calling an Azure Function that in turns retrieves the data from it’s nearest Azure Cosmos DB database. The calls is managed by an Azure Traffic Manager that redirects it to the right Azure Function (region).

The main Azure Cosmos DB is created from the Azure Portal as follows:

The Azure Function used for sending the items data from Dynamics 365 Business Central to Azure Cosmos DB is an Http Trigger deployed to the same Azure region. The function (called SendItemsToCosmosDB) uses the Microsoft.Azure.DocumentDB.Core package and it’s defined as follows:

[FunctionName("SendItemsToCosmosDB")]
public static async Task<IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "post", Route = null)] HttpRequest req,
ILogger log, Microsoft.Azure.WebJobs.ExecutionContext context)
{
   log.LogInformation("C# HTTP trigger function processed a request.");

   string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
   dynamic data = JsonConvert.DeserializeObject(requestBody);

   //Read settings.json
   var config = new ConfigurationBuilder()
      .SetBasePath(context.FunctionAppDirectory)
      .AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
      .AddEnvironmentVariables()
      .Build();

   CosmosDBEndpoint = config["CosmosDBEndpoint"];
   CosmosDBMasterKey = config["CosmosDBMasterKey"];

   SaveItemToCosmosDB(data);

   return ItemNo != null
      ? (ActionResult)new OkObjectResult($"Item {ItemNo} stored.")
      : new BadRequestObjectResult("Error on input data.");
}

The Azure Function supports the http POST method. It receive a JSON document as input (item representation), it retrieves the Azure Cosmos DB parameters from the configuration settings (endpoint and primary key, you can retrieve them from Azure Portal by going to your Cosmos DB database and clicking on Keys) and then saves the item document (JSON) to the Azure Cosmos DB main instance.

The method that stored the JSON data on Azure Cosmos DB is called SaveItemToCosmosDB and it’s defined as follows:

private static void SaveItemToCosmosDB(dynamic Item)
{
   Task.Run(async () =>
   { 
      using (var client = new DocumentClient(new Uri(CosmosDBEndpoint), CosmosDBMasterKey))
      {
         //Create new Cosmos DB database (if not exists) 
         var databaseDefinition = new Database { Id = "ItemDb" };
         var database = await client.CreateDatabaseIfNotExistsAsync(databaseDefinition);
         
         //Create a new database collection (a database is a container that holds a number of collections)
         var collectionDefinition = new DocumentCollection { Id = "ItemCollection" };
         var collection = await client.CreateDocumentCollectionIfNotExistsAsync(UriFactory.CreateDatabaseUri("ItemDb"),
            collectionDefinition);
         
         //Insert the document in the collection
         //To insert new document, you need two things:
         //Path to collection: dbo/{databaseName}/colls/{collectionName}
         //Document Object
         var itemDocument = await client.CreateDocumentAsync(
            UriFactory.CreateDocumentCollectionUri("ItemDb", "ItemCollection"),Item);
      }

   }).Wait();
}

I’ve placed comments between code lines, so I think it’s quite self-explanatory: we create a new database (if it doesn not exists, here called ItemDB), we create a Document Collection ((if it doesn not exists, here called ItemCollection) and we create a document on this collection with the JSON received as input from Dynamics 365 Business Central).

This Azure Function is called from AL by passing a JSON representation of an Item. The AL procedure code is as follows:

procedure SendItem(ItemNo: Code[20])
var

   httpClient: HttpClient;
   httpContent: HttpContent;
   jsonBody: text;
   httpResponse: HttpResponseMessage;
   httpHeader: HttpHeaders;
   Item: record Item;

begin

   jsonBody := ' {"itemNo":"' + item."No." + '",
      "itemDescription":"' + Item.Description +'",
      "itemEnabledForSelling":' + format(Item."Enabled For Global Selling") +'}';

   httpContent.WriteFrom(jsonBody);
   httpContent.GetHeaders(httpHeader);
   httpHeader.Remove('Content-Type');
   httpHeader.Add('Content-Type', 'application/json');
   httpClient.Post(BaseFunctionURL, httpContent, httpResponse); 

   //Here we should read the response 
   message('Item registered on Azure Cosmos DB.');

end;

When the Azure Function is called, we have an HTTP response like the following:

If we check our Azure Cosmos DB via the data explorer in Azure Portal, we can see that the item document is stored:

Azure Cosmos DB adds an ID to the document record itself + other internal fields.

The main Azure Cosmos DB database is geo-replicated on every Azure region we need for our business. The geo-replication can be easily performed directly via the Azure Portal by selection your Azure Cosmos DB instance, then going on Replicate data globally. Here you can select with a click all the region where you want the database replica and if you want a read-only replica or also write enabled:

Thats’s done! Your no-sql database is replicated geographically!

The Azure Cosmos DB is created by selecting the Core (SQL) API (see figure 1). This means that your database supports querying items using Structured Query Language (SQL) as a JSON query language, so you can perform sQL-like query on your unstructured data for retrieving items.

The Azure Function that retrieves the data (here called GetItemsFromCosmosDB) is defined as follows:

[FunctionName("GetItemsFromCosmosDB")]
public static async Task<IActionResult> GetItems(
[HttpTrigger(AuthorizationLevel.Function, "get", Route = null)] HttpRequest req,
ILogger log, Microsoft.Azure.WebJobs.ExecutionContext context)
{
   log.LogInformation("C# HTTP trigger function processed a request.");

   string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
   //Read settings.json
   var config = new ConfigurationBuilder()
      .SetBasePath(context.FunctionAppDirectory)
      .AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
      .AddEnvironmentVariables()
      .Build();

   CosmosDBEndpoint = config["CosmosDBEndpoint"];
   CosmosDBMasterKey = config["CosmosDBMasterKey"];

   string jsonResponse = GetCosmosDBItems();

   return jsonResponse != null
      ? (ActionResult)new OkObjectResult(jsonResponse)
      : new BadRequestObjectResult("Error on retrieving data.");
}

The method that retrieves the Item document from Azure Cosmos DB (here called GetCosmosDBItems) is defined as follows:

private static string GetCosmosDBItems()
{
   string Json = string.Empty;            
   using (var client = new DocumentClient(new Uri(CosmosDBEndpoint), CosmosDBMasterKey))
   {
      var response = client.CreateDocumentQuery(UriFactory.CreateDocumentCollectionUri("ItemDb", "ItemCollection"),
         "select * from c").ToList(); 
      Json = JsonConvert.SerializeObject(response);
   }            
   return Json;
}

The methods performs a sql-like query on our collection and returns all data. Obviously, you can adapt this method to perform the query you need.

If you send a GET request to this Azure Function, this is the response received:

It returns the JSON representation of your document collection data (our Item records stored).

Gain on performances and latency? From 5% (same Azure Region as the Dynamics 365 Business Central instance) to more than 50% (on remote regions).

I think this is something that must be kept in mind… 🙂