MIM installation created jobs in SQL. How to configure these jobs to run on secondary for always on high availability setup

Question

Hi We have a question from the MIM point of view in a high availability always on setup.We have upgraded MIM to Sp2 2016 for our customer and during MIM installation, there are a few jobs created by MIM in SQL server. Now in the high availability approach, there are two nodes - primary node and secondary node.

In case of failover, we understand that the secondary node is made primary automatically and is up and running and databases are synchronised. But the jobs which were running on primary, are not created and will not run on secondary automatically.

So Question 1: from MIM perspective on MIM components, Is there any action pending to be taken in this scenario.

Question 2: From SQL perspective, from our analysis, we understand jobs need to be manually created on secondary. Thus we will have jobs on both primary and secondary servers. And in normal scenario with no failover, the jobs will run successfully on primary server and fail on secondary server and give alerts. How to ensure the jobs dont fail on secondary server, just not run the jobs.

And during failover, when secondary is switched automatically to be primary, will the jobs manually created on secondary run automatically. Has anyone faced this situation and what is the right way to approach it.

Kindly help us.

Answer 1

Hi @Sravya Pullagura ，

DB on secondary node is in read only mode, so you will fail,
Try to use the sys.fn_hadr_is_primary_replica, it can be used to determine if the current replica is the primary replica.
You could have the jobs on both instances, primary and secondary replica and then use sys.fn_hadr_is_primary_replica in the first step of your job.

IF sys.fn_hadr_is_primary_replica ( 'yourDBname' ) <> 1     
   BEGIN    
      -- raiserror, so the job step fails and the entire job fails  
      -- sometimes you may want to set this job to "finish with success" when this step fails  
      -- so that you don't get alerts  
      declare @errMsg varchar(600) = 'This is meant to run on the primary replica'  
      raiserror(@errMsg,16,1)  
   END  
ELSE  
   BEGIN    
      print 'This is the primary replica, continue with the job'  
   END  

See this thread and this for detail

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Question 2: From SQL perspective, from our analysis, we understand jobs need to be manually created on secondary. Thus we will have jobs on both primary and secondary servers. And in normal scenario with no failover, the jobs will run successfully on primary server and fail on secondary server and give alerts. How to ensure the jobs dont fail on secondary server, just not run the jobs.

I don't know what people do in general, but one idea is that you check the colunm sys.databases.state_desc. If it is ONLINE and perform actual action. If it something else, just exit silently. Note that you cannot set the database for the job to be the AG database, you need to select, for instance, tempdb and them move to the database, it is active. This is a lot simpler if the actions of the job are enclosed in a stored procedure. Then the job can read:

IF (SELECT state_desc FROM sys.databases WHERE name = 'MIM') = 'ONLINE'
   EXEC MIM.dbo.some_sp

I'm an SQL Server guy and don't know anything about MIM, so I cannot answer the first question