Synapse Serverless SQL Support

TckFahimKanjiTckFahimKanji Posts: 10 New member
in SQL Prompt
Hi,

Anyone know if SQL Prompt can crawl and register Serverless SQL Databases?  It seems like it can't but I can't find any documentation anywhere suggesting one way or another.
Tagged:
· Share on Twitter

Answers

  • Jon_KirkwoodJon_Kirkwood Posts: 423 Silver 5

    Hi and Thanks for reaching out on the Redgate forums.

    Regarding your query around Azure Synapse Serverless db's. 

    Yes, it should certainly be possible to connect to these instances and have SQL Prompt register them to provide functionality. 

    To test this; I connected to my Azure instance and spun up a new sample DB on a serverless compute instance and connected it to my local SMSS instance to access SQL Prompt functionality.



    Have you tried connecting to an instance and are experiencing any issues in getting our tools to work? 

    If so please provide some further details so we can assist further

    Jon Kirkwood | Technical Support Engineer | Redgate Software
    · Share on Twitter
  • TckFahimKanjiTckFahimKanji Posts: 10 New member
    Hi Jon,

    So I'm an owner on the resource 


    and I don't get any intellisense on the views:



    I've even tried adding myself explicitly as db_owner on the serverless db, but doesn't seem like it's a permissions issue.

    I'm using version 10.9.2.25020



    Here's the log dump when I try to refresh the cache:
     
    2021-11-29 09:15:23.923 -08:00 [Information] Chosen server capabilities "Sql2014"
    2021-11-29 09:15:24.463 -08:00 [Warning] Failed to load server [REDACTED]-ondemand.sql.azuresynapse.net
    System.Data.SqlClient.SqlException (0x80131904):  'sysdatabases' is not supported.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader()
       at RedGate.SqlPrompt.Engine.Cache.Candidates.ServerCandidate.PopulateDatabases(SqlConnection masterConnection, Boolean wasSkippedDueToFilter)
       at RedGate.SqlPrompt.Engine.Cache.Candidates.ServerCandidate.LoadServer()
    ClientConnectionId:2444b62c-a133-4789-a2b5-a06f45eb229f
    Error Number:15871,State:10,Class:16
      
    · Share on Twitter
  • Jon_KirkwoodJon_Kirkwood Posts: 423 Silver 5

    Hi Fahim,


    Looking at the log files it does show an issue connecting to the Azure instance which may be the major cause of this issue. Are you connecting via AD or SQL Server Authentication?

    Is connecting via AD are you able to test a connection using SQL Server Auth and see if this issue persists.


    I did notice that you are potentially using SQL Server 2014. This is not supported past SQL Prompt v10.3 and may also be a contributing factor to this issue.

    End of support for SSMS 2012 & 2014

    Are you able to confirm the version of SSMS you are using, and if possible replicate this on a newer installation to confirm if the issue persists?


    Finally, if possible are you able to provide the full log file so we can look for any other angles of failure.

    The link below has been generated if you are not wanting to share logs over a public forum. The link is unique to this inquiry and expires after a fortnight

    https://files.red-gate.com/requests/pLRiu15mUQGgpBb8sIFZpn

    Jon Kirkwood | Technical Support Engineer | Redgate Software
    · Share on Twitter
  • TckFahimKanjiTckFahimKanji Posts: 10 New member
    Hi Jon,

    Using the latest version of SSMS I believe


    did the test with a SQL user - same result with db_owner provided

    Turned on verbose logging and tried to create "clean" logs of both scenarios for your review.

    RE: I did notice that you are potentially using SQL Server 2014. This is not supported past SQL Prompt v10.3 and may also be a contributing factor to this issue.

    This is the SQL Server compatibility version that synapse serverless SQL endpoint provides by default - I don't think it's configurable, is it?


    Cheers,


    Fahim.
    · Share on Twitter
  • TckFahimKanjiTckFahimKanji Posts: 10 New member
    Hi Jon,

    Any update on this item?  Is there something you're waiting on me to provide?

    RSVP


    Fahim.


    · Share on Twitter
  • Jon_KirkwoodJon_Kirkwood Posts: 423 Silver 5

    Hi @TckFahimKanji

    Regarding this item; I note there have been a few releases for SQL Prompt which have targeted issues with Azure environments. 

    Can you please advise if you have updated your Prompt install in January?

    The latest version (10.10.4) is available here for download - https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.10.4.26165.exe

    Release note info is available here:

    https://documentation.red-gate.com/sp/release-notes-and-other-versions/sql-prompt-10-10-release-notes

    Releases 10.10.0 & 10.10.2 have potential connections to your code suggestions not appearing

    Jon Kirkwood | Technical Support Engineer | Redgate Software
    · Share on Twitter
  • TckFahimKanjiTckFahimKanji Posts: 10 New member
    Hi Jon,

    Same issue after the upgrade.  Here's the relevant pieces of the verbose log:
    2022-01-21 12:56:07.355 -08:00 [Debug] Checking is contained or restricted
    2022-01-21 12:56:08.868 -08:00 [Debug] Contained or restricted: >2012
    2022-01-21 12:56:10.272 -08:00 [Debug] Loading server capabilities
    2022-01-21 12:56:10.341 -08:00 [Information] Chosen server capabilities "Sql2014"
    2022-01-21 12:56:10.860 -08:00 [Warning] Failed to load server XXXXXXXXXXXXXX-ondemand.sql.azuresynapse.net
    System.Data.SqlClient.SqlException (0x80131904):  'sysdatabases' is not supported.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader()
       at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.PopulateDatabases(SqlConnection masterConnection, Boolean wasSkippedDueToFilter)
       at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.LoadServer()
    ClientConnectionId:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    Error Number:15871,State:10,Class:16

    Screenshots of software versions.  I recall you mentioned the SQL Version before - this is synapse on-demand SQL and it's not something I choose - it's what MS provides.





    Also, I'm explicitly configured as the SQL AD Admin on the resource 

    I'm curious about the "sysdatabases" utility in the log.  According to MS that view is just for backwards compatibility - Should updated approaches to getting the necessary information be used https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysdatabases-transact-sql?view=sql-server-ver15
    · Share on Twitter
  • TckFahimKanjiTckFahimKanji Posts: 10 New member
    Hi @Jon_Kirkwood

    ANy update here?  I'm still struggling with this.  Happy to schedule a call with you to show you the issue first-hand!

    RSVP

    Fahim.
    · Share on Twitter
  • gp83gp83 Posts: 2 New member
    Hi @TckFahimKanji, I'm having the same issue as you. Did you manage to resolve this?
    · Share on Twitter
  • TckFahimKanjiTckFahimKanji Posts: 10 New member
    @gp83,

    Jon stopped responding in this thread so I started another one and was told that SQL Prompt doesn't support Azure Synapse (haha)

    https://forum.red-gate.com/discussion/comment/168005#Comment_168005 

    I'm going back to them again - you can track the other thread for more details if they come!


    · Share on Twitter
  • gp83gp83 Posts: 2 New member
    @TckFahimKanji thanks for coming back to me so quickly.

    That's a real shame, I've become so used to using SQL Prompt over the years.

    I don't suppose you've found another product or solution that could temporarily provide some of the SQL Prompt features when working with Synapse serverless DBs?
    · Share on Twitter
Sign In or Register to comment.