none
Maintenance Plans for Always On cluster RRS feed

  • Question

  • I have a couple of servers in always on high availability group with a disk quorum. The secondary is in non-readable mode. Searching for best practices or maintenance plans for this scenario didn't yield much through google.

    Would be great to know how people are doing this? I can set up the maintenance on primary replica but was wondering how that would translate after a failover. Or, if we are supposed to create the same plan on the two servers (how would the maintenance job know which is primary server to perform the job on since the secondary would be non-readable?). 

    I do see people talking about creating backups through maintenance plans in the forum for situation like mine. However, I do not need that. For me it would be more about reorganizing index, update statistics, clean up history and check database integrity.

    Friday, September 20, 2019 6:48 PM

All replies

  • Hi Sir,

    I have  two alwayson nodes here (rw/r). What i've done is creating the jobs in both servers and using sql scripts to checks if the current AG is the primary. Both Jobs are schedule and runing. I've read that there is not way to implement a "failover" in the jobs. If you using sql server from 2014 award we can use the following funtcion to check if the DB is in the Active AG:

    sys.fn_hadr_is_primary_replica ( 'dbname' )  

    For collecting statiscts, for eg., i've used a job with a t-sql code using a cursor where i list just the databases that exists in that active AG.

    SELECT
    	AGDatabases.database_name AS Databasename,
    	Groups.[Name] AS AGname
    FROM sys.dm_hadr_availability_group_states States
    	INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
    	INNER JOIN sys.availability_databases_cluster AGDatabases ON Groups.group_id = AGDatabases.group_id
    WHERE primary_replica = @@Servername

    Regards,
    Rafael

    Friday, September 20, 2019 8:57 PM
  • http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-28-additional-options-tackling-jobs-failo


    Create job category/categories for all jobs you want to sync
    Put all the jobs in them. You may want to note that if you manually update the category on a bunch of jobs by manually updating the sysjobs table that you have to restart the agent otherwise it will throw an error if you try and reopen the job.
    The stored proc loops through all of these jobs using the following logic:
    If primary replica and disabled, enable it
    If primary replica and enabled, do nothing
    If secondary replica and enabled, disable it
    If secondary replica and disabled, do nothing
    Create agent job that runs the stored proc for specified categories      
    Create the alert for message 1480 (post #5 – Setting up Failover Alerts)
    Set the response for this alert to execute the job.
     

    With this approach, Tom doesn’t need a ‘sync check’ job running every N minutes, and he doesn’t need a state table. He also doesn’t need Linked Servers (in order to run synchronization checks) which further decreases complexity. In fact, here’s a sample of the code Tom sent me a few months ago that he’s using:

    ?
    CREATE procedure [dbo].[uspDBAHADRAgentJobFailover] (@agname varchar(200))
    as
    begin 
           declare @is_primary_replicate bit
           declare @job_name sysname
           declare @job_enabled bit
     
           select @is_primary_replicate=DBAWork.dbo.fn_hadr_group_is_primary(@agname)
     
           declare job_cursor cursor for 
           select s.name from msdb.dbo.sysjobs s
           inner join msdb.dbo.syscategories c on s.category_id = c.category_id
           where c.name = @agname
           order by name
     
           open job_cursor
           fetch next from job_cursor into @job_name
           while @@fetch_status = 0
           begin
                  select @job_enabled=enabled from msdb.dbo.sysjobs where name = @job_name
                  if @is_primary_replicate = 1
                  begin
                         if @job_enabled = 1
                               print @job_name+' enabled on primary. do nothing'
                         else 
                         begin
                               print @job_name+' disabled on primary. enable it !'
                               exec msdb.dbo.sp_update_job @job_name = @job_name,@enabled = 1
                         end
                  end 
                  else if (@is_primary_replicate = 0)
                  begin
                         if @job_enabled = 1
                         begin
                               print @job_name+' enabled on secondary. disable it !'
                               exec msdb.dbo.sp_update_job @job_name = @job_name,@enabled = 0
                         end
                         else 
                               print @job_name+' disabled on secondary. do nothing'
                  end
                  fetch next from job_cursor into @job_name
           end
           close job_cursor
           deallocate job_cursor
    end
     
    GO

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, September 22, 2019 4:50 AM
  • Thank you.

    The link you provided seems to be broken.

    So, we won't be able to get this done via maintenance plan from the ssms?

    Monday, September 23, 2019 4:10 PM
  • Sorry, please try this one

    https://www.itprotoday.com/sql-server/alwayson-availability-groups-and-sql-server-jobs-part-28-additional-options-tackling-jobs

    >>>>So, we won't be able to get this done via maintenance plan from the ssms?

    MP creates the same jobs, so why not using jobs directly?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, September 24, 2019 4:21 AM
  • So, when MP creates jobs on one server, does it carry over to the secondary server? I thought it had to be done on both and since the secondary is non readable it would fail on secondary?
    Tuesday, September 24, 2019 3:13 PM
  • Hey,

    "So, when MP creates jobs on one server, does it carry over to the secondary server?"
    No, i think that Uri wanna mean that when you create a Maintenance plan it will create a job as anyway.

    "I thought it had to be done on both and since the secondary is non readable it would fail on secondary?"
    If you create a Maintenance plan for eg. in a sencondary read-only replica to rebuild the index from the secondary database it will fail. Because of this i suggest you creating job's in both servers using that function that you are able to identify your read/write active databases.

    Regards,
    Rafael

    Tuesday, September 24, 2019 8:16 PM
  • >>>So, when MP creates jobs on one server, does it carry over to the secondary server?

    No, on the secondary server  you need to create MP  as well

    >>I thought it had to be done on both and since the secondary is non readable it >>would fail on secondary?

    No, the logic   you put as I described above you need to check if the replica is primary right now and then enable the job

    https://dba.stackexchange.com/questions/45137/sql-server-agent-jobs-and-availability-groups


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 25, 2019 4:28 AM