January 28, 2011 at 10:20 am
Is it possible to replicate 2 tables from 2 different databases if their columns are ordered differently using Transactional Replication?
A
B
C
C
A
B
January 28, 2011 at 10:46 am
I'm not sure I follow you on this.
Transactional Replication has one source and one target. It seems like you're talking about two sources.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2011 at 10:53 am
Yes I am replicating a table from 3 different servers databases its an order table
In the table all the columns are the same its the column order that differs.
one table has
DueDate
PrintFlag
NewDueDate
another has
DueDate
NewDueDate
PrintFlag
Is it possible to replicate these tables to consolidate them?
January 28, 2011 at 12:08 pm
Do they all have the same schema and name? If so, you'll need to replicate them into three different databases (so far as I know). You can then use a View with Union All statements to consolidate them into a single virtual table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2011 at 12:29 pm
Do they all have the same schema and name? If so, you'll need to replicate them into three different databases (so far as I know). You can then use a View with Union All statements to consolidate them into a single virtual table.
If the tables on each server are the same, you can replicate all servrs to one table as long as teh publication has a way to identify it from the data from other servers. There has to be a PK that is used in order to replicate. In my case, we have 64 stores that replicate 2 tabels at the store to a set of two tables at corporate. As part of the PK, we have included the store number. Now update/delete is tied to a PK that ensures that only the data for that store is touched.
There is a way to have tables that have the same fileds but in a different order, but it is a lot more effort in that you have to setup SQL for your replication and if you have a lot of subscribers/distributors then that becomes a management nightmare.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
January 28, 2011 at 12:32 pm
I have a unique way of identifying the records, what I need is to be able to accomodate for columns being out of order. How would I setup replication to accomodate this? Its only for 40 tables on 4 different servers.
January 28, 2011 at 12:34 pm
Oh I forgot to add its only one subscriber subscribing to the four publications
January 28, 2011 at 12:53 pm
what I need is to be able to accomodate for columns being out of order
I believe that setting the default binding to true will accomplish what you are asking, but make sure that you set the Action If Name Is in Use flag to Keep existing object unchanged.
Read up on the default binding first though.
40 tables to replicate per server with different schemas might prove to be a nightmare further down the road. Why do the replicated tables have to be structured differently? Are you doind a select * somplace on these?
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
January 28, 2011 at 12:59 pm
The structure cant be changed because of the fact that there are custom applications written and compiled that could potentially be using SELECT * somewhere and the people who would know for sure are no longer with the company but these applications control everything from EDI to MRP so this is the only other option to create a consolidated database with replication without bringing business to a screeching halt.
January 28, 2011 at 1:06 pm
Again, check to see if the default binding will accomplish your requirement. Otherwise I would opt for GSquared's recommendation of creating a view over the 4 different variations of the tables.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
January 28, 2011 at 1:08 pm
I am using SQL 2000 to accomplish this and I dont see an option for Default Binding.
So if Default Binding doesnt work there is no way to accomplish a consolidated database through replication?
The tables all have the same columns and data types just a few tables have different field orders.
January 28, 2011 at 1:13 pm
Sorry - thought you were using SS2005. Going to stick then with the view. It would be the easiest in your case and for the future.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
January 28, 2011 at 1:29 pm
A view is not going to accomplish what is being requested.
So there isnt a way to customize the replication trigger to accomodate for the different field order?
January 28, 2011 at 1:53 pm
michael.morse (1/28/2011)
A view is not going to accomplish what is being requested.So there isnt a way to customize the replication trigger to accomodate for the different field order?
What method are you using to replicate the insert / update / delete statements? IF you are using stored procedures there will only be one occurrence of the procedure on the subscriber, unless you name them differently in the table properties within the individual publications. The stored procedure creation on the subscriber should take into account the column order (pretty sure).
If you opt to use insert / update / delete statements that will automatically take care of the issue since it will specify columns in the statements and column order won't matter.
The above information is all based on 2005 but I believe it will work in 2000. I know there were stored procedure issues in 2K so, proceed carefully. From what I recall that was mainly with the ability to alter published tables (articles) and having the procedures update correctly. This would be especially complex in your scenario, especially if you have multiple publishers.
One other note, if you go with the direct sql statements it will bloat your cache as they will not be used more than 1 time. So, your plans in cache with a single use count will go way up. Not good if you have a lot of activity.
...please verify all that I say on this too as I have not tested this to validate.... I'm working off understanding of how things work which implies some assumption. Not necessarily safe for a production system. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 28, 2011 at 1:57 pm
How can I tell what I am using? I used the setup wizard
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply