Needing to replicate pieces of CRM database

Replication should work on any SQL database regardless the application that uses it. If Microsoft CRM uses SQL database, as one would expect, then is possible providing you have the right permissions.

Just setup a transactional replication from the CRM SQL server from your database to the target server and database. You will add as articles to be replicated only the tables you need.

The replication will not give you trouble unless the CRM application attempts to modify the table structure. You can't do that in the normal way with replicated tables. If that doesn't happen then you should be fine.

Here is a step by step guide to setup the transactional replication:

http://www.databasejournal.com/features/mssql/article.php/1438201/Setting-Up-Transactional-Replication-A-Step-by-step-Guide.htm

Sorry, that was an old article, here is a newer one:

http://www.code-magazine.com/article.aspx?quickid=0311101&page=1

Anyway, there are wizards and tools when setting up replication so you should be able to do it.

Or here: http://www.informit.com/articles/article.aspx?p=170234

Thanks Zberteoc, but I'm already very familiar with and aware of of SQL transactional replication capabilities, how to setup, etc.  My concern is specifically with MS CRM, and whether this will cause problems for my CRM users.  As far as I can tell, CRM modifies the actual table structure when you add a new custom field for an Account, or Opportunity, etc., and if those tables were replicated I'm sure those modifications would fail.

Any other suggestions from anyone?  I'm considering sorta building my own transactional replication, using triggers to keep track of insert/update/delete comands against the AccountBase and OpportunityBase tables, which I could transfer to the other SQL server later.  Like I said SSIS may be an option but the tables are really large, and I need the data updated as close to real-time as possible, it would be tough to identify only the changed rows using SSIS and just transfer those.

<<Thanks Zberteoc, but I'm already very familiar with and aware of of SQL transactional replication capabilities, how to setup, etc.  My concern is specifically with MS CRM, and whether this will cause problems for my CRM users.  As far as I can tell, CRM modifies the actual table structure when you add a new custom field for an Account, or Opportunity, etc., and if those tables were replicated I'm sure those modifications would fail.>>

If that's the case  mortime452 no offense but I don't understand really the purpose of this question as you already suposedly know the answer to it: simply YOU CAN"T replicate those tables.

On the other hand nobody stops you from pulling the data from the tables where and when you want by using a simple DTS or SSIS, on depending on your SQL version or by creating a linked server to the CRM database. Adding triggers is a less happy solution as it might impact your performance but is doable as well.

So, there are solutions to your problem but replication is not one of them.

Good luck!

OK, thanks all, definitely sounds like transactional replication is a bad idea.

I'm not super familiar with CRM as you can probably tell.  What is the "normal" way folks work with CRM in a disconnected or multi-site environment?  I mean, if I have an office in Singapore and an office in Chicago, and they both require LAN-speed access to the same CRM data, do they both have to access the same SQL server?  Is there no supported configuration for multiple databases or synchronization between sites?

It is only a bad Ideea because of how CRM application works, and I know nothing about that except of what I found in this question. My post was strictly from database perspective.

If a table is replicated simple alter statements will fail so you can't use replication in the CRM context because the application DOES alter tables, as I learned.

Other than that as I already said is a matter of preference how you make data accessible to other servers. Probably  a linked server would work or using OPENROWSET or OPENDATASET functions withd ad-hoc queries or just build your own read only pull data process from the CRM database on the server where you need that data by using DTS, SSIS or just an application for this purpose. As long as you don't change the structure and data in those tables, except through the CRM application of course you should be fine.