Skip to main content
Business Central forum
Suggested answer

integration with ms sql server

editSubscribe (1) ShareShare
ReportReport
Posted on by 2

Hello experts,

 

I'm facing a challenge with integration at the moment. My goal is to transfer data from Dynamics 365 Business Central (cloud base) to my local MS SQL Database server. I'm looking to create a link server without relying on third-party tools. Does anyone have any insights or suggestions on how I can achieve this? Your assistance would be greatly appreciated.

 

Thank you.

Attachments
  • Community member Profile Picture
    Community member 22 on at
    integration with ms sql server

    Certainly! Integrating data between Dynamics 365 Business Central (cloud-based) and your local MS SQL Database server can be achieved using various methods. One approach is to set up a Linked Server in SQL Server to establish a connection between the two databases. Here's a high-level overview of the steps involved:

    Configure Linked Server: In your SQL Server Management Studio (SSMS), you can create a Linked Server by specifying the details of your Dynamics 365 Business Central instance. This involves providing the server name, login credentials, and other necessary connection information.

    Define Queries: Once the Linked Server is established, you can write SQL queries to retrieve data from Dynamics 365 Business Central tables and insert/update it into your local MS SQL Database tables. These queries can be scheduled as SQL Server Agent jobs to automate the data transfer process at specified intervals.

    Data Transformation: Depending on the structure and format of the data in Dynamics 365 Business Central and your local database, you may need to perform data transformation tasks to ensure compatibility and consistency.

    Error Handling and Monitoring: Implement error handling mechanisms to capture and handle any data transfer errors effectively. Additionally, set up monitoring and logging to track the status and performance of the integration process.

    While setting up this integration can be complex and requires technical expertise, our team at Cloudify specializes in cloud-based solutions and integrations. We can assist you in implementing this Microsoft Dynamics  Business Central integration seamlessly, ensuring efficient data transfer between Microsft Dynamics  Business Central and your local MS SQL Database server. Our experts can guide you through the setup process, address any challenges you encounter, and optimize the integration for your specific requirements. Don't hesitate to reach out to us for personalized assistance and support in achieving your integration goals.

    https://www.cloudify.biz/marketplace/products/microsoftBC-integrations

  • Suggested answer
    DAnny3211 Profile Picture
    DAnny3211 9,180 Super User on at
    integration with ms sql server
    hi
     
    in my opinion you should export a saas backup from the admin centre 
    as a .bcpac 
    https://danieleincalza.blogspot.com/2022/11/business-central-export-database-saas.html
    and then restore it to an azure sql, which could be a bit tricky.
     
    DAniele
  • Suggested answer
    Judy Profile Picture
    Judy on at
    integration with ms sql server
    SQL Server allows you to create linked servers to connect to other data sources. Please follow the article shows how to create a linked server from SQL Server to Azure Data Explorer.
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 59,978 Super User on at
    integration with ms sql server
    As far as I know, the standard cannot do this. If your local program is the BC On-Pre version, it can be synchronized from On-Pre to Cloud, but not vice versa.
    More details: How to migrate On-Premises Data to Cloud (SaaS)
     
    And without connectors or APIs, you cannot access the local database from cloud.
     
     
    Hope this helps.
    Thanks.
    ZHU
  • gdrenteria Profile Picture
    gdrenteria 2,743 Super User on at
    integration with ms sql server
    Hi
    I would think that it is not possible because to make some connections we would require dotnet objects. Here are some examples of onprem.
    Business Central OnPremise – Microsoft AL connected to SQL Server by .NET – Roberto Stefanetti Business Central Blog (robertostefanettinavblog.com)
    Could you analyze or design the connection to web services or APIs and not directly to the database?
    Best regards
    Gerardo

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,080 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,601 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Featured topics

Product updates

Dynamics 365 release plans