June 15, 2011 at 4:38 am
I successfully implemented the Transactional replication with Publisher, Distributor and Subscriber at different Machines with push scenario. I replicated all objects of database(Sps,tables,views,functions).
One of my friend told me that you should only replicate tables and then run scripts of Sps,views,functions at both publisher and Subscriber.
My questions are as
Q No1:-what is the better approach.
Q No 2:- what would be the effects on publisher if I Insert new data in table at subscriber in transactional replication.
Q No 3:- I created new table at the publisher and it could not replicated at subscriber. How to do this?
Thanks in Advance.
June 15, 2011 at 11:04 pm
azhar.iqbal499 (6/15/2011)
Q No 2:- what would be the effects on publisher if I Insert new data in table at subscriber in transactional replication.
No effect on publisher.
But, if you insert same data at publisher, replication will try to insert it again at subscriber and it will fail as PK already exists.
azhar.iqbal499 (6/15/2011)
One of my friend told me that you should only replicate tables and then run scripts of Sps,views,functions at both publisher and Subscriber.
Data keeps changing on an OLTP table due to constant insert/delete/update. But SPs, functions, views etc. are not modified at that rate. I think that is why your friend recommended you to run the script instead of replicating them.
azhar.iqbal499 (6/15/2011)
Q No 3:- I created new table at the publisher and it could not replicated at subscriber. How to do this?
Why it could not replicate? What was the error message?
June 16, 2011 at 12:00 am
Thanks for your reply.
There was no Error.but table was not replicated at the Subscriber End.
Thanks
June 16, 2011 at 12:09 am
When you create a new table, you have to add the table as an article.
June 16, 2011 at 1:00 am
One of my friend told me that you should only replicate tables and then run scripts of Sps,views,functions at both publisher and Subscriber.
My questions are as
Q No1:-what is the better approach.
Which approach is better depends on:
a) Do you need these objects on the subscriber - if all you need from the subscriber is access to data then you can ignore having the SP/view/function on the subscriber
b) How much in synch you want to keep these objects (without having to manually script out and re-create the objects when they are modified on the publisher).
If you replicate the SP/view/function then whenever they're modified on the publisher they'll also be modified on the subscriber. One drawback is that you cannot drop and recreate them on the publisher if they are replicated - you can only alter them on the publisher.
Also - if you are replicating SP/view/functions it is a good idea to place them in a publication of their own different from the publication that has the tables (easier maintenance etc).
c) If you want to replicate SP execution - for e.g. if an SP is executed on the publisher instead of sending across all the data changes resulting from the SP execution you can choose to replicate the execution of the SP only on the subscriber (you'll need to ensure that all objects required for the SP execution are also replicated in this case).
June 16, 2011 at 6:01 am
Thanks Sir for your help,
I have configured Replication pretty well.
But after this my boss ask me to run some schema Changes like altering Columns and removing/addition of some primary keys and indexes as well.
Before i run new Script I disable Schema Changes for publications.
When I run the following script
alter table cr_co_loanee_Schd drop constraint PK__CR_CO_LOANEE_SCH__762C88DA
go
Alter Table cr_co_loanee_Schd alter column Loanee_ID bigint not null
go
alter table cr_co_loanee_Schd add primary key (Loanee_Id,Due_Date)
go
I got the error that is well explained in attached image.
Remaining script is also attached that also need your due attention for further precautions for its successful running.
Thanks
June 16, 2011 at 8:33 am
You cannot drop the PK for a replicated article - you'll have to remove the article from the publication (basically not replicate the article), make the changes and add it back.
Setting the "Replicate Schema Changes" option to false will mean that schema changes to a published article will not flow down to the subscriber (for e.g. adding a column to a published article will not flow down to the subscriber) - regardless of whether this is set or not you'll have to follow the steps above if you want to drop the PK for a replicated article. And since you also want to alter columns keep this property to True.
June 16, 2011 at 1:05 pm
You can still add columns to articles with "replicate ddl" off using sp_repladdcolumn.
If you wish to change the primary key but not reinitialise the article, arrange an downtime slot with no data editing and do this:
1. Stop the log reader agent before making changes to the the publication
2. Drop the subscription to the article (not the publication!)
3. Remove the article from the publication
4. Edit the table at publisher & subscriber ends
5. Re-add the article to the publication
6. Add the subscription as @sync_type = 'none'
This will give you the desired result without the need to resnapshot.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply