Overview

Cosmos supports several ways of integrating with data outside of Business Central, including Excel Data Sources and Azure SQL databases. Azure SQL, and SQL in general, are widely used and there are thousands of applications that support extracting data into Azure SQL databases, and many of our customers already have data in Azure SQL that they are looking to leverage. This article will cover how to connect to an Azure SQL database using Cosmos.

How to Create an Azure SQL Data Source

Note: The Azure SQL data source in Cosmos requires an additional data source connector being added to your license. Contact your partner or Cosmos representative to have this added to your license.

Before creating an Azure SQL Data Source in Cosmos, you will first need to get the connection string to your Azure SQL database. This is accessed in the Azure Portal by navigating to the Azure SQL database and clicking on "Show Database Connection Strings". You may need to work with your IT team to get these credentials if you don't have access to your Azure Portal. The Microsoft Learn article below covers how to get this information:

Connect and query - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn

Currently, only SQL Server Authentication (user name and password) are supported by Cosmos.

Once you have the connection string copied, you will navigate to the Data Sources section under Data Management -> Manage and click the New Data Source button. You will give the data source a name and select Azure SQL under the Type field. You will then click Create to create the data source.

Once the data source has been created it's time to set up the connection to your Azure SQL database. To do this, click the Edit button to modify the data source and paste your connection string into the Connection String section. You will then click the Save button. Once the data source is saved, the connection string will be masked and it will never be shown again. If you need to adjust the connection string you will click the Edit button and re-paste in the new connection string. The connection string is encrypted behind the scenes within the Cosmos infrastructure.

You can then click the Validate button to ensure that the connection string is valid. If there are errors connecting, they will be displayed in a new window.

If this is a set of legacy data that only needs to be imported once and not updated whenever the pipeline is run, you can check the Static Data box so that it will only load the data one time. Currently, all data from Azure SQL data sources is run as a bulk load, so all the data is reloaded each time the pipeline is run and incremental loading of Azure SQL data isn't currently supported.

Working with Data from an Azure SQL Data Source

Once you have set up your Azure SQL Data Source you will see it in the Staging section as a new data source. At this point you can treat it as any other data source and check the boxes to bring in the tables, check the boxes for which fields you want to bring in, and then add transformations, lookups, and/or bring the data into its own table in the Data Model section just as you would with standard Business Central data.