Tuesday, January 22, 2013

Principal Object Access Table - POA table grows large

In Microsoft Dynamics CRM 2011 there is a known performance issue when the POA table grow large.
Just the other day I was working with a customer to help them pin down why the POA table was so large in their organization database. We were also investigating if they could benefit from the use of Teams in CRM 2011 instead of using “share with users” as their POA table was growing a lot.
If users frequently share records with other users you could benefit from setting teams as owners of those records instead. Or to have the users share with Teams instead of specific set of users.
As we started our investigation we had a look at the POA table to see which objects they were sharing.
The POA table stores information about records in CRM that is shared with other users or teams. The share can be made either by a user, custom code that shares a record, or by the system due to relationship behaviors or system settings.
You can configure system settings to “Share record with the previous owner”. This might not be needed in your system. When this setting is activated, all records that are assigned to another user will be automatically shared with the original user. And an entry in the POA table will also be created for that share.
clip_image001
.
The relationship behavior cause share records to be created in POA.
Let say User Adam owns an Account named Company A and the relationship between Account and Contact is set to the default. “Parental”, then the following will occur.
clip_image002
If another user creates a contact under Account Company a record in the POA table will be created. This record makes sure that user Adam will have access to the new contact under “his” Account. This might be as you want it to be and this might not be an issue at all.
But if we instead have a custom entity that is a parent of another custom entity that is used for logging or to keep track of something that will generate a lot of records. If the parent record is owned by user Adam and a plugin or some import job creates the child records under a system account (integration / admin account) CRM will create a record for each child record in the POA table. This might result in a POA table growing out of control.
You will not be able to see this under the “share” properties in CRM. It´s only added to the POA table in this case. This is probably not something you need. If you instead set the relationship configuration to “Referential” you will not generate any extra record in the POA table.
clip_image003
Here is a SQL Query you can start with to see what is taking up the space in your POA table.
(Only possible in On-Premise installations)
SELECT ObjectTypeCode, COUNT(ObjectTypeCode) AS Total
FROM PrincipalObjectAccess
GROUP BY ObjectTypeCode
ORDER BY Total DESC
To find the name of the entity (ObjectTypeCode) you can use the following select statement in SQL Server.
Change the name of the database “CRMORGDB_MSCRM” to your organization database name.
SELECT DISTINCT name, objecttypecode
FROM [CRMORGDB_MSCRM].[MetadataSchema].[Entity]
ORDER BY name ASC
I hope this will help you to prevent some unnecessary records in the POA Table
Happy POA record hunting clip_image004
//Hans
















Thursday, December 13, 2012

Error during formLoad Access is denied in CRM 2011

 

Hi.
I would like to share some information with you regarding a little error message that you might stumble on if you have CRM 2011 and enable document management.

If you get an error message from the webpage during form load in CRM 2011 then you might be having the same problem as I just had.

The error message is
Error during FormLoad Access is denied

access_denied_formLoad

This error was generated because the CRM Server was configured with document management and the URL for the SharePoint server was not configured with FQDN (http://SharePointServer) and the users access CRM with the server name in FQDN format (http://crmserver.domain.com)

To resolve this you should use the same format for your SharePoint URL and your CRM URL.

Like http://SharePointServer and http://crmserver
This will place the URLs in the same security zone in Internet Explorer (if your admin has not configured any special IE Policy restrictions)
All URLs that you access without FQDN format will be placed in the Local intranet Zone in Internet Explorer by default

If you click to open the document location in your CRM form within the left navigation area you might also see a warning message from Internet Explorer like the following

image

 

I hope this is helpful for some one else as well Ler

Happy troubleshooting!

//Hans

Wednesday, November 7, 2012

Protect CRM 2011 data with Transparent Data Encryption


In this post I will provide you with a little example on how to protect your Microsoft Dynamics CRM Organization database with SQL Server Transparent Data Encryption (TDE).

If you want to protect your database even the backup files so that no one will be able to take one of your backup files or disks and just move it to another SQL server and be able to get the information from your system you should use TDE to encrypt your database.
TDE will not encrypt data sent over the network!
Do not use this in production without reading more about it first!
Make sure you know enough to maintain it and know all about TDE before you use it in production.

In SQL Server 2012 Enterprise Edition you can try this out.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SuperPassword';
GO
CREATE CERTIFICATE ServerCertificate WITH SUBJECT = 'Server Certificate DEK';
GO
USE CRMORG_MSCRM;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCertificate;
GO
ALTER DATABASE CRMORG_MSCRM
SET ENCRYPTION ON;
GO

Encryptions you may choose from are
DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES

It’s very IMPORTANT to backup you certificate ServerCertificate.
Without the Certificate backup you will not be able to use your database files / backup files if the server crash and burns, or if you need to recover your data.

After you run the following SQL commands the backup files will be placed in your MSSQL\Data folder. Make sure to back these up to a secure location.

USE master
GO
BACKUP CERTIFICATE ServerCertificate
TO FILE = 'ServerCertExport'
WITH PRIVATE KEY
(
FILE ='PrivateKeyFile',
ENCRYPTION BY PASSWORD ='YourOtherStrongPassword'
);
GO

Note* if you use TDE on any database in you SQL instance the TempDB will also be encrypted. This might have an impact on performance.

If you lite to read more about TDE you can start with these pages on MSDN.
About TDE.
http://msdn.microsoft.com/en-us/library/bb934049.aspx

About Encryption keys.
http://msdn.microsoft.com/en-us/library/ms345262.aspx

Be safe!

//Hans

Wednesday, October 24, 2012

Installing CRM 2011 SSRS Data Connector on Windows Server 2012


I will share some information with you about installing CRM 2011 SSRS Connector in Windows Server 2012. You might already have customers that use SQL Server 2012 and Windows server 2012.
Lets say you want to install SQL 2012 on a Windows Server 2012 and then use SQL Server 2012 Reporting Services for your Microsoft Dynamics CRM 2011 server.
Even though CRM 2011 is not supported with Windows Server 2012 yet.

During the installation of CRM SSRS Connector you will get an error.
The error message is Windows Identity Foundation Installation failed
The error looks like this.
error Win 2012 server

To get around the setup error you must add Windows Identity Foundation from Features. In Windows Server 2012 WIF is a Server feature. So just add the feature.
wif

After you have added the Feature you can run the setup again.

ReportConnectorSuccess
Next the setup will continue to install Microsoft .Net Framework 4 if it’s not already installed.
Remember that Windows Server 2012 is not supported for CRM 2011 yet.
Check out Microsoft Dynamics CRM 2011 Compatibility list to see the versions that are Supported. http://support.microsoft.com/kb/2669061
I really look forward to learn more about Windows Server 2012 !
And I’m currently spending my evenings with SQL 2012 preparing for exam 70-462.
So much new things to learn, really exciting Ler
Cheers!
//Hans

Tuesday, October 2, 2012

Limit or no limit using CRM 2011 Quick Find queries

In CRM 2011 Rollup 10 there is a new system setting where you can choose to limit or not to limit the amount of records that is returned by quick find.
The default setting is to limit the records returned when users search using quick find queries.
As a best practice this should also be limited as it might impact performance of CRM if users do these kind of Quick Find queries.
Users should be using advanced find when searching for records that returns more than a few results.
If you choose to disable the limit for quick find queries you will find the setting under Administration / System settings and in the bottom of the general tab you can select “no”
quickfind  
The limit is 10,000 records
If a user performs a Quick find that returns more than that an error will be displayed
error quickfind
Error
The Quick Find limit is exceeded. Narrow your search by entering a longer query, or use Advanced Find.
But there might be business requirements to disable this limit and now you can do it from the System settings.
Hope you find this information helpful!

Wednesday, September 12, 2012

CRM 2011 Better performance for dashboards and advanced find


Recently Microsoft released an updated implementation guide.
I read an interesting note in there about the groups that are created by the CRM 2011 installer.
An new account is added to the SQL server called MSCRMSqlClrLogin.
mscrmsqlclrlogin

This account is used for time zone conversions when you use dashboards, charts and also when using advanced find.
But only when SQL server is enabled to use CLR. As Microsoft says this can significantly improve performance for these features.
To enable CLR in SQL Server you need to run the following query in SQL Management Studio.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Also make sure you are not using lightweight pooling in SQL server before you activate CLR. This is not supported with CLR enabled.
I hope this helps! Cheers

Monday, August 6, 2012

CRM Workflows Stuck in a Waiting State

Why some Workflows is stuck in a waiting state and a Generic SQL error is logged.
I resently had an issue at one of our customers with workflows in Micorosoft Dynamics CRM 2011.
If we triggered multiple instances of a workflow some got stuck in a waiting state, not all.
If we triggered the workflow on one record it worked.
So you might be facing the same issue as i did.
If you open up the waiting workflow under system jobs in CRM 2011 you might see an error like the following.
Workflow paused due to error: Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Generic SQL error.Detail: 
<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
  <ErrorCode>-2147204784</ErrorCode>
  <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
  <Message>Generic SQL error.</Message>
  <Timestamp>2012-06-15T08:47:45.7210596Z</Timestamp>
  <InnerFault>
    <ErrorCode>-2147204784</ErrorCode>
    <ErrorDetails xmlns:d3p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
    <Message>Generic SQL error.</Message>
    <Timestamp>2012-06-15T08:47:45.7220362Z</Timestamp>
    <InnerFault>
      <ErrorCode>-2147220970</ErrorCode>
      <ErrorDetails xmlns:d4p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
      <Message>System.Data.SqlClient.SqlException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #E0E9194D</Message>
      <Timestamp>2012-06-15T08:47:45.7308256Z</Timestamp>
      <InnerFault i:nil="true" />
      <TraceText i:nil="true" />
    </InnerFault>
    <TraceText i:nil="true" />
  </InnerFault>
  <TraceText i:nil="true" />
</OrganizationServiceFault>
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.Update(Entity entity, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType)
   at Microsoft.Crm.Extensibility.InprocessServiceProxy.UpdateCore(Entity entity)
   at Microsoft.Crm.Workflow.Services.UpdateActivityService.<>c__DisplayClass1.<UpdateInternal>b__0(IOrganizationService sdkService)
   at Microsoft.Crm.Workflow.Services.ActivityServiceBase.ExecuteInTransactedContext(ActivityDelegate activityDelegate)
   at Microsoft.Crm.Workflow.Services.UpdateActivityService.UpdateInternal(Entity entity, String stepId)
   at Microsoft.Crm.Workflow.Services.UpdateActivityService.ExecuteInternal(ActivityContext executionContext, UpdateEntity updateEntity)
   at Microsoft.Crm.Workflow.Services.UpdateActivityService.Execute(ActivityContext executionContext, UpdateEntity updateEntity)
   at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
   at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
This can error can be logged because of deadlock issues in the CRM organization database when you run multiple workflows at the same time.
You can run SQL Server profiler and trace locks to confirm that this is the reason for the error.
An excellent way to handle deadlock issues with Microsoft CRM is to activate the following for you CRM organization database.
SET READ_COMMITTED_SNAPSHOT ON
SET ALLOW_SNAPSHOT_ISOLATION ON

Make sure you have your SQL server tempdb on storage some extra space available for the tempdb because the tempdb will be used more by SQL server when you activate the above.