Friday, September 6, 2013

Solid Reasons Not To Do Direct Database (T-SQL) Operations on Dynamics CRM Database From the Pros

I got an email this morning from an old college classmate of mine in kind of a exasperated tone with a one word subject "HELP!".  It turns out that she has fellow IT folks that want to replace some updates she is doing regularly using workflow that have worked fine for a long time with nightly T-SQL batch jobs pushing the data from their data warehouse back into CRM, even though it was already in CRM to begin with.

I decided to delve into this a bit to see what are the real reasons for why this shouldn't happen.  I knew some but I also asked some of my other MVP buddies to get their feedback.  Here is what I came up with.   Feel free to use these arguments if you need to explain to your IT department why manual database operations are a bad idea.

Jamie Miley 
  • If you are using Auditing in CRM, it won't reflect any changes done by manual database update.
  • Modified, by, modified on, etc... will also not be adjusted properly
  • PrincipleObjectAccess table will not be updated based on manual updates and so permissions will not be properly set on any inserted, updated, deleted records.
Scott Sewell
  •  Database updates to CRM, if you did updates to something that affects security (owner, business unit, etc...) no updates would occur to PrincipleObjectAccess table
  • Updating Name fields will cause issues because lookups tend to cache names

Damian Sinay:
  • Plugins and/or workflows won't fire
  • Also caching is an issue, any cached data won't be invalidated by a database operation.
Gustaf Westerlund
  • If you are not 110% sure what youa re doing you are risking the stability of the entire system.
Julie Yack
  • It’s bad karma to do unsupported stuffs when a supported way will do it.
Carston Groth
  • Relations might get lost if you´re only performing the action on one datebase table ignoring all related tables
Joel Lindstrom

  • Biggest reason is that it will appear to work initially but problems will crop up later and you won’t be able to connect the dots to the real issue because the issue won’t be caught by the normal error reporting mechanisms.
Example:

Customer manually loaded contact records and later couldn’t reassign them. Turned out to be because in their manual load they didn’t populate businessunitid. The contacts worked, but couldn’t be reassigned later because that field wasn’t populated, but the error message generated didn’t explain what the problem was, because records created in a supported way always have that field populated

Different customer manually overwrote the createdby and modifiedby using unsupported T-SQL. Records initially appeared to work OK; however, when users attempted to forward the message in CRM, they got an error. Again, since this was a delayed error situation that showed up months later, it was very difficult to find the real reason for the error—all diagnostics did not show the real problem.

So to me, that is the biggest reason not to create records in an unsupported way. It is very difficult to verify that it is correct because standard system data validation does not fire, and if you miss anything, the real problem may not show up for months and will most likely be outside of the normal error reporting mechanism. You are on your own. Was it worth it?

In Conclusion

In the end, I think these are all great ideas regarding the issue.  I really just want to echo Gustaf above.  The fact is that it is considered unsupported for a reason.  Microsoft doesn't want to deal with it either.  All unsupported customizations are adding serious risk to your entire implementation as Microsoft would be entirely within their right to wipe their hands of the entire implementation when they can show that these types of things are being used.  The API and SDK are there for a reason, please use them. There are ways to do almost anything you could be trying to do that would cause you to go an unsupported route.  This is where a good partner can really steer you in the right direction.  You have already invested a lot of money on your Dynamics CRM implementation in software costs and in most cases consulting time to put it into place and get it customized properly, protect that investment!

- I hope this helps!

-

7 comments:

  1. Jamie, Hi from Donaldson just down the street. To me this all sounds like good practice but we have set up the following rules for direct SQL:
    1.No Inserts or Deletes or rows
    2.Updates on custom text fields only.

    It has been my experience that Microsoft sees unsupported things in almost every enterprise implementation and would not worry about simple updates as such.

    Am I missing something?

    ReplyDelete
    Replies
    1. Hello Mark,

      Caching can be a problem on fields that are cached by the application. You won't likely run into that issue with custom fields. But you won't get auditing.

      I also have heard of Microsoft basically wiping their hands of unsupported implementations and saying they had to stand up a new org and migrate their data before they would help with anything.

      In my mind the idea of an "innocent" unsupported customization would be something like hiding a left nav section using jscript in the on-load.

      It is just a bad idea in general when an SDK is provided that allows you to do these things in a supported manner. This sounds like a situation where you need a good partner (like RBA ;) ) that can help you with these things.

      Alicia has my contact information if you would like to talk sometime.

      Thanks!

      Jamie

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Hi Mark/Jamie,

    The Dynamics team have put in so much work under the covers over the last year on batch and accepting parallel execution that we regularly see the API outperform most direct SQL hacks. We see 600+ records per second, update, insert and delete. If you have a look at this video it illustrates the difference using batch + parallel (50 rows per sec and 700 per sec) and this was Dynamics CRM Online! http://player.vimeo.com/video/74729381 at 3:10

    ReplyDelete
  4. Just to add to the list is that Microsoft can decide at any point to change the database schema in ways that may very well break any unsupported hacks. This is most clearly evidenced in the upcoming CRM 2013 release in which, from what I understand, CRM will no longer use the ExtensionBase tables, likely breaking a whole lot of SQL hacks out there. In short, one may think it's a great idea at the time, but will most certainly regret it later!

    ReplyDelete
  5. I have manually modified custom string fields countless times without any ramifications. I think it's relatively harmless for fields like custom strings and booleans (so far that I have tested).

    As you know nothing is ideal and there will always be instances in the workplace where a process causes data that needs to be updated via SQL (either by a failed process or someone screwing up something). In my case this did happen and the quickest and most easiest way was to update the data via SQL

    However, I wouldn't dare insert records manually though as things really messy..

    ReplyDelete
  6. Hi All Gurus,
    From above discussion can I safely assume that creating custom view and store proc are OK as long as its only reading data from CRM and not create/update/delete operations.

    ReplyDelete