| Author |
Topic |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
 Posted - 2008-02-29 : 15:27:55
|
| Actually instead of creating initial snapshot ,I thought of doing Transaction Replication in 2005 with backup/Restore. Everthing worked fine for DML changes which is being replicated to subscriber.But my question is:1)In 2005, DDL changes also replicate by default, Why i am not getting that feature while doing tran rep with backup/Restore?Any replication Experts? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-29 : 16:03:45
|
| All changes get applied when you do backup/restore. You must be doing something wrong if you aren't seeing the DDL changes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-29 : 16:17:04
|
| What my question is: After replication is setup and working: Suppose I created new table in publisher why that doesn't get replicated? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-29 : 20:14:15
|
| You need to add the new table to replication either by adding an article to the publication or by creating a new publication.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-01 : 01:22:36
|
| Adding article didn't solve my problem. Is there anyway i can apply transaction log backup to subscribers to syncronize.? Coz we should be able to replicate DDL changes in 2005? It sounds meaningless to me coz i also choosed the option: replicate DDL changes and its not working at all? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-03-01 : 02:12:00
|
| By DDL Changes, its implied changes to articles already being published. If you create new tables you need to add them to publication so further schema changes to the table can be replicated.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-01 : 11:13:19
|
| Dinakar,I did that but it didn't work. I started by replication by backup. I want to know is there any system proc that will syncronize subscription with log backup. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-03-01 : 13:45:54
|
quote:
Originally posted by sodeep
....I want to know is there any system proc that will syncronize subscription with log backup.
Transactional replication works by applying individual transactions on publisher onto the subscriber. You cant just take a log backup and apply it to the subscriber. As we have all said, use sp_Addarticle to add the new table to publication.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-01 : 14:32:07
|
| I already added articles to my publisher.So do you if i export that table to subscriber will replication take care of that. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-01 : 15:20:55
|
| Did you reinitialize it and have the snapshot run? Is the log reader job running?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-01 : 16:05:32
|
| Tara,When I am doing Tran rep with back/restore ,it has no relation with Snapshot agent.Yes, log reader is working fine. I can see update happening in subscriber with transaction. But DDL changes doesn't happen. what if i export the table to subscriber and add article to publisher? Do you think its gonna work? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-01 : 17:56:12
|
| I have no idea what you mean by transactional replication with backup/restore.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-01 : 18:33:47
|
| Tara, You haven't tried this feature:Check this out:http://msdn2.microsoft.com/en-us/library/ms151705.aspx |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-01 : 20:40:43
|
| Got the answer:you have to manually export table to subscriber and replication will take care of.Thanks Tara and dinashanker |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-01 : 22:13:30
|
| Yes I have heard about that but you didn't explain it well enough for us to know what you were talking about. Perhaps it was a language barrier though.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-01 : 22:25:12
|
| ?? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-01 : 22:42:50
|
| You can enable snapshot with 'sp_changepublication publication_name, immediate_sync, true'. |
|
|
|
|
Backup/Restore in Transaction Rep