Powering mission-critical performance with SQL Server 2016

Data has become the lifeblood of the enterprise. It’s the foundation for keen insight and effective decisions that lead to business growth. One of our primary design goals for SQL Server 2016 was to provide the performance, security, availability and business intelligence that are critical to helping companies manage their data and identify new opportunities.

Over the next three weeks, we’ll give you a few snapshots of some key features in SQL Server 2016 and how you can use them to drive sustained mission critical performance, get deeper insights from your data and benefit from hyperscale cloud capabilities.

Run queries up to 100 times faster

With SQL Server 2016 we enhanced the In-Memory columnstore capabilities, which accelerate highly concurrent workloads by transferring data to memory-optimized tables. With these enhancements in place, you can run queries up to 100 times faster than previously possible.

In addition, we made memory-optimized tables more scalable—able to store up to 2 TB of data each, and support bigger workloads.

To get started, evaluate your workloads using a new feature in SQL Server Management Studio (SSMS), the ability to generate migration checklists

To do this, right-click a database in Object Explorer, point to Tasks, and then select Generate In-Memory OLTP Migration Checklists. This step launches a wizard that displays a welcome page. On the second page of the wizard, specify a location in which to save the checklist and whether to generate a checklist for all tables and stored procedures in the database or for a specific list that you define. After you make this selection, the next page of the wizard includes a Script PowerShell Commands button and a Finish button. If you select the Script PowerShell Commands button, a text file opens to display the following command:

Save-SqlMigrationReport -Server ‘<Server Instance Name>’ -Database ‘AdventureWorks’ -FolderPath ‘C:\Users\<User>\Documents\<Path>’

When you click the Finish button, the wizard begins to generate a separate checklist for each table and stored procedure specified in the wizard. The status of each checklist is displayed in the table so that you can easily see whether any failed. After the wizard completes the checklists, you can find them as HTML files in the Stored Procedures, Tables, or User Defined Functions folders in the output path that you configured in the wizard.

Real-time, operational analytics

SQL Server 2016 also includes options for real-time analysis of datasets that are more dynamic in nature. With added support for columnstore indexes in memory-optimized transactional tables, you can avoid issues around latency and benefit from real-time analytics capabilities that live up to the name.

Batch execution mode has also been improved so results can be processed up to 1,000 rows at a time, greatly reducing execution time and the utilization of CPU resources. Use SSMS to get started, or add a clustered columnstore index to a disk-based table using a T-SQL statement such as the sample below.

Better security built in

SQL Server 2016 comes with a number of new security features built-in, helping lock down your data at all levels and states:

• Always Encrypted protects data at rest and in motion by requiring the use of an Always Encrypted driver when client applications to communicate with the database and transfer data in an encrypted state. Without the encryption key, which is kept at the client side rather than in SQL Server, the data is useless.
• Row-Level Security enables you to protect the data in a table row-by-row, so a particular user can only see the rows to which they are granted access.
• Dynamic data masking obfuscates a portion of the data to anyone unauthorized to view it. Use one of four functions to protect the data returned by a query:
  • Default: Fully masks string data, numeric and binary values, date and time
  • Email: Partially masks email addresses and the length of an email
  • Partial: Partially masks values using a custom definition
  • Random: Fully masks numeric values with a random value, specified by you

Configuring a table for Always Encrypted can be done using SSMS or with T-SQL, which involves a two-step process:

1. Create the column master key definition.
2. Create the column encryption key.

A bit like the outer defenses of a castle, the column master key protects the various column encryption keys that actually encrypt the data within a table. You can create a master key with SSMS, though T-SQL offers a more repeatable process, which starts with a CREATE COLUMN MASTER KEY statement, such as the example shown below.

Database engine upgrades

Under the hood of SQL Server 2016 are a number of improvements to the data engine that are designed to help companies optimize day-to-day performance, while providing a scalable, efficient solution to manage data growth.

For example, rather than only creating one data file to support TempDB, where the essential, secondary work takes place to maintain peak performance of the data engine, the SQL Server 2016 setup wizard adapts to your server environment. It automatically assigns the number of data files (with a maximum of eight), based on how many processors it detects on your server, thus minimizing the likelihood of any lag in performance.

Driving down costs with hybrid

The cost and logistical challenges of storing and managing data has many companies looking to the cloud as a viable option. With SQL Server 2016 we introduced Stretch Database, a hybrid cloud feature that combines the power of Azure SQL Database with the feasibility and familiarity of an on premises version of SQL Server. You get all of the enterprise-grade security and data management features, along with a virtually inexhaustible amount of storage on the back end for cost-effective historic data availability, and it works with both Always Encrypted and Row Level Security.

When you enable Stretch Database, a new Stretch Database is created in Azure, as well as an external data source in your instance of SQL Server and a remote endpoint for the database. You can rest assured that user queries cannot be issued against the remote database, and the Stretch Database is protected by several security measures, including encryption and certificate validation.

You can also monitor Stretch Database from a dashboard in SSMS, making it easier to manage the entirety of your data, ensuring that is secure and accessible.

Summary

The ability to harness and analyze data has become essential to the success of today’s enterprise. SQL Server 2016 gives you all of the tools you need to manage your company’s data and offers a secure, scalable platform to help power the next generation of business apps. Download the SQL Server 2016 e-book to learn more, or visit the SQL Server 2016 product page.