How to reduce SubscriptionTrackingDeletedObject table in CDS/Dynamics 365

How to reduce SubscriptionTrackingDeletedObject table in CDS/Dynamics 365

One of my customers is a B2C customer with a very large online database exceeding 500 GB. With a very active Marketing automation tool interated, we generate a lot of data in Dynamics 365 CE which after defined retention periods needs to be removed. This has caused some side effects, that a table called SubscriptionTrackingDeletedObject has become very large. This article will describe how to set a configuration to reduce its size.

We often monitor the Organizational Insights, and now lately the brand new capacity feature that can be found in the left hand menu in https://admin.powerplatform.microsoft.com, if you have a CDS/Dynamics 365 CE instance.

An interesting table started growing rapidly and we had no clue what this was, and I had during my now 15 years of working with Dynamics 365 never seen it. It was called SubscriptionTrackingDeletedObject. When I came back from my Swedish summer vaccation, it had grow to over an amazing 181M records. Time to fix this.

First thing, as usual is of course to google it (yes, it is a verb, get used to it). All I found was this somewhat informative post by my good friend Chris Cognetta who is an ace with infrastructure issues.

http://cognettacloud.net/2016/06/21/crm-database-log-growth-issue/ 

However, it seemed that they just truncated the table, and we were online so that was a bit tricky, to say the least. I was at this time a bit upset that Microsoft were taking up around 50GB of space for my customer without giving me any way of managing that, or having any direct use of it. I counted to ten and called Microsoft Support.

After a few emails back and forth, the excellent support technician at Microsoft informed me that there is actually a setting in the infamous super secret setting tool with the Star Trek-sounding name OrgDBOrg (it is pronounced “Org-D-Borg” in case you ever get stuck in Dynamics trivia). The setting is called ExpireSubscriptionsInDays. I will quote the support technician in what this table is used for and if anyone has any more information, please leave a comment.

“The SubscriptionTrackingDeletedObject table is the table that logs records for number of days before deleting inactive subscriptions as well as timed out deletion services.”

I am not sure for which purpose. If it is in regards to GDPR or some restore mechanism. I would like to know though. Default value for this i 90, which means that these logs will be stored for 90 days. The minimum they can be set to is 1. As I am currently not entirely sure what these logs are used for, I would not recommend you set them to 1, but I did set my customers to 5, hoping that this is not going to come back with a vengance.

We have during the day seen a dramatic drop in the amount of records in this table, with about 30M but and it is still ongoing, but hard to measure as there is a delay in the capacity measurement the Powerplatform admin portal.

So, how do you do this? First, download the OrgDbOrgSettings tool and install it in the instance where you are having issues. Check out these links below for that:

https://github.com/seanmcne/OrgDbOrgSettings/blob/master/readme.md

https://github.com/seanmcne/OrgDbOrgSettings/releases

A word of advice regarding OrgDbOrg; don’t think that you are Captain Kirk and go flying off into the Beta Quadrant and beam every single setting just because you can. It won’t make your system better, rather the opposite. Make really sure on what you are doing and don’t even trust a blog article like this, read the KB-article linked in the tool and make up your own mind. It is a powerful tool, like a jackhammer.

After you have installed the OrgDbOrgSettings tool, you can see it and open it by clicking the display name.

Then just find the “ExpireSubscriptionsInDays” – Press “Edit” and change to whatever you would like it to be. You will typically have to confirm to save it to Dynamics 365 CE/CDS

With that done you should just have to wait for the magic to be done.

If/when I get some clarity on what this is used for exactly, I will add this.

Forms Pro Flow activity set regarding

Forms Pro Flow activity set regarding

Forms Pro is an awesome new tool that combines the datamodel support that we previously got from Voice of the Customer (VoC) with the look and feel from Office Forms. It also has nice native support for Flow and many other parts of the Power Platform. It is still in Preview, make sure you check it out. A great resource for this is Megan Walkers blog as she has written several artice about this.

One thing that we recently ran into with the new Flow activitiy that can generate personalized sendouts, was how to format the syntax of the regarding field. The reason for the confusion is that it is different than how the Common Data Service (CDS) connector handles the Regarding field, which is with two fields, one for the object id and one for the type. In Forms Pro, it is all in one field in the syntax

<objecttypename> objectid

as can be seen in the picture below.

You can actually get a hint about the syntax if you check the “mouse over”. I wasn’t able to screen capture it.

I want to give a great thanks to Megan Walker and this article for giving me this insight! 

TCP Chimney

TCP Chimney

Today I was working with a customer and we have had some weird SSIS intermittent errors where the Native OLE DB / SQL Client was giving me some trouble with really weird errors.

Errors like these:

CRM service call returned an error: CRM service call returned an error: The request channel timed out while waiting for a reply after 00:01:59.9990005. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout. (Error Type / Reason: Timeout)

