(Post courtesy Partner Technical Consultant specializing on Dynamics CRM)
Partners would like to increase the availability of Dynamics CRM data to customers worldwide or in different locations, thereby achieving redundancy.
They are asking about the possibility of using SQL Server Log Shipping / SQL Server Database Mirroring / SQL Server replication towards this goal. What is the Microsoft-supported synchronization mechanism to achieve some form of load-balancing?
Dynamics CRM 2011 offers a scale-out concept that includes front-end roles, back-end roles and deployment administration roles. With Network Load-Balancing and SSL Offloader we can achieve load-balancing of client side requests. For further details please see the diagram below.
SQL Server for Dynamics CRM is designed as a central data store and cannot be used simultaneously in two locations to distribute the load and have data synchronized bi-directionally.
For performance and data integrity reasons we recommend using SQL Server in a central location and have clients connect over the WAN using technologies such as via Remote Desktop Services.
Log Shipping and Database Mirroring cannot be used for two-way replication. Log Shipping and Database Mirroring can serve as components in a disaster recovery concept and provide a standby environment, which however does not allow for concurrent write access by users. Both technologies have certain requirements on available bandwidth, even though log files allow for high compression.
Log Shipping serves as a mechanism to have a stand-by server restoring the transaction log on a separate copy of the database at certain intervals, which can be configured. The database can be made accessible as read-only – with the exception of the restore times when a log backup gets applied – or for database backup purposes thereby alleviating the production system from that type of workload.
Synchronous Database mirroring is supported with Dynamics CRM. Database Mirroring with automatic failover is a high-availability mechanism which in the current version of SQL Server 2008 R2 operates on a per-database-level. The Dynamics CRM 2011 Implementation Guide provides details how to set up synchronous database mirroring for Dynamics CRM. Note that since Dynamics CRM uses multiple databases, you have to take extra precaution and manually ensure for example that objects in system databases are always in sync.
Only the Enterprise, Developer and Datacenter Edition of SQL Server 2008 R2 support asynchronous database mirroring to cope with large distances or varying bandwidth. Since Dynamics CRM has dependencies on the application tier, there is no documentation available how you would switch to a remote database while maintaining existing frontend and backend servers and Microsoft has not tested this scenario. A high-availability concept needs to consider all layers of the environment (network, switches, role servers, Active Directory, etc). Also note that asynchronous database mirroring can lead to possible data loss and consequently database inconsistencies.
SQL Server replication is not supported with Dynamics CRM 2011 since it causes changes on the database schema, for example by introducing triggers.
Replication solutions for products like Dynamics CRM 2011 or Microsoft SharePoint Server 2010 are typically provided by partners since they depend on a consulting engagement and will be supported by the partner, not Microsoft. You can search for Microsoft partner solutions at http://pinpoint.microsoft.com.
– Access the CRM system remotely by way of an Internet-Facing Deployment (IFD) and Claims authentication or allow controlled access via Forefront UAG for the web client. Note that UAG is not supported with the Outlook client at this time http://technet.microsoft.com/en-us/library/hh490315.aspx.
– Using a different CRM organization in each location and set up a common reporting across tenants. Or use custom programming via CRM API’s to asynchronously replicate defined fields or records via client-side events. Note, however, that this should only be a solution for a very limited synchronization scope.
– Use CRM Online to overcome some of the limitations of an on-premise solution and achieve better accessibility from remote locations. For performance considerations with regards to the geo-location of the CRM Online datacenter please see http://blogs.msdn.com/b/crminthefield/archive/2011/10/31/crm-online-performance-troubleshooting-ideas.aspx.