Is SQL Managed Instance no longer compatible with SSDT Schema compare?

Tim Eldridge 206

As of the last few days, I am no longer able to do SQL schema compare for a database in SQL Managed Instance in Visual Studio using SQL Server Data Tools. This was working last week but now I get the error "Source is unavailable: dbname". After manually editing the .scmp file to tweak the connection string, I get a slightly different error "unable to determine database platform type". When I setup the source/destination connection it can connect to the SQL Managed instance OK using Azure AD - I know this because I get a list of databases in the "Default database" dropdown. However, when I click the "Compare" button to do the actual compare I get the error.

This functionality is still working when connecting to Azure SQL Database, but not Azure SQL Managed Instance. Is the latest SQL Managed instance version (Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 25 2022 16:04:35)) no longer compatible with SSDT?

Dan Guzman 9,411 Reputation points

2022-03-23T13:26:42.947+00:00

@Tim Eldridge , what values does your MI return for SELECT SERVERPROPERTY(N'Edition'), SERVERPROPERTY(N'EngineEdition');?
Tim Eldridge 206 Reputation points

2022-03-23T13:28:11.7+00:00

Edition = "SQL Azure" and EngineEdition = 8

Dan Guzman 9,411

I captured these queries at the start of SSDT schema compare so I was curious as to whether something changed as a result of the upgrade.

SELECT CASE WHEN SERVERPROPERTY(N'Edition') In ('SQL Data Services', 'SQL Azure') OR SERVERPROPERTY(N'EngineEdition') In (5, 6) THEN 'true' ELSE 'false' END
go
SELECT CASE WHEN SERVERPROPERTY(N'EngineEdition') = 6 THEN 'true' ELSE 'false' END
go

YufeiShao-msft 7,151 Reputation points

2022-03-24T08:42:51.287+00:00

In general, this situation is caused by a mismatch in SQL Server version, there's no SSDT standalone installer for Visual Studio , try to upgrade VS with SQL Tools extension
jiexin zhou 26 Reputation points

2022-03-24T09:58:47.85+00:00

We have the same issue on VS 2019 with SSDT 16.0.62111.25150
Butler Jonathan 61 Reputation points

2022-03-24T10:34:14.947+00:00
Hello

Same problem, SSDT Schema Compare working OK previously now cannot connect to Managed Instances, On-Prem is OK (SQL 2016-2019).

Is this helping us transition to the cloud? No

Does this increase risk of failure if we cannot develop for cloud database? Certainly, YES.

Should the interface provide feedback when the connections aren't working properly? YES.

(upload image not working, drag & drop or upload)

After clicking Schema Compare -> select target -> select connection; sometimes the databases will list from the MI other times they won't, in either case the dialogue remains greyed out as if the connection has failed. If a previously connection to another server is listed that one is used in the dialogue and not the instance chosen (but failed silently).

SSDT 16.0.62111.25150

VS Ent 16.11.10

This needs to be and must be better.

We can't have our development projects crippled by functionality failing because the cloud has been updated.

