BYOD, debugging, and getting creative

Today's story involves one of my favorite "integration" features, Bring Your Own Database (BYOD).  I've been working with this feature since before it went GA (and incidentally for many years after Microsoft announced that Azure Data Lakes would be replacing it), so I was shaken when it took me as long as it did to identify the root cause of the issue.

The setup: We set up an Azure SQL DB, and we configured D365 ERP to point at said DB.

The problem: When attempting to publish any entity schema to the database, we received the below error.  Not a lot to work with.  Anybody who has worked with me knows that I call the object reference error the "AX check engine light", as it could be thrown by virtually any code anywhere, so that leaves us focusing on a somewhat vague warning: "Entity %1 cannot be published".

The cause: With these limited messages, we ran out of ideas pretty quickly, so I checked out what was happening in the code.  I searched for the label "Entity %1 cannot be published", then performed a cross-reference on the references to the label to try to find the originating line.

There were two hits, one of which threw an error; since we're getting a warning, I was able to pinpoint the line prompting the warning without debugging.

However, that particular line is part of a rather large try/catch block, so I did have to debug to figure out where the triggering error was being thrown.  It turns out (perhaps unsurprisingly) that it was coming from this static method: 

, which itself depends on the actual failing method in a CLR object, which is of course a compiled resource:

.. which made this part of the exercise more or less a dead end, but also explained the vague warning we were getting in the first place (that sole catch block is supposed to catch anything that this CLR object can throw at it and spit out ... that single message!).

At this point we could've flipped the board, but instead we went back to the BYOD documentation and caught this little note, something I had encountered before, and conveniently forgot sometime before we started this whole troubleshooting exercise:

We checked the BYOD config and the DB tier, and sure enough, the config defaulted to 'Create clustered column store indexes' = YES, and we were indeed not on a SQL premium database.

The fix: We decided to turn off the clustered column store indexes, though obviously an alternative fix would be to upgrade your database.

The lesson(s) learned: Obviously the big one for me is to take better notes (i.e. this blog!) since I'd already encountered this issue previously, completely forgot the actual cause, and couldn't find much about the problem online.  I think for me another important lesson (re-)learned is that, for complex features in D365 ERP like this one, overall validation of your settings doesn't always occur where you would expect it to.  I would've thought that the check for the Azure SQL premium database would happen as soon as I hit 'validate' or saved my connection settings, but no, it happens quite a bit downstream, once I'm actually trying to publish an entity schema.

Epilogue: I have since found this helpful discussion, which I sure wish I had discovered prior to my misguided journey.

Per usual, I sincerely hope that spelling out my troubleshooting in excruciating detail helps somebody out there in the community!