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

Question

Post reply

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

ja_iris

SSC Enthusiast

Points: 177
More actions
October 21, 2021 at 6:18 pm

#3942958
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.

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

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.

This reply was modified 3 years, 1 month ago by Mr. Brian Gale.

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!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ja_iris SSC Enthusiast Points: 177 More actions · Answer 2

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)

ja_iris SSC Enthusiast Points: 177 More actions · Answer 3

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.

ja_iris SSC Enthusiast Points: 177 More actions · Answer 4

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');

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)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ja_iris SSC Enthusiast Points: 177 More actions · Answer 6

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.