locked
Suppressing message 978 in an Availability Group RRS feed

  • Question

  • We are running with Availability Groups in SQL2012 SP1 with a primary and a secondary replica that will allow access if you use application intent read only which we are not doing. We probably have some automatic polling by SCOM 2012 that throws the 978 message all the time causing a large SQL errorlog full of these messages and not much else.

    is there a way to suppress these 978 messages?

    Thanks

    Chris 

    • Moved by Sofiya Li Wednesday, September 4, 2013 9:42 AM the right forum
    Thursday, August 29, 2013 9:14 PM

All replies

  • Hi ChrisAVWood,

    I move your thread into a very appropriate forum so that a dedicated support professional can assist you in a more efficient manner.

    In addition, I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Wednesday, September 4, 2013 9:49 AM
  • ...We probably have some automatic polling by SCOM 2012 that throws the 978 message all the time causing a large SQL errorlog full of these messages and not much else.

    is there a way to suppress these 978 messages?

    ...

    Error 978 is: "The target database ('DatabaseName') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online."

    The error itself cannot be suppressed. But there are action you can take to prevent this error from occurring.

    The reason behind this error is that the “Readable Secondaries” property of the Availability Group has been set to “Read-intent only. This means that Clients have to set “ApplicationIntent=ReadOnly” within their respectice Connection String. If they don’t, the connection will be refused with that error.

    You therefore have 2 options:

    1. Adjust the connection strings of the respective clients to include that setting
    2. Change the Readable Secondaries" Property to “Yes”, which will allow also “old” clients without the new connection string to connect to the secondary. Only if they actually try to do a write-operation they will get a (different) error.

    http://technet.microsoft.com/en-us/library/hh213002.aspx

    http://technet.microsoft.com/en-us/library/hh710054.aspx


    Andreas Wolter (Blog | Twitter)

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Wednesday, September 4, 2013 12:14 PM
  • Hi,

    You can avoid these errors by one of these actions: 
    1. You adjust the connection properties to include "ApplicationIntent=ReadOnly" in the connection string. 
    2. You change the readability setting of your secondary replicas. 
    3. You disable the auditing for failed login attempts on all your secondary replicas.


    Regards,
    Christian HL
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, September 4, 2013 12:59 PM
  • I know this is probably the wrong group for this but if it is SCOM 2012 then can the ApplicationIntent be used? I will possibly ask this in an SCOM forum.

    Thanks

    Chris

    Thursday, September 5, 2013 6:52 PM
  • Hi,

    This is an old thread but your answer is still very helpful.

    Thanks very much!



    • Edited by Premy Lis Thursday, April 18, 2019 2:53 PM
    Thursday, April 18, 2019 2:46 PM
  • Had the same issue.

    The problem was in selected default database for current user.

    Just fixed in Mgmt Studio via Server -> Security -> Logins -> Username -> Properties -> General - Default database (in the bottom)

    Thursday, February 6, 2020 7:40 PM
  • Hi,

    I raised this a number of years ago and we are not in a position to make the changes as the selected answer proposed. I guess I was hoping for some sort of Trace Flag or maybe a database scoped option. We have to live with it even on SQL 2016 where we are now.

    Chris

    Friday, February 7, 2020 5:13 PM