January 3, 2016 at 2:28 pm
All,
We are doing transactional replication (from prod to data warehouse)using a backup(not through snapshot) for a database which is very large. Sometimes, due to lot of data insertions, one of the table causes bottleneck. The workaround has been to remove the table from the replication and during off hours I reload the table from production and add the table back into replication(using GUI, by going into the publisher properties and checking the article). Lately, I've been having the issue of adding this table using GUI where the GUI freezes and I've to kill it and restart SSMS. Since this wasn't not working, I used the following script:
exec sp_addarticle
@publication='abcPublisherdatabase',
@article='abcTablename',
@source_object='abcTablename'
The table gets added to replication with the above script but the data isn't replicating.
Can someone please tell me if the above script is correct or not or I'm missing something?
Thanks!
January 3, 2016 at 5:33 pm
You need to include the article in the subscription - the code you include adds the article to the publication but it does not add subscribers.
Depending on what you are doing, you may need to run the snapshot agent as well ... unless the data is already at the subscriber. You can tell replication whether the data is on the subscriber already (check out parameter @sync_type for sp_addsubscription)
January 3, 2016 at 5:46 pm
Replication is set up using backup not snapshot.
I loaded the table from production into data warehouse. I'm having this problem only while adding it back into replication.
January 3, 2016 at 8:21 pm
Have a read of https://msdn.microsoft.com/en-us/library/ms147834(v=sql.100).aspx - it has the info you need for creating publications from database backups
January 7, 2016 at 12:51 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply