October 29, 2004 at 12:41 pm
I am setting up replication from a Production db to a Reporting db. I intend to use Transactional Replication for all of the data tables. I would like to use Snapshot Replication for SPs, Views, and UDFs. I'd like to automatically have a full, reinitalized, Snapshot run at 3:00am every night. Is there someway to force a full reinitialization of the Snapshots every night? In other words, if an SP, View, or UDF is added or modified, I want to propogate that change to the Reporting db at 3:00am.
I've just been using the Wizard and I haven't had any luck getting this to work.
Thanks
RML51
October 31, 2004 at 4:50 pm
When you configure Transactional Replication, 2 jobs are setup in the process. 1 is the Snapshot and the other is the job that perfroms the Replication.
All you have to do is schedule the Snapshot job to run every night. Keep in mind that when you do this to maintain permissions as the snapshot by default drops the object.
November 1, 2004 at 6:47 am
Your answer hasn't worked for me. A couple of points...
1) I am not going to use Transactional Replication for the SPs, Views, and UDFs, only Snapshot Replication. (The data tables will use Transactional replication)
2) I've tested what you've suggested and this is what I've found. Please let me know if I'm doing something wrong.
a. If I change an SP on the Publisher, the Snapshot doesn't replicate the change when it runs at 3:00am. The Snapshot agent basically says that it doesn't need to run because it thinks it already has run. This is why I want to force a reinitialization.
b. Also, because you have to select all the individual articles (SPs, Views, and UDFs), when you build the Snapshot, you can't add a new SP, View, or UDF, on the Publisher and have that automatically replicate to the subscriber. I suppose you have to use the sp_addarticle, but then this becomes a manual process. I'd prefer to automatically move additions from the Publisher to the Subscriber.
Thanks
November 1, 2004 at 4:57 pm
Ok. I currently have a Snapshot Publication of Stored procedures and Views to another Database and it works fine here.
As long as the Publication has the Stored Procedures and Views that you wish to replicate and you make changes to those objects, it will replicate at the specified time you have specified. Trust me, I have that going right here wher I am!
If you develop new Stored procedures and Views, they will not replicate because they are not part of the Publication. Therfore you will have to include them as part of the publication.
Hope this clears things up! Respond if its still not clear!
November 3, 2004 at 1:22 pm
Thanks Trigger. I'll keep trying. I think it might be easier, in the long haul, for me to use a DTS Export. I've been playing with this and it seems to be more managable for what I'm trying to accomplish.
November 8, 2004 at 6:02 am
just before running the snapshot agent (job) of your snapshot publication, run the sp sp_reinitsubscription. Check in BOL for help, but you should use 'All' in the @Article parameter.
None of the replication options, detects changes to stored procedures of any other object. Not even transactional replication.
If you don't reinitialize the publication, then SQL asumes that there is no need to create a new snapshot, that's why you have to reinitialize it first.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply