October 13, 2008 at 3:57 am
Hi,
I need help with maintenance plan creating. What I have tried to do:
I have defined replication from DB1 to DB2 (on the same server). I have one local publication and one local subscription. I am using snapshot replication without schedule. And I want to do maintenance plan with 3 tasks - first step should generate a snapshot, second step should replicate changes to DB2 and third step is my own T-SQL statement task. Plan is included as attachment of this post.
My problem is, that when I run all three job separately, everything is ok. But when I run the maintenance plan, everything seems to be ok (no error message in logs), but only snapshot is created. What I am doing wrong?
October 14, 2008 at 11:07 am
I assume you mean running the jobs as yourself, manually when you say they work when running seperately.
Is it possible that the job owner doesn't have sufficient privileges to run the other steps?
Chris.
Chris.
October 14, 2008 at 11:59 pm
No, it seems like the jobs doesn't wait for prevoius job completion. So for example snapshot creation is executed and the next step (distribution) is started before the snapshot is created.
I have scheduled jobs separately (with 2 hour interval) and everything is ok. This is not an ideal solution but I am satisfied for now...
October 20, 2008 at 7:43 am
Hi Michal,
the problem is you are "only" starting a job that starts the snapshot creation. This is why the second and third step start too quickly.
When you create transactional replication of objects there are normally 3 jobs created (snapshot creation, tran log reader and tran log writer).
If I understand you correctly, you need to start the snapshot creation and then mark the subscriber for re-initialisation. This will make your subscriber wait for the new snapshot and read that into it's tables and then automatically fill up the remaining changes that happened after snapshot creation.
You could put a "check loop" between step 2 and 3 from your SSIS packet which compares the source with the target and once they are the same it can then pass onto your step 3.
I have attached a diagram to try and explain what I mean. If you can't follow me, let me know and I will try and explain it better.
regards
GermanDBA
Regards,
WilliamD
October 20, 2008 at 8:09 am
Thanks for the reply.
I see what you mean... it is not definitely bad solution. What I use now instead is backup-restore solution. So I am not using replication and this 3-job concept works in this case. It is nice that I can use it in all editions (unlike replication) and it is usefull for me that I can easily DROP tables in source database (they are not bounded with replication). The minor problem of course is that I can not restore the database online but I don't need it (at least not now).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply