January 28, 2011 at 2:03 pm
I only need to modify the replication trigger for each individual database to replicate data to the standard structure and schema invidually from there I will use replication to consolidate into one database. Does this seem like a logical approach if possible?
January 28, 2011 at 2:03 pm
I dont suppose I could replicate a view to dump data into a table?
January 28, 2011 at 2:08 pm
Right click on the publication, Properties, select the table in question, on the right hand side select "Article Properties" drop down and select "Set properties of highlighted table article". Scroll to the bottom and you will see the section called "Statement Delivery". The replication commands are defined in there. The procedures used by each publisher can be unique (I think - again, please test this) but you will have to name them as such as the default name applied would be the same since they just create the procedures with the table name, etc.
To use straight SQL statements you would change the "Insert delivery format" to "Insert Statment", and similarly for updates / deletes.
Both options will probably work. If you have the ability to test this I would try the procedure method first as that will be the most robust moving forward.
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 2:12 pm
Does this apply to SQL 2000?
January 28, 2011 at 2:32 pm
Not certain. Sorry - I don't have a 2000 server to look at either. Are you not seeing those options?
I am curious though why the option that GSquared offered would not work as well. Can you elaborate?
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 2:59 pm
The problem with copying the tables and using a view is the plan is to consolidate and minimize the tables being used for the production applications.
January 28, 2011 at 3:03 pm
Right - but the application would only be pointed to the view. The application wouldn't have to care about the underlying tables.
Let me know if I am not understanding clearly.
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 3:07 pm
This is why it is going to be a nightmare to maintain.
Please provide the schema's for the published databases and the schema for the subscriber.
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 3:10 pm
Can you modify records in a view which in turn would update the tables?
January 28, 2011 at 3:16 pm
michael.morse (1/28/2011)
Can you modify records in a view which in turn would update the tables?
I guess I'm a bit confused. Are you planning on updating the table on the subscriber? IF so, are you expecting the updates to go back to the publisher?
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 3:20 pm
I believe they are planning on updating the tables at the subscriber but those updates dont have to go anywhere. This is in preparation of removing 4 sql 2000 database servers (eventually) and using one consolidated database until then the updates at the 4 sql servers needs to be added into the consolidated database.
January 28, 2011 at 3:22 pm
sjimmo (1/28/2011)
This is why it is going to be a nightmare to maintain.Please provide the schema's for the published databases and the schema for the subscriber.
What schema information do you need? The table structures?
January 28, 2011 at 3:22 pm
Ok, that makes sense. So then back to my earlier question, do you not see the option to change how the commands are replicated in the publication / article properties? If so, I'm thinking that the straight SQL solution will work effectively while you are in this transition. Still should test though.
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 3:25 pm
I did see where it references the stored procedures it uses for the tables. Do I create additional stored procedures for each table and reference the new stored procedure instead?
January 28, 2011 at 3:33 pm
michael.morse (1/28/2011)
I did see where it references the stored procedures it uses for the tables. Do I create additional stored procedures for each table and reference the new stored procedure instead?
Basically on each publisher you would have to change the default name for each procedure. This would be done without the subscription in place. Then you would add the subscription which would create the procedures on the subscriber with their unique names. Please note - this is untested and should be tested before you do it unless your environment is forgiving enough that it won't cause problems.
Changing the delivery option to insert / update / delete statements would be safer in my opinion. Still should test.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply