Tag Archives: Dynamics

Microsoft Dynamic 365 Data Export Service: Duplicating and Syncing SQL database in Azure

One of the most common objections clients express against migrating from Microsoft Dynamics 365 on-premise to Microsoft Dynamics 365 online is that they will loose access to their Dynamics 365 SQL database as it will be hosted by Microsoft and they cannot directly access their SQL database. In this post, we will cover how we can duplicate and sync our Dynamics 365 online SQL database in Microsoft Azure, and still access the data in the Dynamics 365 SQL database tables. The Microsoft Dynamic 365 Data Export Service allows us to duplicate and sync the Dynamics 365 online SQL database in Azure.

High Level Steps

To successfully implement the Microsoft Dynamic 365 Data Export Service and replicate what has been done in this blog post, you will need carry out the following steps (explained in detail below):

Have an Azure instance and a Dynamics 365/CRM Online 8.1 or later instance
User must be a Dynamics 365 System Administrator
Link Azure Active Directory to Office 365
Create a SQL Database in Azure
Access Azure SQL Database using Microsoft SQL Server Management Studio
Create a database user with correct permissions
Install the Azure PowerShell module
Install Data Export Service solution
Create an Azure Key Vault
Enable tracking on custom entities you want to sync
Create a Dynamics 365 Export Profile
Microsoft SQL Server Management Studio Validation

Detailed Steps

1. Have an Azure instance and a Dynamics CRM Online 8.1 or later instance

If you do not have a Dynamics 365 and an Azure instance, you can go Office 365 and get an Office 365 subscriptions with a Dynamics 365 instance and an Azure instance. If you would like to try out the technology using trial licences, you can get a trial Office 365 license, which has a trial Dynamics 365 instance and a trial Azure instance by following the following instructions: Microsoft Dynamics 365: How to Set Up a Free Online Trial Version.

2. User must be a Dynamics 365 System Administrator

To create Export Profiles in Dynamics 365 and implement the steps in this blog post, the user must be assigned a System Administrator security role. The Export Profiles are configurations, in Dynamics 365, that allow you to define Dynamics 365 SQL database duplicating and syncing relationship to Azure i.e. the entities and relationships to be be synced and duplicated.

3. Link Azure Active Directory to Office 365

If your Azure instance and Dynamics 365 are in the same Office 365 organization or if you used the trial license option provided in Step 1 above, the Azure Active Directory will already be linked to Office 365. Microsoft also offers you the option of linking an Azure Active Directory, of another Office Organization, to your Microsoft Office organization.

After the Azure Active Directory has been linked to Office 365, you can see the Office 365 users (that you can find under Active Users in the Microsoft Office 365 Admin Center) in Azure Active Directory. In Office 365:

In Azure Active Directory:

4. Create a SQL Database in Azure

Go to Azure Portal for the account described in Step 3. Click on “SQL databases” option and you get a window similar to the one below. Click on “+ Add” as shown by the arrow below.

You will be presented with a window similar to the one below. You have to provide:

Subscription: the Azure subscription under which you will be creating the database.
Resource Group: If you do not have one or prefer to create another, you can can create one from the window below.
Database Name: The name of the database you will be creating. You cannot have 2 databases under the same name.
Server: Azure server name must be unique. If you do not have one or prefer to create another, you can can create one from the window below. There is a cost associated with the creation of a server.
SQL Elastic Pool: If you choose Yes, the storage resources will be adjusted based on need.
Compute + Storage: You can choose the SQL service tier and performance according to your needs.

After you click on “Review + create”, you will be taken to a Review page where you see the options you have selected before you finally create your SQL database. After you click on “Create”, Azure will proceed with the creation an SQL database immediately.

After the process is done, you will be able to see your new SQL database under the list of SQL Databases in Azure. At the time of writing, the process takes less than 2 minutes.

5. Access Azure SQL Database using Microsoft SQL Server Management Studio

If you do not have Microsoft SQL Server Management Studio already installed, download and install it. It is free. Connect to the Azure SQL Database created in step 4, using your credentials (login/username and password) of the Azure Server hosting your SQL database:

You may also be prompted to enter your Office 365 credentials. After entering the required information, you will able to see the access Azure SQL Database created in step 4 in Microsoft SQL Server Management Studio.

6. Create a database user with correct permissions

The user created in this step is a service account that will be used in Microsoft Dynamic 365 Data Export Service, integrating Dynamics 365 with Azure. The expanded view of Microsoft SQL Server Management Studio, shown in Step 5 will look similar to the image below:

(1 in image) is the Azure database server we connected to in Step 5 above, containing the SQL database we created in Step 4. (2 in image) is the Azure database we created in Step 4. To create the a user in a specific database, e.g. “adventure” in the image above, you need to create the user in the master database and then proceed to add that user to a specific database. Here are the specific steps:

Run the following script against the master database (3 in image):

CREATE USER [<userName>] WITH password='<userPassword>';

Example:

CREATE USER [d365exporter] WITH password='ItsFascinating2020';
Refresh the Azure database server connection (1 in image ).
Navigate to master database’s Users (4 in image) and Logins (5 in image). You should see the user you created in created in part (b), in both places.
If you see the user under master database’s Users (4 in image) but not under the Logins (5), run the following script against the Logins (5 in image):

CREATE LOGIN <userName> WITH PASSWORD = '<userPassword>'

Example:

CREATE LOGIN d365exporter WITH PASSWORD = ' ItsFascinating2020'

Refresh the Logins (5 in image) and you will be able to see the user you created under the Logins (5 in image) as well.
Add the user we created in part (d), with the appropriate permissions, to the Azure database (2 in image) by running the following script against the database:

CREATE USER [<userName>] FROM LOGIN [<userName>];
EXEC sp_addrolemember 'db_owner', '<userName>';

Example:

CREATE USER [d365exporter] FROM LOGIN [d365exporter];
EXEC sp_addrolemember 'db_owner', 'd365exporter';

Refresh the database Users (6 in image) and you will be able to see the user under the list of database users.

7. Install the Azure PowerShell module

If you have not already installed the Azure PowerShell module on your machine, you will need to install it before proceeding to Step 8, where we will create the Azure Key Vault. The step-by-step instructions for installing the Azure PowerShell module can be found on the Microsoft website: Install Azure PowerShell.

8. Install Data Export Service solution

From your Dynamics 365 online instance, download and install the Data Export Service solution from the Microsoft App Source. Here the steps to follow:

Login into your Dynamics 365 online instance
Navigate to Settings >> Microsoft AppSource
In Microsoft AppSource, get the “Microsoft Dynamics 365 – Data Export Service”
Provide the required information and agree to the terms and conditions. The Microsoft Dynamics 365 – Data Export Service will be installed into your Dynamics 365 online instance as a managed solution.
After the installation, you will be able to access the Data Export Service via the web interface: Settings >> Data Export.

9. Create an Azure Key Vault

The Azure key vault is where we will store the credentials of the service account user created in Step 6. Therefore, when the Dynamics 365 Export Service is running, it will securely retrieve these credentials from the key vault to login into the Azure SQL database server and carry out the duplicating and syncing operations. The Azure web interface allows us to create a key vault. However, in this example, we will use the PowerShell script provided by the Dynamics 365 Export Service. The PowerShell script also enables you to provision a secret in the Azure Key Vault. Execute script from the PowerShell command line after replacing the placeholders below. Where to find the information needed to replace the script’s placeholders:

subscriptionId – From the Azure SQL Database created in step 4.
keyvaultName – If you do not not have a Key Vault, enter a name. A key vault will be created under that name, e.g. ‘D365Exp”, after you run the script.
secretName – The key that will be used to look up the secret. You can enter a name of your choice e.g. ‘D365DataExport’.
resourceGroupName – From the Azure SQL Database created in step 4 e.g. ‘D365’.
location – From the Azure SQL Database created in step 4 e.g. ‘East US’.
connectionString – From the Azure SQL Database created in step 4, you view the different formats of the connection string. Take the ADO.NET version of connection string (replace the username and password with credentials of the service account created in step 6).
organizationIdList – In Dynamics 365, navigate to Settings >> Customizations >> Developer Resources >> ID.
tenantId – In Azure, navigate to Azure Active Directory >> Tenant ID.

Here is the script to run in PowerShell after replacing the placeholder values:

----PLACEHOLDER------------------------------------------------------------------
 $subscriptionId = '[Specifies the Azure subscription to which the Key Vault belongs.]'
 $keyvaultName = '[Specifies the name of the Key Vault. If the Key Vault does not exist, the script will create one]'
 $secretName = '[Specifies the name of the secret that is put into the Key Vault. The secret holds the destination database connection string.]'
 $resourceGroupName = '[Specifies the Resource Group for the Key Vault.]'
 $location = '[Specifies the Azure region where the Resource Group and Key Vault is placed.]'
 $connectionString = '[Specifies the destination database connection string that would be placed as a secret in the Key Vault.]'
 $organizationIdList = '[Specifies a comma separated list of all the CRM Organization Id which will be allowed to export data to the destination database.]'
 $tenantId = '[Specifies the Azure Active Directory Tenant Id to which all the specified CRM Organizations belong to.]'
 --------------------------------------------------------------------------------
 Login to Azure account, select Subscription and tenant Id
 Login-AzureRmAccount
 Set-AzureRmContext -TenantId $tenantId -SubscriptionId $subscriptionId
 Create new resource group if not exists.
 $rgAvail = Get-AzureRmResourceGroup -Name $resourceGroupName -Location $location -ErrorAction SilentlyContinue
 if(!$rgAvail){
     New-AzureRmResourceGroup -Name $resourceGroupName -Location $location
 }
 Create new key vault if not exists.
 $kvAvail = Get-AzureRmKeyVault -VaultName $keyvaultName -ResourceGroupName $resourceGroupName -ErrorAction SilentlyContinue
 if(!$kvAvail){
     New-AzureRmKeyVault -VaultName $keyvaultName -ResourceGroupName $resourceGroupName -Location $location
     # Wait few seconds for DNS entry to propagate
     Start-Sleep -Seconds 15
 }
 Create tags to store allowed set of Organizations.
 $secretTags = @{}
 foreach ($orgId in $organizationIdList.Split(',')) {
     $secretTags.Add($orgId.Trim(), $tenantId)
 }
 Add or update a secret to key vault.
 $secretVaule = ConvertTo-SecureString $connectionString -AsPlainText -Force
 $secret = Set-AzureKeyVaultSecret -VaultName $keyvaultName -Name $secretName -SecretValue $secretVaule -Tags $secretTags
 Authorize application to access key vault.
 $servicePrincipal = 'b861dbcc-a7ef-4219-a005-0e4de4ea7dcf'
 Set-AzureRmKeyVaultAccessPolicy -VaultName $keyvaultName -ServicePrincipalName $servicePrincipal -PermissionsToSecrets get
 Display secret url.
 Write-Host "Connection Key Vault URL is " $secret.id.TrimEnd($secret.Version)

After running the script, you can navigate to your Azure instance Key Vaults and view the key vault as well as the secret that was created by running script above. It will look similar to the image below:

10. Enable tracking on custom entities you want to sync

Out of the box entities are enabled for tracking. However, custom entities have to enabled for Change Tracking, if you would like to add them to the Dynamic 365 Data Export Service and have the the Dynamics 365 SQL database changes to be synced to the Azure automatically. To enable tracking for a Dynamics 365 custom entity:

In your Dynamics 365 instance, navigate to Settings >> Solutions
Open the solution containing the custom entity. In the solution, navigate to the Entities section. Select the entity’s General tab.
Enable Change Tracking
Save and publish the solution

11. Create a Dynamics 365 Export Profile

