April 1, 2014 at 3:47 pm
We have a SQL Server 2005 instance that hosts an OLTP environment for our finance system (Navision). We have CRM on a SQL Server 2008R2 instance on a different machine from Navision (SQL Server 2005 instance). When an update of certain fields happens in the Customer table in Navision we want those changes to be pushed, in real time, to a Customer table in CRM. The CRM Customer table will have more columns than the Navision customer table and we only want certain columns from the Navision Customer table to be used to update the CRM Customer table.
Can we set up push replication from Navision to the table in CRM? What caveats may be encountered with something like using a trigger? I am really just hoping for some ideas on what is possible, and what others have done to implement something like this.
April 6, 2014 at 12:50 am
lmarkum (4/1/2014)
We have a SQL Server 2005 instance that hosts an OLTP environment for our finance system (Navision). We have CRM on a SQL Server 2008R2 instance on a different machine from Navision (SQL Server 2005 instance). When an update of certain fields happens in the Customer table in Navision we want those changes to be pushed, in real time, to a Customer table in CRM. The CRM Customer table will have more columns than the Navision customer table and we only want certain columns from the Navision Customer table to be used to update the CRM Customer table.Can we set up push replication from Navision to the table in CRM? What caveats may be encountered with something like using a trigger? I am really just hoping for some ideas on what is possible, and what others have done to implement something like this.
Replication from 2005 -> 2008 works fine, look at this Using Multiple Versions of SQL Server in a Replication Topology article.
A lot more details are needed to accurately comment and recommend on which is the best method, for instance, "in real time" has a very different meaning to different people.
😎
April 6, 2014 at 8:42 pm
Erikur,
What I mean by "real time" is that the update to the table in CRM needs to occur within a few seconds of the change in NAV. The issue I am seeing with replication is that what is being discussed or proposed by the Development team is replicating part of the NAV table to part of the CRM table. When I tested this in a test environment I could not get it to work because replication involves copying the table schema as well. So, because the table design in NAV is different from the table design in NAv, I could not do what I needed to do.
We've considered triggers on NAV to update the table in CRM. We have considered an SSIS package. We have considered Microsoft Messaging Queue.
April 6, 2014 at 9:36 pm
Transactional replication should be able to do what you need. you do not need to replicate to a table with the same structure as the source table. You can use your own stored procs to handle the insert/update/deletes on the replica and the code you use can easily handle what you want to do.
When you create the publication, you can specify that you want to use custom sprocs. It might be easier to start with the sprocs that SQL generates for you first so that you can get an idea of what you need and then customise these. It is important to match the parameter names/types in your custom sprocs.
If I am correct, you already have the target table, possibly with data that you may want to retain. If this is the case, when you initialise the subscription, you can tell replication that it should not recreate the table (and maybe not truncate or delete data in the table).
May 27, 2014 at 5:53 pm
Keep a watch on the distributor's transaction log, make sure it doesn't fill to capacity or that will halt things.
----------------------------------------------------
May 27, 2014 at 7:53 pm
MMartin1 (5/27/2014)
Keep a watch on the distributor's transaction log, make sure it doesn't fill to capacity or that will halt things.
That remark seemed to come from left field.
As per the original question, it appears the answers provided should answer it just fine. You can filter columns in transactional replication.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply