You’re offline. This is a read only version of the page.
Hi experts,
We found the good feature of exporting data from Dynamic CRM to Azure SQL, which can nearly real time to capture the data creation, update and deletion, but the exported table in Azure SQL has no relationship, which lead performance issue when consume these data. Giving we need some data from CRM in another system, and it also has its own transaction data, in this scenario, do you have some good practice for the DB architecture,
1. Should we put the table together with the DB we export from CRM, if so how to improve performance
2. Should we add additional tables in exported DB, and define some trigger or ETL CRM data to our own table, if so, this DB is not for CRM data source only, what should we do if we have another system in future which also need to consume CRM data
3. Create a new DB, it ETL data from exported DB, and store its own transition info
We want to make sure the other system can reflect the latest data from CRM with a good performance. What's the recommended solution for this situations? Appreciate for your help.
Thanks,
Edison
*This post is locked for comments
You can create indexes and they will remain there after the table is created during the initial metadata sync. In fact Sql azure will suggest indexes for you and you can turn on auto apply of the suggestions it makes!
If the data is continually updated from CRM, or it needs to be continually available to consume, then you'll need to keep the indexes continually in place, though it would be a good idea to have a maintenance job to periodically rebuild them to maintain best performance.
However, if you update the data from CRM on a schedule, and the data is not consumed at that time, you'll probably get best performance for the updates by dropping the indexes prior to the data update, then recreated them after the data update. Having said that, I doubt you'd get much performance benefit from this, as the main overhead is the latency of data transfer from CRM
Does the index never be overwritten when we extract the data from CRM export service? I mean the table will be created by service automatically when we configure and set them up, when could we create the index?
How did you get on?
Hi,
If you simply want to query the data then I would add the indexes that you need to the sql azure database to give the performance you are looking for. I don't think there is any need to ETL into another database unless your other system has limitations on where it can read data from, or if the transformations are non-trivial and would cause performance issues themselves.
Hope this helps
Scott
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (