CRM & SQL Server BizTalk Integration

We had a project in which there was a requirement for an integration between Microsoft SQL server and Microsoft Dynamics CRM 2011 using BizTalk 2013 R2, the solution in brief was Listening on SQL server using the WCF-Custom Adapter then using the orchestration to do data mapping then send the new transformed message to a custom WCF service that handles inserting the message as a new case in CRM, below are the hard learned lessons for such integration:

  • When receiving message from SQL server with a specific schema, make sure the SQL statement is returning xml results matching the same schema defined in the orchestration or the mapping process will not be able of transforming the message to the target schema.
  • When constructing a message using message assignment in an orchestration, check the message in the constructed messages property in the construct shape to resolve the build time error in case trying to assign value to a message not constructed from a receive shape.
  • When configuring the WCF-Custom SQL binding connection, if there is only one default instance on the SQL server then you need to use the below format with double slash between the server name and the database name for the URI connection string: mssql://ServerName//DatabaseName
  • When using the WCf-Custom adapter, there is an important property named UseAmbientTransaction which is defaulted to true, this property specifies whether the adapter will use DTC transactions in the communication with SQL server or use normal queries that doesn’t require DTC, you need to make sure that DTC is enabled on SQL server and is working properly by using a tool named DTCPing, sometimes the adapter will not work properly while not throwing any errors on the BizTalk server due to DTC issues like DNS name resolution that is blocking the transaction only on the SQL server side.

The experience with this integration was fruitful, interesting, and leveraged how BizTalk can be used to build robust and powerful integration solutions.

WCF and ASP.NET Web API, When to use what?

I found myself confused between WCF services and the new ASP.NET Web API, I researched about the differences and resulted to the following:

The following table describes the major features of each technology.

WCF ASP.NET Web API
Enables building services that support multiple transport protocols (HTTP, TCP, UDP, and custom transports) and allows switching between them. HTTP only. First-class programming model for HTTP. More suitable for access from various browsers, mobile devices etc enabling wide reach.
Enables building services that support multiple encodings (Text, MTOM, and Binary) of the same message type and allows switching between them. Enables building Web APIs that support wide variety of media types including XML, JSON etc.
Supports building services with WS-* standards like Reliable Messaging, Transactions, Message Security. Uses basic protocol and formats such as HTTP, WebSockets, SSL, JQuery, JSON, and XML. There is no support for higher level protocols such as Reliable Messaging or Transactions.
Supports Request-Reply, One Way, and Duplex message exchange patterns. HTTP is request/response but additional patterns can be supported through SignalRand WebSockets integration.
WCF SOAP services can be described in WSDL allowing automated tools to generate client proxies even for services with complex schemas. There is a variety of ways to describe a Web API ranging from auto-generated HTML help page describing snippets to structured metadata for OData integrated APIs.
Ships with the .NET framework. Ships with .NET framework but is open-source and is also available out-of-band as independent download.

Use WCF to create reliable, secure web services that accessible over a variety of transports. Use ASP.NET Web API to create HTTP-based services that are accessible from a wide variety of clients. Use ASP.NET Web API if you are creating and designing new REST-style services. Although WCF provides some support for writing REST-style services, the support for REST in ASP.NET Web API is more complete and all future REST feature improvements will be made in ASP.NET Web API. If you have an existing WCF service and you want to expose additional REST endpoints, use WCF and the WebHttpBinding.

Additionally;

  1. Choose WCF when you want to create a service that should support special scenarios such as one way messaging, message queues, duplex communication etc.
  2. Choose WCF when you want to create a service that can use fast transport channels when available, such as TCP, Named Pipes, or maybe even UDP (in WCF 4.5), and you also want to support HTTP when all other transport channels are unavailable.
  3. Choose Web API when you want to create a resource-oriented services over HTTP that can use the full features of HTTP (like URIs, request/response headers, caching, versioning, various content formats).
  4. Choose Web API when you want to expose your service to a broad range of clients including browsers, mobiles, iphone and tablets.
  5. Choose Web API when you don’t have a requirement to support SOAP.

Reference:

https://msdn.microsoft.com/en-us/library/jj823172%28v=vs.110%29.aspx

http://www.codefoster.com/webservicesforward/

