Answered by:
CRM: Two-way SQL replication

Question
-
Hello all.
Is it possible to setup a two-way SQL replication for my CRM 2011 servers?
Basically, I have a primary and DR server. I would like to set up a replication so users can update eather database and changes would hit both servers. Has anyone tried this?
Thanks a lot.
Lana Feldman
Tuesday, May 1, 2012 3:00 PM
Answers
-
First of all, I really wouldn't recommend bi-directional replication. It would be hard to avoid data conflicts, you'll probably have significant performance overheads, and you'd need to reestablish replication whenever a schema change is made (e.g. new field or entity is created or deleted).
To answer the specific question about the MSCRM_Config database - this database should not be replicated in its entirety, as it contains deployment-specific data. However, it will contain information about users, so if you add a new user into one CRM deployment, you'd have to add equivalent data into the MSCRM_Config in the other deployment, and there's no supported way to do this.
To summarise, you might be able to get this to work if you don't make any subsequent schema changes, and you don't add new users, and even so I wouldn't trust the stability. I'd suggest you either use log shipping, or backup/restore with an import of the CRM organisation whenever you want to copy the data
Microsoft CRM MVP - http://mscrmuk.blogspot.com http://www.excitation.co.uk
- Edited by DavidJennawayMVP, Moderator Wednesday, May 2, 2012 8:06 AM
- Proposed as answer by Jamie MileyModerator Friday, May 4, 2012 1:47 PM
- Marked as answer by lana7220 Friday, May 4, 2012 1:48 PM
Wednesday, May 2, 2012 8:06 AMModerator -
If you add users to different environments or if you simply add new users then you must sync the CONFIG db. Some system-configuration changes also affect the CONFIG database.
With regards synchronising the notes, when you sync you basically copying data, and data is treated equally regardless of the format or encryption, you are always transferring bits across. Your question then depends on how many attachments and how large was allowed to be uploaded. It is down to storage growth and DR storage capacity and internet link usage etc. Hope this helps :)
Note: A 2-way sync should only be considered for DR purposes.
Visit my blog for CRM material, improving performance, kerberos, IFD, development tips, etc. :) http://quantusdynamics.blogspot.com
- Edited by nrodriEditor Wednesday, May 2, 2012 4:34 PM
- Marked as answer by lana7220 Friday, May 4, 2012 1:48 PM
Wednesday, May 2, 2012 3:14 PMAnswerer -
That should be fine. Otherwise for disaster recover purposes clustering might be a better scenario.
Jamie Miley
Check out my about.me profile!
http://mileyja.blogspot.com
Linked-In Profile
Follow Me on Twitter!- Proposed as answer by Jamie MileyModerator Friday, May 4, 2012 1:46 PM
- Marked as answer by lana7220 Friday, May 4, 2012 1:48 PM
Friday, May 4, 2012 1:46 PMModerator
All replies
-
Hi lana,
That is quite difficult to maintain, if you have two users updating records on two different databases, even if you replicate changes as they happen there is always delays in users getting the same data.
If is for a DR scenario you should only do 1-way replication. 2-way replication should only be used for sites/offices with different time-zones where you have users working at different times.
Regards
Nuno
Visit my blog for CRM material, improving performance, kerberos, IFD, development tips, etc. :) http://quantusdynamics.blogspot.com
- Edited by nrodriEditor Tuesday, May 1, 2012 4:42 PM
Tuesday, May 1, 2012 4:42 PMAnswerer -
Thank you for the reply!
In my case 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.
So I was thinking if the replication is bi-directional we can simply let users work from ether office and have the data synconized automatically.
What do you think? Thanks.
Lana Feldman
Tuesday, May 1, 2012 4:58 PM -
In that case that would be okay. In case you synching the CONFIG database as well, bear in mind the Organization table SQL strings needs a manual update.
Visit my blog for CRM material, improving performance, kerberos, IFD, development tips, etc. :) http://quantusdynamics.blogspot.com
Tuesday, May 1, 2012 8:37 PMAnswerer -
Two questions:
When would I need to sync Config DB?
Is it ok to replicate tables with "notes"? They are large datatype plus they could contain attachments?
Thanks
Lana Feldman
Tuesday, May 1, 2012 8:41 PM -
First of all, I really wouldn't recommend bi-directional replication. It would be hard to avoid data conflicts, you'll probably have significant performance overheads, and you'd need to reestablish replication whenever a schema change is made (e.g. new field or entity is created or deleted).
To answer the specific question about the MSCRM_Config database - this database should not be replicated in its entirety, as it contains deployment-specific data. However, it will contain information about users, so if you add a new user into one CRM deployment, you'd have to add equivalent data into the MSCRM_Config in the other deployment, and there's no supported way to do this.
To summarise, you might be able to get this to work if you don't make any subsequent schema changes, and you don't add new users, and even so I wouldn't trust the stability. I'd suggest you either use log shipping, or backup/restore with an import of the CRM organisation whenever you want to copy the data
Microsoft CRM MVP - http://mscrmuk.blogspot.com http://www.excitation.co.uk
- Edited by DavidJennawayMVP, Moderator Wednesday, May 2, 2012 8:06 AM
- Proposed as answer by Jamie MileyModerator Friday, May 4, 2012 1:47 PM
- Marked as answer by lana7220 Friday, May 4, 2012 1:48 PM
Wednesday, May 2, 2012 8:06 AMModerator -
If you add users to different environments or if you simply add new users then you must sync the CONFIG db. Some system-configuration changes also affect the CONFIG database.
With regards synchronising the notes, when you sync you basically copying data, and data is treated equally regardless of the format or encryption, you are always transferring bits across. Your question then depends on how many attachments and how large was allowed to be uploaded. It is down to storage growth and DR storage capacity and internet link usage etc. Hope this helps :)
Note: A 2-way sync should only be considered for DR purposes.
Visit my blog for CRM material, improving performance, kerberos, IFD, development tips, etc. :) http://quantusdynamics.blogspot.com
- Edited by nrodriEditor Wednesday, May 2, 2012 4:34 PM
- Marked as answer by lana7220 Friday, May 4, 2012 1:48 PM
Wednesday, May 2, 2012 3:14 PMAnswerer -
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
- Proposed as answer by Jamie MileyModerator Friday, May 4, 2012 1:46 PM
Friday, May 4, 2012 1:43 PM -
That should be fine. Otherwise for disaster recover purposes clustering might be a better scenario.
Jamie Miley
Check out my about.me profile!
http://mileyja.blogspot.com
Linked-In Profile
Follow Me on Twitter!- Proposed as answer by Jamie MileyModerator Friday, May 4, 2012 1:46 PM
- Marked as answer by lana7220 Friday, May 4, 2012 1:48 PM
Friday, May 4, 2012 1:46 PMModerator