I’ve worked on some cases related to Data Management exports to BYOD recently and I thought I would share some observations on things you should consider when exporting data to BYOD. This blog post is not meant to serve as a comprehensive document outlining how the process works, but rather to raise awareness of the sorts of factors that may impact how consistently successful your exports to BYOD will be. Much of this is common sense and non-technical, and it is valid for other data movement scenarios using different technologies.
If you are experiencing data exports failing or only partially succeeding on a recurring basis then I suggest that you look at the following in your efforts to try to track down a cause and resolve such issues:
EXAMPLE SCENARIOYou are regularly exporting data from Dynamics 365 for Finance and Operations to a BYOD Azure SQL DatabaseBring your own database (BYOD)https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/export-entities-to-your-own-database1) What kind of error pattern are you seeing?
Do the exports always fail or is the problem intermittent?
Are you seeing this across a bunch of different exports or is a specific failing very often?
Does there appear to be any date/time pattern to the export failures?
Do the failures seem to occur only for entities where a lot of data is being pushed to the BYOD Azure SQL Database?
2) How are your exports configured?
Are you exporting relatively static data or dynamics (e.g. transactional) data?
How many entities are you trying to export in one go? Might it be possible to split the workload into several more granular entities?
Are you taking full advantage of change tracking and INCREMENTAL PUSH where possible and appropriate?
Are you doing regular FULL PUSH of data that continues to grow in size each time it is exported?
3) Where is your BYOD Azure SQL Database and what service tier is it running on?
If you are pushing data to a different data centre or your BYOD service tier is “too low” resulting in throttling of deletes and inserts then that could lead to timeouts.
Single database: Storage sizes and performance levelshttps://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits-single-databases#single-database-storage-sizes-and-performance-levels
For optimal performance you should ideally be running your BYOD Azure SQL Database on a Premium service tier, i.e. P2 or higher.