Top Table Usage in PPAC

Top Table Usage in PPAC

Top Ten Table usage is back from being lost when Organizational Insights was discontinued. A bit tricky to find so check out the video. Awesome tool when trying to reduce the size of large instances, especially important now that the prices per GB are going up to $40/GB (subject to you license agreement).

How to reduce SubscriptionTrackingDeletedObject table in CDS/Dynamics 365 – Updated!

How to reduce SubscriptionTrackingDeletedObject table in CDS/Dynamics 365 – Updated!

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. UPDATED – Based on some new learning and information from Microsoft this article has now been updated!

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.

Update! The size of the table fell to 160M rows but never below this so after some further discussions with Microsoft support they did some more investigation into this subject and came back with the following recommendation:

1. Reduce the value gradually from 90 to 60 and then on
2. Never go below 15

There is however, another related setting called ExpireChangeTrackingInDays which is located just next to the ExpireSubscriptionsInDays. This is defaulted to 30. We reduced this to 15. 

Based on these recommendations we tried 60 days and this resulted in a most dramatic drop to around 20M rows. – End of update

 

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.

As far as I have understood these two settings and this table is used to indicate how long changes and deletes are stored in this table and related tables for integrating systems to be able to read. This can, for example be Data Export Service, the old Dynamics for Outlook client etc. Hence reducing the numbers to, for instance 15 (the lowest recommended number by Microsoft) can result in some changes not being propageted to these integrated systems in the case that the integrations or just an offline client being offline for more than 15 days. And I also got the feeling that setting it to 5 was below some internal threashold and hence wasn’t really supported despite the fact that it says in OrgDBOrg that the lowest value is 1.

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

Setting up Data Export Service without PowerShell Script

Setting up Data Export Service without PowerShell Script

Setting up Dynamics 365 Data Export Service requires a Azure KeyVault to be set up which is typically done using a PowerShell script which can be found in the Data Export Service setup wizard. However, if you run into issues setting this up, it might be easier to do this directly in Azure by minimizing the steps of the scripts. This was a tip that my friend and Business Solution MVP Scott Durow recommended. He mentions this in his very instructive video, but doesn’t actually show how, so I thought I’d just detail how I made it work.

First some background. The reason why I even started investigating how to do this manually was that when I tried running the PowerShell script supplied by Microsoft in the wizard.

Press the “i” icon to get a window containing the PowerShell Script that Microsoft recommends for setting up the Key Vault.

When running the PowerShell script both as myself (not a global admin) and asking a global admin to do it, it failed in the latter parts. The key vault was created by some of the access policies seemed to be missing and it just didn’t work. My users rights in Azure was Contributor in the Resource Group, and it was a bit interesting cause the global admin and I got different error messages, but when I finally managed to create the key vault manually, I could do it all with my user, so it didn’t seem I was missing any rights to do it.

First step is to make sure you have all your data straight. The power shell script is good for this. Check out Scott clip if you want to know how to find the different strings. He shows it very clearly.

 Just copied from the PS-Script:

$subscriptionId = '<subscription ID>'
$keyvaultName = '
MyVault'
$secretName =
'MySecretName'
$location = '
North Europe'
$connectionString = 'Server=tcp:
<db-name>,
1433;
Initial Catalog=<catalog>;
Persist Security Info=False;
User ID={your_username};
Password={your_password};
MultipleActiveResultSets=False;
Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;'
$organizationIdList = '<DYN365GUID>

$tenantId = ‘<AZURE TENANT ID> 

The highlighted parts have to be replaced by your settings. I will use these variables to have something to reference to further in this article.

Search for Key Vault and add the “Key vault”, the top one in this picture

Then we have to set it up. Not so tricky if you have worked with Azure before. Consider if you want to work in an existing Resourcegroup or if you want to create a new one. Typically you need to have Azure SQL services running as well so it might be good to keep them all together to be able to see the costs and control who has access why a resource group might be a good idea. But that should hence already exist. If not, you can create it. I would recommend keeping Azure SQL and Key vault in the same, not sure if it actually works in different resource groups, probably does, but I haven’t tested.

