none
Unable to view all data using linked server between two Azure Managed Instances RRS feed

  • Question

  • I am unable to view all data using linked server between two Azure Managed Instances.

    I have two managed instances MI1 and MI2.  I created a linked server on MI2 to access MI1.

    When I query tables Msdb.dbo.backupset and Msdb.dbo.backupmediafamily using the linked server, I do not see all the data.

    It looks like I only see the data corresponding to the msdb data for that managed instance. The one I am querying from.

    Example:

    Select * from LINKEDSERVER.msdb.dbo.backupset
    Select * from LINKEDSERVER.msdb.dbo.backupmediafamily

    I created a linked server from on prem to the ManagedInstance and I am able to see all the data from these 2 tables.

    Do I need to set additional access or permissions?


    • Edited by Sylviarf Wednesday, July 17, 2019 11:58 PM Non Issue
    Wednesday, July 3, 2019 5:26 AM

All replies

  • When I query tables Msdb.dbo.backupset and Msdb.dbo.backupmediafamily using the linked server, I do not see all the data.

    It looks like I only see the data corresponding to the msdb data for that managed instance. The one I am querying from.

    Obviously, you would see only data for one instance. Just because you are using a linked server does not mean that you will get data for both.

    Or are you saying that when you are on MI1, and you have a linked server MI2 and run
       SELECT * FROM MI2.msdb.dbo.backupset

    you see the data for MI1 and not for MI2?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 3, 2019 8:02 AM
  • Yes sorry I miss worded that.  I do see data for one server.

    When I am on MI2 and have a linked server to MI1 and run the following from MI2

    SELECT * FROM MI1.msdb.dbo.backupset

    I see the data for MI2 not MI1.

    I did create the linked server with catalog. I deleted the linked server and now I am having network issues recreating it.  Once I am able to recreate the linked server I will try to recreate it without a catalog and see if that helps.

    Thank you for your response!

    Wednesday, July 3, 2019 5:15 PM
  • Hello,

    I was finally able to have the linked server work correctly.  There was a network issue and even though my link server looked good in the UI, it was not actually getting data from the second server.

    Thank you,

    Sylvia

    Wednesday, July 17, 2019 11:57 PM