locked
Jobs Failing on Secondary Replica (Always On) RRS feed

  • Question

  • Hi Guys,

    I am new to Always on Availability Technology.

    So there are 2 nodes Primary and Secondary server part of Always on Availability Group.

    Below are the settings:

    Ø     Availability Mode:

    Synchronous Commit

    Ø Failover Mode:

    Automatic

    Ø Readable Secondary:

    NO

    Ø Backup Preference :

    Prefer Secondary (Default)



    So the jobs are running fine on Primary node but on Secondary it is failing.

    My question is :

    1. As the readable secondary is set to NO , it will not execute the jobs on secondary.

    2. In error log i see below error on Secondary server  (Replica)

     Error: 976, Severity: 14, State: 1

    The target database, 'test', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

    so is this normal/ expected?



    3. in the job history i see below messgae:

    Message
    Unable to connect to SQL Server '(local)'.  The step failed.

    What does this mean?



    Am i doing something wrong , or should disable the logs on Secondary Server??



    Thank you.


    Saturday, March 11, 2017 5:43 AM

Answers

  • Secondary (Failing)

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

    BACKUP DATABASE [TEST] TO  DISK = N'B:\Backup\TEST' WITH NOFORMAT, INIT,  NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO

    So , is it like that whenever we have to take backups on "Replica" we need to take it with "COPY_ONLY OPTION"?

    Yes you have to add copy_only clause

    BACKUP DATABASE [TEST] TO  DISK = N'B:\Backup\TEST' WITH NOFORMAT, INIT,  NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10,copy_only
    GO


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by NEKUK Sunday, March 12, 2017 7:21 AM
    Sunday, March 12, 2017 6:46 AM

