Blog written by:
Mark Brummel
Time to read: 10 Minutes
18 June 2021
Cosmos versus Azure SQL and how to integrate with Dynamics 365 Business Central #MSDYN365BC
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.
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.
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.
GitHub
https://github.com/qbsgroup/QBS.BusinessCentral.Cosmos
https://github.com/qbsgroup/QBS.AzureFunction.Cosmos
MORE ARTICLES
You may also find this interesting
-
The Dynamics 365 Migration Journey – Infographic
-
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 […]
-
HELP! My Application Insights EXPLODES!
At QBS Group we are promoting the use of […]
-
From Dynamics 365 Business Central to Business Applications
How do I move from Dynamics 365 Business Central […]
-
Cloud Printing Service for Dynamics 365 Business Central
Printing in cloud scenarios can be a bit challenging, […]
-
Revised MB-800 preparation packs in the QBS Academy in 2021
QBS Group is now launching the revised offerings to […]
-
Dynamics 365 Update Webcast 25 March 2021
This series of webcasts is intended to share the […]
-
How to use the Dynamics 365 Business Central API with C# in Service-to-Service scenarios
A question that was raised more frequently than others […]
-
Dynamics 365 & Power Platform – Special Interest Group
The Power Platform Special Interest Group is designed to […]
27 August 2021 Webcast -
Get Insights from Microsoft about the directions of Business Central in the SMB Channel
Michael Hartman, CEO of QBS Group, interviewed Mike Morton, […]
-
ERP Migration – the Friendly Hill That One Day May Become a Hostile Mountain
Cloud computing is probably the most profound IT-innovation of […]
-
What is Microsoft Cloud Business Applications SMB Option?
If you were not already aware, Microsoft has created […]
-
Why is Application Insights so important with Dynamics 365 Business Central?
I can understand why some people would still refrain […]
-
Dynamics 365 Update Webcast 10 December 2020
This series of webcasts is intended to share the […]
-
Dynamics 365 Update Webcast 12 November 2020
This series of webcasts is intended to share the […]
-
Dynamics 365 CRM Update Webcast 6 November 2020
Get the latest insights from Microsoft Dynamics 365 CRM […]
-
Get the latest insights at the Directions EMEA 2020
Directions4Partners Virtual 2020 is focusing on bringing state-of-the-art keynotes and […]
-
Why is Azure SQL so important for Dynamics 365 Business Central?
Let us start with a common question: “When a […]
-
Look back at the Microsoft Dynamics 365 Business Central 2020 Release Wave 2 Launch Event
Microsoft has arranged an absolutely splendid launch event for […]
-
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 […]
-
Get the latest insights at the Directions EMEA 2020 and get inspired by QBS Group.
Directions4Partners Virtual 2020 is focusing on bringing state-of-the-art keynotes […]
-
Dynamics 365 CRM Update Webcast 4 September 2020
Get the latest insights from Microsoft Dynamics 365 CRM […]
-
Why use Azure BLOB Storage with Business Central?
What is Azure BLOB Storage and why is this […]
-
Closer Cooperation between QBS Group and 1Click Factory
As of September, Soren Fink Jensen, CEO of 1ClickFactory […]
-
QBS Summer School 2020
Even with the new normal, summer remains the perfect […]
-
Complete installed base migration to Microsoft 365 Business Central SaaS
This weekend Microsoft reseller Beta migrated their complete installed […]
-
Dynamics 365 Update Webcast 25 June 2020
This series of webcasts is intended to share the […]
-
Dynamics 365 CRM Update Webcast 24 June 2020
Get the latest insights from Microsoft Dynamics 365 CRM […]
-
From NAV via Business Central on-premises to Cloud
In this time of digital transformation, it is recommended […]
-
Best practices of migrating your customer to Business Central SAAS
“Migrating Dynamics NAV customers to Dynamics 365 Business Central […]
-
Dynamics 365 and Power Platform licensing model explained
Get the latest insights from Microsoft Dynamics 365 CRM […]
-
Dynamics 365 Update Webcast 4 June 2020
This series of webcasts is intended to share the […]
-
Dynamics 365 CRM Update Webcast 11 May 2020
Get the latest insights from Microsoft Dynamics 365 CRM […]
-
Dynamics 365 Update Webcast 7 May 2020
This series of webcasts is intended to share the […]
-
QBS Academy – Online Courses VILTs
For New Starters / Consultants coming from Compete ERP […]
-
Migrating your Dynamics NAV customers
-
Dynamics 365 Update Webcast 5 March 2020
This series of webcasts is intended to share the […]
-
Business Central on-premise Essential and Premium users
Essential to Premium users When a customer has a […]
-
Dynamics 365 Update Webcast 6 February 2020
This series of webcasts is intended to share the […]
-
Dynamics 365 Update Webcast 9 January 2020
Get the latest insights from Dynamics 365 and get […]
-
Update on Dynamics 365 Business Central 12 December 2019
This series of webinars is intended to share the […]
-
Dynamics 365 Update Webcast
Get the latest insights from Dynamics 365 and get […]
30 September 2021 Webcast -
Update on Dynamics 365 Business Central 18 November 2019
This series of webinars is intended to share the […]
-
White Paper – the Microsoft Dynamics ecosystem must win more customers
At QBS group we’re all about Dynamics partners – […]
-
Microsoft Incentives – The most important changes per October 1, 2019 explained
In my previous blog post, I discussed the changes […]
-
Dynamics 365 Business Central 2019 release Wave 2
As of October 1, 2019, Microsoft has launched Dynamics […]
-
QBS Marketplace
-
Update on Dynamics 365 Business Central 27 August 2019
This series of webinars is intended to share the […]
-
Microsoft raised the bar for competencies
October 24, 2019 update. Microsoft has gone back on their […]
-
New Pricing for Dynamics 365 Customer Engagement & Unified Operations per the 1st of October 2019
Microsoft announces new Pricing for Dynamics 365 Customer Engagement […]
-
D365 Business Central April 2019 update – key findings
April 19 2019 Dynamics 365 Business Central Release Our […]
-
Update on Dynamics 365 Business Central 13 June 2019
This series of webinars is intended to share the […]
-
Goodbye to NAV and Hello to 365 Business Central – with a German twist
Just as the Dynamics roots in QBS run deep […]
-
The 7 secrets you must know to be successful with your Dynamics 365 App
In 2019 we celebrate 30 years of the world […]
-
Ready to Start – Dynamics 365 Business Central
-
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.
-
Press release: QBS group becomes majority shareholder of 1ClickFactory
Leusden, The Netherlands, April 2019 QBS group and […]
-
Dynamics subscription price changes per October 1st 2019
From October 1st 2019, NAV Subscription prices will be increased and aligned with the Business Central on-prem Subscription prices.
-
Update on Dynamics 365 Business Central 11 April 2019
This series of webinars is intended to share the […]
-
Daily reports from eXtreme 2019, Amsterdam
Our Dynamics Expert Arjen Jansen is providing daily reports […]
-
Business Central is now available in Microsoft Graph – could this be the end of RapidStart?
Business Central API in Microsoft Graph Dynamics 365 Business […]
-
Update on Dynamics 365 Business Central 14 March 2019
Technical update and License update 1 April 2109
-
All you need to know about Microsoft Dynamics NAV coming to an end and becoming Microsoft Dynamics 365 Business Central
(this page will continuously be updated as information arrives) […]
-
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.
-
Update on Dynamics 365 Business Central 7 December 2018
This series of webinars is intended to share the […]
-
Microsoft Dynamics 365 – the perfect platform for new business ventures
Microsoft has thousands of business partners. Is this market not the red ocean?
-
How to integrate Power BI in Dynamics 365 Customer Engagement dashboards
You are well familiar in the world of ERP […]
-
Update Dynamics 365 Business Central
This series of webinars is intended to share the […]
-
Hot news from Directions EMEA 2018
Like every year, QBS group is attending Directions EMEA […]
-
We’re on the verge of something big – reflections on Directions EMEA 2018
With Directions EMEA 2018 coming to an end, there […]
-
Introduction in D365 Marketing
Dynamics 365 Marketing is the new kid on the […]
-
Microsoft Dynamics 365 Business Central (On-Premise) is here! What does this mean for you as Partners?
Microsoft has just released the On-Premise Version, and this means big things in the on – premise market, as well as the existing NAV market
-
Update Dynamics 365 Business Central
This series of webinars is intended to share the […]
-
Build a Powerapp in 5 minutes
Microsoft Flow and PowerApps are designed to integrate […]
-
Dynamics 365 Business Central Explore Workshops
-
It’s that time of year again
Dear Dynamics partners, It’s that time of year again, […]
-
Business Report Directions EMEA 2018 – Reserve your copy now!
Are you not able to attend Directions EMEA 2018 […]
-
Update Dynamics 365 Business Central
This series of webinars is intended to share the […]
-
eLWIS Has Left the Building – Goodbye to Customization of ERP Systems
If you are in the ERP-industry, you have undoubtedly […]
-
Microsoft partner success with Dynamics 365 Business Central: Building a high volume sales engine
This spring saw the long-awaited release of Microsoft Dynamics […]
-
The Strength of PowerApps: Accelerate your business with PowerApps
A constant message during Inspire was about partners adding […]
-
QBS group achieves the 2018/2019 Inner Circle for Microsoft Business Applications
Leusden, The Netherlands – August 1st, 2018 “QBS group is […]
-
Hot News from Inspire 2018
Microsoft’s most important partner event of the year is […]
-
Thoughts from Inspire Day 3
Today started with the keynote from Satya Nadella, and […]
-
Fresh from Inspire – we inform you about the latest and greatest in Microsoft’s Business applications product news
The latest insights into the Microsoft’s Business applications product […]
-
Thoughts from Inspire Day 2
Today started again with a keynote from Gavriella Schuster, […]
-
Thoughts from Inspire Day 1
So, Inspire the largest Microsoft partner conference ever started […]
-
This is how we got posted on AppSource!
QBS is constantly paying attention to help partners keep […]
-
Ready to Go – Keep Your App Up-to-date
-
Ready to Go – Set-up Extension Testing
-
Ready to Go – Scope Your Extension Development
-
Ready to Go for Dynamics 365 Business Central
-
Update Dynamics 365 Business Central
This series of webinars is intended to share the […]
-
What every Microsoft Reseller should know about Microsoft Dynamics 365
Watch this video to know everything about the pricing and licensing of Microsoft Dynamics 365.
-
Update Dynamics 365 Business Central
This series of webinars is intended to share the […]
-
Introduction into Microsoft Dynamics 365 for Project Service Automation (PSA).
On this CRM Inspirational Webinar, Arjen Jansen will host an […]
-
Add value on top of Dynamics 365 Business Central with Extensions 2.0 knowledge!
do you stay and extend your property or do you go upmarket
-
NAV posting procedures
One of our partners requested some background information on […]
-
Ready to Start Microsoft Dynamics 365 Customer Engagement program – Reference MKB365
MKB365 has set up a separate business model for […]
-
Hot news from the eXtreme 365 EMEA event
The most important Microsoft Dynamics partner event in the […]
-
Update on Dynamics 365 Business Central
This series of webinars is intended to share […]
-
Highlights from eXtreme 365 EMEA 2018
Get all the highlights of eXtreme 365 EMEA 2018 and watch this 6 minutes video.
-
“Finally!”
“Finally!” was one of the reactions I heard stated […]
-
Dynamics 365 ”Tenerife” is just around the corner – are your customers still running NAV 2009?
NAV customers still using old versions are vulnerable to competitive offerings.
-
What can Dynamics CRM Partners expect from eXtreme365 EMEA 2018?
eXtreme 365 is the annual event for Microsoft Dynamics […]
-
How to upgrade from Microsoft Dynamics AX 2012 to Dynamics 365
In my previous article Upgrade from CRM Online to […]
-
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!
-
The Dynamics 365 Acceleration Program has enabled us to acquire the global perspective of implementation of Dynamics 365 for Sales.
Sofkia Consulting and development. “Within the process of continuous […]
-
MKB365 : “We are ready for Dynamics 365”
“The result of the Dynamics 365 Acceleration Program is […]
-
“Ever change a winning team”
“Ever change a winning team” Alex Ferguson, former football […]
-
Why should I care about IoT as an Microsoft D365/NAV partner?
Adoption of Internet of Things solutions across industries continues […]
-
What’s new NAV 2018
Understanding these new possibilities is crucial for your product and / or marketing plans.
-
4 Business reasons to start a CRM practice
Microsoft switches from single category products like Dynamics NAV […]
-
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
-
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 […]
-
Upgrade from CRM Online to Dynamics 365 in simpel three steps
Explain in 3 steps how to make a smooth transition from CRM Online to Dynamics 365.
-
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 […]
-
ISV Program
NEWS! Introducing the QBS Marketplace. As an ISV your […]
-
Indirect CSP for Microsoft Resellers
As a Microsoft Reseller, you are considering to collaborate […]
-
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, […]
