| Groups | Blog | Home |
sql server replication : Changing and Deleting Tables once Replication has begun
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
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? -- Regards Glen Pankhurst
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? -- Regards Glen Pankhurst Research & Development Manager [quoted text, click to view] "Paul Ibison" wrote:
> 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 > >
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? -- Regards Glen Pankhurst Research & Development Manager [quoted text, click to view] "Paul Ibison" wrote:
> 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 > > > >
great.
thank you. -- Regards Glen Pankhurst Research & Development Manager [quoted text, click to view] "Paul Ibison" wrote:
> 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 > > > > > >
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
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
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? -- Regards Glen Pankhurst Research & Development Manager [quoted text, click to view] "Paul Ibison" wrote:
> 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 > > > > > >
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 -- Regards Glen Pankhurst Research & Development Manager [quoted text, click to view] "Paul Ibison" wrote:
> 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? -- Regards Glen Pankhurst Research & Development Manager [quoted text, click to view] "Paul Ibison" wrote:
> 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? -- Regards Glen Pankhurst Research & Development Manager [quoted text, click to view] "Paul Ibison" wrote:
> 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 > >
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:
[quoted text, click to view] > 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. -- Regards Glen Pankhurst Research & Development Manager [quoted text, click to view] "Paul Ibison" wrote:
> 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 > > > >
@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? -- Regards Glen Pankhurst Research & Development Manager [quoted text, click to view] "Paul Ibison" wrote:
> @publication = 'Database' is wrong - it should be @publication = > 'Publication Name' > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |