Catch up: SQL Server continues to innovate and evolve
This is the first in a new series of software Catch Up articles. Did you once use SQL Server 2008 and now want to use the upcoming 2016 version but find it too different? Maybe you want to go back to SharePoint after a 3 year break but a few key things have changed? Our Catch Up articles and training links should give you the foundations to pick-up right where you left off.
Gavin Payneis a principal architect for Coeo, a SQL Server and Azure professional services company, and a Microsoft Certified Architect and Microsoft Certified Master. His role is to guide and lead organisations through data platform transformation and cloud adoption programmes.
The new features appearing in preview releases of SQL Server 2016 remind us of Microsoft’s commitment to keeping the database server product modern. The next major release of SQL Server shows how much the product has changed in just a few years. This article looks at two new features that will bring significant improvements over recent versions: real-time analytics using ColumnStore indexes and row level security.
Real-time analytics using ColumnStore indexes
Business users have often wanted to perform large analytical queries against operational databases to get a real-time understanding of what the business is doing. Until recently, whatever approach database teams used there would be either a latency in age of the data reports used or an impact to the transactional systems generating reports.
Several years ago, SQL Server 2012 introduced the ColumnStore index feature that laid the foundations to solving the problem of performing analytical queries against operational data. However, even though ColumnStore indexes could massively reduce the runtime of analytics queries, in SQL Server 2012 they were read-only. They were a great feature for data warehouses but not for reporting on read-write tables. SQL Server 2014 provided updateable ColumnStore indexes but the devil in the detail meant the technology was still more suited for historic data warehouse queries rather than real-time analytics.
SQL Server 2016 will finally provide a solution for real-time analytics. It allows database teams to optimise a table for both transactional and real-time analytics workloads without degrading the performance of the other workload.
The solution supports a table’s transactional workloads by maintaining a traditional row-based table and indexes to support operations that work on small numbers of rows – common for transactional applications. At the same time, SQL Server maintains a ColumnStore index to support analytics workloads that aggregate data from large numbers of rows on an ad-hoc basis. Unlike previous versions of SQL Server, both the row-based and column-based indexes are updateable and constantly kept synchronised.
You can find further information on the evolution of the ColumnStore index in SQL Server 2016 on the MSDN Blog
Row Level Security
Restricting a database user’s access to specific data in a database has always been harder than it sounds. It might be easy to stop a manager seeing the personal details of staff they don’t manage in an HR application, but what happens when they want direct access to the underlying database to perform some organisational modelling in Power BI?
Until recently, there was no straightforward answer to limiting data access in the above scenario. However, SQL Server 2016 now provides us with one – its new row level security feature.
Row level security allows a database team to restrict which rows in a table a database user sees, even when they perform a select * query directly against the database. It moves the implementation of this level of security from the application tier to the data tier – so the data access security logic will always get applied.
SQL Server 2016 provides row level security by applying a query predicate (a where clause) stored in regular SQL Server function. The function is associated with a table’s security policy, a new configuration setting, before applying it to every query against the table.
MSDN gives some end-to-end T-SQL examples of implementing the feature, however below are some examples of the security based query filters you can use:
WHERE @Manager = USER_NAME()
This example configures SQL Server to only return rows where the value of the column Manager are the same as the user name for the current SQL Server session:
WHERE DATEPART(hh,GETDATE()) BETWEEN 9 AND 17
This example shows how regular T-SQL functions can be used to introduce some creativity into using row level security. Here, a check is made to ensure the query only returns data during working hours.
New solutions to old problems
In summary, SQL Server 2016 not only brings new capabilities to support new requirements – it also brings new solutions to existing problems. The two examples I used above are common problems that database teams have often had to write bespoke solutions for. Bespoke code should be what organisations use to differentiate themselves, not achieve an everyday operational task. SQL Server 2016 hopefully brings us one step nearer to achieving that.