3

We use transaction and push replication. Every night after the data import 2 replication targets are initialized with the following commands.

USE [dbImport]

-- Execute at the Publisher to reinitialize the push subscription.
EXEC sp_reinitsubscription 
@subscriber = N'Target2',
@destination_db = N'dbImport',
@publication = N'dbImportPub';
GO

-- Start the Distribution Agent.
USE msdb
EXEC sp_start_job @job_name = 'Source1-dbImport-dbImport-Pub-Target2-35'
GO

The next step waits for 15 minutes because this reinitialization needs to take normally 10 minutes and we add some more time to be on the safer side. Sometimes the reinitialization needs a little bit longer than 15 minutes, so the indexes which are created in next step on target aren't correct.

I can't check for stopping of the distibution job because in transaction replication it never stops.

How can I wait for the end of reinitialization (initial push of all scripts and data) on target2 (to afterwards add the indexes "on time")?

Stefan

1

The only way I can think of doing this is to slightly hack the process. It would involve the following steps:

  • Copy the Distribution agent job and rename it to indicate it is run manually. You'll also need to change/disable the schedule and remove the "-Continuous" flag from the exec command of this new job.
  • Create a job that does the following:
    • Step 1: Stops the main, continuous Ditribution job
    • Step 2: Executes the sp_reinitsubscription stored proc with @invalidate_snapshot = 1
    • Step 3: Launches the Snapshot agent
    • Step 4: Checks that the Snapshot agent is complete*
    • Step 5: Launches the manual Distribution agent job
  • Create a job that checks if the manual Distribution agent job is finished
    • Step 1: Check if job is running.*
    • Step 2: Restart the main Distribution agent job

*I initially suggested setting the job to fail if the agent job was still running and configuring retry attemps. It might be better to write a WHILE loop with a WAITFOR DELAY instead:

  WHILE EXISTS(
   SELECT
     ja.job_id,
     j.name AS job_name,
     ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id, Js.step_name FROM msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id JOIN msdb.dbo.sysjobsteps js ON ja.job_id = js.job_id AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC) AND j.name = 'Snapshot or distribution agent job name' AND start_execution_date is not null AND stop_execution_date is null) BEGIN --PRINT 'Waiting...' WAITFOR DELAY '00:05:00' END RETURN

| improve this answer | |
  • Hi Tommy, I think there is a misunderstanding. I don't want to check if the snapshot creation completed. This is easy, as you can monitor the appropriate snapshot agent job. I need to see if the reinitialization of the target is completed, so that all tables are transfered completly. This can't be monitored via a SQL agent job. – StefanK Sep 1 '15 at 12:44
  • @StefanK I've edited my answer. I meant Distribution agent, not snapshot agent. As far as I understand, for snapshot replication, two jobs are created. One that runs the Snapshot agent and the other that runs the Distribution agent. When I mocked it up on my server, the jobs created were: [Publisher-ReplTest-ReplTestSnapshot-7] (Snapshot) and [Publisher-ReplTest-ReplTestSnapshot-Publisher-6] (Distribution). In my tests when the 2nd job finished, the snapshot was pushed out successfully. – tommyfly Sep 1 '15 at 14:17
  • I think you use a snapshot replication. We use a transaction replication, so the distribution job is running constantly. Sorry, my initial posting don't stated that! I'll correct it. – StefanK Sep 2 '15 at 6:23
0

Since I don't like to have "unrequired" jobs and want to handle things on a simple way I've created another solution.

The following code is used in a job step after the replication is initialized and started on subscriber with associated job name with postfix "-59".

WHILE 0 = (SELECT COUNT(1) FROM distribution.dbo.MSdistribution_history
-- search for suffix of the SQL-job, here '59'
WHERE (agent_id = 59) 

  -- the following text appears in the table once the snapshot-transfer is complete
  AND (comments LIKE N'%The snapshot in subfolder% was transfered in % milliseconds%')      

  -- here I search for the last start time of our import job
  AND (time > (SELECT ja.start_execution_date      
  FROM msdb.dbo.sysjobactivity ja 
  LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
  JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
  WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
  AND start_execution_date is not null
  AND j.name = 'data import production')))

  BEGIN
    WAITFOR DELAY '00:00:10';
  END

Perhaps you agree that this solution is easier to integrate in many jobs and keep the sql job catalog clear and tidy.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.