Managed Instance, cross-database queries. What are the hidden requirements?

  • Have a managed instance with 2 databases. In theory I should be able to query between them like I would do on my local SQL Server.

    I'm getting the message which I thought was for Azure SQL, not Azure SQL Managed Instance:

    Reference to database and/or server name in ‘**removed**’ is not supported in this version of SQL Server.

    Are there some requirements/settings that I'm missing?

    I read that the Basic pricing tier can't be used, but changing that didn't work.

    1. It's the monthly $150 Azure subscription that come with the Visual Studio Enterprise licence.
    2. I changed the pricing tier on both databases. Tried Standard, and General Purpose.
    3. I created the 2 database with the sample data through the Azure Portal, to rule out my data.
    4. There's no elastic pool.
    5. I haven't set up elastic query, as the docs read I shouldn't need to.
  • To me it sounds like you have an Azure SQL instance, not an Azure SQL Managed Instance.

    Did a quick google and found this document from Microsoft:

    https://docs.microsoft.com/en-us/azure/azure-sql/database/features-comparison

    If you scroll down a bit, it states that "Cross-database/three-part name queries" are supported.  Even Cross-database transactions are supported within the same instance.

     

    EDIT - was just looking at my Azure portal, and when I look at the plans for an Azure SQL Managed Instance, my only option is "Azure SQL Managed Instance".  Could be that I am not on the enterprise VS subscription, but thinking it may be something with your setup.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • Yep, their doc definitely says it supports it. That's why I'm wondering what's going on.

    I'm pretty sure I went to 'Create a Resource'  in the https://portal.azure.com/ and chose 'Azure SQL Managed Instance'.

    I supposed there's no harm in me trying to create a 2nd test server and see if that works...

    Should you see similar options for the server in SQL Management Studio to a local SQL Server?

    Is there something like select @@VERSION that will tell you whether it's a managed instance?

    (Generally I'm looking into moving to Managed instances as an interim measure until we have everything in 1 database , then Azure SQL)

     

  • Maybe I chose 'create new' to create a new server while creating the 1st Azure SQL database.

    How can you tell if it's a managed instance?

    Don't have enough credits to try it out until next month starts.

  • Edition of 5 means it's Azure SQL, would have been 8 if Azure SQL Managed Instance.

    Edition Enum (Microsoft.SqlServer.Management.Smo) | Microsoft Docs

    select @@VERSION

    SELECT SERVERPROPERTY('EngineEdition');

    SELECT SERVERPROPERTY('Edition');

    Microsoft SQL Azure (RTM) - 12.0.2000.8 Sep 18 2021 19:01:34 Copyright (C) 2019 Microsoft Corporation

    5

    SQL Azure

    Local SQL Server:

    Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19043: ) (Hypervisor)

    3

    Developer Edition (64-bit)

  • There is a HUGE difference between Managed Instance and Azure SQL Database. So, as everyone has pointed out, if you're in Azure SQL Database, the standard three-part multi-database queries will not work. You can do cross database queries in Azure SQL Database, but, as you can see here, it's WAY more complicated. The syntax for using it is different too. You have to change everything. Where as Managed Instance is pretty much identical to running it on a standard SQL Server instance. The exact same behaviors with the exact same caveats and issues. Nothing any different.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • You're not allowed to create a SQL Managed Instance with the Azure $200 free credits.

    So looks like I'll have to wait until the next calendar month.

    A bit poor really if that's the thing you want to try out Azure for.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply