Introduce UseAzureSql/UseAzureSynapse as alternatives to UseSqlServer

[roji]

We're increasingly seeing T-SQL divergence, where certain features are not available e.g. on Azure Synapse (e.g. #33555), and some features are only available Azure SQL (e.g. the new JSON type, the new vector search capabilities). To account for this divergence, we'll allow users to specify which database is being targeted by providing UseAzureSql/UseAzureSynapse alternatives to UseSqlServer.

Note: we'd need to duplicate all current variants of UseSqlServer.

[ErikEJ]

So given the following engine editions:

2 = Standard (For Standard, Web, and Business Intelligence.)
3 = Enterprise (For Evaluation, Developer, and Enterprise editions.)
4 = Express (For Express, Express with Tools, and Express with Advanced Services)
5 = SQL Database
6 = Azure Synapse Analytics
8 = Azure SQL Managed Instance
9 = Azure SQL Edge (For all editions of Azure SQL Edge)
11 = Azure Synapse serverless SQL pool

UseSqlServer: 2, 3, 4, 8 (5, 9)

UseAzureSql: 5, 8 (8 depending on Update policy - default is SQL Server 2022 compatibility)

UseAzureSynapse: 6, 11

[roji]

@ErikEJ are you suggesting we auto-detect based on the engine queried from the database, or something similar? Or allow the user to specify the engine edition directly?

[ErikEJ]

@roji No - just trying to clarify for myself (and maybe others) what the options will actually mean

[ErikEJ]

@roji Just was made aware of this, affecting Azure SQL Managed Instance

[roji]

@ErikEJ interesting, thanks - I wasn't aware of this... It seems to optionally bring SQL Managed Instance closer to SQL Azure in terms of the engine/features. Do you see this as changing the above proposal?

[ErikEJ]

@roji Yeah, just got this in a newsletter, seems like a brand new thing. It does not affect the above proposal, but I have made some small opdates to my list above.

[ajcvickers]

Removing the milestone and adding needs-design to revisit if we are doing the right thing here, and what the mappings should be.

[drmcclelland]

Thank you so much for working on this issue - it will be very helpful to users of Azure Synapse like me! The lack of support in Synapse for ESCAPE (in LIKE clauses) and OFFSET (for paging) make it harder to use EF for this particular variant of T-SQL.