Microsoft SQL Server 2016

Microsoft SQL Server 2016  – is a relational database management system developed by Microsoft. As you know, support for SQL Server 2005 ended in April 2016 and currently there are the following versions of SQL Server: SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 and now SQL Server 2016.

New features of Microsoft SQL Server 2016

And we will start, of course, with a review of new functionality, i.e. those new features that have appeared in SQL Server 2016.

• Always Encrypted (i.e. “Always Encrypted”) is a feature that allows you to store some data (e.g. personal data) in encrypted form, with the encryption key stored on the client, resulting in a separation between those who own the data and can view it, such as a client whose personal data is encrypted and those who manage the data but should not have access to it, such as administrators or SQL Server programmers. This feature provides data protection, both at rest and in motion, i.e. during transmission;
• Real-Time Operational Analytics is the ability of SQL server to process both working OLTP load and analytical queries on the same database tables simultaneously, i.e. in real time. The traditional approach to analytics has several drawbacks such as: the complexity of implementing ETL processes, the cost of purchasing additional data warehouses and licenses, and one of the most important drawbacks, which is unacceptable for many enterprises, is the delay of data. This is due to the fact that an ETL job is usually executed once a day, for example at the end of a working day, and the data can only be analyzed accordingly the next day. The possibility of Real-Time Operational Analytics allows in some cases to eliminate all the above mentioned disadvantages, i.e. ETL and data warehouses are no longer needed. One of the nuances of Real-Time Operational Analytics is that this feature is aimed at working with a single data source that participates in both OLTP load and analytics. If you need to analyze data from multiple sources, the need for a separate data warehouse does not disappear;
• SQL Server R Services (In-database Analytics) is a new SQL Server component that allows you to perform analysis inside a database using the R language.
• Screenshot 2R is a programming language and open source libraries, which are widely used as a tool for data analysis. All features of the R language, including the ability to use a fairly large number of open source packages for everyday tasks are integrated into SQL Server 2016, which avoids problems with their integration into applications. In addition, users of R Services can use the library of algorithms ScaleR – a set of functions scalable to handle hundreds of billions of lines through parallel computing, which provides performance that is impossible to achieve using common open source packages;
• PolyBase is a technology that allows you to send requests to both relational and non- relational data, as well as to import and export this data. In other words, SQL Server now has the ability to access data in Hadoop or Azure Blob storage;
• AlwaysOn Availability Groups – SQL Server 2016 has improvements in AlwaysOn Availability Groups, such as the ability to have up to three synchronous replicas, circular load balancing on secondary replicas, support for Distributed Transaction Coordinator (DTC), support for Group Managed Service Accounts (gMSA), and improved log replication bandwidth;
• Stretch Database is a technology that allows you to store cold data in the Azure cloud with quick access to that data (cold data here means data that is rarely used or not used at all). In other words, if you have a table or tables in your company’s database, which stores historical data reaching terabyte volumes, while this volume is constantly increasing, and you need to ensure the safety of these data, so that they can be instantly accessed, the Stretch Database technology will help you. The advantages of using Stretch Database are as follows: no need to purchase additional storage systems, instant access to “cold” data, the usual way to access “cold” data, i.e. there is no need to modify requests and applications, the transfer of “cold” rows (for example, if the same table has both “hot” and “cold” data, you can move to the cloud Azure only “cold”). All you need to do to start using Stretch Database is to have SQL Server 2016 and subscribe to the Azure cloud to create a new SQL Server Stretch Database;
• SSRS Mobile Reports – added to Reporting Services the ability to create reports optimized for viewing on mobile devices;
• Improvements for Columnstore Indexes – SQL Server 2016 has a number of improvements for columnstore indexes, for example: a cluster columnstore index now supports one or more non-clusterized rowstore indexes, a table can now have one updated non-clusterized columnstore index, and you can now create one columnstore index on a memory optimized table;
• Improvements to In-Memory OLTP – this technology has also received new functionalities such as: support for operators UNION, UNION ALL, DISTINCT, support for UNIQUE indexes, restrictions CHECK and FOREIGN KEY, triggers, and nested requests;
• Database Scoped Configurations – this feature allows you to configure a number of database configuration parameters on an individual database level;
• Live Query Statistics – SQL Server Management Studio 2016 provides the ability to view a live plan of the active query execution, in other words, you can now see in real time how the query is executed. With this feature, we can easily determine which part of the request will be executed long enough and adjust the request accordingly, i.e. optimize it;
• Query Store – This feature automatically records the history of queries, plans, and statistics at runtime and saves them for review, allowing database administrators to track resource-intensive queries and optimize them;
• System-Versioned Temporal Tables is a new type of temporary tables that allows you to save the full history of data changes and get information about these data at any time, not just the current one;
• Built-in JSON support – in SQL Server 2016 it is possible to process data in JSON format, i.e. now we can export and import JSON data with standard tools;
• Row-Level Security (RLS) – this technology allows controlling access to rows in the database table based on the characteristics of the user executing the request (for example, group membership or execution context). In other words, we can make it possible for one or more users to see (and accordingly process) only certain rows in the table;
• Dynamic Data Masking is a function of dynamic data masking. The purpose of this feature is to restrict the disclosure of sensitive data by preventing users who should not have access to this data from viewing it. This feature does not encrypt data, so it is recommended to use Dynamic Data Masking in combination with other security technologies such as Encryption or Row-Level Security to better protect your data;
• Other improvements. More information can be found in the official documentation here the link – SQL Server 2016 Technical Documentation.

SQL Server Version Comparison

Further I suggest to compare all supported versions of MS SQL Server that evolution of SQL server was clearly seen, i.e. in what version this or that functionality appeared.

Microsoft SQL Server 2016 editions

SQL Server 2016 is available in four major editions: Enterprise, Standard, Developer and Express. Enterprise and Standard are reactions that are used in industrial operation. Developer Edition is used for application development, Express for training, or just to get acquainted with SQL server. Below is a comparison table of these editions, from which you can find out what features and limitations this or that edition has.

System requirements for SQL Server 2016 installation

Now let’s look at the requirements that must be met in order to install SQL Server 2016. The table shows the minimum requirements for the processor and RAM. It is recommended to use higher performance, also it should be noted that x86 processors are no longer supported, which means that SQL Server 2016 can only be installed on a computer with an x64 processor and full functionality will only be available in 64-bit versions of Windows.

SQL Server 2016 download

The first thing to do is download the distribution itself. You can download it from Microsoft server – www.microsoft.com/en-US/download/

Microsoft SQL Server 2016 Installation

Step-by-step instruction to install Microsoft SQL Server 2016. Check if all components are installed.

Step-by-step instruction to install Microsoft SQL Server 2016Launching the Microsoft SQL Server 2016 installation:

Launching the Microsoft SQL Server 2016 installation

We are in the Product Key section. During the installation process, the content of the points on the left will change. Select the trial version or specify the installation key. Next.

Product Key sectionChoose components for installation

Choose components for installationSpecify (or leave as default) the instance name

Specify (or leave as default) the instance nameSQL Server Agent – Automatic

SQL Server Agent – Automatic Specify Administrators

Specify Administrators

Prescribe directories for Bases, Logs and Backups:

Prescribe directories for Bases, Logs and BackupsSetting up TempDB. Here are the settings for 1C. If your server is not assumed under 1C, then leave the default values.

Setting up TempDB

Installing Updates and Service Packages

Installing the latest Cumulative Updates and Service Packages – support.microsoft.com/sql-server-2016-build-versions 

Installing the latest Cumulative Updates and Service Packages* If there’s nothing to choose and the checkboxes are empty, you downloaded the wrong Cumulative Update. Follow the link again, there are 2 groups, the first is ServicePack and the second is CU. Depends on the SQL 2016 build that you have installed.

Installing Management Studio

Installing Management Studio

You can use this link or better yet, download and run it in advance – docs.microsoft.com/download-sql-server-management-studio

Microsoft SQL Server 2016 tutorial