November 9, 2012 at 2:05 pm
We have transactional replication setup some time ago. Recently we created a new table, it has a PK, and I added it to articles.
However, it did not show up at the subscribing database. So I created it with the script taken from the publisher. But it's not getting populated with data.
What other steps should be taken to insure that a new table is really replicated ?
Thanks
November 9, 2012 at 6:54 pm
Just out of curiosity, how much data is being replicated? Please describe your architecture...for instance, how many articles are in the publication? When you created your publication, what options did you choose?
If you simply added articles and pretty much accepted the "defaults" then certain things happen. The main thing is after adding the "new" article you should have received a message stating that you were invalidating the existing snapshot (or similar wording) and would you like to generate a new snapshot. Again, depending on your set up this may have been a quick thing...or in a rather large environment, you probably would choose "NO" to this. The reason for this is because, by default, a new snapshot will be generated for EVERY article in the publication, and not just the new article you added. This occurs because by default immediate_sync is set to "true". In a large enterprise, or in a heavy OLTP env. this could have potentially disastrous consequences.
To check how it's setup, run this TSQL against the distribution databaseSELECT publisher_id, publication_id,
publisher_db,
publication, allow_anonymous, immediate_sync
FROM distribution.dbo.MSpublications WHERE publication_type = 0This ought to show you a listing of all your publications.
While IMHO, a rather poorly documented process in BOL, all you need to do to allow the adding/dropping of individual articles in a publication is to set the immediate_sync option and allow_anonymous to zero. --On the PUBLISHER, using the PROPER database
EXEC sp_changepublication @publication = 'YOUR_PUBLICATION', @property = 'allow_anonymous', @value = 'false'
GO
EXEC sp_changepublication @publication = 'YOUR_PUBLICATION', @property = 'immediate_sync', @value = 'false'
GO
Now to get the data back over to your subscriber and create the link needed for replicated to occur, you need to start the Snapshot Agent. You can do this either in the SQL Agent or by opening up the replication monitor in SSMS, finding the Agent Tab, and right-click the Agent choosing "start agent"
That should be all you really need to do.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 10, 2012 at 4:43 am
SQL Guy 1 (11/9/2012)
Recently we created a new table, it has a PK, and I added it to articles.
How?
If you did this by TSQL using sp_addarticle then you still need to rerun sp_addsubscription to add the additional article to the subscriptions and rerun the snapshot agent to generate a partial snapshot (new table only)
November 12, 2012 at 6:25 pm
Thank all for replies. Sorry for delay, I was busy all day today.
MyDoggieJessie:
The database size is 36 GB including log.
Totally 16 tables, only 10 of them have PKs, and all 10 are replicated.
I created replication in GUI several months ago, so I don't remember all the details. But probably I chose all suggested defaults.
I did not receive an error message when adding new article.
As a result of the query that you posted, both values were 1 and 1.
After updating with two S.P.s, they became 0 and 0.
About Replication monitor:
Sometimes it shows red circle with X inside with status "Error", sometimes it is blue "OK", while subscriber is green "Running".
Snapshot agent is Completed.
Log Reader agent is Running.
Queue reading agent is grayed-out, and I can't start it.
All 6 maintenence jobs are grayed-out as well.
I believe that my replication is seriously ill.
MysteryJimbo:
I created replication only in GUI, I did not run any scripts.
You advice me to "generate a partial snapshot". How ? The problem is that I created snapshot, but it took hours to completely synchronize. Partial snapshould would be definitely much faster.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply