• Entra in QBS
  • Login
  • Italia
    • Global
    • Belgique - Français
    • België - Nederlands
    • Danmark
    • Deutschland
    • España
    • France
    • Nederland
    • Norge
    • Österreich
    • Polska
    • Schweiz
    • Sverige
    • United Kingdom
  • Global Global Global en
  • Belgique - Français Belgique - Français Belgique - Français fr-be
  • België - Nederlands België - Nederlands België - Nederlands be
  • Danmark Danmark Danmark da
  • Deutschland Deutschland Deutschland de
  • España España España es
  • France France France fr
  • Italia Italia Italia it
  • Nederland Nederland Nederland nl
  • Norge Norge Norge no
  • Österreich Österreich Österreich at
  • Polska Polska Polska pl
  • Schweiz Schweiz Schweiz ch
  • Sverige Sverige Sverige sv
  • United Kingdom United Kingdom United Kingdom en-uk
QBS group
Menu
  • Info Center
    • Blogs
    • Events
    • News
    • References
    • Training
    • Close
  • Solutions
  • Servizi
    • Take the lead
    • Ready to Start – Dynamics 365 Customer Engagement
    • QBS Academy
    • Upgrade Services
    • QBS Azure Services
    • Close
  • Partner Program
    • Diventa un partner
    • Indirect CSP
    • Programma per ISV
    • I Nostri Partner
    • Close
  • Chi siamo
  • Contatti
Blog a cura di:

Marije Brummel

Durata:10 Minutes
Condividi questo:
  • Condividi su Facebook
  • Condividi su Twitter
  • Condividi su Google+
  • Condividi su Linkedin
  • Condividi attraverso Mail
18 Giugno 2021

Cosmos versus Azure SQL and how to integrate with Dynamics 365 Business Central #MSDYN365BC

Sei in: Home » Cosmos versus Azure SQL and how to integrate with Dynamics 365 Business...

Business Central

Dynamics 365

Tech

One of the highest-scoring FAQs from partners and customers moving to Business Central is about direct access to the SQL Server database in combination with the maximum database size.

When all you have is a hammer, everything looks like a nail. Since Dynamics NAV in the last ten years only ran on SQL Server this is what most partners know. Large customers often buy CPU licenses and since they have a SQL Server this is what they often start using as a Data Warehouse too.

The cloud equivalent of SQL Server is Azure SQL and this is fully supported by Business Central. In fact, this is what Microsoft uses for hosting the SaaS platform.

One might be tempted to use Azure SQL then also for cloud data storage, but this may not always be the best choice. On Azure, we have more than just a hammer.

Azure Cosmos Database is a good alternative in Azure if you are looking to store large amounts of data from different sources that you want to use in a Web Shop or other Web-Based solution.

The question that rises is then, what would this cost, and how do I send information back and forth between Cosmos and Business Central.

The pricing model for Cosmos is different from Azure SQL in a sense that you pay for real throughput and storage. The storage model is cheaper than the model for Azure SQL.

Cosmos is also auto-scale and auto indexed even though I expect that most of the workload from SMB customers will not be affected by this a lot.

Cosmos vs. Azure Data Lake

In this article we discuss Cosmos as an alternative to Azure SQL. Cosmos is not the only alternative and in the next articles, we will also discuss Azure Data Lake and other blob-based solutions.

Working with Cosmos & Business Central

As you may expect from my profile the main topic of this blog is the technology required to get data to move from Business Central to Cosmos. For this there are two options and I’ll describe both and share code examples.

In both cases we are going to use the Cosmos Rest API.

The AL way

The first option and probably the option of choice by experienced AL developers is to call the API directly from Business Central. This is a straightforward HTTP request where we send json information to a Cosmos container. The thing that may require to fiddle with a bit is the security tokens, but this has been laid out in the example code. You can find code examples on our GitHub.

The C# way

Another option which will most likely be the preferred option by developers who are new to our community is to call the Business Central REST API from an Azure Function and push that data into Cosmos using some C# code. This is also part of the examples you can find on GitHub.

Best of both worlds

Both options can also be combined. This depends on how you want to schedule the synchronization. Scheduling can be done using a Logic App that synchronizes data every x minutes, or hours, or you can push the Azure Function from the Business Central Job Queue. Alternatively, you can even call the Azure Event Grid from a Business Central Webhook and use that to transform data from Business Central to Cosmos.

The Azure Function also allows data to be stored from other data sources such as a SOAP endpoint.

Pitfalls and tips

You can store all kinds of data in Cosmos, based on containers just like Azure Blob Storage.

Cosmos is a no SQL database so it does not force you to follow schemas.

The consequence of the lack of schema’s is that it becomes the responsibility of the owner of the database to make sure data in a container is consistent and all json files follow the same structure.

As an example, let’s assume you have created a container with Item Inventory by date and you want to add location code as a new field. You need to update old records with the location code or create a new container with fresh data.

The example code provided on the GitHub contains a Version No. field and some extra Metadata about the json stored in the container. This allows you to have multiple versions of a record in the same container and store, for example, items and resources in the same container. This can have an advantage if you need an aggregation across Business Central entities in one container.

Because throughput is the main driver for cost in Cosmos it can also be beneficial to duplicate data across containers if there is a clear difference in data requirements across certain BI dashboards. For example, certain dashboards only require a customer name, where other dashboards require post codes or city.

Since data is stored in json format you can store parent and child information in one container, such as Sales Headers and Lines.

Creating a new Cosmos Instance & Database

One of the reasons Cosmos can be interesting for the SMB market and Business Central users is that it provides a free entry level of 25GB and 1000ru. This should suffice for an average Datawarehouse combining data from Business Central and other entities.

Cosmos versus Azure SQL and how to integrate with Dynamics 365 Business Central #MSDYN365BC
API

You can choose any API you are familiar with, but the examples on our GitHub are based on Core SQL. This syntax is relatively close to Transact SQL that most Business Central power users are familiar with.
Cosmos versus Azure SQL and how to integrate with Dynamics 365 Business Central #MSDYN365BC

Serverless or Provisioned

Probably serverless since we are having very incremental workloads. If we don’t use Cosmos, we only pay for storage.

Account Name

The account name is a generic name, used for all databases, just like an Azure SQL Server.

Other options may be left default or may vary based on your organizational backup and security policies and are not part of the scope of this document.

Creating a Database and a Container

In our code examples, the Database and Container are created by the Azure Function in C# if you try to write to a Database and/or Container that does not exist. The AL code does not contain examples of creating a Database or Container automatically. If you go for AL only you need to either write this code or create them manually via the Azure Portal.

Cosmos versus Azure SQL and how to integrate with Dynamics 365 Business Central #MSDYN365BC

GitHub
https://github.com/qbsgroup/QBS.BusinessCentral.Cosmos
https://github.com/qbsgroup/QBS.AzureFunction.Cosmos

Altri articoli

