Connecting a Azure SQL database with Dynamics 365 Online

apollo7
apollo7 used Ask the Experts™
on
Dynamics 365 Online, CRM 2013 on prem, SQL Azure

We are upgrading a CRM 2013 system to D365 Online.  The 2013 system has several SQL Procs that currently write information to the CRM 2013 system.  I stood up an Azure environment and imported one SQL proc (and associated database) into Azure.  

I can now query the database associated with the SQL proc, run the sp commands, query the database.  It now functions like the SQL Proc and database that writes to CRM 2013

I am now not sure how to use the SQL Proc/database on Azure with Dynamics 365 Online.  

How do I replicate the process where the SQL proc writes data to CRM 2013  and then use this in D365?

Is this a job for Scribe or KingswaySoft?

Please advise.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Hi apollo7,

Yes. What you want to achieve is totally unsupported On Premise and there is no direct way to do this for D365 Online.

You will have to either go with Scribe and Kingswaysoft. Or a custom developed tool.

Regards,
Chinmay.

Author

Commented:
Is there another way to proceed?  Would converting the SQL Procs to plugins or something that would be compatible with D365 be a better way to go?
Pratik SomaiyaApplication Development Associate

Commented:
Hello,

To export the current CRM data to Azure SQL Database you can use the CRM Data Export Service.

Regards,
Pratik
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
When people write SQL apps they tend to forget D365 constraints hence I am not sure if those queries can be translated to Plugins. You will have to go case by case i.e. take a query check it's feasibility to be converted to a Plugin.

PS: Data Export Service is used to - as the name suggests export data out of CRM, it cannot be used to write data to Dynamics 365 Online.

Author

Commented:
I have looked at this, I also need to export data from the non-CRM data generated by the SQL Proc to CRM
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
How your non-CRM data is generated? And what is the volume of such data?

Author

Commented:
Depends on the stored proc, it generally gathers data from an external source (Like an aspx app), processes it and then writes it to CRM.  Some of the stored procs generate a great deal of data and run at a scheduled time.
Pratik SomaiyaApplication Development Associate

Commented:
To export non CRM to CRM you can use Azure Data Factory V2.
Chief Technology Ninja
Distinguished Expert 2018
Commented:
I think you should try and use Kingswaysoft for this requirement. You will have to think of the cost of running stuff entirely in cloud or from on premise. Also, if you need to insert related records or need better control over the process, using the Kingswaysoft adapter is a better choice.

Author

Commented:
Can Azure Data Factory V2 do bi-directional like Scribe?  Would connectors to CRM need to be custom-developed?  What do you think Azure Data Factory V2 does better than Scribe or similar tools?

Thanks
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
And for those single records, you should call CRM Apis and insert the records.

Author

Commented:
Thanks, Chinmay.  I have been wanting to get involved with Kingswaysoft.
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
That's the right call. I think Scribe is a good solution in general, but if I have to do something that involves data migration I would go with Kingswaysoft.
Pratik SomaiyaApplication Development Associate

Commented:
ADF V2 has a connection to CRM. You just need to configure the link details.

Author

Commented:
Thanks for the answers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial