none
a jobstep, in the secondary instance while it functions as secondary role, was configured to run in the scope of a mirrored database could be executed RRS feed

  • Question

  • we are running SQL server 2017 in our production environment with SQL mirroring to a secondary server.
    some of our jobs are designed with a first 'Check if principal' step that checks if the server functions as the principal role by querying mirroring_role from  sys.database_mirroring as well as checking the database state in sys.databases and only then continues to next jobstep.
    most of these jobsteps are configured to run in the scope of master.
    we had one job in which this jobstep was configured to run in the scope of one of the mirrored databases.
    we would expect it would fail since the database is not accessible but it ran successfully.
    moreover, we have simulated this behavior by creating another job and we could even insert data into the mirrored database from a job created in the secondary server.
    we have lately upgraded our SQL servers from SQL 2014 to 2017 and this job remain untouched from previous version.
    this kind of behavior did not occur in 2014.
    it looks like a bug
    please advise

    Kiran

    Thursday, December 26, 2019 11:46 AM

Answers

  • Hi Kiran,

    Yes, a mirror database  on the  mirror server can’t be access.  You can’t insert data into it. Your situation is strange.  Please check your  job is created for the mirror database on mirror server not on principal server.  I test it on my environment, I can’t create an insert data job for the mirror database on mirror server. It shows the following error message. The database "mirror" cannot be opened. It is acting as a mirror database. 

    Please check your mirror environment is well. If it is a bug, you can submit your issue to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server . This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

    Best regard,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 27, 2019 7:27 AM
  • Hi Kiran,

    Any update? If the reply could help you, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.
     
    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by juniorkiran Tuesday, January 7, 2020 2:58 PM
    Monday, December 30, 2019 1:04 AM

All replies

  • Hi Kiran,

    Yes, a mirror database  on the  mirror server can’t be access.  You can’t insert data into it. Your situation is strange.  Please check your  job is created for the mirror database on mirror server not on principal server.  I test it on my environment, I can’t create an insert data job for the mirror database on mirror server. It shows the following error message. The database "mirror" cannot be opened. It is acting as a mirror database. 

    Please check your mirror environment is well. If it is a bug, you can submit your issue to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server . This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

    Best regard,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 27, 2019 7:27 AM
  • Hi Kiran,

    Any update? If the reply could help you, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.
     
    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by juniorkiran Tuesday, January 7, 2020 2:58 PM
    Monday, December 30, 2019 1:04 AM
  • Done Cathy

    Kiran

    Monday, December 30, 2019 6:26 AM