Dynamics 365 Export Profiles allow you to make the connection between your Dynamics 365 instance and your Azure instance. You have to provide the Azure SQL Database connection Key Vault URL, and the Dynamics 365 entities and relationships to be added to the Dynamics 365 Export Service, among other details.

To create an Export Profile in your Dynamics 365 instance, navigate to: Settings >> Data Export. Click on “+New” and you will get a window similar to:

To get the “Key Vault URL”, go back to the Key Vault created in Step 9, click on Secrets. Keep drilling down until you find the “Secret Identifier”. Copy that field paste into the “Key Vault URL” field, on the Export Profile.

Click on “Validate”. After a successful validation, click Next to select the entities and relationships you would like to duplicate in your Azure SQL database, and then complete the creation of your Profile. This may take a few minutes depending on the amount of data being duplicated.

12. Microsoft SQL Server Management Studio Validation

After the process is complete in Step 11, you can return to Microsoft SQL Server Management Studio to see the tables containing the entities you selected in Step 11 (refresh your Azure SQL Server if you had Microsoft SQL Server Management Studio open when you were running Step 11). In my case, I selected 4 out of the box entities in Step 11: Account, Incident, Contact and Product. We now run queries against those tables like any other SQL database.

Every time we add a new record or update an existing record, in Dynamics 365, for one the entities added to the Export Profile, within a few seconds those changes will be synced from the Dynamics 365 SQL table into our SQL database in Azure.

Microsoft Dynamics 365: Varying Form Behavior On Form-type and Save-mode

Microsoft Dynamics 365 offers the ability to vary the behavior of forms based on the form type and how the form is saved, using JavaScript. In this blog post, we cover how we can make use of this flexibility offered by the platform to deliver unique solutions for end users.

Summary of the key syntax

Here is a high-level summary of the main Microsoft Dynamics 365 JavaScript syntax covered in this blog post.

Getting the type of form:
formContext.ui.getFormType();
Hide a tab on a form (where “TabName” represents the name of the tab you would like to hide):
formContext.ui.tabs.get("TabName").setVisible(false);
Get the object with methods to manage the Save event:
executionContext.getEventArgs();
Get the form Save Mode:
executionContext.getEventArgs().getSaveMode();
Inhibit the default save behavior on a form
executionContext.getEventArgs().preventDefault();
Check if the default save behavior was inhibited
executionContext.getEventArgs().isDefaultPrevented();
Get the form context
executionContext.getFormContext();

Form Type

To get the Form Type, you use the following JavaScript code:

formContext.ui.getFormType();

Here are the different Dynamics 365 form types and their respective JavaScript return values:

Form type	Return Value
Undefined	0
Create	1
Update	2
Read Only	3
Disabled	4
Bulk Edit	6

Application Example: Varying form behavior on Form Type

In this application example, we will hide the Products and Services tab on the Create type form (with JavaScript Return Value = 1). However, it will be still be visible on all other applicable form types. This prevents users from adding information to the Products and Services tab before a Contact record has been created and saved. Therefore, the user must first create the Contact record (using the Create form type) and after the record has been created, the users will be presented with the Update form type (with JavaScript Return Value = 2) and will be able to access the Products and Services tab as well as its contents.

The Contact entity’s create form type will look similar to this (i.e. the Products and Services tab is hidden):

In contrast, the Update form type still shows the Products and Services tab.

Here is the code to executes the requirement above.

//Hide Products and Services tab on Create form 
function HideProductsServicesTab(executionContext) {   
  //Get form context   
  var formContext = executionContext.getFormContext();   
  //Get form type   
  var formType = formContext.ui.getFormType();   
  //If formtype is Create, hide Products and Services tab on form     
  if (formType == 1) {
    formContext.ui.tabs.get("productsandservices").setVisible(false);   
  }   
  //To see the form type return value in your browser console
  console.log("Form type = " + formType); 
}

Save Mode

To get the form’s Save Mode, you use the following JavaScript code:

executionContext.getEventArgs().getSaveMode();

Here are Dynamics 365’s different Save Modes and their respective JavaScript return values as well as the applicable entities.

Entity	Save Mode	Return Value
All	Save	1
All	Save and Close	2
All	Deactivate	5
All	Reactivate	6
Email	Send	7
Lead	Disqualify	15
Lead	Qualify	16
User or Team owned entities	Assign	47
Activities	Save as Completed	58
All	Save and New	59
All	Auto Save	70

Application Example: Varying form behavior on Save Mode

Here is an example of how you can vary the form’s behavior on the Save Mode.

//Inhibit auto save functionality on a form
function InhibitAutoSave(executionContext) {
  var eventArgs = executionContext.getEventArgs();
  //To see the Save Mode return value in your browser console
  console.log("Save Mode = " + eventArgs.getSaveMode());
  //If save mode is 'Save and Close' or 'Auto Save', inhibit default behavior i.e. save 
  if (eventArgs.getSaveMode() == 2 || eventArgs.getSaveMode() == 70) {
    eventArgs.preventDefault(); 
  } 
  //To see if the auto save behavior was prevented in your browser console
  console.log("Default behaviour prevented: " + eventArgs.isDefaultPrevented());
 }

If you have some unsaved changes on a form, the code above inhibits the regular form auto save behavior as well as inhibits the regular Save and Close auto save behavior, that you ordinarily get when you navigate away from from a form with unsaved changes. Instead of auto-saving (i.e. the default behavior), if you try to navigate away from a form with an unsaved changes, the JavaScript code above will block the auto save behavior and offer you the pop up notification below. If you choose “OK”, you will loose all your unsaved changes, and if you choose “Cancel”, you will remain the same form. With the default auto save behavior inhibited, users have to manually save the form, by clicking on the Save button or using the keyboard shortcut: “Control + S”.

What if you want to inhibit the auto-save behavior throughout your Dynamics 365 organization (i.e. disable auto-save on all forms), instead of a specific form? It would be inefficient to implement the JavaScript code above on all forms across all your Dynamics 365 organization entities. An efficient way execute such a requirement (i.e. on all forms) is to:

Go to Settings > Administration.
Click on System Settings.
Click on the General tab
Set the Enable auto save on all forms option, to No.
Click OK, in the footer of the window.

Microsoft Dynamics 365: How to Set Up a Free Online Trial Version

For organizations seeking a comprehensive, robust and extendable customer relationship management platform, it is worth considering the Microsoft Dynamics 365 platform. In this post, we will cover a quick case for considering Microsoft Dynamics 365 for your customer relationship management and provide a guide on how set up a free Dynamics 365 Online 30 day Trial Version.

A Quick Case for Microsoft Dynamics 365

Why should you choose Microsoft Dynamics 365 over other customer relationship management platforms? Here are 6 reasons to choose Microsoft Dynamics 365 over its competitors:

Familiar, intuitive and easy to adopt user interface: If an organization is familiar with the Microsoft Office suite, it is easy to adopt the Microsoft Dynamics 365 user interface, as they are designed by the same organization. You can spend less on change management.
Integration with the Microsoft ecosystem: You can easily integrate Microsoft Dynamics 365 with the rest of the Microsoft ecosystem like Outlook, Office 365 (Word, Excel, PowerPoint, Outlook, etc), Windows Server, Exchange Server, Skype for Business, SharePoint, Power BI, and PowerApps.
Powerful analytics, social sales and embedded intelligence features like LinkedIn Sales Navigator Application Platform and Relationship Assistant, to enhance productivity, efficiency and effectiveness.
Flexible and extendable architecture, deployment options, and pricing, making it easy for an organization to customize Dynamics 365 to its specific requirements and only pay for its unique needs.
Detailed, flexible and seamless security model: Dynamics allows you to control who can access general sections and specific areas like fields. Users only see areas they have been granted access to.
Microsoft’s commitment to the product quality and innovation as well as partner support: Microsoft is committed to quality and regularly releases updates, continuously innovating and improving the product. Also, Microsoft has a global network of partners to support clients across the world.

Setting up a Microsoft Dynamics 365 Free Online Trial Version

Are you convinced about Microsoft Dynamics 365? If you are, and ready to take it to the next level, here are the steps you need to set up a Microsoft Dynamics 365 online trial version:

Step 1: Go to the Microsoft Dynamics 365 product home page.

Step 2: Click on “Schedule a demo”.
Click on "Schedule a demo"

Step 3: Click on “Sign up for a free trial”.

Step 4: Click on “Sign up here”.
Click on "Sign up here"

Step 5: On the pop-up, if you are not a Microsoft employee or partner organization, click on “No, continue signing up”.

Step 6: Enter the required information and click “Next”.

Step 7: Enter the required information about the Microsoft Dynamics 365 trial organization you are about to create and click on “Create my account”. Also, if you would like to hear from Microsoft or its partner in your geographical location to provide support on your Dynamics 365 journey, select the options provided below.
Click "Create my account"

Step 8: Enter your phone number for Microsoft to verify that you are not a robot. You will receive an automated text or phone call, depending on the option you select.
Enter your phone number to verify that you are not a robot

Step 9: Hooray!!! You have created your Office 365 and Dynamics 365 organizations, you will get the page below. Take note of the “Sign-in page’ and “Your user ID”, you will need this information to sign into your new organization.
You have created a D365 organization

Step 10: Go to the Microsoft Office 365 home page. If you are requested to sign in, click on “Sign in” and login using the user ID from step 9 and the password you created earlier, in step 7.
Go to Microsoft Office 365 home page and sign into the organization you created in Step 9.

Step 11: After logging in, click on the “Admin” app, to access your Office 365 organization’s administration options and configurations.
Office 365 home - select Admin app

Step 12: Adding other users to your new Office organization and Dynamics 365 organization: In the Admin app, navigate to Users >>> Active Users. You will get a page similar to the one below. Click on ‘Add a user’ to start the process of adding a new user to the Office 365 organization.
Add other users

Step 13: Enter the user’s basic information and click Next:
User's basic info

Step 14: You can add a user to your Office 365 organization without giving them access to the Dynamics 365 organization. However, if you if you want the user to be both a member of the Office 365 organization and Dynamics 365 organization, you have to grant the user a Dynamics 365 license (see the image below). You can also assign the user other product licenses from this section. Click Next.

The creator of the organization has a Global Administrator role, in Office 365, as well as System Administrator and System Customizer security roles, in Dynamics 365, giving them the rights to add users to both Office 365 and Dynamics 365.
Assign licenses

Step 15: You can also assign administration roles to the user if you wish. These settings are optional. Click Next.
Optional settings

Step 16: Review the user details. If you have happy with the setup details, click on ‘Finish adding’ to add the user to the organization.
Finish adding user

Step 17: After successfully adding the user, you will get a page similar to image below. You can add more users from this window. After you finish add users, click ‘Close’.
Successfully added user

Step 18: Back in the Admin page, in the left navigation, navigate to ‘All admin centers ‘. If you cannot see ‘All admin centers ‘ in your left navigation panel, click on ‘Show all’. In the main window, click on ‘Dynamics 365’ to access the Dynamics 365 administration center, in order to complete the set up of your Dynamics 365 organization.

Step 19: To complete your Dynamics 365 setup, select the apps you are interested in. If you are not interested in the provided apps, you can select “None of these. Don’t customize my organization” for a vanilla Dynamics of 365 organization. Click on ‘Complete Setup’.
Select the Dynamics 365 apps you are interested in.

Step 20: Depending on the app(s) you selected in the previous step, you will get a launch pad similar to this:
D365 Published Apps

Step 21: Select the Dynamics 365 app you would like to launch by clicking on it and you will get the app’s home page. Here is an example of the Dynamics 365 Customer Service app home.
D365 -Customer Service

Your brand new Dynamics 365 instance has been set up and is ready for your adventures. Feel to explore and play around with the environment. You just have entered the fascinating world of Dynamics 365. Welcome!!!