March 20, 2006 at 7:38 am
After searching and searching, I can't seem to find an answer for this...
We have a new software at work that uses SQLServer 2000 that we want to replicate for our clients to have fast access to their data.
The problem is, every new project we have creates a new set of tables. So if we create project # 3000, it would create tables something like this
Revenue_3000
Orders_3000
Clients_3000
Customers_3000
Now from what I can see in the replication wizard, there's no way to tell it to automatically add new tables/articles to a Publication. And it seems you need to remove all Subscriptions before you can even manually add a table/article to a publication. And after that, you need to recreate the subscriptions, and then re-snapshot....
After some digging, I've found a mass load of replication related stored procs in the master db. Now I *assume* that if I knew enough, I could probably use the stored procs to automate the changes, but I have a feeling this is no small feat...
If it is possible to achieve that with stored procs, what is the rough timeline needed to create this? A week? A few weeks? Months?
If there's another option for me to do this besides replication, please feel free to mention them too...
Thanks...
March 20, 2006 at 8:22 am
Dan
sp_addarticle should be what you are looking for. The syntax looks daunting, but once you've figured it out, it should be fairly easy to generate a script to add an article given a new table name. You will also have to create the new table at the subscriber. I've never tried adding a table article to an existing publication, so test it to make sure that data is replicated without your having to drop and recreate the subscription and/or re-run the snapshot agent.
I believe that log shipping is more friendly for databases that undergo frequent schema changes, so you may wish to investigate that as an alternative to replication.
John
March 20, 2006 at 8:38 am
I was able to figure out sp_addarticle, and it did modify the publication correctly, but never updated the subscriber.
So if the table on the subscriber exists, it will automatically start replicating?
March 20, 2006 at 9:06 am
Dan
That's the bit I'm not sure of. I'd say as a minimum you'd have to restart the LogReader Agent after adding the article. Please try it out - I'd be interested to hear how you get on!
John
March 20, 2006 at 9:23 am
It's odd, my log reader is giving me errors now so even a straightforward replication is not working...
Now I'm getting...
"The process could not execute 'sp_repldone/sp_replcounters' on 'MYSERVERNAME'"
March 20, 2006 at 10:02 am
Never mind that last post.. I've fixed that portion of it....
Even after adding the article and having the new table exist on the subscriber, the new article does not work. Even after reinitiallizing, and taking another snapshop, it does not work.
Only after I remove the subscriber, and re-adding him does it seem to work.
Is there stored procs that would allow me to
a) add an article to a publication (sp_addarticle)
b) remove a subscription to the pub (?)
c) add a subscription to the pub (?)
d) take a new snapshot (?)
March 21, 2006 at 1:31 am
a) sp_addarticle, as you say.
b) sp_droppublication (although I've a feeling you have to use sp_dropsubscription first)
c) sp_addpublication (and then sp_addsubscription)
d) if you used @sync_type='none' in sp_addsubscription then you don't need to take another snapshot. However, if you prefer to take a snapshot anyway, I suppose you can use xp_cmdshell to run snapshot.exe. Or, if you run it as part of a job you can copy the job step from the Snapshot job that is created automatically when you set up replication.
March 21, 2006 at 5:20 am
Adding new tables part of existing publication is pretty simple . Unfortunately there is no system sp which one could rely on to do this. You can use the EM - GUI itself to do this process.
a. Create the new tables at both ends- subscriber and publisher.
b. Create the rowguid column ,along with a non-clustered index for the rowguid column.
The above steps can be easily done using simple SQL scripts
c. Additionally publish these articles part of the existing subscription. (Need to use the EM- GUI )
d. Snapshot will regenerated automatically and you will able to complete the process sucessfully.
We have done this process couple of occasions, and we did not face any issues during these steps
Hope this helps
rangark
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply