SQL Server 2017

Replace bridge tables in a Data Warehouse with SQL Server 2017 graph database

March 8, 2018 by Sifiso Ndlovu

Just like in Santa’s Bag of Goodies, every release of SQL Server often has something for everyone – be it enhancements to DMVs for the DBAs, new functions for T-SQL developers or new SSIS control tasks for ETL developers. Likewise, the ability to effectively support many-to-many relationships type in SQL Graph has ensured that there is indeed something in it for the data warehouse developers in SQL Server 2017. In this article, we take you through the challenges of modelling many-to-many relationships in relational data warehouse environments and later demonstrate how data warehouse teams can take advantage of the many-to-many relationship feature in SQL Server 2017 Graph Database to effectively model and support their data warehouse solutions.

Impact of CLR Strict Security configuration setting in SQL Server 2017

February 13, 2018 by Sifiso Ndlovu

Every seasoned SQL Server developer will tell you that no matter how hard you try, there are just operations in SQL Server better implemented elsewhere than relying on native Transact-SQL language (T-SQL). Operations such as performing complex calculations, implementing regular expression checks and accessing external web service applications can easily lead to your SQL Server instance incurring significant performance overhead. Thankfully, through its common language runtime (CLR) feature, SQL Server provides developers with a platform to address some of the inconveniences of native T-SQL by supporting an import of assembly files produced from projects written in. Net programming languages (i.e. C#, VB.NET). I have personally found CLR to be very useful when it comes to splitting string characters into multiple delimited lines.

The history of SQL Server – the evolution of SQL Server features

February 2, 2018 by Daniel Calbimonte

Introduction

This article will explain the main features in SQL Server 2017, 2016, 2015, 2014, 2012, 2008, 2005, 2000, 7, 6.5, 6.0, 4.2, 1.1 and 1.0.

In the past, the first SQL Server versions supported OS/2 (an operative system created by Microsoft and IBM) and Windows.

Now, the new versions of SQL Server (vNext and SQL Server 2017) can be installed in Linux. 15 years ago, it was impossible to think that. Linux and Microsoft were just like oil in water and now, Microsoft loves Linux.

Also, we now enjoy full integration with Azure, Tabular Databases, SSIS, SSAS and more. In this article, we will talk about all these changes and improvements.

Top 8 new (or enhanced) SQL Server 2017 DMVs and DMFs for DBAs

January 17, 2018 by Prashanth Jayaram

Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.

SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.

How to plot a SQL Server 2017 graph database using PowerBI

January 9, 2018 by Sifiso Ndlovu

In the article How to plot a SQL Server 2017 graph database using SQL Server R, I highlighted the lack of built-in graph data visualisation as one major limitation of the SQL Server 2017 graph database feature. In the same article, I went on to suggest making use of SQL Server R as one workaround that could be utilised in order to successfully plot and visualise diagrams out of SQL Server 2017 graph database objects. However, whilst 3^rd party graph database vendors such as Neo4j provide an interactive and hyperlinked graph diagrams that allows you to – amongst other things – easily drilldown and identify node-relationships as indicated in Figure 1, the graph plotted using SQL Server R is not very interactive in fact it is simply a static image file as shown in Figure 2.

Get more out of Python on SQL Server 2017

January 5, 2018 by Gerald Britton

Introduction

One of the new features announced with SQL Server 2017 is support for the Python language. This is big! In SQL Server 2016, Microsoft announced support for the R language – an open source language ideally suited for statistical analysis and machine learning (ML). Recognizing that many data scientists use Python with ML libraries, the easy-to-learn-hard-to-forget language has now been added to the SQL Server ML suite.

There’s a big difference between R and Python though: R is a domain-specific language while Python is general purpose. That means that the full power of Python is available within SQL Server. This article leaves ML aside for the moment and explores a few of the other possibilities.

How to plot a SQL Server 2017 graph database using SQL Server R

January 3, 2018 by Sifiso Ndlovu

A few years ago, one common business case I came across in my professional career that required modelling of data into a many-to-many entity relationship type was the representation of a consultants and their projects. Such a business case became a many-to-many entity relationship type because whilst each project can be undertaken by several consultants, consultants can in turn be involved in many different projects. When it came to storing such data in a relational database engine, it meant that we had to make use of bridging tables and also make use of several self-joins to successfully query the data.

How to enable and disable the Identity Cache in SQL Server 2017

December 20, 2017 by Sifiso Ndlovu

Every data warehouse developer is likely to appreciate the significance of having surrogate keys as part of derived fields in your facts and dimension tables. Surrogate keys make it easy to define constraints, create and maintain indexes, as well as define relationships between tables. This is where the Identity property in SQL Server becomes very useful because it allows us to automatically generate and increment our surrogate key values in data warehouse tables. Unfortunately, the generating and incrementing of surrogate keys in versions of SQL Server prior to SQL Server 2017 was at times challenging and inconsistent by causing huge gaps between identity values. In this article, we take a look at one improvement made in SQL Server 2017 to reduce the creation of gaps between identity values.

How to implement a graph database in SQL Server 2017

December 19, 2017 by Esat Erkec

Introduction

Graph database

A graph database is a type of database whose concept is based on nodes and edges.

Graph databases are based on graph theory (a graph is a diagram of points and lines connected to the points). Nodes represent data or entity and edges represent connections between nodes. Edges own properties that can be related to nodes. This capability allows us to show more complex and deep interactions between our data. Now, to explain this interaction we will show it in a simple diagram

How to configure SQL Server 2017 on Linux with mssql-conf and other available tools

December 13, 2017 by Prashanth Jayaram

Configuration of any system plays a vital role in its working efficiently. However, configuring a system needs not necessarily be a daunting task. There are several tools that help with the process.

SQL Server on Linux provides a wide range of options for configuration, management and administration.

mssql-conf
Transact-SQL
SQL Server Management Studio
PowerShell

An introduction to a SQL Server 2017 graph database

December 4, 2017 by Prashanth Jayaram

The graph database is a critically important new technology for data professionals. As a database technologist always keen to know and understand the latest innovations happening around the cutting edge or next-generation technologies, and after working with traditional relational database systems and NoSQL databases, I feel that the graph database has a significant role to play in the growth of an organization. Not only are traditional database systems generally inefficient in displaying complex hierarchical data, but even NoSQL lags a little. We usually see a degradation in performance with the number of levels of relationship and database size. Also, depending on the relationship, the number of joins may increase as well.

Data Interpolation and Transformation using Python in SQL Server 2017

November 21, 2017 by Prashanth Jayaram

As a continuation to my previous article, How to use Python in SQL Server 2017 to obtain advanced data analytics, a little bit of curiosity about Deep Learning with Python integration in SQL Server led me to write this latest article.

With Python running within SQL Server, you can bring the existing data and the code together. Data is accessible directly, so there’s no need to extract query data sets, moving data from storage to the application. It’s a useful approach, especially considering issues of data sovereignty and compliance, since the code runs within the SQL Server security boundaries, triggered by a single call from T-SQL stored procedures.

How to use BULK INSERT to import data locally and in Azure

November 16, 2017 by Daniel Calbimonte

Introduction

BULK INSERT is a popular method to import data from a local file to SQL Server. This feature is supported by the moment in SQL Server on-premises.

However, there is a new feature that is supported only in SQL Server 2017 on-premises. This feature allows importing data from a file stored in an Azure storage account to SQL Server on-premises using BULK INSERT. This feature will be supported in Azure SQL versions in the future.

In this article, we will show two examples. The first example will show how to use the traditional BULK INSERT statement from a local CSV file to Azure and the second example will show how to import data from a CSV file stored in Azure to SQL Server on-premises.

The evolution of SQL Server towards Digital Transformation challenges

October 9, 2017 by Prashanth Jayaram

In a data-driven world, where every second see a transfer of billions of pieces of data, enterprises are focused on making the access to data fluid and capable of being accessed on demand on a myriad of devices. Leveraging these capabilities to deliver better business results is now the prime focus. Information Technology is no more what it has been. What the web saw during the dot-com boom is what enterprise data is seeing now. Social, mobile, analytics, cloud, Big Data, Internet of Things… They’ve been enabling organizations to scale.

A Quick start Guide to Managing SQL Server 2017 on CentOS/RHEL Using the SSH Protocol

August 8, 2017 by Prashanth Jayaram

In one of my previous articles Installation of SQL Server vNext CTP on Linux Distribution CentOS 7, I spoke about the installation of SQL Server 2017 on a Linux computer. Let us now look at another way to showcase that a SQL Server installation and configuration can also be managed using the Secure Shell (SSH) protocol using PuTTY. We shall look at the important details, such as, how to get PuTTY, SQL Server, how to install/uninstall SQL 2017 CTP2.1, Upgrade to SQL 2017 RC1, and install/remove SQL Tools.

$C:\Users\craig.CRAIGFLIX\AppData\Local\Microsoft\Windows\INetCache\Content.Word\SSMS_17.png$

What’s New in Reporting Services (SSRS) 2017

July 26, 2017 by Craig Porteous

To further the discussion related to Mohamed’s post on Reporting Services 2016 What’s new in SQL Server 2016 Reporting Services (SSRS), I wanted to cover the new features and functionality we see in Reporting Services 2017 as RC1 was released last week SQL Server 2017 Reporting Services Release Candidate now available, separately from SQL Server 2017 which reached RC1 a few days prior.

Overview of Resumable Indexes in SQL Server 2017

June 26, 2017 by Prashanth Jayaram

Managing indexes is a critical component of database maintenance but we often don’t think about the indicators behind the index maintenance operations. SQL Server 2017 (CTP 2.0) introduces a very useful index feature, to mitigate the administration overhead of index maintenance which we’ll review and discuss in this article.

How to use Python in SQL Server 2017 to obtain advanced data analytics

June 20, 2017 by Prashanth Jayaram

On the 19^th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last few years, made great strides in accelerating the pace of innovation to enable businesses to meet the demands of a dynamic marketplace and harness the incredible power of data—more securely and faster than ever before.

Top SQL string functions in SQL Server 2017

June 8, 2017 by Prashanth Jayaram

SQL Server 2017 has been in the talk for its many features that simplify a developer’s life. With the previous versions, developers had to write T-SQL, or user-defined functions using temporary tables, and build complex logic, just for string manipulation. In almost every database I work with, I see many user-defined functions for string manipulation and string aggregation.

How to install SQL Server on Ubuntu

May 18, 2017 by Stefan Veselinovic

Introduction

With an increasing number of new servers running on Linux, and even a third of Microsoft’s Azure virtual machines running Linux, they have made a choice to bring SQL Server to Linux in order to increase their market share and attract new customers.

Installation of SQL Server vNext CTP on Linux Distribution CentOS 7

May 10, 2017 by Prashanth Jayaram

It was a paradigm shift in December 2016, when Microsoft made their SQL Server database available for Linux; it was the first time in history that Microsoft ever designed SQL Server to run on a non-Windows operating system. SQL Server vNext was released for public preview so the user community could test and deploy SQL Server on a Linux operating system. Microsoft took a major step in diversifying the database technology into the non-windows platform for the first time.

Adaptive Query Processing in SQL Server 2017

April 28, 2017 by Musab Umair

The SQL Server Query Processing Engine is the most complex and sophisticated part of SQL Server. This engine has many responsibilities, but one of the most important parts is to design the query execution plan and execute it in a best and most efficient possible manner.

SQL Server vNext – Does Microsoft love Linux?

April 21, 2017 by Daniel Calbimonte

Introduction

In the 90s and at the beginning of the 21 century, Linux and Windows were decidedly working at cross purposes

However, starting on 2009, Microsoft started to contribute with code in the Linux Kernel. On 2013, Azure supported Virtual Machines in Ubuntu and later came the support to .NET in Linux, Visual Studio for Linux, PowerShell in Linux, support to monitor Linux server to monitor in SCOM (System Center Operation Manager) and now, the support for SQL Server that is on the previous version when this article was published and it is planned to be released this year (2017). Microsoft is a Platinum member of the Linux Foundation now.

How to connect to a remote SQL Server

February 18, 2016 by Marko Zivkovic

In this article, we will explain step by step how to connect remotely to a SQL Server Express instance. Remote access is the ability to get access to a SQL Server from a remote distance in order to manipulate data which are located on that SQL Server.