2

I need to one-way-synchronize external Data with CRM on a regular basis (i.e. nightly).

This includes creating new Records as well as updating existing ones.

That means, I have to keep track of IDs of CRM-Entities created by my synchronization process.

emphasized textI already managed to create and update records in CRM from lines of database-tables so this is not a problem.

Currently, my mapped tables have the following columns

  • id: The tables primary key set when inserting a new row
  • new_myentityid: Primary Attribute of the mapped entity, set after the record was created by the synchronization process
  • new_name etc.: the values of the records attributes

However, I see a way to drastically simplify the whole process:

Instead of having a PrimaryKey (id) in the database and keeping track of the CRM ID (new_myentityid) in a seperate column, I could as well get rid of the id-columns and make the CRM-ID-Column (new_myentityid) primary key of the table and set it when inserting new records (newid()), so basically substitute id with new_myentityid from a database perspective. I could then bulk-upsert via ExecuteMultipleRequest in combination with UpsertRequest.

This way, I would save a column in each mapped table as well as logic to store the CRM IDs after creating them.

Question

Would this be acceptable or is there anything that should make me avoid this?

nozzleman
  • 9,529
  • 4
  • 37
  • 58

4 Answers4

6

Disclaimer: I'm not aware of a best practice for this so this is just my personal opinion on the matter having developed for Dynamics several times.

I think that using the CRM Entity GUID for your primary key is a good idea. It's less complicated and is handled well in SQL. I assume the column in your database is uniqueidentifier.

My only comment is to not generate the GUIDs yourself. Let CRM generate them for you as it does a better job at keeping everything sequential and indexed.

See this blog entry on MSDN for further detail

Equalsk
  • 7,954
  • 2
  • 41
  • 67
  • Yeah, the its of type `uniqueidentifier`, however, in sql I can also create sequential guid by using (`NEWSEQUENTIALID()`) which is what i do for the id column too, i guess crm does the same – nozzleman Jan 23 '17 at 10:21
  • Yes but they would be sequential only to your synchronization database, not in the greater context of the CRM database which is where it's important. It's hard to say what kind of performance increases you'd see if any, I suppose it depends on whether we're talking millions of rows here. The way I would put it is: "Do you trust yourself to do a better job of creating and indexing GUIDs than Microsoft?" ;-) – Equalsk Jan 23 '17 at 10:25
  • I see, you`re right with that. basically its a compromise of performance vs dev-experience when mapping relations and such. However, the link you provided as well as you statement answers my question so i am going to accept that. Thx! – nozzleman Jan 23 '17 at 10:27
3

I'm probably a little late to this discussion but just wanted to add my tuppence worth.

There is nothing inherently wrong with specifying the GUID when creating a new record in CRM, and this behaviour is explicitly supported by the SDK.

A common real life scenario is when creating records by script; it is useful to have the same GUID for an entity in Dev, Test and Production environments (Admittedly we normally use the GUID auto generated in Dev).

The reason that it is considered best practice to allow CRM generate its own GUID (https://msdn.microsoft.com/en-us/library/gg509027.aspx) is that CRM will generate the GUID sequentially. Using newid() generates a statistically random GUID. This has a performance impact on SQL server around index maintenance. This thread provides some insight: What are the performance improvement of Sequential Guid over standard Guid?

But basically specifying your own GUID can cause the underlying SQL INSERT statement to become more expensive. Read and Update operations should remain the same.

If you are generating you own GUIDs is SQL you can always use NEWSEQUENTIALID (https://msdn.microsoft.com/en-us/library/ms189786.aspx) for a sequentially generated GUIDs.

Community
  • 1
  • 1
Malachy
  • 307
  • 8
  • 16
0

Hi previous posts cover this well. Just to note that if you did go with generating GUIDs outside of CRM you could mitigate against the potential performance impact (INSERTS) simply by running a weekly Maintenance plan to refresh the clustered indices directly on the SQL database(s) this would I believe ensure that GUIDs were ordered sequentially. In any case, CRM/API will always be the bottleneck, so best to do things in the way that the platform expects to avoid issues later on.

Antony
  • 51
  • 2
-2

Why not save in new table?

likes origin exist a table named "customer",and your new data save in "customer_update",the field as same as the origin table.

it's will help you in future.maybe you want have a look the data's orgin.

Du Jianyu
  • 61
  • 6
  • Thanks for the response, but there is no need for that, because I have a leading data-source. I want to simplify the process. Changes from leading datasource should override CRM Data. Also, this doesn't answer the actual question. – nozzleman Jan 23 '17 at 10:07