In which we explore unsupported Dynamics CRM database operations

March 24, 2013 in Microsoft Dynamics CRM, CRM 2011

If you work with Dynamics CRM long enough, I can almost guarantee you will one day encounter a problem where a direct SQL insert/update/delete (DML) against your CRM database is either the only possible solution or the alternatives are so cumbersome as to make the direct SQL approach the only practical solution. (Lest any purists tell me how wrong I am, I will mention a personal experience when I needed to make 240 million updates to existing contact records as part of an initial data load, and our project was already running a few weeks behind schedule.) This is of course a problem because every good Dynamics CRM practitioner knows that running SQL against the database (except for querying filtered views and creating new indexes) is completely unsupported by Microsoft.

Emperor Palpatine This guy wants you to run DML in your CRM database

In addition to being unsupported, running DML directly against your CRM database will cause you to miss out on all sorts of Dynamics CRM goodness like triggering workflows, generating audit trails, cascading operations and so on. Even after taking all that into account, sometimes direct SQL still can seem to be the best option, so, in the interest of disclaiming any responsibility for any problems you might cause yourself, let me first say, DON'T EVER DO WHAT I AM ABOUT TO SHOW YOU. If you feel like exploring the dark side of CRM, read on.

In my opinion, unsupported operations in a Dynamics CRM database fall along a spectrum of low risk (wrap it in a transaction, and you're safe) to medium risk (if you make a mistake, you could have some minimal data corruption and need to break out the transaction log backups) to high risk (ZOMG!!!! Are you loco!?!?!?)

Here are some examples:

  1. High risk - DDL (other than indexes), any DML against systemuser records, inserts/deletes of activity records
  2. Medium risk - Inserting or deleting most kinds of records
  3. Low risk - Updating one or more records

Updates

As I mentioned above, updates are the least risky unsupported SQL operation you can run in a Dynamics CRM database. That's because CRM has already taken care of all the extra business logic related to those records when they were created, so it's not like you generally have to worry about screwing something up too badly. That being said, I would seriously counsel you to never update a systemuser record (though I did once, and my application server didn't explode). There are three main things to keep in mind with any direct SQL operations:

  1. Use a transaction.
  2. Test, test and re-test against a non-production database.
  3. Make sure you have good backups and have validated your database restore/recovery plan before you run the SQL.

Keeping those things in mind, here's a SQL update sample that updates an account record. It updates a custom field in the accountextensionbase table, and then it updates the modifiedon and modifiedby fields in the accountbase table. A transaction is used to wrap both updates so that if one fails, you can rollback the other.

BEGIN TRANSACTION  
BEGIN TRY  
UPDATE accountextensionbase SET new_effectivedate = '2009-03-01 06:00:00.000'   
WHERE accountid = 'XXXXXX'  
  
UPDATE accountbase SET modifiedon = getutcdate(), modifiedby='GUID OF USER TO SHOW MODIFIED BY'  
WHERE accountid = 'XXXXXX'  
  
COMMIT TRANSACTION  
END TRY  
  
BEGIN CATCH  
--DO SOME SORT OF ERROR HANDLING/LOGGING HERE  
ROLLBACK TRANSACTION  
END CATCH

This works on a single record, but if could easily be changed to run against multiple records by changing the predicates to something like "WHERE accountid in (SELECT accountid from filteredaccount WHERE . . . )."

Inserts and deletes (non-activity records)

I'm actually not going to provide any examples of inserts or deletes because I have found those are bit more custom than simple updates. If you want to do an insert or delete, I find the best way to prepare it is to run SQL Profiler and watch for the SQL that gets run when you do a single create/delete operation in CRM. This way you will make sure to capture the SQL embodiment of all the business logic. Once you see all the SQL that gets executed, you can modify it as necessary and wrap it in a transaction. As long as you've thoroughly tested and made sure you have good backups, you should be OK.

High-risk operations

Here is a brief summary of my thoughts on the high-risk operations. Since I generally avoid them (and you should, too), I don't have any examples or further guidance to provide.

  1. DDL is something you shouldn't do because the thing that is most likely to completely hose your system. I think I had to run DDL once back in CRM 3, but now I can't think of a legitimate reason you would need to do this.
  2. DDL for systemuser records is the thing that is second most likely to completely hose your system. When SQL potentially impacts security and record ownership/permissions in CRM, I just say no.
  3. Avoiding inserts/deletes of activity records is more of a personal preference. Messing around with those gives me a touch of the heebie-jeebies, but, in all honesty, if you follow the guidelines for other types of inserts and deletes, you're probably not going to get into trouble.
comments powered by Disqus