Azuredatastudio: Data Virtualization doesn't work against SQL Server in CU5

[bweissman]

• Azure Data Studio Version: 1.19.0
  Data Virtualization Extension: 1.5.2

Steps to Reproduce:

Connect to a BDC running CU5
Pick any User Database
Run the "Virtualize Data" Wizard
Pick SQL Server as your Source
Provide master key, data source and credentials (I used another BDC and an azure SQL DB with adventureworksLT to test)
in the table selection dialogue:
expand tables, select one table, click on that table: columns don't load and the wizard is now non-functioning.

[adsbot[bot]]

We need more info to debug your particular issue. If you could attach your logs to the issue (ensure no private data is in them), it would help us fix the issue much faster.

To find your logs:

• Open command palette (Click View -> Command Palette)
• Run the command: Developer: Open Logs Folder

This will open the log file locally. Please include renderer.log

[bweissman]

20200625T152100.zip

Full logs attached. Also, happy to jump on a call or try a preview VSIX if that helps.

[Charles-Gagnon]

In the meantime - putting the instructions here for anyone else hitting issues using the new version of the Wizard.

To roll back and use the previous version of the Wizard :

1. Go to the Extensions Gallery in ADS
2. Find the Data Virtualization extension and uninstall it, then restart ADS
3. in ADS go to settings and uncheck this box (this will prevent the extension from auto-updating to the new version again)

1. Download https://sqlopsextensions.blob.core.windows.net/extensions/datavirtualization/datavirtualization-1.4.0.vsix
2. In ADS go to File -> Install Extension from VSIX Package and select the VSIX you downloaded
3. Confirm the prompts and then restart ADS

That should revert back to the previous version of the extension. Note that by doing this you will only be able to virtualize data from SQL and Oracle instances again - MongoDB and Teradata won't be supported.

[Charles-Gagnon]

@bweissman Does this happen even if you create a smaller source database? There's some perf issues with larger databases that I'm going to look into improving, but I want to make sure there's nothing else you might be running in to as well.

[bweissman]

It even happens with adventureworksLT - switching back to CU4 or the previous extension fixes it for me so far consistently every single time.

[Charles-Gagnon]

Could you run this command on the BDC that has CU5 (replacing the data_source value with the name of the External Data Source you created for your source server)

declare @data_source SYSNAME = <External Data Source Name here>;
declare @object_root_name NVARCHAR(MAX) = NULL;
declare @max_search_depth INT = 3;
EXEC sp_data_source_objects @data_source, @object_root_name, @max_search_depth

I just want to know if it runs successfully and about how long it took - no need to paste the contents.

If that runs successfully could you then still try just making an empty database/table and virtualizing that to make sure it's not just a perf issue?

[bweissman]

The query works. Takes 5 seconds on first run, 1 second after that.

Doing this with an empty database doesn't help as the error requires me to select a table.
When doing it with a database that only holds one table, same error occurs.

[bweissman]

Also, just tried on a non-BDC SQL Server 2019 CU5 running on Linux: same issue

[bweissman]

And on Windows CU5: same

[Charles-Gagnon]

And when you say "error" - there isn't any actual error that pops up right? It just doesn't do anything?

[Charles-Gagnon]

Could you capture a recording of you going through the wizard and hitting this? I want to make sure I'm not missing anything here since currently I'm not able to repro this myself with similar setups.

[Charles-Gagnon]

@bweissman Ok so we've identified an issue with the geography type. This is unfortunately an issue with the Polybase backend so isn't something we can fix on the extension side. We'll work on a fix which should be included in CU7 but there isn't any workaround aside from not virtualizing tables with that type aside from rolling back to a previous version of the extension.

You're saying you see this even with a simple table though?

[Charles-Gagnon]

@bweissman Alright, I've made a change to how we fetch the data that should help with the perf problems I mentioned before. When you get a chance could try it out and let me know if you're still seeing issues?

1. Uninstall current version of the extension
2. Download https://sqlopsextensions.blob.core.windows.net/extensions/datavirtualization/datavirtualization-1.6.0.vsix
3. In ADS go to File -> Install Extension Package from VSIX
4. Select the VSIX you downloaded in step 2
5. Once it's installed run your scenario again and if you're still seeing issues capture the logs and video as requested so I can see if there's some other issue you're hitting here.

[bweissman]

This version of the extension fixes the issue! Thanks!

[Charles-Gagnon]

@bweissman Alright, I've published the new extension to the gallery. I'd suggest uninstalling the current version and then reinstalling from the gallery. Thanks for the help tracking down these issues!