locked
SQL Server and Aroura Linked Server RRS feed

  • Question


  • I have configured our Aurora instance to be a linked server to an existing MSSQL instance using OLEDB connector. I am able to query the Aurora instance. However, we are running into an issue with some commands (mostly DELETE so far) that seem to get stuck. Analyzing in MSSQL we see OLEDB waits (going on 3 days now). On Aurora end using the Client Connections view we see the same connections that seems to be stuck on the Aurora end. We had two (the longest running ones) that were in a Sleep command with a "delayed send ok done" State. We are new to MySQL, but doing some searching I'm unable to find reference to that State so am guessing it's a custom Aurora State. Based on some other reading it sounds like the Aurora end might not be sending back a complete status to the MSSQL end.

    So my question/s are is there some way to figure what is causing that State on our queries? Secondly, is using Aurora as linked server an ok practice?
    Tuesday, June 21, 2016 12:07 PM

Answers

  • I'm completely unfamilliar with Aurora.

    When you use a linked server, SQL Server is with regards to the data source access just one program that is accessing it. That is, if you would write a program that accesses Aurora in the same manner as SQL Server does, you would probably see the same problem.

    The difference between a custom-written program is that SQL Server is written from a more generic perspective. That is, talks with an OLE DB provider that it does know as such, but queries the provider about its capabilities to determine which exact calls to use next. This means, that SQL Server could expose issues in the OLE DB provider for Aurora. Or, in the case, you use MSDASQL + an ODBC driver for Aurora, issues in the ODBC driver.

    If there is a vendor or other support channel for Aurora, you may inquire there if they have tested accessing Aurora from SQL Server.

    Tuesday, June 21, 2016 12:46 PM
  • >Secondly, is using [some product I've never heard of] as linked server an ok practice?

    No, probably not.  Linked Server drivers are loaded in long-lived processes, and need to be very high quality.  Instead use SSIS which will load the drivers into a short-lived process only when necessary.

    You can use the SSIS Data Streaming Destination if you want to consume the output of an SSIS package in SQL Server.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, June 21, 2016 2:45 PM

All replies

  • I'm completely unfamilliar with Aurora.

    When you use a linked server, SQL Server is with regards to the data source access just one program that is accessing it. That is, if you would write a program that accesses Aurora in the same manner as SQL Server does, you would probably see the same problem.

    The difference between a custom-written program is that SQL Server is written from a more generic perspective. That is, talks with an OLE DB provider that it does know as such, but queries the provider about its capabilities to determine which exact calls to use next. This means, that SQL Server could expose issues in the OLE DB provider for Aurora. Or, in the case, you use MSDASQL + an ODBC driver for Aurora, issues in the ODBC driver.

    If there is a vendor or other support channel for Aurora, you may inquire there if they have tested accessing Aurora from SQL Server.

    Tuesday, June 21, 2016 12:46 PM
  • >Secondly, is using [some product I've never heard of] as linked server an ok practice?

    No, probably not.  Linked Server drivers are loaded in long-lived processes, and need to be very high quality.  Instead use SSIS which will load the drivers into a short-lived process only when necessary.

    You can use the SSIS Data Streaming Destination if you want to consume the output of an SSIS package in SQL Server.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, June 21, 2016 2:45 PM
  • Hi i-Bro,

    Were you able to resolve the problem ? Are you able to use Aurora as linked Server with MSSQL?

    Thanks,

    Tulika

    Wednesday, May 2, 2018 12:03 AM