https://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DEV-B209#fbid=

Install CRM 2013 Developer Toolkit on Visual Studio 2013

I had a hard time to install the CRM developer toolkit found in CRM 2013 SDK on VS 2013 that is running on Windows Server 2012 R2, so I thought of sharing the steps to make this happen:

  1. Download the CRM 2013 SDK and after extracting it navigate to: sdk\Tools\DeveloperToolkit\
  2. Open a Command Prompt as Administrator
  3. Run: msiexec /a CrmDeveloperToolsVS12_Installer.msi /qb TARGETDIR=c:\temp
  4. Navigate to the folder which you extracted the files
  5. Open the Visual Studio folder
  6. Open the archive file Microsoft.CrmDeveloperTools.vsix (I used 7-Zip for this)
  7. Edit extension.vsixmanifest
  8. Replace: InstalledByMsi=”true” with: InstalledByMsi=”false”
  9. Replace all instances of: Version=”[11.0,12.0)” with: Version=”[11.0,12.0]“ (note the closing bracket changed) and update the file in the archive.
  10. Double click to install the VSIX package Microsoft.CrmDeveloperTools.vsix found in the visual studio folder.
  11. Add the files found in the folder named “CRM MSBuild” extracted from the CrmDeveloperToolsVS12_Installer.msi to the folder in the path “C:\Program Files (x86)\MSBuild\Microsoft\CRM”
  12. If things didn’t work out you may need to edit C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe.config and add the following lines:
    <dependentAssembly>
    <assemblyIdentity name=”Microsoft.Windows.Design.Host” publicKeyToken=”b03f5f7f11d50a3a” culture=”neutral” />
    <bindingRedirect oldVersion=”4.0.0.0-4.1.0.0″ newVersion=”4.2.0.0″ />
    </dependentAssembly>

In case the CRM explorer window is not loading when opening the CRM Toolkit project types you will need to add the below lines in the visual studio solution file:

Global
  GlobalSection(CRMSolutionProperties) = preSolution
    SolutionIsBoundToCRM = TRUE
  EndGlobalSection
EndGlobal

Installing CRM 2011 on Windows Server 2012 R2

After upgrading the OS from Windows Server 2008 R2 to Windows Server 2012 R2 I found that all IIS websites and app pools were deleted and was faced with the below message when trying to open IIS 8.5:

One or more problems have been detected during IIS upgrade. As a precaution, one or more IIS services have been disabled. Please review the following error messages:

Data loss has occurred
You upgraded your operating system or switched from one Windows SKU to another SKU, and data from the previous installation of IIS has been lost or copied incorrectly. As a result, the services W3SVC and MSFTPSVC have been disabled. Before enabling these services, you should review the latest IIS 7.0 compatibility information.

You will need to install latest windows updates first then enable the disabled WWW service as well as start other services that were stopped afterwards you should repair or reinstall CRM 2011 to recreate the CRM IIS website and application pool.

There are many articles on the internet showing how the install CRM 2011 on Windows Server 2012 R2 after updating the setup files like that one:

https://dynamicscrmherald.wordpress.com/2014/01/06/installing-microsoft-dynamics-crm-2011-on-windows-server-2012-r2/

The 2 points to consider after the installation specially when upgrading from Windows Server 2008 R2 is to update the URL rewrite module to version 2.0 to be compatible with IIS 8.5 and ensure that the application pool account is having read permission on the CRMWeb folder.

CRM 2011 Deletion Service Error

We encountered a daily error reported by the deletion service as follows:

Error: Deletion Service encountered an internal error for
Exception: System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Error: Deletion Service failed to clean up the PrincipalObjectAccess table for
Exception: System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

The deletion service was scheduled to run at 7 AM at the start of the core business hours and that error caused strange CRM outage in which CRM is showing 404 directory not found error without any further errors thrown on the front end servers.

The first quick resolution we applied by modifying the deletion service scheduled execution time using the great tool CRM 2011 Job Editor

Second resolution which take around a month with Microsoft premier support was to clear the table named [SubscriptionTrackingDeletedObject] in the CRM organization database, you need to make sure before clearing that table that all users using CRM for Outlook are in Online mode at that time (that in case you are using the offline mode in your company)  and that made the magic and stopped the deletion service error.