SQL Server 2017 is coming

It’s official: the next version of SQL Server, planned to be released this year, will be called SQL Server 2017. It is currently in the Community Technology Preview (CTP) 2.0 stage which is the first production-quality preview of the product.

Even though it’s coming just a year after SQL 2016, it’s bringing some game-changing features. Here are the highlights:

  1. It runs on Windows, Linux and Docker containers! In other words – it can run on any platform including Mac OS. And we are talking about the identical functionality of the database engine on all those platforms, not 3 different versions.
  2. It brings Adaptive Query Processing automatically executing database queries as efficiently as possible through batch mode memory grants, adaptive joins and interleaved execution.
  3. Graph Data processing – going beyond traditional relational databases supporting nodes and edges, multi-hop navigation and join-free pattern matching.
  4. Python support! With R introduced in SQL 2016, this allows using both popular languages for machine learning and data science WITHIN the SQL Server! This makes SQL Server the first and only RDMS with built-in Artificial Intelligence (AI).

There will be a lot of blog posts about this in coming weeks. Until then:

Advertisements

Dynamic Data Masking

Here’s a new feature that I like very much: Limiting exposure of sensitive data like user emails, phone numbers, addresses, credit card numbers and so on.

This feature has been available in the Azure SQL Database for a while and now it is included in the new SQL Server 2016 . So let’s see it in action…

Continue reading

SQL Server 2016: configure TempDB during installation

Proper configuration of the TempDB system database is crucial for optimal performance of a SQL Server instances during heavy loads. The recommended strategy is to create multiple data files, one per CPU core (logical processor), but not more than 8.  Those files should be equal in size and with the same autogrowth settings. In past, this had to be done manually after the initial installation of SQL Server so many instances went to production with default values. SQL Server 2016 allows the configuration of TempDB DURING the setup process and it also sets better default settings than ever before. Continue reading

Row-Level Security part 2 – Controlling Write Access

My previous post was focused on controlling read operations. With the latest enhancements to the row-level security, it is now possible to restrict write operations as well. This feature is currently available as a preview now generally available in Azure SQL Database V12. Let’s see how that works.

Continue reading

Row-Level Security in SQL Server and Azure

Row-Level Security (RLS) is a new feature of SQL Server 2016 and Azure SQL Database that enables data access control based on the users executing those queries: if a user isn’t authorized to access certain rows in a table then those rows are automatically filtered out by the database engine. This feature promises to simplify design and coding of applications, especially in complex multi-tenancy environments, as the access control logic is moved from the application to the database. In short, instead of writing queries like this:

CREATE VIEW vwInventory AS ... (implements security logic);
SELECT * FROM vwInventory WHERE isVisibleTo = 'Paul'

We write simple queries like this:

SELECT * FROM Inventory;

Isn’t that cool?

Continue reading

Azure SQL Database – Version and Compatibility Level

In the on-premises world, when we install a SQL Server on our own servers, we decide on SQL Version (2012, 2014….), Edition (Standard, Enterprise, Epress…) and we set the database Compatibility Levels according to our applications’ specs. But what is it like with the Azure SQL Database (PaaS)? The answer is – it’s very different so let’s go through the key points to have in mind:
Continue reading

SQL Server in Azure

There are two flavors of SQL Server in Azure: one is the traditional SQL Server platform hosted in Azure virtual machines, also described as Infrastructure as a Service (IaaS). The other is called Azure SQL Database, database-as-a-service or Platform as a Service (PaaS).  there are several important differences in functionality and features, so let’s look at them:

Continue reading