March 1, 2002 at 8:39 am
I was able to synchronize servers using publication and subscription procedure through enterprise manager. Now, Iam in
the process of automating this procedure. I could create/delete a publication and subscription using SQL store procedures.
But I was unable to find any store procedure or command to run snapshot agent and start synchronizing with out using Enterprise
Manager GUI. I looked in SQL books on-line and also searched in forums but not successful so far.
And also is there a store procedure/coomand that could add all articles in a database to a publication. The add article store
procedure takes only one article at a time. However, the drop article store procedure can drop either a single or all published
articles.
Thanks for any help.
Raju.
March 1, 2002 at 9:01 am
You have a few choices. You can schedule the replication agents to run automatically. You can obtain the job name or job id of the replication job(s) and use msdb..sp_start_job to run them. Or you can use the command line replication utilities - see "Replication Agent Utilities" in BOL.
As far as automating the creation of the pulibcations or articles, there's not a sp I know of that will do what you want. But you could automate it yourself by building dynamic SQL strings to invoke the "add article stored procedure" for each table in your database (or from a list of tables you create). I use a method similar to this to build subscription scripts based on the list of authorized subscribers (MSsubscriber_info).
Jay
Jay Madren
March 1, 2002 at 1:24 pm
I use the method of finding the job and then running it. Works pretty good. Drawback to using the exe's is that the logreader one requires a control-c to exit it (working on an article for the work around though!). Since EM creates the job I just leverage that. Normally it's scheduled and forgotten.
Andy
March 1, 2002 at 2:21 pm
Thank you gentlemen for your tips.
Lets consider this scenario:
- I create publication using EM and then generate SQL scripts and store it in a SP.
- I create a SP with SQL commands to create a subscription.
- Run snapshot agent manually using EM.
- Start synchronizing using EM so that distribution agent copies tables.
- I record the job names of both snapshot and synchronization.
- Now, I delete subscription and publication using scripts so that I can update the tables/articles.
- After tables updated, create publication/subscription using SPs.
- Now, can I able to run snapshot and distribution agents using previously recorded job names that belong to the first version of publication/subscription which do not exist any more. If not, is there a way to use same job names for any version of publication/subscription.
Is msdb..sysjobs the place to find the job names or any other source?
I need to do this because many databases in our applications require frequent changes. And I dont want agents to run at scheduled intervals.
Thanks again for the help,
Raju.
March 1, 2002 at 4:17 pm
Actually I tried what I described earlier. I was able to run both snapshot and distribution agents after I found their names.
However, after I delete and recreate publication/subscription, I could still use the same snapshot job name but not the distribution job name - because it is not any more in the list of jobs. A new distribution agent job seems to be created when I ran the snapshot agent and this new job was started automatically. If this snapshot job name can stay always, I can use this and somehow find the new distribution job name and run this one if it is not already started.
Though I tried this procedure, not quite sure on how things actually supposed to work. Hope someone can clear it 🙂
thanks, Raju.
March 2, 2002 at 12:06 pm
I'm doing what you're describing, but with merge replication. But I think it would work the same. I use sp_addpublication_snapshot to create the snapshot agent for my merge publication. It has a parameter called @snapshot_job_name, which BOL says is "For internal use only". But if you script out an existing publication from EM, you'll see they are assigning the job name with this parameter. So I assign my own job name, and it's been working perfectly. I can drop and recreate the publication and always have the same job name.
I think the same would apply for all the other replication sp's, since most of them have some sort of "job_name" parameter, and they all say "For internal use only". Wouldn't hurt to try.
Another option, which is going to be my next project, is to use Microsoft's replication ActiveX controls in a vb app. You're supposed to be able to control every aspect of replication with them. I'll let you know how it goes, if you interested (it'll probably be a few weeks).
Jay
Jay Madren
March 4, 2002 at 2:02 pm
Thanks Jay for the tips. You are right that the generated sql (for creating publication) has the snapshot agent name. I used this name to start the job. This also starts distribution agent job, but UNFORTUNATELY tables are not updated consistently - only some times tables are updated.
Earlier I thought the snapshot agent is creating a distribution agent, but it was created when I added a new subscription. And SQL is not using the distribution agent name that I passed to its sp_addsubscription procedure. It uses system generated name.
If snapshot agent does not start distribution agent, I could try getting the distribution job name from sysjobs and start it myself. I guess I have to wait until this distribution agent job was completed smoothly and then start it myself to ensure tables updated always.
Now Iam putting all these steps into store procedures and then executing them one at a time.
Any one, any help on distribution agent...
thank you, raju.
March 4, 2002 at 4:43 pm
Not sure what you're asking. If you run the distribution agent before the snapshot is complete nothing will happen. After that you can run it as little or as often as you like, transactions just sit in the distribution db. If you have a large number of transactions..or lots of large transactions, you may have to run it more than once or run it longer to get it to complete.
Andy
March 5, 2002 at 9:42 am
Sorry for not making my question clear. My question is that when I start snapshot agent, it also seems to run the distribution agent. But somehow the distribution agent is not updating the tables onto target server some times. So, how can I ensure tables are updated consistently? Do I have to wait until this job is finished and then start distribution agent myself?
If I understood correctly, are you (warren) saying that I may have to run the distribution agent more than once.
Another question is why add subscription procedure does not take the name I provide for distribution agent?
thanks, raju.
March 5, 2002 at 9:51 am
I think it defaults to starting the distribution agent. If it's got the -continuous flag set it will continue to run, if not will run again later based on whatever schedule you've set. It will NOT hurt anything or do anything until the snapshot is complete. In what way are you seeing tables not updated correctly?
Not sure about the job name. I've always just let it use the default job name and then searched sysjobs for it when I needed it.
Andy
March 5, 2002 at 10:13 am
Thank you Warren for a quick reply. I have updated a table on source server prior to creating publication/subscription and running a snapshot agent. However, after everything has run, the table on the target server has not shown the updates. It does work sometimes though.
And btw, could you also tell me where to find and set this continuous flag. With this, I would like to start distribution agent - some how Iam confident that this would work. Because, prior to automating these steps, I used to run snapshot agent in EM, and then synchronize server (which starts distribution).
Thanks again, Raju.
March 5, 2002 at 12:31 pm
Just look at step 2 of the distribution agent and add -continuous. Run the job and it will continue running until shutdown.
If you do a standard snapshot it is at that point in time. If you do a concurrent snapshot to reduce blocking the logreader has to run after the snapshot to pick up changes applied to the db after the snapshot began. Either way you should end up with a consistent db.
Re-reading this, I think Im still not clear on what you're trying to do. I find that using EM is the easiest way to set up replication. After doing that you really have two options, run distribution continuously or not, run the logreader continuously or not. EM takes care of building the articles, the snapshot, the jobs, everything. If you elect to put either the logreader or distribution agent into non continuous mode then you have to manage it, either by a schedule or by running the job yourself manually or via some other process. I use that technique myself and I just find the job associated with the publication and run it.
Once you're all set up then you can script it out and save it, comes in handy if you have to remove replication and then reapply it - or in my case when you'll be applying the same settings to quite a few other db's with same schema.
Andy
March 5, 2002 at 12:53 pm
When ever we get new data we update dev server and then synch the target server. I first used publication / subscription steps using EM and it worked fine except that I had to delete subscription and un-publish articles each time database needed an update. Thus, I generated SP for each of the following (took advantage of Generate SQL for add/delete publication):
1- add publication (snapshot agent job name is used here)
2- add subscription (it also creates a distribution agent)
3- run snapshot agent using sp_start_job and snapshot agent name.
4- delete subscription
5- delete publication
I want to delete both subscription and publication right after step 3 so that the tables would be ready for an update.
To verify update, prior to executing SPs I update time field in a temp table on source server, and then execute these SPs and check the time on target server. And here it fails updating sometimes.
So I tried running distribution agent after step3, but I get an error indicating that the distribution agent is already running.
Now I hope I made it clear 🙂 sorry Andy, for confusion.
thanks, raju.
March 5, 2002 at 2:34 pm
I guess the problem is because the steps are being executed with no synchronization. Subscription and Publications are being deleted prior to agents finish their jobs.
Is waiting in a loop till the agent task is finished the best way to ensure synchronization?
thanks, raju.
April 3, 2002 at 4:02 am
SQL Server 2000 allows changing articles without dropping replication..
I don't know if that will apply in your situation but it's just a suggestion.
Good Luck
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply