Easily set up Dynamics 365 CE \ CRM replication (incremental) to Azure SQL / SQL On-Premise using Skyvia’s Data Integration services

In the earlier post, we saw how we can use Skyvia’s query feature to execute SQL command against our Dynamics 365 CE

https://nishantrana.me/2019/10/25/execute-sql-select-insert-update-and-delete-command-on-dynamics-365-customer-engagement-data-using-skyvia-query/

In this post, we will cover how we can use Skyvia’s replication feature to create a copy of our cloud data i.e. Dynamics 365 Online data in a relational database like SQL Server and most interestingly keep it up to date automatically with basic configuration steps.

Creating replication of cloud data can be useful in different data integration scenarios and most importantly for reporting and data analysis need as with online we are limited with the use of Fetch XML.

The Skyvia’s replication service takes care of syncing Create, Update and Delete operation performed on Dynamics 365 Entity records with the destination SQL Database.

For this we need to Skyvia expects an object’s key as well as modifiedon, createdon fields to be included for replication.

For delete to work it expects, audit history to be enabled on that Entity.

In this post, we will take a simple example of replicating Contact entity records from Dynamics 365 Online to SQL Azure.

Login to Skyvia

https://app.skyvia.com/#/packages

Create the connections

Source Dynamics CRM Connection:

Destination SQL Azure Connection:

We have a database named SampleDB already created in SQL Azure.

With connections created, now let us create a new package of type replication

For the replication, we have specified source connection as the CRM connection and destination as the SQL Azure.

We have selected the option of Incremental Updates along with Create Tables and Drop Tables option which will delete if there is an existing table of the same name as the entity and will create a new one.

In the Select Objects section, we have a specified Contact entity. We can also select multiple entities for replication.

Edit task option in the selected entity let us specify the fields and filter criteria on the source.

We also have the option of scheduling that we will look at in the end of the post.

Save the package and click on Run to see it in action.

While it is running, the Run History section shows the details

It auto refreshes with all the details.

We can see the 228 records of contact records replicated in SQL Azure.

Now let us create a new contact and run the package again.

Here we have created a contact record as shown below in CRM.

On running the package, we can see that it has added a new record in the Run History

Back in SQL Azure also our count has increased to 229.

Now let us update one of the records in the source and run the package. The run history shows one of the records being updated.

Finally, let us delete a couple of records (9) from the source and run the package. (We need to make sure we have enabled Audit on Contact entity for delete to work)

Back in SQL Azure, we can see total record count decreased

Here we were running the package manually, however, in real-world scenarios we would be scheduling it.

For this, we can edit our package and navigate to the Schedule section and click on Set Schedule.

For e.g. we have scheduled here it to run every hour starting from 6 p.m. till 10 a.m.

On save, we can check the run history

Thus, we saw how easy it is to replicate the data from Dynamics 365 CE to our SQL Database using Skyvia’s Integration service.

To achieve the same using Scribe Online Replication Service

https://nishantrana.me/2019/01/09/using-scribe-online-replication-services-rs-to-replicate-dynamics-ce-data/

and KingswaySoft 

http://www.kingswaysoft.com/blog/2017/04/04/How-to-Retrieve-Incremental-Changes-from-CRM-in-your-ETL-Process

Hope it helps..

Single Instance / Organization Considerations in Dynamics 365 Customer Engagement / Dynamics CRM

Let us start from the scenario that the enterprise has decided to use Microsoft Dynamics CRM /365 CE as a preferred customer relationship solution for their organization.

For a large enterprise and/or having multiple different departments, the first question that comes up is, do they need to have a single instance or organization or multiple instance or organization. Well, that decision is not that easy but one thing is for sure the decision should be made as early as possible in the project.

Well you could refer to these posts that could help us to make the decision

https://medium.com/capgemini-dynamics-365-team/does-enterprise-scale-dynamics-require-multiple-instances-f1931a443930

https://nishantrana.me/2019/07/23/single-organization-vs-multiple-organization-single-instance-vs-multiple-instance-key-points-to-consider-in-dynamics-365-ce-on-premise/

Now suppose we have made the decision to go with the single instance \ organization, so what are some of the key points we need to consider.

There could be multiple ways of implementing and defining the segregation layer as well as what needs to be shared from data and customization perspective among the different business units, the most important being the security model and the business unit structure being one of the key components of it.

Check out the below Webinar –

http://www.xrmcoaches.com/2017/07/free-webinar-friday-creating-your-dynamics-365-security-model/

also listen to the below podcast to get some guidance around the setting up the security model

http://crmmvppodcast.com/episode-19-setting-up-your-security-architecture-like-a-crm-mvp

In one of our projects, the way we segregated different departments, after defining the appropriate business unit structure, was by having the Business Unit lookup (custom field) in all the common entities that were being shared by the departments and populating that field accordingly, every time record is created either through User Interface or through code and to refer the same field in the customizations (plugin, workflow etc.) as and when applicable.

In the other project, we were referring to the Business Unit lookup field in the System User / Team Entity.

Well there could be multiple ways of doing the same with its own pros and cons.

But what if you have already implemented for one of the units and didn’t consider that the same instance would need to be extended to accommodate the different business unit(s).

Well, then it is time to modify a couple of things to make the Business Unit structure scalable (if it is not).

Let us go through the each of the main components one by one and how they can be segregated among different business units.

Wrong root business unit name?

Set Parent Business Unit lookup field as optional, open the Business Unit record and rename it.

Need to rename the organization?

For Online à

Navigate to Admin Portal, select the instance and click on edit and update the organization name and if required the URL as well as shown below.


For On-Premise à

  • Follow the below steps to update the Display Name

https://blogs.msdn.microsoft.com/darrenliu/2013/09/21/change-crm-organization-display-name/

  • Updating Organization Database Name

https://blogs.technet.microsoft.com/crmbipl/2008/01/14/how-to-change-crm-4-0-organizations-database-name/

Forms

Create separate new Main forms for different business units.

Enable appropriate security roles for the Form.

Views

Fields

Reuse the fields that are common instead of ending up creating a large number of fields.

Field security profiles can also be used if required when certain entity’s records are shared across the business unit and we want to hide the sensitive information.

Business Rules

Business rule can be configured to run on the scope of Form instead of the Entity.

Dashboards

For Dashboards, we can enable the security roles.

Workflows

  • Use scope fields to make sure the workflows fire appropriately.
  • Or put the first step to check for which business unit it is firing (depends on the segregation logic implemented)

Plugins

Have a field in the records to identify to which Business Unit it belongs to and check for this field as the first thing in the plugin or check for the Business Unit of the user under whose context the plugin is getting fired or use the secure \ unsecure configuration etc. it purely depends upon the way the segregation logic has been implemented.

SLAs

Use SLA lookup field to apply the SLA dynamically through plugin or workflow.

https://nishantrana.me/2019/07/24/apply-sla-choosing-the-sla-in-the-sla-lookup-field-for-the-sla-enabled-entities-in-dynamics-365-ce/

Business Process Flow

If there are multiple business process flow, make sure appropriate one is applied

Apply business process flow while creating an entity record.

https://www.powerobjects.com/blog/2019/04/24/business-process-flows-switch-bpf-dynamics-365-javascript/

There would be multiple other key points to be considered apart from the one listed above specific to each implementation; the above points are some of the things that can be taken as guidelines.

Lastly we must consider  creating different apps for different department \ teams\ users and add required solution components to it.

Design custom business apps by using the app designer

Please feel free to add comments and share your thoughts.

Hope it helps..

Generate Dynamics 365 CE (CRM) Business Unit Org Chart through SmartDraw

Recently we were looking at the options of generating a Business Unit Org Chart for quick review.

Using SmartDraw we were able to do achieve it very quickly.

Steps

  • Keep only the Name and Parent Business column and delete all the other columns including the hidden ones.

  • Excel with required columns.

  • Create the trial account of SmartDraw cloud

  • Click on Org Charts from the New Document menu.

  • Select Build Org Chart from Data option

  • Browse and select the xls file.

  • Click on Process.

  • Select Show in Shape for the columns and click on the Build diagram.

  • The result

  • The other option is to select any of the Organizational Charts

  • Select Import from file

  • Select the excel file

  • The result

  • The chart can be exported, shared and print.

Thus SmartDraw makes it very easy to generate the Org Chart for the Business Unit.

To achieve the same through Visio

http://www.planetxrm.com/12-steps-to-creating-crm-business-unit-org-charts/

Hope it helps..

Fixed – Workflows not firing “Waiting For Resources” in Dynamics 365 On-Premise

Recently in one of our environments, we started getting the issue that all our workflows were getting stuck at “Waiting for resources”.

As usual first step was to check if Microsoft Dynamics 365 Asynchronous Processing Service is running it or not.

The service was disabled, so we enabled it. But still, it didn’t fix the issue.

Next thing we tried was as suggested in the below article

https://www.powerobjects.com/blog/2013/03/28/how-to-fix-a-crm-workflow-stuck-in-progress/

The value for AsyncSdkRootDomain was blank which we updated followed by a restart of the service. Still no luck.

Eventually, we came to know that Servers setting for the roles Full Server were in the disabled state inside the Deployment Manager for the organization.

The reason CRM was still working because these services (including IIS Web Site) were already manually started earlier, however, the status was still disabled for this setting within Deployment Manager. (These settings were done by the network team who were planning to create the replica of these machines, and they wanted to disable the applications and our CRM technical team was unaware of it.)

Enabling it fixed the issue for it.

Hope it helps..

You do not have the necessary privileges to import security roles. Microsoft.Crm.CrmSecurityException: SecLib::CheckPrivilege failed while importing solution in Dynamics 365 CE

Recently after upgrade of our Dynamics 365 CE from 8.2 to 9.1, when the System Administrator tried importing the solution (managed), he got the below error

You do not have the necessary privileges to import security roles. : Microsoft.Crm.CrmSecurityException: SecLib::CheckPrivilege failed. User: e7282250-7d82-e711-80df-1458d0431690, PrivilegeName: prvWriteRole, PrivilegeId: bd123e14-17ba-40f6-8d8b-18f4bffa7e50, Required

The error message is quite weird, first as we have never got this error earlier and here it was System Administrator user who was importing.

We tried importing the solution with the using another System Adminstrator account and it worked for that other user.

Hope it helps..

The workflow cannot be published or unpublished by someone who is not its owner error while importing solution in Dynamics 365 CE

Recently after upgrade of our Dynamics 365 CE from 8.2 to 9.1, when the System Administrator tried importing the solution (managed), he got the below error

The workflow cannot be published or unpublished by someone who is not its owner.

Error while importing workflow {ec05f40d-fd14-46ab-96b3-be1f5b108ef3} type Workflow name Marketing List – Update CT Relationship: The workflow cannot be published or unpublished by someone who is not its owner.

What the error says is that the user who is importing the solution needs to be the owner of the workflow that is there in that solution.

http://gonzaloruizcrm.blogspot.com/2011/05/processesworkflow-ownership-faqs.html

https://stackoverflow.com/questions/17159920/crm-2011-ru13-the-workflow-cannot-be-published-or-unpublished-by-someone-who-is

The thing was that we have never got this issue earlier for that System Admin user and we would have imported the solution at least 10 times earlier as well. This time the only thing different was that it was in the newer version 9.1

Changing the owner of the workflow and assigning it to the one who is importing will fix this issue. However interestingly and as expected when we tried importing the same solution using a different system administrator (again not the owner of the workflow in the target system), the import was successful with warning.

“The original workflow definition has been deactivated and replaced”

Read the below post to understand how workflows are managed internally

https://www.inogic.com/blog/2010/11/workflows-behind-the-scene/

Please share your thoughts, if someone has faced this issue recently and how they managed to fix this?

And what caused the issue to the first system admin and worked for the other.

Sample Code to retrieve instances using Online Management API in Dynamics 365 Customer Engagement

With version 9.0 of Dynamics 365 CE we now have Online Admin API that supports the following operations like Create, Retrieve, Delete, Backup and Restore instances. The user needs to have the Global Administrator or Service Administrator role in the Office 365 tenant to perform these operations.

As a first step we need to register the application with Azure Active Directory.

https://nishantrana.me/2016/11/13/register-a-dynamics-365-app-with-azure-active-directory/

and get the values for

Client Id and Authority.

Get the Service URL here

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/online-management-api/get-started-online-management-api#service-url

Sharing a sample code which can be used to retrieve all the instances in a specific tenant.

The sample code: –


public class MySampleApp
{

private const string Resource = "https://admin.services.crm.dynamics.com/";

// username and password
private const string UserName = "username.onmicrosoft.com";
private const string Password = "password";

// client id and authority of the application registered
private const string ClientId = "b2c5028d-57e6-4df7-9940-3243214948b";
private const string Authority = "https://login.microsoftonline.com/3432432e2-5fbe-4b78-a55a-bcb342d4f859/";

private static AuthenticationResult _authResult;

private static void Main(string[] args)
{
var authContext = new AuthenticationContext(Authority, false);
var credentials = new UserCredential(UserName, Password);

// Get the token
_authResult = authContext.AcquireToken(Resource, ClientId, credentials);

Task.WaitAll(Task.Run(async () => await GetInstances()));
}

private static async Task GetInstances()
{
var httpClient = new HttpClient
{
BaseAddress = new Uri(Resource),
Timeout = new TimeSpan(0, 2, 0)
};

httpClient.DefaultRequestHeaders.Add("OData-MaxVersion", "4.0");
httpClient.DefaultRequestHeaders.Add("OData-Version", "4.0");
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer",
_authResult.AccessToken);

var retrieveResponse =
await httpClient.GetAsync("/api/v1/instances");

if (retrieveResponse.IsSuccessStatusCode)
{
var jRetrieveResponse =
retrieveResponse.Content.ReadAsStringAsync().Result;

var result = JArray.Parse(jRetrieveResponse);
foreach (var data in result)
Console.Write("Id " + data["Id"] + "\nUnique Name " + data["UniqueName"]);
}
}
}

Result: –

The other properties: –

SiteMapName in the AppModuleSiteMap is null or empty error while importing V9 Solution in Dynamics 365 Customer Engagement

We recently upgraded our Dev and Test environment to V 9.0.

Dev

Test

While importing a solution from Dev to Test which had SiteMap we got the below issue.

“The SiteMapName in the AppModuleSiteMap is null or empty”

It turns out that the following tags were required and were missing in the Customization.xml

We added that tag and were able to import the solution successfully.

Interestingly if we export the same solution from Test and search for this tag, it is again missing, however, we can see the following XML section added there

This section was not there in dev’s sitemap.

Hope it helps..

Different Updating Instance status during Dynamics 365 Upgrade

The day has come when we had scheduled the upgrade of our Dynamics 365 instance Test Instance(from 8.2 to 9.0).

We can see the below message during the scheduled time à “The update will being within 24 hours. No rescheduling is available”.

The following Reschedule Update option will go missing during the scheduled time.

After waiting for few minutes, we see the Update Status as Queue (Not Started)

Followed by the status as “Database Upgrade”

In between the Status message will be Backup and Restore.

https://docs.microsoft.com/en-in/dynamics365/customer-engagement/admin/manage-updates#update-status

Then it kept interchanging the status from Updating Instance (with no status) to Updating Instance – Database Upgrade for close to 2.5 hours.

Then although the status was Updating Instance the Open link got enabled after 2.5 hours.

Which opened the following notification

And finally, after 10 minutes or so à

We were all set with version 9.1

In total it took around 3 hours approximately.

Time to enjoy the successful upgrade and explore the new features ..

 

Plugin on Pre-Validation Stage in Dynamics 365 CE

Recently we had a requirement to delete the Account record without deleting the associated Contact records.

If we try deleting the account record we’d get the following message box

The relationship definition can’t be updated as well to achieve this

So, we wrote a plugin on the pre-validation stage of pre-delete event of Account, which will retrieve and loop through all the child contact records and set its parent customer field as null.

In case of pre-operation the child records were not available.


public void Execute(IServiceProvider serviceProvider)
{
ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
IPluginExecutionContext pluginContext = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService organizationService = serviceFactory.CreateOrganizationService(pluginContext.UserId);
EntityReference targetEntity = (EntityReference)pluginContext.InputParameters["Target"];
QueryExpression getContacts = new QueryExpression("contact");
getContacts.Criteria.AddCondition(new ConditionExpression("parentcustomerid", ConditionOperator.Equal, targetEntity.Id));EntityCollection allContacts = organizationService.RetrieveMultiple(getContacts);foreach (Entity contact in allContacts.Entities)
{
Entity contactToBeUpdated = new Entity("contact");
contactToBeUpdated.Id = contact.Id;
contactToBeUpdated.Attributes["parentcustomerid"] = null;
organizationService.Update(contactToBeUpdated);
}}

 

Another practical scenario

https://www.inogic.com/blog/2017/03/plugin-pre-validation-operation-to-show-an-error-message-as-well-as-log-the-error/

Hope it helps..