Puoi trovare interessante anche questo

  • Do you really need the Business Central Universal Code Initiative? Are you shocked? Let’s hope not…

    What’s the Business Central Universal Code Initiative? For a […]

  • Directions EMEA in Milan and we as QBS Group will be there!

    It was a tough year without meeting each other […]

  • Dynamics 365 Update Webcast 1 July 2021

    This series of webcasts is intended to share the […]

  • Dynamics 365 Update Webcast 27 May 2021

    This series of webcasts is intended to share the […]

  • Why is Azure SQL so important for Dynamics 365 Business Central?

    Let us start with a common question: “When a […]

  • Dynamics 365 CRM Update Webcast 16 October 2020

    Get the latest insights from Microsoft Dynamics 365 CRM […]

  • Dynamics 365 Update Webcast 15 October 2020

    This series of webcasts is intended to share the […]

  • What is the MB-800 Microsoft Dynamics 365 Business Central Exam and why does it matter?

    Microsoft has launched a brand-new certification exam for Dynamics […]

  • Dynamics 365 Update Webcast 25 June 2020

    This series of webcasts is intended to share the […]

  • Dynamics 365 and Power Platform licensing model explained

    Get the latest insights from Microsoft Dynamics 365 CRM […]

  • Dynamics 365 CRM Update Webcast 11 May 2020

    Get the latest insights from Microsoft Dynamics 365 CRM […]

  • Dynamics 365 Update Webcast 6 February 2020

    This series of webcasts is intended to share the […]

  • Update on Dynamics 365 Business Central 27 August 2019

    This series of webinars is intended to share the […]

  • New Pricing for Dynamics 365 Customer Engagement & Unified Operations per the 1st of October 2019

    Microsoft announces new Pricing for Dynamics 365 Customer Engagement […]

  • Update on Dynamics 365 Business Central 13 June 2019

    This series of webinars is intended to share the […]

  • Update on Dynamics 365 Business Central 9 May 2019

    Watch here the Webinar update of the 9th of May 2019.

  • What does the latest Dynamics 365 Business Central Update (April ’19) mean for Europe, Middle East & Africa?

    The April 2019 Business Central Licensing Guide has recently been released, but are there differences from the previous ones? In short, yes.

  • Update on Dynamics 365 Business Central 11 April 2019

    This series of webinars is intended to share the […]

  • Update on Dynamics 365 Business Central 14 March 2019

    Technical update and License update 1 April 2109

  • White Paper – Microsoft Dynamics 365 the perfect platform for digital transformation in the mid-market

    At QBS group we’re all about Dynamics partners – […]

  • Update on Dynamics 365 Business Central 7 February 2019

    This series of webinars is intended to share the […]

  • Ready to Start Microsoft Dynamics 365 Customer Engagement
  • Directions EMEA 2018 Business Report

    This unique report features a broad but insightful and independent business reporting on the happenings at DIRECTIONS EMEA 2018.

  • Microsoft Dynamics 365 – the perfect platform for new business ventures

    Microsoft has thousands of business partners. Is this market not the red ocean?

  • Ready to Start Microsoft Dynamics 365 Customer Engagement program – Reference MKB365

    MKB365 has set up a separate business model for […]

  • Highlights from eXtreme 365 EMEA 2018

    Get all the highlights of eXtreme 365 EMEA 2018 and watch this 6 minutes video.

  • What can Dynamics CRM Partners expect from eXtreme365 EMEA 2018?

    eXtreme 365 is the annual event for Microsoft Dynamics […]

  • The evolution of Dynamics 365 opens the door for new opportunities

    Being a Dynamics CRM partner today is like dancing on a volcano – far from boring!

  • “Ever change a winning team”

    “Ever change a winning team” Alex Ferguson, former football […]

  • What’s new NAV 2018

    Understanding these new possibilities is crucial for your product and / or marketing plans.

  • GDPR … and what now ?

    Starting from 25 May 2018 there is going to be a […]

  • Position NAV as stepping stone for more Dynamics 365 ‘Tenerife’ deals

    Since the introduction of the Dynamics 365 Business Edition in the US and Canada late 2016, the position of Dynamics NAV is under pressure! Competitors took the chance to position NAV as an old-fashioned solution and that’s a direct threat to your NAV business!

  • Exclusive interview with Jeff Edwards Director for ERP Channel Strategy Microsoft

    “If you’re an ERP partner not selling CRM, and not having a focus on verticals in 2018 – I don’t know what you’re doing”

  • QBS Azure Services
  • Upgrade Services
  • Dynamics 365 for Sales Acceleration Program
  • Programma per ISV

    La priorità di ogni ISV è sviluppare e fornire […]

  • Indirect CSP

    In qualità di Microsoft Reseller, state prendendo in considerazione […]

  • DIRECTIONS EMEA 2017 business report is available now!

    Never before it was so important for Dynamics partners […]

  • Business Edition is off – long live Tenerife!

    Our team is just getting back from Directions in […]

  • QBS Academy
  • Directions EMEA 2017 Madrid

    Find here all the information you as Microsoft Reseller […]

  • Reflections on this weeks announcements from Microsoft

    One of our founders summarized this week plain and […]

  • The CSP Direct model is like an iceberg

    We noticed that the market is changing, our clients are, more and more, requesting a subscription based approach. This allows them to not have their own services nor the hassle.

  • General Data Protection Regulations (GDPR) – a Microsoft perspective

    The EU General Data Protection Regulation (GDPR) is the […]

  • Inspire 2017 – A look back at 11 big stories for Microsoft Dynamics Partners

    As a longtime WPC / Inspire-observer with a perspective […]

  • Part 4 Microsoft Dynamics Development Preview

    Microsoft released during last Christmas an Azure Virtual Machine […]

  • What Dynamics Partners can expect from Inspire 2017

    Microsoft is on the move! Ever since the start […]

  • 7 Strong arguments to sell Dynamics NAV today

    Dynamics NAV finds itself in an interesting position today! […]

  • Dynamics NAV Development: Extensions or Extinction?

    No one wants to be a dinosaur, it does […]

  • Do you know your new competitors with Dynamics 365 for Financials?

    The new kids-on-the-block with outsell you on Dynamics 365 […]

  • What is the Microsoft Cloud Solution Provider Program (Microsoft CSP)

    The Microsoft Cloud Solution Provider Program (CSP) enables partners to directly manage their entire Microsoft cloud customer lifecycle.

  • Dynamics 365 Business Edition: Opportunity or Threat

    Microsoft will be launching Dynamics 365 Business Edition in […]

  • Get yourself a niche, in order to be successful

    At QBS group we believe that our partners deserve […]

  • After Directions EMEA 2016, Microsoft Dynamics NAV partners face crucial decisions on their future

    As a longtime Microsoft-observer with a perspective geared toward […]

  • What is the Microsoft CSP Program?

    In order to be successful in a Cloud first, […]

