{"id":21305,"date":"2017-11-15T09:30:30","date_gmt":"2017-11-15T17:30:30","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/?p=21305"},"modified":"2024-01-22T22:51:06","modified_gmt":"2024-01-23T06:51:06","slug":"sql-server-2017-features-bring-choice-to-developers","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2017\/11\/15\/sql-server-2017-features-bring-choice-to-developers\/","title":{"rendered":"SQL Server 2017 Features Bring \u2018Choice\u2019 to Developers"},"content":{"rendered":"

Data is everywhere today: in the cloud, on premises, and everywhere in between, tied up in systems of nearly endless complexity. Microsoft solutions allow developers to innovate while also scaling and growing their data infrastructure. In SQL Server 2016 SP1, SQL Server made available a consistent programmable surface layer for all its editions, making it easy to write applications that target any edition of the database.\u00a0 This year\u2019s release takes it a step further with native support for Linux and Docker.<\/p>\n

Microsoft puts the needs of the developer front and center in its data solutions. We have created the most advanced set of tools to radically lower the barriers to getting data \u2013 of any type, from anywhere \u2013 into the application design and build process. Today with the preview of Microsoft SQL Operations Studio, you can now access, run and manage this data from the system of your choice, on Windows, Linux and Docker.<\/p>\n

Committed to choice for both database platform and tools<\/h2>\n

SQL Server 2017 also makes it easier to drive innovation via a CI\/CD pipeline with the support of Docker containers.\u00a0 Since the Community Technology Preview of SQL Server 2017, there have been over 2 million Docker pulls.\u00a0 You can use any container orchestration layer, as SQL Server 2017 effectively becomes an application component within your compiled code hosted in the container.\u00a0 It is light weight and very fast to install \u2013 SQL Server on Linux installs in less than a minute.\u00a0 As a result, you can update the entire software stack with each check-in and deployment. Learn more on DevOps using SQL Server<\/a>.<\/p>\n

There are also SQL Server client drivers for the major languages, including C#, Java<\/a>, PHP<\/a>, Node.js<\/a>, Python<\/a>, Ruby<\/a> and C++<\/a>. Any language that supports ODBC data sources should be able to use the ODBC drivers.\u00a0 And any language based on the JVM should be able to use the JDBC or ODBC drivers. Choose any of the above languages to trial at our new hands-on labs<\/a>.<\/p>\n

In the spirit of choice, the data tools team today released SQL Operations Studio for public preview (see below). It is a light weight, cross-platform database development and operations tool designed to help non-database professionals with routine tasks necessary to update and maintain a database environment. It\u2019s based on .NET Core and forks from Visual Studio Code, making it extremely extensible and easy to use. Download<\/a> it, try it out<\/a>, and please give us feedback via GitHub issues!<\/p>\n

\"DevOpsForDevs_1\"<\/p>\n

R + Python built-in for in-database analytics<\/h2>\n

The confluence of cloud, data and AI is driving unprecedented change. The ability to manage and manipulate data and to turn it into breakthrough actions and experiences, is foundational to innovation today. We view data as the catalyst to augment the human ingenuity, removing friction and driving innovation. We want to enable people to change and adapt quickly. Most of all, we want to equip today\u2019s innovators and leaders to turn data into the insights and applications that will improve our world.<\/p>\n

Developers and data scientists who explore and analyze data also have several new options.\u00a0 Now that SQL Server 2017 on Windows supports R and Python natively, they can either write R or Python scripts in the text editor of choice, or they can embed their scripts directly into their SQL query in SQL Server Management Studio. See example below:<\/p>\n

\"DevOpsForDevs_2\"<\/p>\n

Or if the analysis calls for highly complex joins, SQL Server 2017 also supports graph-based analytics, making it possible to describe nodes and edges within a SQL query. See example below:<\/p>\n

\"DevOpsForDevs_3\"<\/p>\n

CREATE TABLE Product (ID INTEGER PRIMARY KEY, name VARCHAR(100)) AS NODE;<\/p>\n

CREATE TABLE Supplier (ID INTEGER PRIMARY KEY, name VARCHAR(100)) AS NODE;<\/p>\n

CREATE TABLE hasInventory AS EDGE;<\/p>\n

CREATE TABLE located_at(address varchar(100)) AS EDGE;<\/p>\n

In-memory + performance for blazing-fast applications<\/h2>\n

And we mentioned, choice does not need to sacrifice performance!\u00a0 SQL Server 2017 also has some great performance enhancing features, including adaptive query processing (AQP) and automatic plan correction (APC).\u00a0 AQP uses Adaptive Memory Grants in SQL Server to track and learn how much memory is used by a given query to right-size memory grants.\u00a0 While APC ensures continuous performance by finding and fixing performance regressions.\u00a0 Customers have been highly favorable of these features, including dv01 who switched off their OSS stack on AWS to move everything to their stack run on SQL Server.<\/p>\n

In-Memory OLTP is the premier technology available in SQL Server and Azure SQL Database for optimizing performance of transaction processing, data ingestion, data load, and transient data scenarios.\u00a0 Expect to see a 30x-100x increase in performance by keeping tables in-memory and using natively compiled queries.<\/p>\n

A couple of steps to consider if you\u2019re going to use In-Memory OLTP:<\/p>\n

1. Recommended to set the database to the latest compatibility level, particularly for In-Memory OLTP:<\/p>\n

ALTER DATABASE CURRENT<\/b>\r\nSET COMPATIBILITY_LEVEL = 140;<\/b>\r\nGO\r\n<\/b><\/pre>\n
2. When a transaction involves both a disk-based table and a memory-optimized table, it\u2019s essential that the memory-optimized portion of the transaction operates at the transaction isolation level named SNAPSHOT:
\n<\/b><\/p>\n
ALTER DATABASE CURRENT SET <\/b>MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON<\/b><\/span>\r\nGO<\/b><\/pre>\n
3. Before you can create a memory-optimized table, you must first create a memory-optimized FILEGROUP and a container for data files:<\/p>\n
ALTER DATABASE AdventureWorks ADD FILEGROUP AdventureWorks_mod CONTAINS memory_optimized_data<\/b>\r\nGO\u00a0 <\/b>\r\nALTER DATABASE AdventureWorks ADD FILE (NAME='AdventureWorks_mod', FILENAME='c:\\var\\opt\\mssql\\data\\AdventureWorks_mod') TO FILEGROUP AdventureWorks_mod<\/b>\r\nGO<\/b><\/pre>\n
Security built-in at every level<\/h2>\n
Every edition of SQL Server provides a robust set of features designed to keep organizational data separate, secure, and safe. Two of the most interesting security features for developers are Always Encrypted and Row-Level Security.<\/p>\n
Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (social security numbers), stored in Azure SQL Database or SQL Server databases. Always Encrypted allows customers to encrypt sensitive data inside their applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results. See the graphic below:<\/p>\n
\"\"<\/a><\/p>\n
Row-Level Security (RLS) enables customers to control access to rows in a database table based on the characteristics of the user executing a query (for example, group membership or execution context).<\/p>\n
Row-Level Security simplifies the design and coding of security in an application. Row-Level Security enables organizations to implement restrictions on data row access. For example, an organization can ensure that employees can access only those data rows that are pertinent to their department, or restrict a customer\u2019s data access to only the data relevant to their company.<\/p>\n
To configure Row-Level Security, follow the steps below:<\/p>\n
1. Create user accounts to test Row-Level Security<\/p>\n
USE AdventureWorks2014; <\/strong>\r\nGO<\/strong>\r\nCREATE USER Manager WITHOUT LOGIN; <\/strong>\r\nCREATE USER SalesPerson280 WITHOUT LOGIN;<\/strong><\/pre>\n
2. Grant read access to users on required table
\n<\/strong><\/p>\n
GRANT SELECT ON Sales.SalesOrderHeader TO Manager; <\/strong>\r\nGRANT SELECT ON Sales.SalesOrderHeader TO SalesPerson280;<\/strong><\/pre>\n
3. Create a new schema and inline table-valued function<\/p>\n
CREATE SCHEMA Security; <\/strong>\r\nGO <\/strong>\r\nCREATE FUNCTION Security.fn_securitypredicate(@SalesPersonID AS int) <\/strong>\r\n RETURNS TABLE <\/strong>\r\nWITH SCHEMABINDING <\/strong>\r\nAS <\/strong>\r\n RETURN SELECT 1 AS fn_securitypredicate_result WHERE ('SalesPerson' + CAST(@SalesPersonId as VARCHAR(16)) = USER_NAME()) <\/strong>\r\n OR (USER_NAME() = 'Manager');<\/strong><\/pre>\n
4. Create a security policy adding the function as both a filter and block predicate on the table<\/p>\n
CREATE SECURITY POLICY SalesFilter <\/strong>\r\nADD FILTER PREDICATE Security.fn_securitypredicate(SalesPersonID) <\/strong>\r\n ON Sales.SalesOrderHeader, <\/strong>\r\nADD BLOCK PREDICATE Security.fn_securitypredicate(SalesPersonID) <\/strong>\r\n ON Sales.SalesOrderHeader <\/strong>\r\nWITH (STATE = ON);<\/strong><\/pre>\n
5. Execute the query to the required table as each user to see the result (can also alter the security policy to disable policy)<\/p>\n
Thanks for joining us on this journey to SQL Server 2017. We hope you love it! Going forward, we will continue to invest in our cloud-first development model, to ensure that the pace of innovation stays fast, and that we can bring you even more and improved SQL Server features soon.<\/p>\n
Here are a few links to get started:<\/p>\n