Creating the key vault – in this case I am creating a new resource group, normally it would already exist

Azure will add you as the default principal with access to the key vault. We will add Data Export Service to this later. For now, just create it.

Now we need to open the Key vault and select the “Secrets” section in the menu on the left hand side and press the button:

“+ Generate/Import” 

Then you have to enter you Secret name ($secretName) and the connection string ($connectionString) into the value.

Creating a secret – $secretname in Name and $connectionstring in Value

Press “Create”.

You should now return to the previous screen and see a row for your secret.
Select it.

It should open the settings panel for the Secret, press the “Tags” part which is located in the middle and add a tag which has $OrgIdList ($organizationIdList) as the key and Tennant ($tenantId) as value. I have blurred them out below as they are rather private.

Adding a tag with OrgIdList and tenantId to a Secret

You then need to go back to the Key Vault and click on the “Access Policies” menu item, you should then see yourself as the principal as this was set when we created the key vault. We now need to add Data Export Service as a valid Principal with read access rights.

So click “Add”, click “Select Principal” and search for “b861dbcc-a7ef-4219-a005-0e4de4ea7dcf” which is the ID for Data Export Service. It should show up like this:

It needs to have “Secret Management Operations – GET” permissions and nothing else.

Now, go back to the Secret and copy the URI to the Secret.

Getting the URI for the Key Vault Secret

Paste it into the Data Export Service Wizard field for Key Vault.

Fill in the other information and press validate. Hopefully it will work out well!

Some issues

Being too cheap with the Azure SQL level
If you don’t go for a Azure SQL P1 and choose a lower tier, you might get this warning:

We tried an S0 for our Dev environment and tried to sync a couple of million records and that just didn’t work, we got tons of errors. We upgraded the ASQL to S2 and then at least we didn’t get any errors. We are planning for P1s in UAT and production.

Might have to set activation date on secret
Seems that you might have to set an activation date on the secret. Not sure why this is, the PS-script doesn’t seem to do this. But not very hard.

Added activation date on the Secret from June 4.th

Using Database schema that is not created
The default database schema is “dbo” in the Data Export Service Wizard. If you change this to something else like “crm” and you haven’t created this in the database, you will get an error. It is simple to fix, you just have to go into the database and create the schema. To create the schema “crm” open a query and run:
CREATE SCHEMA crm

For more information on how to create schemas, check this site: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-2017

Once the schema has been created, there should be no problem using it, as long as the user has permissions using it.

I hope this works for you. If you have any questions, don’t hesitate to leave a comment.

Gustaf Westerlund
MVP, Founder and Principal Consultant at CRM-konsulterna AB
www.crmkonsulterna.se

Dynamics 365 Support – your best friends when you have Dynamics 365 Online

Dynamics 365 Support – your best friends when you have Dynamics 365 Online

If you are using Dynamics 365 Online, Dynamics 365 Support is your IT-department for your Dynamics system and you need to know or start learning how to working with them in a good way. There are several things you should consider.

Registering a support ticket

First of all, registering a support ticket is of course important. This might not be as trivial as it seems, as it depends on which type of license you have. If you have bought your licenses using CSP (Cloud Service Provider) or Select/Volume Licensing then you will most probably be directed to your CSP/Select partner for any support issues. You can of course buy premier support from Microsoft but that might not always be an option for smaller companies. There are some tricks as well if you are stuck with CSP/Select or similar, and will be happy to tell you about these in person if I meet you, but I won’t write about them, as I am afraid it will be “fixed” which would be sad.

If you have bought your licenses directly through the O365 portal, you can also create a ticket using the portal. You need to be a Global Admin or a Dynamics 365 Service Admin to place a ticket. Currently you cannot indicate that the ticket is for Dynamics so you simply write so in the text and the O365 support team will create a new Dynamics 365 ticket for you. I was amazed at how fast the Office 365 support called me after registering a ticket, I think it took less than 1 minute. So I would suggest you choose the option “call me”.

