Is SSMS Fully Supported for Connecting to Azure SQL Database?

David Beavon 976 Reputation points
2020-06-15T13:44:36.047+00:00

Is SSMS supposed to be fully supported as a tool for managing an Azure SQL Database? Or does Microsoft expect us all to start using Azure Data Studio?

I'm using SSMS v18.5 (15.0.18330.0).

I had noticed that SSMS can take about 20 or 30 seconds to an Azure SQL database. After digging into this for far too long, I discovered that the underlying problem is fairly silly. It appears that the user who is connecting to a given database "MyAbc" needs to also have database-level access to the "master" database. If a user has access to "MyAbc" but not the master database, then SSMS seems to struggle while making a connection and (I'm guessing) it sends a number of failed queries to the master database, but displays no indication in the U/I to show these problems, other than to slow everything down.

The problem doesn't happen when connecting with ADS.

I'm new to Azure and would rather not struggle with an unsupported client tool. I'm not sure if SSMS is supposed to be formally used for Azure databases. It would be good to hear one way or another.

This connection problem with SSMS seems to be something that others have experienced over the years as well. See:

Any information about the SSMS support would be appreciated.

Azure SQL Database
Accepted answer
  1. David Beavon 976 Reputation points
    2020-07-01T19:16:58.397+00:00

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2020-06-15T18:08:06.687+00:00

    As a result of a bug in the QnA system, I cannot publish my answer directly, so I post it in my blog temporarily. In addition I attahc a file with the original content which I tried to publish here. I HIGHLY recommend to read it fully since there are some confusions and inaccuracy in part of the information in this thread. In addition, I already took recording of tracing of ADS and SSMS, and I will add few images and the recording to my post, as soon as I will have time. In the meantime, this is a response for the question in this thread.

    https://ariely.info/Blog/tabid/83/EntryId/263/compatibility-of-SSMS-to-Azure-Database-and-Differences-between-SSMS-and-ADS-regarding-the-performance.aspx

    Original content which I tried to post ion the forum: 10007-qna-q36069.txt

    0 comments
  2. David Beavon 211 Reputation points
    2020-07-02T23:44:02.99+00:00

    @pituach
    When I said "Azure ADF appears to be affected" by slow connectivity I was referring to Azure Data Factory. It has slow interaction with SQL for unrelated reasons. (initially I was hoping that if I was able to discover the reason for the SSMS delays, then I would know how to fix data factory as well. That didn't turn out the way I had hoped.)

    Azure Data Factory uses an "integration runtime" that introduces it own long delays, to make it appear as if it is SQL that is slow. In actuality SQL is not slow and I have the xevents to prove it.

  3. David Beavon 976 Reputation points
    2020-07-04T16:22:12.877+00:00

    @Zagato36

    We've already explored using beefier VM's that are both azure-hosted and self-hosted. This doesn't seem to be the solution. Whenever a pipeline has a lot of small activities that interact with SQL (eg. running a sproc that should take only 5 ms) then it gets bogged down for some inexplicable reason. I have another question here in the forums about that. (here: https://learn.microsoft.com/en-us/answers/questions/36323/adf-performance-troubleshooting.html )

    For example, ADF will introduce a substantial overhead on a sproc that, according to SQL xevents, should be taking 5 ms. That ADF overhead can run for 3 seconds long or up to 20 seconds long after it is wrapped with an ADF/IR "activity".

    I would guess that this type of behavior is generally well-known by people who use ADF . However the explanation for this doesn't seem to come up in my searches. My next step is just to create a repro with a simple loop, and then demonstrate to Microsoft tech support that ADF is introducing some delays that are inconsistent and extremely long. I suspect this is because of the way the IR is designed to use an async/polling mechanism to query the ADF instance for work (...ie. the IR asks ADF for work to do, rather than the other way around).

    In general my primary complaint about ADF is the lack of a large community and the inability to find much in google searches about this. I suspect the tool is still in its infancy and probably has a ways to go before people start demanding more performance out of it. The current users of ADF may not know or care why an activity that is supposed to take 5 ms is being wrapped with an additional 20 second delay within the IR itself.

    0 comments
  4. Tung Dang 1 Reputation point
    2022-01-19T22:43:55.453+00:00

    I had the same issue when using any version of SSMS and connecting to an azure sql database.windows.net

    Solution - Go to Options>> and select the Connection Properties tab...

    Network protocol: change from default to TCP/IP
    Network packet size: change from 4096 to 8192
    Trust server certificate: checked

    0 comments