 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Replication / August 2006Changing and Deleting Tables once Replication has begun
Hi, i have SQL 2005 SP1 on two Windows 2003 R2 Enterprise Servers. One is the main DB and the other is just being replicated to. I would like to make some changes to existing tables as well as add some tables to a database which has been published and subscribed to by the second server. I notice that making changes are okay - but i dont see them in the replicated DB. Deleting Tables is not allowed and Adding new Tables works but doesnt seem to filter through to the 2nd Database. What should i do to be able to make changes to my tables?  SignatureRegards Glen Pankhurst Research & Development Manager Adding new tables needs to be followed by a snapshot and then the distribution agent should pick them up. Schema changes to existing tables done by Alter Table should be automatically picked up by the distribution agent provided @replicate_ddl is set to true. So, please have a look at sp_helppublication to see what the setting is and check that the distribution agent has synchronized since the schema change. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com I am using transactional replication.... so would i just go to the main DB... make a snap shot and i don't have to do anything else? SignatureRegards Glen Pankhurst Research & Development Manager > Adding new tables needs to be followed by a snapshot and then the > distribution agent should pick them up. [quoted text clipped - 5 lines] > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com After adding a new table to your publication, you should be able to run the snapshot and the next time you run the distribution agent it'll pick up the details of the new ODBCBCP files and apply them to your subscriber. BTW I'm assuming that the @sync_type = automatic here which is the default. If this doesn't work, then please take a look at the history of the agents and post up any errors. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Okay just so i am sure... i don't need to stop the DB? I leave it running.. generate a new snap shot and then the rest is up to sql? SignatureRegards Glen Pankhurst Research & Development Manager > After adding a new table to your publication, you should be able to run the > snapshot and the next time you run the distribution agent it'll pick up the [quoted text clipped - 4 lines] > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com Yes. The snapshot will contain just the new article, assuming you don't have any other subscribers which haven't yet been initialized, or have anonymous subscribers. Everything else should be left running. On SQL Server 2005 the concurrency option for @sync_method is set to true by default so no exclusive lock is required on the publisher's article. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com great. thank you. SignatureRegards Glen Pankhurst Research & Development Manager > Yes. The snapshot will contain just the new article, assuming you don't have > any other subscribers which haven't yet been initialized, or have anonymous [quoted text clipped - 3 lines] > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com Actually after creating the snap shot i looked at the 2nd DB and i cant see the new tables there. Have i done something wrong? Do the tables have to have content first? SignatureRegards Glen Pankhurst Research & Development Manager > Yes. The snapshot will contain just the new article, assuming you don't have > any other subscribers which haven't yet been initialized, or have anonymous [quoted text clipped - 3 lines] > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com Are the new tables in the snapshot (text files)? If so, have you run the distribution agent since creating the snapshot? Also, was the initial initialization a nosync one? Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Where do i go to see those? Distribution agent is always running and yes the initial install was a no sync one - its just push and not push and pull SignatureRegards Glen Pankhurst Research & Development Manager > Are the new tables in the snapshot (text files)? > If so, have you run the distribution agent since creating the snapshot? > Also, was the initial initialization a nosync one? > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com For a nosync initialization, you had @sync_type = none, and when you run sp_addsubscriptin for the new article, you'll need @sync_type = automatic for the snapshot of the new table to get applied. Otherwise you'll need to do nosync again for the separate article. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Okay how do i set @sync_type = automatic? And is that all i need to do? SignatureRegards Glen Pankhurst Research & Development Manager > For a nosync initialization, you had @sync_type = none, and when you run > sp_addsubscriptin for the new article, you'll need @sync_type = automatic > for the snapshot of the new table to get applied. Otherwise you'll need to > do nosync again for the separate article. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com Use scripts for this. exec sp_addarticle @publication = 'tTestFNames' , @article = 'tEmployees' , @source_table = 'tEmployees' exec sp_addsubscription @publication = 'tTestFNames' , @article = 'tEmployees' , @subscriber = 'RSCOMPUTER' , @destination_db = 'testrep' , @sync_type = 'automatic' Run the snapshot agent then synchronize. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com So i type this into the query window: exec sp_addarticle @publication = 'publication_DB', @article = 'article/subscription_DB???', @source_table = 'Table' exec sp_addsubscription @publication = 'publication_DB', @article = 'article/subscription_DB???', @subscriber = 'DB2', @destination_db = 'DB', @sync_type = 'automatic' What is article? SignatureRegards Glen Pankhurst Research & Development Manager > Use scripts for this. > [quoted text clipped - 11 lines] > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com If your table to be replicated was called 'Customer', then the commands would be: exec sp_addarticle @publication = 'publication_DB', @article = 'Customer', @source_table = 'Customer' exec sp_addsubscription @publication = 'publication_DB', @article = 'Customer', @subscriber = 'DB2', @destination_db = 'DB', @sync_type = 'automatic' ie 'article' is the article name, which for a table, might as well be the tablename to avoid confusion. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com When i run this: > exec sp_addarticle @publication = 'Database', @article = 'Table', > @source_table = 'Table' i get this error message: Msg 14013, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 168 This database is not enabled for publication. And the Database is definately published... DB2 receives all new rows except the new tables i generated. SignatureRegards Glen Pankhurst Research & Development Manager > If your table to be replicated was called 'Customer', then the commands > would be: [quoted text clipped - 11 lines] > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com @publication = 'Database' is wrong - it should be @publication = 'Publication Name' Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com I tried using the publication name as it appears under publications on the DB server but i got the same error. Also i tried editing a table that is already being replicated (changing primary key to a clustered key) and it gives me the error that it can't because there is a subscriber or publication. How do i get around this? Also another question... the DB i am replicating too.. Can i cancel the publications and subscriptions... do my changes and start publication and subscription again? Does the DB to be replicated to have to have an exact copy of the main DB or will the main DB just copy whats not there on the replication DB to it? SignatureRegards Glen Pankhurst Research & Development Manager > @publication = 'Database' is wrong - it should be @publication = > 'Publication Name' > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.