If you have a partner, I strongly suggest that you register them in the Office 365 portal for the Dynamics 365 subscription as Partner of Record (PoR). This will make your company show up in their Microsoft partner portal where they can register a support ticket for you.

Checklist
When registering the ticket with Microsoft, there are some things you have to note:

  1. Be polite. Even if your system is slower than sirup in January (a Swedish saying) or nobody can log in, you will not be getting any help faster by screaming or being rude. I like to quote my grandfather who had a good saying: “Better to be strong in argument and withheld in words, than the other way round.” I have actually gotten written compliments for being nice by the support people, do you think they will go the extra mile for me?
  2. Be very exact with what is you think is wrong. The best way is to formulate it like this: “I did X and expected Y but I got Z”. Also be clear on when you define the case as closed. This is particularly important, and hard when you have performance issues or intermittent errors. But try to be as exact as you can. For example: “When users run Chrome open a Contact (after the contact form already has been loaded for another record) it takes 10 seconds. We expect it to take no more than 2 seconds.”
  3. Only have one issue per ticket. Better to have many tickets open. I have had 4 tickets open for one single customer at the same point in time.
  4. If you think the issue is important, describe it in business impact terms. For example “The bug X affects all sales people in the organization effectivly stopping them from using Dynamics 365. It severly risks our entire Dynamics 365 investment as users look at other solutions and the user adoption drops dramatically and the trust in the system is drastically lowered for every hour this bug is not fixed. We have 4354 sales people in our global sales organization spread out over 34 countries. It also affects managements abilities to manage the company as no pipleline and forecast numbers are produced which could potentially cause sever business damages that cannot easily be fixed within the coming 4-5 years.” – This was just made up 🙂 but it is important to not hold back when doing this, especially if you are Swedish or from other cultures which value humbleness.
  5. Be clear on limits of the error. 
    1. Which Operating system?
    2. Which browsers?
    3. What times? Can you see any clear patterns on when it happens?
    4. Geos?
    5. Security roles?
    6. Types of users affected, AD users federated to O365 or Cloud users?
    7. How long?
    8. Which versions of Dynamics 365 are you running?
    9. Which instances have the issue (the url:s)?
Types of tickets
What types of tickets can be registered? Should you only register a ticket when you want help with something?
Well, no, you can and should register a ticket for many different reasons. As I mentioned at the top of the article it is important to think about Microsoft Support as your Dynamics 365 IT-department. They will help you with a lot of things that your IT-department would have helped you with had they hosted your Dynamics system. The main difference is probably that they do nothing else than host Dynamics 365, every day, year after year… not many IT-departments that can compare with that.
Typical issues they can help out with are:
  • Something seems to have stopped working that used to work, and you didn’t change anything. Like you cannot create an Application User anymore.
  • Something clearly is a bug in Dynamics 365 and you want Microsoft to know about it (they might not know) and you want to let them know how important it is for you (they will surely not know this).
  • You need some information from the Server, like the “Top 10 table Usage”  report from the SQL database.
  • You have performance issues.
  • You need to change the planned upgrades because your team won’t make the set time.
  • You want to set some non-clustered indexes on the database.
  • You are doing a migration of large amounts of data and you will be a bad neighbour for a few days and if possible you would like a little more than the 2 allocated threads in the API.
  • You want a database copy prepared for installation into an on-prem equivalent.
  • You need some detailed information on how the service is managed, like
    • How backups are stored
    • How to restore backups from the 90 day tape backup
    • Where an instance is hosted
  • You would like to move an instance to another datacenter
  • You need to remove a managed solution but it has dependencies that shouldn’t be there…
  • You need to do some task with an instance that you cannot do yourself with the instance manager.
In other words, they will be your superfriend when working with Dynamics 365 and you want to be really nice to your super friend!

Gustaf Westerlund
MVP, Founder and Principal Consultant at CRM-konsulterna AB
www.crmkonsulterna.se