Azure SQL databases ALL go online when connecting to server

Mark van Eck 56

Hi all,

We have setup approx. 20 Azure SQL databases (serverless with 1 hour on auto-pause).

Issue:
When we make a connection with SSMS or Azure Data Studio with a server and a specified database, then only the specified database resumes and goes online again.
However, when someone connects to the server, WITHOUT specifying a database name, ALL databases resume and go online.

Is there a way that we are able to connect to the server, without specifying a databasename, and NOT resuming all paused databases?

The reason we want to see the list of databases below a server connection is because we need this when we want import/export data with tooling (like data migration assistant).

thanks in advance,
Mark

Erland Sommarskog 100.9K Reputation points MVP

2022-12-08T22:22:22.147+00:00

Can you describe the exact steps you are doing? I was trying to repro the issue, but I was not able to. But I had to make my guess of what you are doing.
Mark van Eck 56 Reputation points

2022-12-09T08:36:29.57+00:00
Case:

1) I have 20 Azure sql databases (serverless). Auto pause on 1 hour.
2) Let's assume these databases are all offline and at that moment I make a connection with SSMS (18.12.1) with following settings:

Fill in only sql server name

AAD Integrated

Connect to database: <default>

Connection is established and below the servername all 20 databases are listed. At that point I can see in the Azure Portal that all databases are resuming and go online.
Mark van Eck 56 Reputation points

2022-12-09T11:55:30.013+00:00

Sorry, I just checked, and it seems that SSMS is not causing that all databases go online.

It happens when connecting with Azure Data Studio, with same settings as above.
Erland Sommarskog 100.9K Reputation points MVP

2022-12-09T22:36:11.23+00:00

Alright, that sounds familiar. I seem to recall that I heard MVP colleagues complain about this behaviour with Azure Data Studio, but I was surprised when you also mentioned SSMS.

I guess that ADS has the idea to check whether you have access to the database. Which is not a very good idea for serverless databases. Beside the cost, it can take some time if the databases have been unloaded entirely.
GeethaThatipatri-MSFT 27,102 Reputation points Microsoft Employee

2022-12-14T15:10:37.393+00:00

@Mark van Eck Following up to see if the below suggestion was helpful. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

-Geetha
Mark van Eck 56 Reputation points

2022-12-15T07:31:07.123+00:00

Hi Geetha,

All answers are helpful of course. However, the answers did not solve the issue. In fact this is quite a bug in the Microsoft package. You would assume that if you have an infrastructure with serverless databases to keep the cost low, that you don't want ALL the databases to come online because someone connects to the server only.

Is this something that can be brought to a higher level at Microsoft. You've got any ideas. Appreciate your time!

Kind regards,
Mark
GeethaThatipatri-MSFT 27,102 Reputation points Microsoft Employee

2022-12-15T11:51:05.27+00:00

@Mark van Eck Sorry for the inconvenience, I am checking internally and provide you with more details.

-Geetha
GeethaThatipatri-MSFT 27,102 Reputation points Microsoft Employee

2022-12-15T16:45:21.597+00:00

@Mark van Eck Could you please confirm if you are observing all serverless databases resuming immediately after you connect to the server, or if that happens only after you expand the Databases folder in Object Explorer?

Regards
Geetha
GeethaThatipatri-MSFT 27,102 Reputation points Microsoft Employee

2022-12-16T17:59:12.093+00:00

@Mark van Eck Just wanted to check if you had the chance to check my response.

-Geetha
Mark van Eck 56 Reputation points

2022-12-19T12:21:54.413+00:00

Hi Geetha, I am back in the office tomorrow and will check your proposal, and let you know asap.
Mark van Eck 56 Reputation points

2022-12-20T09:56:48.973+00:00
Hi Geetha,

Thanks for waiting ;-)

Situation:
Using Azure Data Studio as tool
Azure SQL server has got 4 serverless databases. 1 database is online, 3 databases are offline.
Connection is made with following options:

Server filled in

Azure AD

Account: myAccount

Database: <default>

Encrypt: TRUE

Result:

Connection established, 2 folders are listed below the server ("Databases" and "Security"). When looking in Azure Portal NONE of the 3 databases go online, That's good!

When collapsing folder "Databases" it is resuming database2, and then the listing of the databases failed.

database2 is now also online. Rightclick on "databases" and click refresh. Now database 3 is resuming and goes online. List of databases is still failing because database 4 is not yet ONLINE. When database4 is also online I finally see a list of all databases below the server (in Azure Data Studio).

Additional:
In SSMS this behaviour is different. There you can list all databases without being them online. Only when you collapse or doubleclick on a specific database, the database goes ONLINE. Which is exactly what we want as a behaviour.
However, we have developers who like to use ADS as well.

Hope the info is sufficient and understandable. If not, please let me know.
Kind regards,
Mark

Accepted answer

Erland Sommarskog 100.9K Reputation points MVP

2023-01-25T19:33:20.3333333+00:00

Azure Data Studio 1.41 was released today. I see this in the Release notes:

I hope this clears up Mark's issue.
Please sign in to rate this answer.

2 people found this answer helpful.