How about some info in the Visual Studio window if VS does need updating (trying that next - not trivial when I have to create update images first because it's so large to distribute!).

Thanks
Jonathan
Butler Jonathan 61 Reputation points

2022-03-24T10:40:02.297+00:00

Hello YufeiShao-MSFT

Please see my comment below regarding this functionality break for SSDT (due to cloud update?).

Do I need to raise a Microsoft support call for a resolution response time, i.e. is this a MI fix or SSDT fix (don't hold your breath).

This is a breaking [change] for our environment and deployment pipeline process.

Please could you offer guidance/reassurance/help to fix and timings?

Thanks
Jonathan
Tim Eldridge 206 Reputation points

2022-03-24T10:46:53.043+00:00

Hello YufeiShao-MSFT

I installed VS 2022 community edition with the latest version of SQL Server data tools yesterday and the issue still occurs. I agree with ButlerJonathan-2222 - this has broken development and deployment processes for a large number of people/teams and it needs an urgent fix. I think the fix is an update to SSDT to support the latest version of SQL MI

Thanks

Tim
YufeiShao-msft 7,151 Reputation points

2022-03-25T02:15:20.08+00:00

This is a feedback site with suggestions for submitting questions:https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0
Butler Jonathan 61 Reputation points

2022-03-25T08:41:33.217+00:00

You have got to be joking.

Microsoft break Visual Studio SSDT projects as a development pipeline and you want us to ask for the feature returning?

I think the cat is out of the bag. Microsoft is not a reliable partner. I'm now looking at other products and tooling where support is a priority.

I haven't heard back from the support call yet but I'll update here and on the feedback I raised through VS yesterday (link below to upvote).

Utter shambles and a disgraceful response so far.

Upvote the feedback to get some action on this utter shambles: https://developercommunity.visualstudio.com/t/Is-SSDT-Schema-Compare-broken-for-Azure/1702437

Thanks
Jonathan
Butler Jonathan 61 Reputation points

2022-03-25T08:48:50.123+00:00

Also, can as many people who are able to, please upvote this issue (please all who are following the problem? we are at 14 votes with 32 following updates).

Many Thanks
van Heerde, Jered 11 Reputation points

2022-04-05T07:28:53.907+00:00

I have raised a ticket with MS. This was the response:

I have looked internally and realized that new version of MI (code package 16.0.41.326) is making it impossible for customers to use SSDT/Visual Studio with their managed instances. However rest assured, PG teams from Visual Studio and Managed Instances are working on the fix. Changes will first become widely available to our customers in VS 17.2 preview 3, which is scheduled to release on April 12th.

In the meantime, a quick workaround is to downgrade the MI version and then test it, for which I am now raising a case with our Product team and let you know on the progress soon.

I'm still waiting on the rollback and currently on versions :
SQL Azure 8
Microsoft Visual Studio Enterprise 2019: Version 16.7.3
SQL Server Data Tools 16.0.62008.03130
Harmen Verburg 1 Reputation point

2022-04-05T18:29:23.02+00:00

Any idea when this will be fixed? Releasing our SQL database is becoming incredibly difficult now.
van Heerde, Jered 11 Reputation points

2022-04-06T09:39:28.56+00:00

after the roll back on managed instance i can now confirm SSDT Schema compare is working again. MS confirmed a new release of SSDT is required (no ETA yet)

I would suggest to create a support ticket if you want to have this fixed prior to release of a new version of SSDT
Butler Jonathan 61 Reputation points

2022-04-06T10:46:45.71+00:00

Hi

The short answer is that if you want to get Visual Studio working (Server Explorer, SSDT) with the Managed Instances then you need to raise a support call.

The fix is supposed to be released in VS 2022 17.3 available here - i haven't tried it yet.

But i need 2019.......can't find any update to that yet..

https://learn.microsoft.com/en-gb/visualstudio/releases/2022/release-notes#17.1.3

Good luck.......with Microsoft you'll need it.

JB
van Heerde, Jered 11 Reputation points

2022-04-06T13:58:28.987+00:00

feedback from ticket on the ETA:

I have checked with PG about the ETA on the fix and they mentioned that VS update with a fix should be released on April 12th.
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2022-04-25T21:56:29.913+00:00

The fix for this issue is available in Visual Studio 2022 Preview (Version 17.2.0 Preview 3.0).
As short-term mitigation we suggest customers install Visual Studio 2022 Preview. This version can be installed side-by-side with other versions, and can be downloaded from https://visualstudio.microsoft.com/vs/preview/#download-preview
As a long-term mitigation, once the fix reaches the Visual Studio 2022 GA editions (version 17.2.0 or higher), we suggest customers start using Visual Studio 2022 GA (Community, Professional, Enterprise). There is currently no ETA for this fix to reach Visual Studio 2019.
Note that the use of Visual Studio 2022 Community is permitted for organizations of any size when used for SQL Server Data Tools development (the software license terms is available at https://visualstudio.microsoft.com/license-terms/vs2022-ga-community/).
Visual Studio (GA) can be downloaded from https://visualstudio.microsoft.com/downloads/

We apologize for the impact to affected customers. We are continuously taking steps to improve the Microsoft Azure Platform and our processes to help ensure such incidents do not occur in the future

Regards,
Oury
Ilkka Ellilä 1 Reputation point

2022-05-18T08:11:44.457+00:00

https://learn.microsoft.com/en-us/visualstudio/releases/2019/release-notes#release-notes-icon-visual-studio-2019-version-161115

The fix is here for the VS2019
اخلاص وردى ايوب نصير 1 Reputation point

2022-06-01T09:21:23.797+00:00

THANKS

19 answers

Sahaj Quinci 21 Reputation points

2022-03-24T10:18:46.943+00:00

Same issue here :(
Please sign in to rate this answer.

4 people found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Andrew Duckers 21 Reputation points

2022-03-24T11:14:05.603+00:00

Using Azure Data Studio and the Schema Compare extension works; we've also logged a ticket for the Microsoft Managed Instance team, I'll update you with any response we get....
Please sign in to rate this answer.

4 people found this answer helpful.
Lesinski, Martin 31 Reputation points

2022-03-24T12:07:49.45+00:00

Appreciate that, @Andrew Duckers

JUMARIE Fabrice 1 Reputation point

2022-03-24T13:21:59.29+00:00

Does Data compare extension exist?

I found many information about Schema Compare but nothing about Data compare

Mark Poynter 26 Reputation points

2022-03-24T14:16:48.953+00:00

No I don't believe there is a data compare extension in Azure Data Studio - only schema compare.

My workaround was to use the Generate scripts option in the Tasks menu in SSMS on the MI SQL instance. I selected only the tables I needed to data compare and also selected the Types of data to script to schema and data (which in the advanced button in Set scripting options) . I then used this script to create the tables on a non-MI database and then used the data compare tool in VS on this instead.

It's very long-winded and inconvenient but it does do the job.

Butler Jonathan 61 Reputation points

2022-03-24T15:57:21.417+00:00

I have also raised a support call, this time through the Developer Tools - SSDT route.

Hopefully someone at Microsoft will give a flying and sort the Program Teams out..can't have these SNAFUs totally derailing development pipelines!!

But we also need a fix for this now....

Thanks
Jonathan
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Mark Poynter 26 Reputation points

2022-03-23T17:02:37.64+00:00

I get the same issue (unable to determine database platform type) for both schema and data comparisons in VS2019 and VS2022. I think they've rolled out an update for SQL Managed Instances in readiness for version 160 (i.e. SQL server 2022). My theory is this is not yet compatible with SSDT, as I can still compare all my non-MI SQL databases OK.

As per Tim my SQL Server Data Tools for VS2019 is also version 16.0.62201.17150 . As this is controlled by the VS 2019 installer - so I can't see a way how you can upgrade SSDT to 16.0.62111.25150 even if you wanted to, as there's no stand-alone installer?

VS2022 is using SSDT 17.0.62201.12090 and this has the same problem as VS2019.
Please sign in to rate this answer.

2 people found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Johannes Gerhold 11 Reputation points

2022-03-24T12:33:03.237+00:00

Also having the same issue here on

VS 2019 (16.11.11)

with SSDT 16.0.62111.25150
Please sign in to rate this answer.

2 people found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Mark Poynter 26 Reputation points

2022-03-23T17:29:28.91+00:00

For those that need it, I've found the following workaround that does seem to work with SQL managed instances.

If you have the latest SSMS installed then you will also have Azure Data Studio. In Azure Data Studio there's an extension you can install for SQL Server Schema Compare. It seems to be exactly the same tool as Visual studio, just with a different look and feel.

Personally I find Azure Data Studio a bit clunky, but at least it does the job for now.
Please sign in to rate this answer.

1 person found this answer helpful.
Tim Eldridge 206 Reputation points

2022-03-24T10:07:43.327+00:00

Thanks MarkPynter-5754 - this looks like a possible workaround, particularly as it supports database projects too which is our main use of schema compare. After trying it a bit, I agree that it is a bit clunky which is OK for a temporary workaround but hopefully Microsoft will fix the core issue in SSDT soon so that we can switch back to an environment that we are familiar with

Lesinski, Martin 31 Reputation points

2022-03-24T10:37:17.153+00:00

Workaround works for us as well, but it sure isnt as convenient as before.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Is SQL Managed Instance no longer compatible with SSDT Schema compare?

19 answers

Your answer