February 16, 2015 at 10:52 pm
hi,
I have read riplication when sql server 2005 came, please tel me if replication can be used in following situation
q1)I have two database with quite diffrent schema.
but we are able to transfer some data which is comman using tsql, right now it is one sided , because one database has more data and other one has less.
so two way is not possible.
Q2) There are some masters which can be made two sided but schema is quite diffrent . Becasue data is more or less same so i can think of two sided also by fixed few values where less data is getting in.
yours sincerley.
February 17, 2015 at 1:35 am
No. Replication allows for different indexes, different procedures/views, but the tables should be the same structure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2015 at 2:53 am
For replication table structure must be same. You can use TSQL and create SQL Server agent jobs if tables structure is different.
February 17, 2015 at 5:13 am
Do you mean the schema differs between the Publisher and the Subscriber?
If so, we have this situation here and it can be done via scripting (I haven't used the Wizard for a very long time).
An example of scripting the Publication where the schema differs between Publisher and Subscriber is at http://sqlrambling.net/2014/10/06/replication-scripting-the-pull-subscription-basic-example/
February 17, 2015 at 7:58 pm
If you are using Transactional Replication, there is no requirement for the table definitions to be the same. It is much easier if the tables are the same at the publisher and the subscriber but it is not a hard requirement.
You would need to use stored procedures to deliver the transactions to the subscriber. You would need to write custom stored procedures for each article in the publication. For each article you would need to write 3 stored procedures - one each for INSERT, UPDATE and INSERT transactions. In the definition of each article you will need to specify the name of each of the stored procedures. You will also need to make sure that they get created etc.
Personally, I would avoid this is I could but if you need to do it, it definitely is possible. It will just take a lot of effort to write and test the code you are using and that may end up stopping you from doing this sort of thing.
If you are planning to use Merge Replication, I think that you cannot have tables with different definitions. There is a whole heap more system generated code in each database to make Merge Replication work - too many places were the table structure needs to be known (and the same for each article).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply