SQL Server 2017

Microsoft SQL Server 2017 – the new version of SQL Server is released in the same editions as the previous Microsoft SQL Server 2016.

Enterprise – the most comprehensive release, includes all the features of SQL Server 2017, designed for large databases that require maximum performance, reliability, scalability and availability, as well as have very strict requirements for business intelligence. Of course, this edition is the most expensive.

Standard is the most common edition and includes key data management and business intelligence capabilities. Unlike the Enterprise edition, Standard has limitations, for example:

• The maximum number of cores that can be activated is 24;
• Maximum amount of data in optimized memory per database is 32 GB;
• Maximum Columnstore segment cache per 32 GB;
• Advanced high availability is not available: Always On Availability Groups, multi-database failover, readable secondary replicas;
• Transparent data encryption is not available;
• Mobile Reports are not available;
• Extended machine learning integration is not available: full parallelism of R and Python analytics, ability to run on GPUs;
• And some other restrictions;
• If the absence of the above mentioned functionality is not critical to you, you can use it, i.e. buy this edition.

Developer – an edition for software developers, which includes the full functionality of SQL Server, it allows you to create and test applications based on SQL Server without restrictions. It is free, but it can only be used by programmers to develop and demonstrate applications, in other words, it cannot be used as a database server in an enterprise.

Express is a free edition of SQL Server, it is suitable for training and development of applications for data processing on desktop computers and small servers (up to 10 GB in size). This issue, of course, has limitations, for example:

• You can only use four cores;
• Maximum database size 10 GB;
• Maximum data size in optimized memory per database is 352 MB;
• Maximum Columnstore segment cache per instance is 352 MB;
• There is no high availability of Always On:
• There is no basic data integration: SQL Server Integration Services, built-in connectors;
• There are no SQL Server Analysis Services;
• And also everything that’s not available in the Standard edition.

New features of Microsoft SQL Server 2017

Now let’s look at the key innovations that have appeared in the new version of Microsoft SQL Server 2017.

Linux platform support

SQL Server 2017 can now be installed on the Linux operating system. The following Linux distributions are currently supported:

• Red Hat Enterprise Linux 7.3;
• SUSE Linux Enterprise Server with Service Pack 2 for version 12;
• Ubuntu 16.04.

Before installing SQL Server 2017 on Linux, you should consider the following restrictions, they may be critical to you:

• RAM should be at least 3.25 GB, otherwise SQL Server 2017 simply will not install;
• The BTRFS file system is not supported (XFS or EXT4 is supported);
• SSIS (SQL Server Integration Services) is not supported;
• SSAS (SQL Server Analysis Services) is not supported;
• SSRS (SQL Server Reporting Services) is not supported;
• SQL Server R services are not supported;
• There is no possibility of using the database mirroring;
• The functions of transaction replication and merge replication are missing;
• No PolyBase support;
• No system extended stored procedures (XP_CMDSHELL, etc.);
• Data Quality Services are not supported;
• Master Data Services is not supported;
• No AD authentication for linked servers;
• There is no system for tracking changed data;
• There is no managed backup;
• And some other restrictions.

Microsoft promises that in future versions of SQL Server many functions currently unavailable for Linux will be available.

Python language support

Let me remind you that in the previous version of SQL Server 2016 was integrated language R, Microsoft decided to go further, and already now in SQL Server 2017 was integrated language Python, which can be used for analytics, creating intelligent applications in a database SQL Server. In other words, a user application can simply call a stored procedure on an SQL server where the R or Python code will be executed and analyze the data in the database without passing it to the user application.

Adaptive query management

In SQL Server 2017 there is a new generation of query processing functions that allow you to improve the performance of queries in SQL Server by adapting to the application workload environment. This feature includes the following functions:

• Feedback on temporarily provided memory buffer in batch mode;
• Adaptive connections in batch mode;
• Alternate execution for functions with a tabular value and multiple instructions.

Other new and useful features of SQL Server 2017

• Renewable index rebuild – this feature allows you to resume index rebuild operation from the moment you stop after a failure or simply suspend and resume index maintenance later;
• Availability groups without clusters – this feature allows you to create an Always On availability group without using the base cluster;
• New feature of SELECT INTO design in T-SQL – now this design supports table loading into a file group different from the default user group. You can specify a file group name using the ON keyword;
• Transactions between all databases that are part of the Always On availability group, including those that are part of a single instance, are now supported;
• New string functions have appeared: CONCAT_WS, TRANSLATE and TRIM and WITHIN GROUP is now supported for STRING_AGG;
• LOB compression in Columnstore indexes – previously it was difficult to include data containing LOB into the Columnstore index because of its size. LOB data can now be compressed, making it easier to work with LOB.
  Graph Databases – SQL Server 2017 allows you to build applications using graph data;
• Horizontal Scaling in SQL Server Integration Services (SSIS) – SSIS now allows you to speed up the execution of a package by distributing the load on multiple computers. These packages are executed in parallel, in zoom mode;
• Improvements to SQL Server Reporting Services (SSRS) – reports now have comments that allow you to share your views and interact with other users, in other words, the user can participate in the discussion of reports.

Download Microsoft SQL Server 2017

Download a free trial version, as well as free editions of Microsoft SQL Server 2017 can be found on the download page of the official site – Files for downloading from SQL Server.

Installing Microsoft SQL Server 2017

Open the distribution and start the installation. In the menu on the left, click “Installation” => New SQL Server stand-alone installation

New SQL Server stand-alone installation

In the new opened window you will be asked to choose the free version of the installation, for example “Evaluation”, or if you have an activation key for SQL Server 2017 Standard, or any other full-function key, then enter it.

When installing SQL Server 2017, we will offer you a trial key ourselves, it is similar to Evaluation – PHDV4-3VJWD-N7JVP-FGPKY-XBV89, for the test we will use it. (Note: The trial version of this key only works for 180 days, then the application closes its functionality without a valid activation key).

activation key

We agree to the license agreement

We agree to the license agreement

We agree to download updates if you have access to the Internet (or you can refuse, here is optional)

We agree to download updates

In the next window we are offered to select the MS SQL Server components that you want to install. Choose the right components for your tasks and go on.

Select the MS SQL Server components that you want to install

Now we have to install the copy. If your server or virtual machine has no SQL Server installed yet, you can select “Default instance”, if SQL Server has already been installed earlier and you put another instance on top of it, you need to select “Named instance” and give it a name.

Named instance

In the next window we are offered to select service accounts, these are those accounts from which the SQL Server Agent, SQL Server Database, SQL Server Browser services will start. By default, personalized accounts are used.

SQL Server Agent, SQL Server Database, SQL Server Browser services will start

But you can select your domain account if your network is running on a domain (the server is included in the domain).

Don’t forget to choose how the services will start, automatically, manually, or will be disabled at all if they are not needed for your tasks.

choose how the services will start

We would like to note that the selected accounts should preferably not be SQL Server “administrators”.

In the same window, go to the Collation tab.

Collation tab

Collation is the setting of an encoding table. As well as sorting, how to consider upper and lower case, how to react to characters, etc.

Collation is the setting of an encoding table

The next step is to select the server configuration. Here you can choose that you can connect only with Windows logins to SQL Server, or Mixed that you can still connect with internal SQL Server accounts by creating them in SQL Server.

After that at this stage it makes sense to click “Add Current User” and add the current user, as well as you can add an account for your domain.

select the server configuration

In the same window, go to the “Data Directories” tab. In this item you can select the Root directory, the directory where the default databases will be created, the directory of log files and the directory for backups. Accordingly, it is desirable that all directories are on different hard disks.

Data Directories

On the next tab, move on to the settings of the TempDB base. This is a system base that is used to store temporary objects.

The number of files is the default of the number of cores. Initialization size in megabytes – here it makes sense to increase the size to at least 16, or 32 mb.

Autogrowth MB – This is the number of megabytes that will be incremented if the file is filled 100%. Depending on your tasks and the size of your database, we choose the average size of 64mb by default, but it’s better to have no more than 1 gb so that the performance would not suffer. If you don’t know what the load on your base will be, leave it at that.

The directory for temporary files should ideally be a separate drive.

settings of the TempDB base

The next tab is Filestream’s settings, which allows you to store files in the NTFS file system, it can be either activated or not, optionally.

In the Ready to Install section you can see all the settings that you have previously prescribed.

Filestream’s settings

Press Next and wait until everything is set.

wait until everything is set

If everything is installed successfully, all components will be “Succeeded” at the end.

Succeeded

MICROSOFT SQL SERVER 2017 TUTORIAL