Advertisements

Archive

Archive for the ‘SQL Server 2017’ Category

SQL Server 2017 RTM, download, and cool “New Features” !!!

November 16, 2017 Leave a comment

 
On 2nd October 2017 Microsoft released the full & final General Available (GA) version of SQL Server 2017.

As announced earlier with the first CTP release, the new SQL Server 2017 will run both on Windows & Linux. Not only Linux, but it will be supported on Docker, and macOS (via Docker) too.
 

–> Download SQL Server 2017 RTM bits:

To download the SQL Server 2017 RTM you can Register and Download the Full version or Free evaluation version (180 days).
 

–> Download SSMS 17.x bits: download here
 

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 16299: ) (Hypervisor)

 

–> Here are few few “Top Features” added in SQL Server 2017:

1. SQL Server on Linux: Now run SQL Server on all platforms, Windows, Linux, Mac, docker.

2. New Graph Database: for modeling many-to-many relationships.

3. Resumable online index rebuild: Resumes an online index rebuild operation from where it stopped after a failure or a pause.

4. Adaptive query processing: feature optimizes strategies to your application workload in runtime condition.

5. IDENTITY_CACHE option: to avoid gaps in identity values in case of server restarts or failover.

6. SQL Server Machine Learning Services: which added Python support, with existing R language.

7. SSIS “Scale Out” and HA (High Availability): execute SSIS packages across multiple servers.

 

–> For a full list of various features already rolled out with previous CTP versions, please check my previous posts below:

– All new features added in SQL Server 2016 SP1, [link].

– New features added in:

   – SQL Server 2017 CTP 1.x.

   – SQL Server 2017 CTP 2.x.

   – SQL Server 2017 RC1
 

–> Videos on SQL Server 2017

Download & Install SQL Server 2017 & SSMS on Windows

Install SQL Server on Linux
 

–> References:

>> SQL Server 2017 official Page

>> Docs for SQL Server 2017


Advertisements

SQL Server 2017 Release Candidate (RC1, full & final version) is available for download

July 21, 2017 1 comment

 
On 17th July 2017 Microsoft released the full & final Release Candidate 1 (RC1) version of SQL Server 2017.

As announced earlier with the first CTP release, the new SQL Server 2017 will run both on Windows & Linux. Not only Linux, but it will be supported on Docker, and macOS (via Docker) too.
 

–> Download SQL Server 2017 bits:

To download the SQL Server 2017 you can Register and Download the Full version or Free evaluation version (180 days).

Or, directly download the ISO (~1.7 GB): SQLServer2017RC1-x64-ENU.iso
 

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2017 (RC1) – 14.0.800.90 (X64)
Jul 11 2017 07:03:16
Copyright (C) 2017 Microsoft Corporation. All rights reserved.
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 14393: ) (Hypervisor)

 

–> New Features & Enhancements: This Release Candidate version is the final version of SQL Server 2017 and adds following features:

1. SQL Server on Linux supports Active Directory Authentication, which enables domain-joined clients on either Windows or Linux to authenticate to SQL Server using their domain credentials and the Kerberos protocol.

2. SQL Server on Linux can use TLS to encrypt data that is transmitted across a network between a client application and an instance of SQL Server.

3. Added more model management capabilities for R Services on Windows Server, including External Library Management. The new release also supports Native Scoring.

4. Additional DMVs, enabling dependency analysis and reporting (DISCOVER_CALC_DEPENDENCY, MDSCHEMA_MEASUREGROUP_DIMENSIONS).

5. Added support for SSIS scale out in HA environments, customers can now enable Always On for SSIS.

6. Features already rolled out in previous CTP versions:

– All new features added in SQL Server 2016 SP1, [link].

– New features added in SQL Server 2017 CTP 1.x, [link].

– New features added in SQL Server 2017 CTP 2.x, [link].
 

–> Videos on SQL Server 2017

Download & Install SQL Server 2017 & SSMS on Windows

Install SQL Server on Linux
 

–> References:

>> SQL Server 2017 official Page

>> Docs for SQL Server 2017


Microsoft released SQL Server 2017 CTP 2.0 – new features, enhancements & what’s new for Linux

April 19, 2017 3 comments

Today on 19th April 2017 Microsoft released the CTP 2.0 version of SQL Server 2017.

As announced earlier with the first CTP release, the new SQL Server 2017 will run both on Windows & Linux. Not only Linux, but it will be supported on Docker, and macOS (via Docker) too.
 

–> Download SQL Server 2017 bits:

To download the SQL Server 2017 you can Register and Download the Full version or Free evaluation version (180 days).

Or, directly download the ISO (~1.7 GB): SQLServerVnextCTP2.0-x64-ENU.iso
 

–> Check version and SQL build:

select @@version

Microsoft SQL Server vNext (CTP2.0) – 14.0.500.272 (X64)
Apr 13 2017 11:44:40
Copyright (C) 2017 Microsoft Corporation. All rights reserved.
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 14393: ) (Hypervisor)

 

–> New Features & Enhancements:

1. Support for Graph Data to model many-to-many relationship, with Nodes and Edges Table types.

2. Resumable online index rebuild, allows you to resume an online index rebuild operation from where it stopped after a failure. You can also Pause and later Resume an Online index rebuild operation.

3. IDENTITY_CACHE option for ALTER DATABASE SCOPED CONFIGURATION, to avoid gaps in the values of identity columns in case a server restarts unexpectedly or fails over to a secondary server.

4. Batch Mode Adaptive Join to improve plan quality.

5. Interleaved Execution for multi-statement T-SQL TVFs to improve plan quality.

6. Adaptive Query Processing, for automatically running database queries efficiently.

7. SQL Server Machine Learning Services (till now, SQL Server R Services), added support for the Python language with existing ‘R’.

8. Run the Python language in-database to scale and accelerate machine learning, predictive analytics and data science scripts

9. And for SQL Server with Linux:
    – Additional SQL Server Agent capabilities
    – Listener for Always On availability groups

… I’ll discuss about all these features in my coming posts.
 

–> Feature Selection Page:


 

–> You can check other features released in CTP 1.x here.
 

–> References:

>> SQL Server 2017 official Page

>> MSDN Blog announcement

>> Docs for SQL Server 2017


New built-in function CONCAT_WS() in SQL Server 2017

January 20, 2017 1 comment

 
In my previous posts I discussed new Functions introduced in SQL Server vNext (or 2018), like STRING_AGG(), TRIM(), TRANSLATE().
 

Here in this post I’ll discuss about one more new function i.e. CONCAT_WS(), here “_WS” means “With Separator”.

This is very similar to the existing CONCAT() function introduced back in SQL Server 2012, which concatenates a variable number of arguments or string values.

The difference is the new function CONCAT_WS() accepts a delimiter specified as the 1st argument, and thus there is no need to repeat the delimiter after very String value like in CONCAT() function.

Also the new CONCAT_WS() function takes care of NULL values and do not repeat the delimiter, which you can see in 2nd example below.
 

Syntax:

CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )

 

–> Example #1:

With CONCAT_WS() we will use the delimiter just once and it concatenates the names separated by ‘-‘, and do not repeat the hyphen where the middle name is NULL.

USE [AdventureWorks2014]
GO

SELECT TOP 10 
	CONCAT_WS(' - ', FirstName, MiddleName, LastName) as FullName, 
	FirstName, MiddleName, LastName
FROM [Person].[Person]

concat_ws-01
 

–> Example #2:

With CONCAT() the delimiter needs to be used after every argument, it concatenates the names separated by ‘-‘, do repeats the hyphen where the middle name is NULL.

SELECT TOP 10 
	CONCAT(FirstName, ' - ', MiddleName, ' - ', LastName) as FullName, 
	FirstName, MiddleName, LastName
FROM [Person].[Person]

concat_ws-02


Connect SQL Server on Linux with SSMS from a Windows machine

January 6, 2017 1 comment

 
In [previous post] we saw how to install & setup SQL Server on Linux. We used PuTTY to connect to the Linux Azure VM and query SQL Server there.

Here in this post we will try to connect to SQL Server on Linux via SSMS from a Windows machine.
 

So to connect remotely to SQL Server on an Azure VM, you have to configure an inbound rule on the network security group. The rule allows traffic on the port on which SQL Server listens (default is 1433).
 

–> Setup Inbound Rule:

1. You need to go to portal.azure.com and login with your credentials. On the Dashboard you will see your Linux VM as shown below:

linux-ubuntu-on-azure-07
 

2. Click on the Linux VM widget, it will open up and list out the Settings, click on Network Interfaces, and select the available Network Interface from the list.

install-sql-on-linux-09
 

3. Now click on the Network Security Group:

install-sql-on-linux-11
 

4. On the Network Security Group page, select the Inbound security rules, and click on the “+” button create new for SQL Server:

install-sql-on-linux-12
 

5. On the Add inbound security rule page, provide a name to this rule, select “MS SQL” under Service drop-down, and click OK:

install-sql-on-linux-13
 

–> Connect using SSMS:

6. Now from any Remote machine or even you PC which has SSMS installed and have internet connection, try connecting the SQL Server Linux Instance:

– Server Name: provide the IP address of the Linux VM (you can get it by opening the “Linux VM widget” and clicking on “Connect” button at top)

– Authentication: should be SQL Server Authentication

– Login: “SA”

– Password: apply the same password that you set it up while Configuring SQL Server on Linux.

install-sql-on-linux-14
 

7. On successful authentication you will see the Object Explorer showing the SQL instance that’s running on a Linux machine. On a glance you can’t make out any difference b/w a Linux SQL instance or a Windows SQL instance.

Thus I executed the SELECT @@version statement which shows the SQL Server version and on which Operating system its running.

You can even see the Database SQLdbOnLinux that we created by connecting from the PuTTY client.

install-sql-on-linux-15
 

That’s it for now, will write more about SQL on Linux on coming updates from Microsoft !!!

–> You can also watch this on YouTube: