How to upgrade Microsoft Dynamics AX2009, AX2012 to D365 for Finance and Operation is one of the hottest topics in the AX community right now.If you are considering how to upgrade your ERP solution or planning a move from Microsoft Dynamics AX2009, AX2012 to the new Microsoft Dynamics 365 for Finance and Operation you are at the right place. In this article, you will learn how to manage and create custom System Views and Dashboard within Dynamics 365. Microsoft Dynamics 365 & Dynamics CRM experts provide reviews and opinions to aid professionals with the Dynamics 365 selection process.
One of the limitations of D365 Online opposed to the On Prem version is that there is no access to the backend SQL Database .The Dynamics Data Export Service is a new Add-On that enables users to replicate the CRM Online Data Base to a Customer Owned Azure SQL Database. The data export service support both Microsoft Azure SQL Database and Microsoft SQL Server on Microsoft Azure virtual machines. The Data Export Service synchronizes the entire Dynamics 365 data initially and thereafter synchronizes delta changes on a continuous basis as they occur in the Microsoft Dynamics 365 (online) system.
So with this tool you can replicate the CRM Online data base to a Azure Hosted SQL data base without any trouble in a matter of minutes . Few of the business scenarios this add-on may come in handy is where
- The user wants to create a data warehouse with the CRM data
- The user wants to have access to the backend SQL databases views to create complex reports.
Few things to keep in mind with this add-on
- This add-on will only work with a Microsoft Azure SQL Database or a Microsoft SQL Server on Microsoft Azure virtual machines.
- This add-on is for data Export only and cannot be used for data importing purposes back to the source CRM.
What is needed to get this add-on configured.
- An Azure Subscription (Non Trial subscription. I will explain why later)
- A Dynamics 365 Subscription (30 day trial will be sufficient )
Steps required to get this add-on configured.
- Link the Office 365 AD with Azure AD
- Create a Azure SQL database instance
- Install Dynamics 365 Data Export Add-On
- Execute a PowerShell Script that will create the link and the service between the CRM DB and the Azure SQL DB for exporting data
- Configure Entities in CRM that needs to be exported to the Azure SQL database.
Step 1: Link the Office 365 AD with Azure AD
Basically in this step what we are doing is linking two separate Azure and Office 365 subscriptions so that we can access the Office 365 tenant from the Azure subscription.
The entire process is explained in this article by Microsoft therefore I won’t be discussing about that in details.
Note : This is where I would suggest using a Non Trial subscription. The reason been when adding a 0365 AD to the Azure AD there is an option in Azure AD to add an “Existing AD” , this option was not visible for me with the trail subscription.
Now in terms of getting a non-Trial subscription there are few workarounds .
- Sign up for Pay-As-You go subscription where you will only pay for the services you have used . There is a fee involved here but it would be very minimum
- Sign up for a MPN Subscription if your Microsoft ID is associated with your company’s Microsoft profile . Read here for further details.
Step 2: Create an Azure SQL database instance
- Login to your Azure Portal
- Click on “SQL Databases”
- Create a “SQL Databases” – Click on the “Add” button to create a new SQL database.Specify the details required for SQL database. Ensure you create “Blank Database”. Because I am using this for demo purposes I selected the Basic Pricing Tire.
- Create a SQL user that will be used by the Data Export Service to write the data to the SQL database. To do this install the latest version of SQL Server Management Studio that supports Azure SQL databases. Connect to the Azure SQL Database. You can get the Server Name and login user account by going to the Azure SQL Database Properties
- Create the SQL user that will be used by the Data Export Service to write the data to the SQL database . Ensure you create this user record against the Master Database
- Assign the “db ower” Role for the above user account on CRM Database you just created.Ensure you create this user record against the CRM Database.Note : Because this is a demo I assigned the dbowner role against user account which is not the best practice . For the Database permissions required for the data export user account please refer to this article by Microsoft
Step 3: Install Dynamics 365 Data Export Add-on
- In CRM Navigate to Settings –Dynamics Marketplace
- Search for Dynamics 365 Export Service
- Select the Dynamics 365 tenant to add the Data Export Add-On.
- Agree to the Terms and Conditions
- This will begin the installation process of the Data Export Service. You can see the Solution under the “Manage your solutions” area on the portal
- Once the solution is installed a new menu item will appear on the Settings area in CRM
Step 4: Execute a PowerShell Script
Basically, what we are doing in this step is that, we will be providing details of the Azure Instance and SQL Database, so that the data export service will know which instance to connect and where to store the data. Here will be providing information about the SQL connection string user account (user name and password) we created in Step 2. Because this information will contain credentials and connection data , it will be stored in the Azure Key Vault. Read more about Key Vault here.
Before we get started with this step. You must ensure all the Azure PowerShell Extensions are installed. To get the necessary extensions refer to thisarticle by Microsoft
Now let’s get started. First we need to get the PowerShell script. The easiest place to get this is by going to “Settings -> Data Export-> Select New” and click on the blue exclamation mark next to the “Key Vault URL” field.
Once we have the script copy the script to a Note Editor and update the highlighted parameters. Below are details of those parameters and where to find them.
- $subscriptionId – Specifies the Azure subscription to which the Key Vault belongs.Goto the Azure SQL Database -> Select Overview
- $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. Goto the Azure SQL Database -> Select Overview
- $location – Specifies the Azure region where the Resource Group and Key Vault is placed.Goto the Azure SQL Database -> Select Overview
- $connectionString – Specifies the destination database connection string that would be placed as a secret in the Key Vault.Goto the Azure SQL Database -> Select Overview (select the ADO.NET connection string)
- $organizationIdList – Specifies a comma separated list of all the CRM Organization Id which will be allowed to export data to the destination database. (Setting -> Customisations -> Developer Resources )
- $tenantId – Specifies the Azure Active Directory Tenant Id to which all the specified CRM Organizations belong to Goto the Azure Activre Directory -> Select App Registrations -> End Points (select the Federation Metadata Document)
Step 5: Configure Entities in CRM and Create an Export Profile
- The entities to be added to the Export Profile the needs to have the “change tracking” enabled.
- Navigate to Dynamics CRM -> Setting -> Data Export and click new
- Populate fields as required . Refer to the below table for details on each
Field Description Name Unique name of the profile. This field is mandatory. Prefix. Prefix to be used for the table names created in the destination database. This helps you easily identify the tables created for the Export Profile in the destination database. When specified, make sure that the prefix is less than 15 characters. This field is optional and only alphanumeric characters are allowed. Retry count The number of times a record is retried in case of a failure to insert or update in the destination table. This field is mandatory. Acceptable values are 0-20 and the default is 12. Retry interval The number of seconds to wait before a retry in case of a failure. This field is mandatory. Acceptable values are 0-3600 and the default is 5. Write Delete Log. Optional setting for logging deleted records. - Select the entities that you want to export to the destination SQL Database, and then click Next.
- You can synchronize the N:N (many-to-many) relationships that exist with the entities you selected in the previous step. Click Next.
- In the Summary step, click Create and Activate to create the profile record and connect to the Key Vault, which begins the synchronization process.
- In Microsoft Dynamics 365 (online), go to Settings> Data Export
- In the All Data Export Profile view, select the Export Profile that you want to change.Select the entities or entity relationships that you want to add or remove. Click Update to submit your changes to the Export Profile.
- Export Profiles must be deleted and then re-created when you restore or move a Microsoft Dynamics 365 (online) instance to a different country/region. To do this, delete the Export Profile in the EXPORT PROFILES view, then delete the tables and stored procedures, and then create a new profile.
- The Data Export Service doesn’t work for Microsoft Dynamics 365 (online) sandbox instances that are configured with Enable administration mode turned on.
- To use the Data Export Service the Microsoft Dynamics 365 (online) and Azure Key Vault services must operate under the same tenant and within the same Microsoft Azure Active Directory. More information:
- The Azure SQL Database service can be in the same or a different tenant from the Microsoft Dynamics 365 (online) service.
- When you remove an entity or entity relationship from an Export Profile it doesn’t drop the corresponding table in the destination database. Before you can re-add an entity that has been removed, you must drop the corresponding table in the destination database.
This has certainly been one of the longest how to posts I have done . But its heaps of fun . I learnt a lot about Azure while doing this and hope this gives you the same exposure . Please get in contact if you have any further questions
Sahan Wijayasekera
I ran across this article a few weeks ago and want to make a copy of it here just in case it gets removed from it’s source.
Email | Print
Creating Email Templates in Microsoft Dynamics CRM a very simple task, on the surface. You write what the Subject will be, followed by the Body of the message, and just like that you’ve created an email template. However, there are instances when you might want to add some Dynamic values based on the record. Inserting dynamic values from out of the box entities like User, Account, or Contact is pretty straightforward. At the top of the template select Insert/Update:
From there, simply select the Record type, and the field you want to insert. Here’s an example of what using the Contact’s First name would look like within the template:
That’s easy enough, but what if you want to add Dynamic values from a custom entity? According to the Software Development Kit (SDK), it says:
. . .
Well it doesn’t say anything actually, but not to worry, there is a way. In the template, where you want the value to appear, type within 2 brackets an exclamation point followed by the entity logical name. After the entity name, add a colon, and then the field logical name, ending it with a semi colon. If you’d like a default value if nothing was found, after the semi colon add the default value.
{!<entitylogicalname>: <fieldlogicalname>; <Default Text>}
For example, let’s say we have an entity called football team, and we want our template to use the Football Team’s Team Name field, and if it’s empty use Denver Broncos. In order to do this, we would type:
{!new_footballteam : new_teamname; Denver Broncos}
After saving it will look like this:
Well it doesn’t say anything actually, but not to worry, there is a way. In the template, where you want the value to appear, type within 2 brackets an exclamation point followed by the entity logical name. After the entity name, add a colon, and then the field logical name, ending it with a semi colon. If you’d like a default value if nothing was found, after the semi colon add the default value.
{!<entitylogicalname>: <fieldlogicalname>; <Default Text>}
For example, let’s say we have an entity called football team, and we want our template to use the Football Team’s Team Name field, and if it’s empty use Denver Broncos. In order to do this, we would type:
{!new_footballteam : new_teamname; Denver Broncos}
After saving it will look like this:
Single line of text fields are easy enough, works the same for most fields: currency and numbers. Dates will return the Date and Time value. In order to return just Dates add at the end of the fieldlogicalname, /@date. Similarly for just the Time portion, add /@time, for example:
{!new_footballteam:new_firstsuperbowlvictory/@date;}
{!new_footballteam:new_firstsuperbowlvictory/@time;}
{!new_footballteam:new_firstsuperbowlvictory/@date;}
{!new_footballteam:new_firstsuperbowlvictory/@time;}
Option Set Values will return the numerical value. In order to return the Friendly name add /@name,
{!new_footballteam:new_conference/@name;}
{!new_footballteam:new_conference/@name;}
Lookups return the GUID value, to return the Primary Attribute’s Friendly Name, add /@name,
{!new_footballteam:new_currentquarterback/@name;}
{!new_footballteam:new_currentquarterback/@name;}
These are all of the methods we’ve found so far. We are hoping to find a way to retrieve data from related Entities, but as of now it seems to be nearly impossible.If you have any feedback around tricks with email templates, comment to add your feedback.
Don’t miss PART 2 of this blog, it can be seen here: http://www.crmsoftwareblog.com/2017/01/dynamic-values-email-templates-part-2/
As a result of some great questions that I received on my first blog, Dynamic Values for Custom Entities in Email Templates, I wanted to write a follow up blog highlighting the resolutions to some of those inquires. I’m going to assume you’ve read the previous entry, which is posted above.
Template Types
When creating a new email template, you first get prompted to select an Email Template Type.
If you select a Template Type aside from Global, you must have a record matching that type in either the Regarding field or any of the Send To, CC, or BCC fields. You will also be able to easily insert dynamic values into the template for that record type. If you so choose, you could write out the dynamic values as I had shown, but it’s much easier to use the out of the box method to select them.
With global templates, you can insert the template regardless of what entity record you select:
The Global Template Type is what you’d want to use for custom entities, or any other entity not listed in the template type drop down menu. And just to reiterate, regardless of the way you insert values, whether you use the out of the box insert method or you manually type it in, you can only insert values from one record.
The Inclusion of HTML
There were also some great questions around HTML. For starters, all of these dynamic values can be used, like all templates, with HTML code. For example, if we wanted to bold a dynamic value, we could put the dynamic value between a bold tag, seen here:
After inserting the template, you’ll notice the bold text is now visible:
Furthermore, if you have a field value that is a URL, you can insert this value into the hrefon an anchor tag and create a dynamic hyperlink in your email:
This will be displayed as:
Dynamic URL Pointing to CRM Record
Someone asked about creating a dynamic URL that pointed to an actual record within Dynamics CRM. I was optimistic that something similar to the ‘Record URL (Dynamic)’ that you can find when creating a dialog existed. But unfortunately, this isn’t the case.
However, I did find a work around! In order to open a record with a URL, all you need is the server name (which is just your CRM URL), the entity logical name, and the entity ID. Then using those three components, we can include them within our href on our anchor tag:
{Server Name}/main.aspx?id={Entity Id}&newWindow=true&etn={Entity Name}&pagetype=entityrecord
*Note: You might notice we have a few other options. “newWindow=true” just means we want to open this URL in a new window. “Pagetype=entityrecord”, just means we want to open the entity record.
We should know the Entity Logical Name already. If not, it should be easy to find, and looking at the URL, we can get the Server Name. So the only thing we are missing is the Entity Record ID, but we can get this using our dynamic value. Usually (you may need to check the fields on your entity to confirm this), the ID field is the entity name plus ‘id’. For my football team entity, this field is new_footballteamid. If we let our server have the IP name of 192.168.1.1, we now have all the information we need to create the URL and insert it into the href of our anchor tag.
This will be displayed as:
Well that’s all for now. As always, please don’t hesitate to ask any questions!