Azure SQL Database in Visual Studio - target platform differences (V12 vs. plain)?

Question

I've searched all over and I can't find a clear consensus on this.

In both Visual Studio 2017 and Visual Studio 2019, SQL Database projects have the following Azure platform targeting options when you look at the properties of the project:

• Microsoft Azure SQL Database
  • Setting this option sets the DSP property in the .sqlproj file to: Microsoft.Data.Tools.Schema.Sql.SqlAzureDatabaseSchemaProvider
• Microsoft Azure SQL Database V12
  • Setting this option sets the DSP property in the .sqlproj file to: Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider

We've been using the V12 option primarily, but we're not sure what we're missing (if anything?) by not using the "vanilla" targeting option.

Can someone clarify what the difference between the two options are?

Answer 1

The main features v12 brought to the table are the following:

Increased application compatibility with SQL Server.

A key goal for SQL Database V12 was to improve the compatibility with Microsoft SQL Server 2014. Among other areas, V12 achieves parity with SQL Server in the important area of programmability. For instance:

• Window functions, with OVER
• XML indexes and selective XML indexes
• Change tracking
• SELECT...INTO
• Full-text search

More premium performance, new performance levels

In V12, we increased the database throughput units (DTUs) allocated to all Premium performance levels by 25% at no additional cost. Even greater performance gains can be achieved with new features like:

• Support for in-memory columnstore indexes.
• Table partitioning by rows with related enhancements to TRUNCATE TABLE. The availability of dynamic management views (DMVs) to help monitor and tune performance.

Reliable performance

If your client program connects to SQL Database V12 while your client runs on an Azure virtual machine (VM), you must open the following port ranges on the VM:

• 11000-11999
• 14000-14999

Click here for details about the ports for SQL Database V12. The ports are needed by performance enhancements in SQL Database V12.

Better support for cloud SaaS vendors

Only in V12, we released the new Standard performance level S3 and the public preview of elastic database pools. This is a solution specifically designed for cloud SaaS vendors. With elastic database pools, you can:

• Share DTUs amongst databases to reduce costs for large numbers of databases.
• Execute elastic database jobs to manage databases at scale.

Security enhancements

Security is a primary concern for anyone who runs their business in the cloud. The latest security features released in V12 include:

• Row-level security (RLS)
• Dynamic Data Masking
• Contained databases
• Application roles managed with GRANT, DENY, REVOKE
• Transparent Data Encryption (TDE)
• Connecting to SQL Database By Using Azure Active Directory Authentication
• SQL Database now supports Azure Active Directory authentication, a mechanism of connecting to SQL Database by using identities in Azure Active Directory (Azure AD). With Azure Active Directory authentication you can centrally manage the identities of database users and other Microsoft services in one central location.
• Always Encrypted (in preview) makes encryption transparent to applications and allows clients to encrypt sensitive data inside client applications without sharing the encryption keys with SQL Database.

Increased business continuity when recovery is needed

V12 offers significantly improved recovery point objectives (RPOs) and estimated recovery times (ERTs):

For more information, visit this resource.