Cosa gli altri dicono di QBS

  • Ferry Schrijnewerkers

    Owner en Co-founder at MKB365

    Since the start of our company 4 months ago, we have already completed 12 Dynamics 365 for Sales implementations.

    Read the reference case
  • Joaquim Antón Gimeno

    GRUPO CHOICE

    "Our relationship with QBS group allows us to improve our marketing strategy."

    Read the reference case
  • Sofkia

    "Our alliance with QBS group has been very important in the development of our business strategy."

    Read the reference case
  • VS Sistemas

    "QBS group helps us build our road to success."

    Read the reference case
  • Berith Skov - C2IT Business Solutions

    “We are very satisfied with the services provided by and through QBS group."

    Read the reference case
  • Herke ICT Group- Karing Buzing

    We are once again part of a NAV community.

    Read the reference case
  • Owe Loonstra - Verito

    Manager Sales & Marketing

    The fact that we belong to a larger organisation gives our customers security.

    Read the reference case
  • Adfocom - Jeroen Kersten

    Partner Care saved us a lot of time.

    Read the reference case
  • NavAzure - Michael Francois Knudsen

    “It is important to me, that I through QBS become part of a greater network, where partners can share experience"

    Read the reference case
  • Blisss - Dirko Wijers

    Without the help from QBS group we wouldn’t have succeeded.

    Read the reference case

Programma

  • Diventa un partner
  • Indirect CSP
  • Programma per ISV
  • I Nostri Partner

Info Center

  • References
  • Blog
  • Training
  • Event
  • News
  • Newsletter

Servizi

  • Dynamics 365 for Sales Acceleration Program
  • Upgrade Services
  • QBS Academy
  • QBS Azure Services

Follow QBS group

© Copyright QBS group 2023
  • Cookie Policy
  • Privacy Policy
Dynamics 365 Update Webcast 27 May 2021 Dynamics 365 Update Webcast 1 July 2021
Please complete the form

Contact us, please complete this form

Join QBS Group, please complete this form

Join QBS Group please complete the form

Sign up QBS Group Membership

Send me an email.
Please complete the form


If you are not seeing the form, please check your ad-blocker settings.

Microsoft’s Power platform - Webinar 12 November 2019 - Austria

Download Center

Please complete the form

Register for this event
QBS
  • Italia
    • Global
    • Belgique - Français
    • België - Nederlands
    • Danmark
    • Deutschland
    • España
    • France
    • Nederland
    • Norge
    • Österreich
    • Polska
    • Schweiz
    • Sverige
    • United Kingdom
  • Info Center
    • Blogs
    • Events
    • News
    • References
    • Training
    • Back
  • Solutions
  • Servizi
    • Take the lead
    • Ready to Start – Dynamics 365 Customer Engagement
    • QBS Academy
    • Upgrade Services
    • QBS Azure Services
    • Back
  • Partner Program
    • Diventa un partner
    • Indirect CSP
    • Programma per ISV
    • I Nostri Partner
    • Back
  • Chi siamo
  • Contatti

This website uses Cookies to give you the best experience. Agreed by clicking the 'Accept' button. After you've clicked on "Accept" you accept Privacy Policy.

Scorrere verso l’alto