All replies

  • Hi,

    That's normal given that your databases are in no-readable mode.

    You have to add extra logic to your jobs so they can detect if they're running on the primary or the secondary, if the database is readable or not, etc.

    Saturday, March 11, 2017 6:09 AM
  • Hi Sebastian,

    Thanks for the response.

    1. So the jobs failing on Replica server is normal ?

    2.If i set the Readable Secondary to YES , will these jobs will run on secondary as well? (just for load balancing purpose) ?

    3. And do i need to add the logic to all the jobs to detect if the server they are running on is primary or secondary?

    4.  Any article for the same if you can help with?

    Thank you

    Saturday, March 11, 2017 6:24 AM
  • Neha,

    Please first let is know what you job is doing or please post the code, your question is imcomplete without code

    1. The databases on secondary replica are only for read only purpose if you are trying to do something more than read only then it will fail. Like if you are trying to run index rebuild on secondary replica it will fail. If you are trying to drop a table or constraint or trying to do DML on table it will nto work.

    2. Not likely it is ONLY for read purpose, depending on what job is doing it may or may not succeed

    3. For extra logic if you want query to always run on primary replica you can add below login and no matter on which replica runs it will always connect to primary and run the job. See this thread

    if (select
            ars.role_desc
        from sys.dm_hadr_availability_replica_states ars
        inner join sys.availability_groups ag
        on ars.group_id = ag.group_id
        where ag.name = 'YourAvailabilityGroupName'
        and ars.is_local = 1) = 'PRIMARY'
    begin
        -- this server is the primary replica, do something here
    end
    else
    begin
        -- this server is not the primary replica, (optional) do something here
    end


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, March 11, 2017 7:57 AM
  • Thanks Shashank,

    1. The databases on secondary replica are only for read only purpose if you are trying to do something more than read only then it will fail. Like if you are trying to run index rebuild on secondary replica it will fail. If you are trying to drop a table or constraint or trying to do DML on table it will not work.

    ---> MY Readable Secondary Setting is set to NO. 

    Still i can run the jobs ??

    The Jobs are as below:

    1. Full Backup.

    2. Transaction Log backups and Shrink Task.

    3. Event Task Cleanup Test

    ( It’s a vCentre Stored procedure

    EXECUTE cleanup_events_tasks_proc)
    Saturday, March 11, 2017 8:51 AM
  • You can only run read only tasks that read the database - no writing allowed.

    1) you may be able to do full backups - depending on your setting on your AG properties. Right click on your AG, go to properties and look at the backup preferences tab. You can control where the backups can occur here. Note that if you are using backup compression there is a cpu hit associate with this and you may want to run them on the secondary. You may also, depending on your preferences need to do a backup with copy only.

    2) You may find that if you do backups on your secondary they will occasionally fail as the lsn has not been hardened on the primary yet. This may necessitate running them on the primary. Now you should not do shrinks at all, but you may find out of control log growth on your primaries which will require you to do out of band log backups and an occasional shrink. I use alerting to let me know when the logs get too large.

    3) if it does any writes it must be run on the primary.

    Saturday, March 11, 2017 12:16 PM
  • Yes you can run both full and transaction log backups on Secondary replica subject to condition you have chooses appropriate option. I would suggest dont perform shrink task at all no matter what replica it is.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Saturday, March 11, 2017 2:20 PM
  • Hi Below are the settings i have selected:

    below are the settings:

    Ø     Availability Mode:

    Synchronous Commit

    Ø Failover Mode:

    Automatic

    Ø Readable Secondary:

    NO

    Ø Backup Preference :

    Prefer Secondary (Default)

    The same Backup Job runs just fine on primary but fails in secondary with below message:

    Message
    Unable to connect to SQL Server '(local)'.  The step failed.

    Also in error log i see below message:

    Error: 976, Severity: 14, State: 1.
     The target database, 'Test', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

    Sunday, March 12, 2017 5:01 AM
  • Thanks Shahshank,

    So if i am getting it right , even if Readable Secondary is set to NO , i can still run backup jobs on Replica server and they will run fine?

    I will remove the shrink jobs as suggested.

    Thank you.

    Sunday, March 12, 2017 5:03 AM
  • Thanks Shahshank,

    So if i am getting it right , even if Readable Secondary is set to NO , i can still run backup jobs on Replica server and they will run fine?

    I will remove the shrink jobs as suggested.

    Thank you.


    Yes you can , but the important point here is when you perform full backup on secondary replica you have to use COPY_ONLY clause otherwise it is not possible and may fail. This holds true for both full and transaction log backup. So are you taking full backup with copy_only on sec replica , I guess not. Please try with copy_only option

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Sunday, March 12, 2017 5:40 AM
  • Hi Shashank,

    I guess this is the Catch.

    See below command from Both Primary & Replica , its same.

    Primary (Running Successfully)

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

    BACKUP DATABASE [TEST] TO  DISK = N'B:\Backup\TEST .bak' WITH NOFORMAT, INIT,  NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO


    Secondary (Failing)

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

    BACKUP DATABASE [TEST] TO  DISK = N'B:\Backup\TEST' WITH NOFORMAT, INIT,  NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO

    So , is it like that whenever we have to take backups on "Replica" we need to take it with "COPY_ONLY OPTION"?

    Sunday, March 12, 2017 6:36 AM
  • Secondary (Failing)

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

    BACKUP DATABASE [TEST] TO  DISK = N'B:\Backup\TEST' WITH NOFORMAT, INIT,  NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO

    So , is it like that whenever we have to take backups on "Replica" we need to take it with "COPY_ONLY OPTION"?

    Yes you have to add copy_only clause

    BACKUP DATABASE [TEST] TO  DISK = N'B:\Backup\TEST' WITH NOFORMAT, INIT,  NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10,copy_only
    GO


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by NEKUK Sunday, March 12, 2017 7:21 AM
    Sunday, March 12, 2017 6:46 AM
  • One more question Shashank,

    After failover this Replica will act as Primary , then this Job will run successfully with copy_only option?

    or do i have to change anything?

    As of now Job on primary  is working fine without copy_online option.

    Sunday, March 12, 2017 9:06 AM
  • Hi NEKUK,

    can you post error message. with job details.

    below one is correct query. use this

    Yes you have to add copy_only clause

    BACKUP DATABASE [TEST] TO  DISK = N'B:\Backup\TEST' WITH NOFORMAT, INIT,  NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10,copy_only
    GO

    Thanks

    Ramu


    A.Ramu

    Monday, March 13, 2017 7:20 AM
  • If you have the readable secondaries set up for NO (no connections) you will not be able to do a backup (database or log on them).

    If you choose to do your log backups on your secondary you may run into occasional failures with the following message:

    Log backup for database on a secondary replica failed because the last backup LSN from the primary database is greater than the current local redo LSN

    This will likely be a problem for point in time recovery which is why I recommend doing your log backups on your primary.

    You will run into problems with your transaction log sizes on your primaries. you will need to shrink them. A shrink job scheduled is not a good idea, but you may want to keep it around disabled to shrink the logs on your replicated databases.

    You will need to do copy_only backups on your secondaries as backup databases are not suppored there - you will (not may) get a message stating:

    Msg 3059, Level 16, State 1, Line 1
    This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    But again, if your Readable Secondaries are setup as NO (no connections, not yes, or read-intent only), you will not be able to do any backup on them.

    Monday, March 13, 2017 7:32 PM