Installing Dynamics 365 Customer Engagement v9 On Premise

Microsoft Dynamics 365 Customer Engagement v9 has been available On Premise for some time now, I believe it is since February-March sometime (Q1 timeline?). I haven't been able to fiddle with it until very recently and it was actually the first time in 3 versions where there were considerable issues during the installation, which was sort of a disappointment.

This was published on CRM-Konsulterna's blog but I keep it here as well to be able to search it myself.
 
To begin with, all of us having installed Dynamics CRM/365 Customer Engagement since different amounts of "way back when" have gotten used to a HUGE amount of versions of different software that is compatible or soon-to-be-compatible or even used-to-be-compatible-but-not-since-rollup-8. This is now a thing of the past for good or bad, well at least the first two, I can't really say that there will be software that won't be supported as updates come.

For Microsoft Dynamics 365 Customer Engagement v9 On premise the list is very short. It's Windows Server 2016 Standard or Data Center and SQL Server 2017 or SQL Server 2016 SP2 standard or enterprise (or developer for dev purpose). That's it.
The entire list can be found here https://docs.microsoft.com/en-us/dynamics365/customer-engagement/on-premises/software-requirements-for-microsoft-dynamics-365-server

This means that we almost certainly can forget about in place upgrade of the production instance since the current system almost certainly doesn't run on these versions.

The issues I've had installing v9 On Premise so far are two during the installation phase:
The first and most severe in my humble opinion is that you can't install CE using anything but Latin1_General_Cl_Al (or possibly *_CI_AI) SQL collation, which is bad in my neck of the woods since Finnish_Swedish_CI_AS is the one that makes data order correctly for us.

Not only can't you install and set up a new organization with anything but Latin1, you can't upgrade an existing organization with anything but Latin1. I might be painting rather broadly saying "anything" since I've only used Finnish_Swedish, but from searching the web this is what most people say.

When I've been trying to create or update and organization with Swedish_Finnish collation, not only does the installation/update fail, you also get an error message which is so long you can't see the end of it and click whatever button might be available at the bottom.

In the CRM server logs, the error message is this:

Create new Organization (Name=0728c85d-0b6b-e911-841d-00155d02e70f, Id=test0430) failed with Exception:

System.Exception: Error.ActionFailed Microsoft.Crm.Tools.Admin.SetDatabaseCollationAction ---> System.Data.SqlClient.SqlException: The statistics 'Attribute.ndx_Attribute_EntityId_ColumnNumber' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

The object 'fn_CollectForCascadeShare' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

The object 'fn_CollectForCascadeUnShare' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

ALTER DATABASE failed. The default collation of database 'test0430_MSCRM' cannot be set to Finnish_Swedish_CI_AS.

I really don't know where in the database these objects reside but somehow they are very hard linked to the collation.

Issue number two is that reports aren't working after the installation. I've heard that people are having issues installing the SRS. I haven’t had no such issue, but the reports weren't working when I was done.

Now, there is an update available from Microsoft with version number 9.0.3.770 which, according to discussions, I've seen should address reporting issues and collation problems.
I don't know if the update has been hidden since I couldn't find it earlier but was pointed to it on Microsoft's partner forums. Here's a page of available updates though. The 9.0.3 can be found as well as other updates: https://support.microsoft.com/en-gb/help/3142345/microsoft-dynamics-365-onpremise-cumulative-updates.

Unfortunately, this doesn’t solve the issue with collation and I've yet to see a solution for this so all you who are running 8.2 with Finnish_Swedish collation, I don't think you can upgrade just yet.

After I installed the server, I encountered one more issue and that was user creation. I was totally unable to create any users. The error message I got was: "Assembly Microsoft.Dynamics.Service.Plugins.dll can not be loaded.". Asking away at one of my favourite go-to-places for weird-shit-happening-in-Cr...EEeeh...Microsoft Dynamics 365 Customer Engagement, the Community at Microsoft (in this case https://community.dynamics.com/crm/f/117/t/316849), I got an answer as you can see in the thread which I at first disregarded - as you can see - but then thought I might have a look anyway. So, not the answer per se but a nudge in a direction at least.
The file was present in the directory but why on earth can't it be loaded?

Having no idea really what's needed system-wise to be able to use the assembly folder and seeing that the permissions set for the async and sandbox service accounts match that of earlier versions, I cranked it to 11 setting “everyone can do anything” in that folder.
This did the trick. I can now create users in a system which isn't behaving how I want it to do considering the collation isn't what I want. I think that it’s probably not the greatest of ideas to have this security setting in that folder but I don’t know how to set it more restricted and still be able to use the system.
 The assembly folder isn't something I've used since.... well... ages. I've used it for debugging some stuff since it was easier to place the dll there when it wasn't behaving but after the introduction of solution in CRM 2011 the database is the place to have plugin code. Now it seems that Microsoft are starting to use the assembly folder for internal stuff, probably since it's easier to do so in respect to the application not being a monolith any longer but that's speculation on my side. What's annoying is that the permissions aren't set properly.

Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se 

Test and enable programmatically

We have currently an issue with a customer running CRM 8.1 which have been migrated to Office 365 from an on-prem Exchange. This issue means that the Server side sync isn't really working and only does one sync run when you click test & enable on the mailbox.

 To make a long story short, CRM 8.1 is said to support hybrid connections between CRM and Exchange where CRM is on prem and Exchange is online (O365). This seems however not be entirely true according to Microsoft who are saying that it isn't "fully supported".

One thing that might cause the issue is that there are appointments being migrated from the on-prem exchange and I can't really tell what is the integration key, they might have gotten a new ID in O365 and the old ID is still present in CRM.

The funny thing is that when you click test & enable on the mailbox in the GUI and also click the "sync for this organization" checkbox, the mailbox is syncronized, once, and appointments are pushed to O365. Then it does nothing after this. We've been working with MS support during the summer to see if this can be solved but no luck yet. We even got an SQL script that should run test & enable which can be scheduled and it does test & enable but for some reason the syncing isn't run.

There is no "run test&enable" call in the API to the best of my knowledge, however I did find a post saying that if you set the fields orgmarkedasprimaryforexchangesync and testemailconfigurationscheduled to true on the mailbox with the api, the test&enable is run and the sync is also performed.

This is pretty much the code that is doing this.

Entity updMailbox = new Entity(mb.LogicalName, mb.Id);
updMailbox["orgmarkedasprimaryforexchangesync"] = true;
updMailbox["testemailconfigurationscheduled"] = true;
updMailbox["postponemailboxprocessinguntil"] = DateTime.Now;

service.Update(updMailbox);

Hope this helps

Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se 

Round up of data consumption in Microsoft Dynamics 365 Customer Engagement

I thought I should somehow round up the “Minimal copy” blogs with some sort of conclusions or rather recap perhaps. The base of this blog is that a minimal copy of a production instance wound up being 4.3GB which later was downsized with roughly 3GB.

The base of this text is “What are we paying for” when a subscription of Microsoft Dynamics 365 Customer Engagement is started. This has actually just changed but there are still some questions from my side. As of now, or when your subscription renews, you will have 10GB of database capacity, 20GB of file capacity and 2GB of log capacity per subscription.

The change is to begin with that the “attachments to notes or emails in Dynamics 365 Customer Engagement applications and PowerApps” (from the license document) no longer is counted as database storage, which is a good thing. I don’t know how many customers have wanted to delete attachments and there’s even a solution moving attachments to Sharepoint, which still is a good idea since Sharepoint is better at storing documents. 

The next thing that’s nice is that logs are counted as a separate store. I can’t say from the top of my head how much 2GB of logs are worth since it depends on how much auditing you are doing, the possible downside of this is that you don’t get any extra log storage from buying more licenses of Dynamics 365 Customer Engagement (I will address this later).

Sometimes this is how I feel

Now, database storage. 10GB of database storage, what does that mean? Again from the licensing document: “Common Data Service Database Capacity stores and manages entity definitions and data. The tenant for Dynamics 365 Customer Engagement plan and application subscriptions includes by default 10GB for database capacity.”

Ok, entity definitions and data. This means we are paying for the data we put into the system and for the schema. Not only the customizations we do but the standard schema as well. This might very well be reasonable but I don’t think this is what most customers think they’re paying for so it’s a good thing to know if you’re a partner and also good to know if you’re a customer.

This is where things gets interesting to me because a completely out of the box new instance of Dynamics 365 v9 on premise (ok latin-something collation since finnish_swedish is not working atm) is roughly 1.1GB compared to a Dynamics 365 v8.2 which is around 0.6GB. This 1GB storage of an empty instance is also true when we have Dynamics 365 Customer Engagement online, which I actually thought was a bit much when discussing it with the support but it turns out that the vanilla-no-data instance has doubled in size during the last version.  So when we look at 10GB of data per subscription, more than 1GB of that is taken per instance we’re having on it. For example, if we would have 1 production instance and 2 sandboxes (1 development and 1 test), 3GB of data would be consumed by just the instances. This is something that’s good to know at least.

Moving on with the licensing experience (ok, I don’t know if it’s still called this but it’s a too good a name to not use. It was called this during a presentation of Dynamics CRM 2013) you will get an additional 0,25GB of database storage and 2GB of file storage per Enterprise license. No log storage is added by having licenses. Extra storage is bought by the GB per type and I haven’t been able to find any price for this but that might be a case of bad Google-fu on my part (sorry but Bing-fu doesn’t sound as catchy).

When I was starting to dig into this I was able to download a report from the Power Platform Admin Center (one of three or four admin centers at the moment) which showed in which tables data was consumed. That report has since been removed after Microsoft changed the storage thingy to a new name and a new portal which doesn’t show as much information as it used to, yet at least. These portals and information flows tend to shift over time. The result as of 2019-05-20 is that you can’t tell where your data is consumed. What didn’t show in that report though was how much data that was consumed by “not your data” i.e. the system so a completely empty instance will eat some 1GB of data storage as I wrote earlier. And with that report being removed, I can’t tell what a completely empty instance will show as consumed for contacts for example.

Conclusions 

This all boils down to that there’s no real way of telling what you actually consume and we have to rely on Microsoft telling us what is actually in our instances which in this case wasn’t entirely true and I had to push back rather hard to make them believe that 4.3GB of data for an empty instance is not reasonable. This is easy to think that something’s wrong in an instance that you would think is empty but when you have an instance filled with data it’s quite the opposite.

In this case we made a minimal copy from production to an empty instance and started to fill it with data from source systems thinking we would use that instance as the new production so we cleared the “old production”. After a while we made a choice to redo the migration and did a minimal copy from the just filled instance to the “old  production” and when that was done, the “sort-of-.reliable-source” of this was erased. So, for most cases you risk getting this issue in the instances you set up from another instance. And in most cases that shouldn’t be all your instances, even if it’s not unthinkable it’s more than one.

The data usage per table report that has been removed will hopefully be re-introduced shortly.

Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se 

Minimal copy follow up.

I started a case with the minimal copy issue as stated earlier. About a week later after some rather heated discussion with the support a bit over 3GB of each instance was removed.

It was at times interesting to say the least when talking to the support crew. That being said, I have no grudge whatsoever with the people I was talking to since it wasn't their job that I was debating but rather the back end.

The first answer I got was that it was data from the instance I was copying to, since that wasn't empty when I started, there wasn't any real explanaition to why I couldn't access any of that data to remove it though.

Answer number two was that it was metadata and customizations, when I asked why the development instance was a mere 1.3GB that didn't really give any answers either. I think that the 1.3GB is a bit on the heavy side too considering that a somewhat filled Dynamics 365 Customer Engagement 8.2 on premise database is around 0.8GB so I was asking how come an "empty" instance is almost twice the size of an on prem one too. After an hour long screen sharing session where I did a reset of the test instance before I did a new minimal copy from production which gave the same result as before, which I assume removed the idea that it was old data taking up the space, the case was still open.

At this time the power platform admin center wasn't available any longer for this instance, and I don't think it's back up yet either so I couldn't see which tables were creating the most impact any longer. the Dynamics admin center told me that the size was still on the large side though.

After little more than a week the size was considerably smaller, a little over 3GB per instance was shaved and the case was closed. I didn't get any info on what data was removed more than that scripts had been run on the backend to recalculate the size of the instances.

The moral of this is that it's a good idea to have a look at your instances after a minimal copy to see if they are around the 1GB mark unless you have a shitload of customizations of course. Sometimes the answers given by the support team isn't correct unfortunately and it helps having had a look at how the databases behave. The thing that annoys me most in all of this is that I got answers two times that didn't really told the truth and that Microsoft charges by the GB, in this case 6 of them, which is just air.

Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se 

Minimal copy of instance and what the result is.

We've been working a bit with migration the past couple of weeks and in this job we've done a few instance copies using the "Minimal Copy Instance" from the Administration center. This resulted in a sandbox instance with 4.5 GB of data which seemed sort of funny.

What we were doing is to copy the production instance to a sandbox dito with the minimal copy instance option from the Administration center. According to the documentation from Microsoft found at https://docs.microsoft.com/en-us/dynamics365/customer-engagement/admin/copy-instance  the entities that are copied are the following:

• BusinessUnit
• ConnectionRole
• Currency
• DuplicateRule
• DuplicateRuleCondition
• EmailServerProfile
• FieldPermission
• FieldSecurityProfile
• ImportMap
• InternalAddress
• Mailbox
• Organization
• Position
• Report
• Resource
• ResourceGroup
• Role
• RollupField
• SavedQuery
• SLAKPIInstance
• Solution
• Subject
• Team
• TeamTemplate
• Template
• SystemUser

Now, I had a look at the storage report of the instances and we have three of those, one development, one test and one production. These are 1.5GB, 4.5GB and 8.5GB which is sort of interesting since the test one should be completely empty of data.

Digging deeper into this I had a look at what data is present and there's quite a lot of Audit logs according to the admin center, 850 MB of those, but when I look at the Audit logs in the instance, there's 250MB that I can find. Looking at the Entity list, Audit logs aren't present there making it even more confusing.

The top three biggest tables are contoso_paymentBase, ContactBase and AuditBase (contoso_paymentBase is called something else really, but it's a custom entity, or the "contoso_payment" is a custom entity at least). Now, these tables should be the SQL tables of the entities "contoso_payment", "Contact" and "Audit", but how can they be so large considering there are NO records in the first two and yes, 250 MB in the Audit table which I can see, but not 800 MB.

I started a support case with Microsoft in this matter at the first contact I was told that I should do a reset of the instance, which isn't really what I want since I want the metadata presented above plus all customizations, Microsoft was going to investigate and come back to me.

Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se