What's new in SQL Database V12
This topic describes the many advantages that the new V12 version of Azure SQL Database has over version V11.
We continue to add features to V12. So we encourage you to visit our Service Updates webpage for Azure, and to use its filters:
- Filtered to the SQL Database service.
- Filtered to General Availability (GA) announcements for SQL Database features.
The latest information about resource limits for SQL Database is documented at:
Azure SQL Database Resource Limits.
A key goal for SQL Database V12 was to improve the compatibility with Microsoft SQL Server 2014, and to maintain the compatibility as new versions of SQL Server are released. Among other areas, V12 achieves parity with SQL Server in the important area of programmability. For example:
See here for the small set of features not yet supported in SQL Database.
Starting in June 2016, newly created databases on Azure SQL Database V12 have their compatibility level start at 130, which matches Microsoft SQL Server 2016 GA.
You can use
ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 120 if you prefer.
Databases created before June 2016 do not have their compatibility level changed by this change of default. Nor is the level of a database changed by upgrading it from V11 to V12.
For an explanation of how you can compare your most important queries between the latest versus previous compatibility level, see:
In V12, we increased the Database Transaction 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.
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:
Click here for details about the ports for SQL Database V12. The ports are needed by performance enhancements in SQL Database V12.
Only in V12, we released the new Standard performance level S3 and the public preview of elastic database pools. Elastic database pools is a solution designed for cloud SaaS vendors. With elastic database pools, you can:
- Share DTUs among databases to reduce costs for large numbers of databases.
- Execute elastic database jobs to manage databases at scale.
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.
V12 offers improved recovery point objectives (RPOs) and estimated recovery times (ERTs):
|Business continuity feature||Earlier version||V12|
|Geo-restore||• RPO < 24 hours. |
• ERT < 12 hours.
|• RPO < 1 hour. |
• ERT < 12 hours.
|Active Geo-Replication||• RPO < 5 minutes. |
• ERT < 1 hour.
|• RPO < 5 seconds. |
• ERT < 30 seconds.
See SQL Database business continuity for more information.
There are many good reasons why customers should upgrade now to Azure SQL Database V12 from V11:
- SQL Database V12 has a long list of features beyond the features of V11.
- We continue to add new features to V12, but no new features will be added to V11.
- Most new features are released on SQL Database V12 before they are being released for Microsoft SQL Server.
One easy way to see if you have a database or logical server running on an earlier version of the SQL Database service is to do the following:
- Go to the Azure Portal.
- Click Browse.
- Click SQL Servers.
- The icon next to your server or database tells the story:
- V12 logical server
- Earlier version logical server
Another technique to ascertain the version is to run the
SELECT @@version; statement in your database, and view the results similar to:
- 12.0.2000.10 (version V12)
- 11.0.9228.18 (version V11)
A V12 database can be hosted only on a V12 logical server. And a V12 server can host only V12 databases.
If you are not yet running on V12, you can upgrade your logical server by following the steps in Upgrade to SQL Database V12 in place.
- By July 31, 2015, all regions had been promoted to General Availability (GA).
- V12 was released in December 2014, but only at the status of Preview.