0 comments No comments
Sign in to comment

1 additional answer

Alberto Morillo 32,886 Reputation points MVP

2022-12-08T14:24:33.52+00:00
Try using PowerShell instead or CLI to list the databases. With dbatools you can do a lot of great things with PowerShell.

$servers = Get-AzSqlServer foreach($server in $servers){ Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName | Where-Object {$_.DatabaseName -like 'jos*'} }

SSMS v18.1 or earlier were auto-resume databases when a new query Windows was opened. Latest versions of SSMS do not do that. Try SSMS 19 preview or SSMS 18.12 to have a better experience with Azure SQL Serverless.

Can your users consider to use Azure Data Studio? Try this tool instead.
Please sign in to rate this answer.
Mark van Eck 56 Reputation points

2022-12-08T14:47:25.3+00:00

Thanks Alberto,

For our team of users that is way to technical. Besides this, we have to use the data migration assistant and within this tool you cannot select a database. You have to select a database from a list and therefor users need read access to the master database.

On-topic:
I am using 18.12 but still its strange that whenever I connect to the Azure sql server, that all databases go online. Even if I do not have access to some of the databases. It just auto-resumes when anyone just connects to the server.

Alberto Morillo 32,886 Reputation points MVP

2022-12-08T14:55:11.603+00:00

I don't think there is a workaround for this behavior for SSMS at this time. Can your user use Azure Data Studio instead?

Mark van Eck 56 Reputation points

2022-12-08T14:58:15.353+00:00

Again, thanks for your answer.

Yes, we also use Azure Data Studio, but gives same result.

Also tested with DataGrip, and then it works fine. But this tool is no option because of licenses.

Alberto Morillo 32,886 Reputation points MVP

2022-12-08T15:03:58.083+00:00

Let me share this with the SSMS and Azure Data Studio team.

Mark van Eck 56 Reputation points

2022-12-08T15:10:06.557+00:00

Thanks,

The behaviour is coming from the fact that users have READ permissions on master database. This is what we added to allow users to see the list of databases within the server.

If we remove this READ permission on Master we are able to connect to server/databasename just fine, and we won't auto-resume the other databases.
However, for some reason we like the users to see these listing of databases for certain tooling (migration assistant, import/export wizard, etc.)

Mark van Eck 56 Reputation points

2022-12-09T11:58:11.457+00:00

Hi Alberto,

I just checked again. It seems that SSMS is not causing that all databases go online. That works fine. All databases are listed below the server and only the database you collapse (or doubleclick) go online.

However, when using Azure Data Studio and connecting to the server with database selected: <default> --> then all databases within this server go online after connection with only the server.

Alberto Morillo 32,886 Reputation points MVP

2022-12-14T20:21:56.283+00:00

Use SSMS then. Glad to know SSMS is not causing the issue. I received no response from MSFT product teams

Mark van Eck 56 Reputation points

2022-12-15T07:34:13.533+00:00

Hi Alberto,

Thanks for your answer.
The problem is that I have a group of 40 people who work with Azure Data Studio AND SSMS. Isn't it a bug in Azure Data Studio, or is that the way it behaves?

You would assume that if you have an infrastructure with serverless databases to keep the cost low, that you don't want ALL the databases to come online because someone connects to the server only.

Is this something that can be brought to a higher level at Microsoft. You've got any ideas. Appreciate your time!

Kind regards,
Mark

Erland Sommarskog 100.9K Reputation points MVP

2022-12-15T21:55:50.343+00:00

You can report bugs here:
https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0

Make sure to include the business case. That is, the fact it reduces the benefit of Serverless.

you don't want ALL the databases to come online because someone connects to the server only.

But as Geeta asks: it's not the connection as such that opens the database, but when you expand the Databases node in Object Explorer.

Mark van Eck 56 Reputation points

2022-12-20T13:13:41.85+00:00

Thanks Erland,

I created a report a minute ago.

Still I think it is strange that a connection to server resumes ALL databases (or as you are saying expand the database node).
Our business case was built upon that our sql databases are used for a short period and the serverless model is the best option in terms of cost saving.
Besides this, SSMS is behaving very well. Only ADS is doing strange stuff when connecting to a sql server with lots of serverless offline databases behind the hood

Again,
Thanks,
Mark

Erland Sommarskog 100.9K Reputation points MVP

2022-12-20T22:10:56.727+00:00

Do you have an URL for the feedback item?

Mark van Eck 56 Reputation points

2022-12-22T10:26:07.62+00:00

Yes, sure. Here it is.

https://feedback.azure.com/d365community/idea/21815a9c-6580-ed11-a81b-000d3adb7ffd

Kind regards,
Mark

Erland Sommarskog 100.9K Reputation points MVP

2022-12-22T22:13:35.43+00:00

Thanks! I've voted! Don't forget to vote yourself!

Erland Sommarskog 100.9K Reputation points MVP

2022-12-23T16:30:51.007+00:00

I forgot that there is a GitHub repo for ADS where you can file issues. I got a pointer to https://github.com/microsoft/azuredatastudio/issues/6450, which is about this specific issue. You may be interested to look at it.
Sign in to comment