[OLE DB Destination [611]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Communication link failure”.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “TCP Provider: An existing connection was forcibly closed by the remote host.

After some searching and troubleshooting, I found the following blog which finally seemed to address the error at hand. When I tried removing the “TCP Chimney” , it the error went away. Please read more about it here:

TCP Chimney Setting and SQL Server Error: TCP Provider: An existing connection was forcibly closed by the remote host

SSIS and Kingswaysoft are great tools but sometimes the errors are really tricky and you have to be rather persistant to fint the root cause. In this case it seems to be related to the NIC.

 

Azure guest accounts in CDS/Dyn365

Azure guest accounts in CDS/Dyn365

Azure Active Directory (AAD) has a feature where it allows users of foreign tennants to be granted access to the current tennant. In other word, if you are running contoso.com and a user of northwind.com would like to have access, you can add this user as a guest account in Azure. However, I have found that giving this user access to Dynamics is not fully straight forward, although, it is far from rocket science. In this article I will show how this is done.

Do note that I have heard from people in the product team that there are features of the powerplatfor that cannot currently be accessed using a guest account, I think it was Canvas Apps and Flow. I will have to try this out and get back to you (or someone else could! – I would appreciate a link back to this article) in a later article. I also do think that they are workin on this.

On a high level, what we need to do is:

  1. Add user in AAD
  2. Grant License
  3. Wait for the user to pop up in CDS/Dynamics
  4. Assign a security role in CDS/Dynamics

To start with, we need to go to the Azure Portal: https://portal.azure.com – and click on the AAD menu item on the left.

 

 

Browse to portal.azure.com -> click Azure Active Directory (AAD) -> Click Users

Click “New guest user”

Enter the email address of the user, and perhaps some nice personal email message showing you are not some evil spammer!

Then go to portal.office.com and you will now be able to see the new guest user in here.

Select the guest user and click “Edit product licenses” – Note, I have not been able to set licenses directly by opening the user, only this way.

Assign the license required, P2 or Dynamics Customer Engagement App or Plan – in the example above, a Dyn365CE Plan 1 (trial)

After you have assigned the guest user a license, you have to wait a while until the asynchronous service in O365 provisions the user in the CDS. This often is rather quick, but sometimes takes more time. When I was making this, it took more than 15 minutes.

To find the user in CDS/Dyn365 go to Settings and click on Security. (Old UI)

And then click on “Users” in the Security area.

This is how a guest user look like in Dynamics 365/CDS. It has a # sign in front of it. As you can see, I have another one with my name previously created.

The last thing that has to be done is to grant the guest user the correct role.

After this, just give the user the direct URL to the system and they should be able to log in with their normal users.

This is a very useful method to use when setting up trials for someone as they do not have to sign in with another account to access they system. I strongly recommend it.

As mentioned in the beginning of this article, there might still be some issues with using canvas apps and Flow using guest users, so do be aware that not all features could be available.

 

Uninstalling Employee Self Service Portal – step by step

Uninstalling Employee Self Service Portal – step by step

A customer of involontarily gotten a Dynamics 365 Employee portal installed. I had to remove it. It has 21 (!?) solutions which are interdependent. This is how I fixed it.

As always, I start off by trying to find if anyone else has run into the same issue, almost. This guy had with another portal type:
https://community.dynamics.com/crm/b/dynamicscrmbestpractices/archive/2017/10/14/dynamics-365-uninstalling-microsoft-portals-steps 

And then my Portal buddy Nick Doelman did some heavy lifting (he actually does!) and sent me this link. It did turn out to be a bit old though. Still useful, but the solutions described in it are not entirely accurate.

https://community.dynamics.com/365/b/dynamics365portalssupport/archive/2017/02/27/portal-troubleshooting-part-three-uninstalling-portal-solutions 

Sitemap

First of all, before you do anything else, start by removing the following things from the sitemap (unless you havn’t added more things pointing to Portal components)

1. The entire Portal “Area”
2. The SubArea and Group under Settings for Portal Settings.

Solution removal

So which solutions are we to uninstall? I will take it from the top, in the order I uninstalled them.

1 ESSPortal
2 BaseHtmlEditor_portal
3 MicrosoftForumsWorkflows
4 MicrosoftForums
5 Feedback
6 KnowledgeManagement

When removing KnowledgeManagement I got this error:
The entity with ObjectTypeCode = 10460 was not found in the MetadataCache
Tried different way, after some time, just refreshed and it was gone. Maybe this should be removed earlier. Not entirely sure which entity this was, as it was removed and I didn’t save a metadata reference before starting.

7 MicrosoftAzureStorage
8 MicrosoftBingMapsHelper
9 CustomerService
10 WebNotification
11 MicrosoftGetRecordIDWokrflowHelper
12 MicrosoftIdentity
There are two dialogs that 
Process/Dialog – Change Password, Removed all Steps. Save & Close
Reset Security Stamp – Removed all steps. Save & Close

13 MicrosoftIdentityWorkflows
14 MicrosoftIdentitySystemWorkflows
15 MicrosoftCrmPortalBaseWorkflows
16 MicrosoftCrmPortalBaseSystemWorkflows
17 Portal Timeline
18 MicrosoftWebForms

When removing this solution I also got the same weird error: The entity with ObjectTypeCode = 10439 was not found in the MetadataCache. 

What I found was that it was actually the sitemap that I hadn’t cleaned up from Portal things. This is why I recommend removing all portal related entries from all Sitemaps before starting to remove any solutions.

19 MicrosoftCrmPortalBase
20 MicrosoftCrmPortalDependencies
21 Portal Privacy Extensions