locked
CRM 2011 with SQL Peer-to-Peer Replication RRS feed

  • Question

  • I am attempting to setup peer-to-peer replication using CRM 2011 and MS SQL 2008 R2.  When I publish the company_MSCRM database, and then attempt to modify the subscription options and enable peer-to-peer subscriptions, i get an error message that says: "peer-to-peer publications do not support replicating timestamp columns as varbinary(8)."  what gives?  What special options do i need to configure in order to setup bi-directional (peer-to-peer) database replication?  FYI, i began this process by closely following instructions in the technet library article: http://technet.microsoft.com/en-us/library/ms152536.aspx  Any help is very much appreciated!  Thanks in advance!

    Thursday, February 24, 2011 9:27 AM

All replies

  • I think you'll find it is very hard to get any database replication to work with CRM, and I really don't think you'll get bi-directional replication working. The biggest problem is that certain entities (such as users) will have different primary keys in each database, and if you tried directly replicating a record it would be linked to a primary key that does not exist in the destination database. This is just one problem; I expect there would be many others

    I suggest you look at this whitepaper for more appropriate approaches


    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Thursday, February 24, 2011 7:16 PM
    Moderator
  • Hey David,

    They will not have different primary keys since CRM is setup on one box, and then the config and data DBs are backed up and restored onto the second box.  all sql data is identical on the second box at the time of replication setup.  from then on i can setup a secondary crm installation that reads the existing company data and config db from the replicated / restored / identical databases on the second SQL Server.  there should be no variance in primary keys, guids, or anything.... 

    Thursday, February 24, 2011 8:04 PM
  • I'm sorry, i should add that i have thoroughly read the whitepaper and guide that you linked to.  Unfortunately it primarily refers to a multi-tenant situation, where one tenant is editing parts that the other tenant should see.  In my case, i have just one tenant and i merely need LAN speed access at two locations which are connected with a solid 3mbps pipe which is sufficient for the tiny number of users and records that we have but very much insufficient for remotely accessing the CRM site since the response time is way too laggy.  Also, offline outlook caching is a very bad solution for my particular case, and downright unpleasant for most others.
    Thursday, February 24, 2011 8:09 PM
  • I still think you're significantly underestimating the difficulties in getting this to work. Have you actually got a second CRM installation to work by backing up and restoring the config and mscrm databases ? I would be very surprised if you have, as the config database contains deployment specific information, such as server URLs, so I wouldn't expect you could restore it onto another deployment and have it work. You could consider just restoring the mscrm database, but the config database stores the systemuserid of users, and I think you wouldn't be able to do this and keep the users working. Again, this is just the first of many problems that I'm not sure can be resolved.

    Don't get me wrong, I think it would great if SQL replication could be used in your scenario, but I just don't think it's possible, and my recommendation would be to give up now, rather than after spending more time on it. Sorry if this is not the message you want to hear.

    What's the network latency between the sites ? If the overall latency is not too high, then your best option would probably to invest in increasing the bandwidth to give reasonable network performance 


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    Friday, February 25, 2011 7:15 PM
    Moderator
  • Andrew,

    Did you have any success with replication? I have similar requirements: I have two servers with  databases that I'd like to keep in sync. One is production and other is DR. Normally all the users update primary database. But we conduct DR test two times a year. In this case all the users work from another office and update DR server. So before the test we need to synchronize DR with Prod so it has exactly the same data. After the test we need to bring all the changes to Prod server. It's so cumbersome..

    I was thinking to set-up bi-directional transactional replication  the kind when you can update the publisher or the subscriber?

    Thanks a lot,

    Lana


    Lana Feldman

    • Proposed as answer by icy.wuBanned Friday, May 4, 2012 9:55 AM
    Tuesday, May 1, 2012 9:26 PM
  • Hi Lana,

    I never got this working, I got side-tracked and the whole project fell by the wayside.  I got stuck at a point where SQL wasn't allowing replication because certain table elements weren't supported in a replication scenario.  I suspect that i could have ignored the unsupported tables/collumns and kept going with little/no consequences.  I'd be very interested in hearing about the results of your efforts.

    Best,

    Andrew

    Tuesday, May 1, 2012 9:29 PM
  • Thanks for the response.

    I am concerned regarding replicating fields like "notes". They a big data types and may include attachments. Well, along with other unexpected problems. I wish MS would clearly state what is supported and what is not :) I'll let you know if I'll have any success.

    Do you have multiple crm DB now? I wonder how you keep them in sync?

    Thanks.

    Lana


    Lana Feldman

    Tuesday, May 1, 2012 9:36 PM
  • Lana,

    I use salesforce now, :-/.  What you are trying to do (and what i was trying to do) is not a supported configuration of CRM.  The CRM team will offer you no support in this.  However, at it's core, it's a just like any other SQL database and I firmly believe it should be possible to replicate...i just lost the will to work through it.

    /Andrew

    Tuesday, May 1, 2012 9:40 PM
  • To respond to the last post, I suggest it's a serious underestimation of the complexity to consider this to be 'just like any other SQL database'. The two main specific complexities are:

    • CRM has 2 databases, with data interdependencies between them. SQL replication works at the database level, so cannot maintain this reliably. Also, one database contains deployment-specific data, which shouldn't be replicated. The data interdependency relates to user accounts and their creation, and I don't see a way you could add new users and have them available in the replicated system
    • SQL replication is based on a stable SQL schema (tables and columns). CRM is a customisable system, and it is normal for there to be ongoing customisations that create new tables or columns. Whenever this happens, you'd have to reestablish replication

    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk


    Wednesday, May 2, 2012 8:14 AM
    Moderator
  • David,

    What do you think about regular, one-way replication for only "company" database. Let's say we'd use the replicated copy for read-only access. Does this work? Thanks.


    Lana Feldman

    Thursday, May 3, 2012 1:50 PM