Languages and coding

How to create charts from SQL Server data using PowerShell

February 8, 2018 by Jefferson Elias

Introduction

Intended audience

This document is intended for application developers and database administrators who are willing to learn how to generate charts from data stored in Microsoft SQL Server using PowerShell.

Context

In previous article entitled Overview of PowerShell Charting capabilities we’ve seen some components of an assembly called System.Windows.Forms.DataVisualization assembly that is part of the .NET Framework and how to load it inside PowerShell.

Now, it’s time to use what we’ve learned so far.

Overview of PowerShell charting capabilities

February 6, 2018 by Jefferson Elias

Introduction

Intended audience

This document is intended for application developers and database administrators who are willing to learn how to generate charts from data stored in Microsoft SQL Server using PowerShell.

Introduction to mssql-cli, a new interactive command line tool for SQL Server

January 31, 2018 by Bojan Petrovic

Microsoft recently released a new and interactive command-line query tool for SQL Server with modern features such as auto-completion and syntax highlighting. It’s called mssql-cli and this fully open source tool works on cross-platform under the BSD-3 license.

Migrating SSRS content with PowerShell

January 29, 2018 by Craig Porteous

With a distinct lack of up-to-date, fully featured or built-in options to get Reporting Services content cleanly from A to B, it can often be a challenging task maintaining proper Development and QA environments or even moving reports from a SharePoint integrated installation to a native mode one, and vice versa.

I want to explore the two most efficient methods of bulk-migrating Reporting Services content & also explore other options I’ve used over the years and those that have come and gone.

How to implement array-like functionality in SQL Server

January 16, 2018 by Daniel Calbimonte

Introduction

I was training some Oracle DBAs in T-SQL and they asked me how to create arrays in SQL Server.

I told them that there were no arrays in SQL Server like the ones that we have in Oracle (varray). They were disappointed and asked me how was this problem handled.

Some developers asked me the same thing. Where are the arrays in SQL Server?

The importance of Python in SQL Server Administration

January 8, 2018 by Prashanth Jayaram

Some of my previous articles on Python provided insight of the basics and the usage of Python in SQL Server 2017.

This article is an effort to collect all the missing pieces and try to showcase the importance of using Python programming in SQL Server.

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.

6 methods to write PowerShell output to a SQL Server table

December 25, 2017 by Prashanth Jayaram

PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework and uses Object Models such as COM, ADSI, ADO, and WMI. PowerShell has replaced the traditional way of scripting that used many legacy scripting practices to monitor SQL instances.

I’ve been asked on several occasions about how to store the output of PowerShell WMI data into the SQL table. The question comes up so frequently that I decided to write this article.

When sending data within a system (such as a PowerShell object to a cmdlet), the process is straightforward. However, with non-native data interchange (for instance, WMI to SQL), the process can potentially get complicated. Due to this, many purists suggest sticking to simple interchange formats, such as CSV, JSON or in some cases, XML.

How to use sargable expressions in T-SQL queries; performance advantages and examples

December 22, 2017 by Esat Erkec

The challenge

One of the main tasks of a SQL Server database administrator is performance tuning. Sometimes, though, coders or developers don’t always prioritize database performance or query optimization. Here is a typical scenario

Imagine that developers create a new table and then insert some records in a test environment and test their queries to retrieve data from it
The query executed successfully and does not exhibit any symptoms of performance problems
The developer team release this table and query into production
One day you take a telephone from your colleague and he says my report is very slow
Bingo! In production, this table contains a lot of records and this is resulting in performance bottlenecks when querying it

How to return data use, index compression, and row information with PowerShell

December 6, 2017 by Timothy Smith

Background

We recently inherited a database environment where we’re facing significant data growth with limits on the sizes we can allow our databases to grow. Since we maintain multiple development, QA and production environments and these environments must be sized appropriately. We set a few standards about tables that exceed certain sizes – rows, data or both, or have certain growth patterns and our standards force compression at thresholds we set for our different environments (including using page, row, or clustered columnstore index compression) We’re looking for how we can get information about data and compression in tables and options we have so that we can quickly determine candidates that don’t currently match our best practices design we’ve set for our environments.

What to choose when assigning values to SQL Server variables: SET vs SELECT T-SQL statements

November 29, 2017 by Ahmad Yaseen

SQL Server provides us with two methods in T-SQL to assign a value to a previously created local SQL variable. The first method is the SET statement, the ANSI standard statement that is commonly used for variable value assignment. The second statement is the SELECT statement. In addition to its main usage to form the logic that is used to retrieve data from a database table or multiple tables in SQL Server, the SELECT statement can be used also to assign a value to a previously created local variable directly or from a variable, view or table.

R script word-cloud in SQL Server Report Builder

November 28, 2017 by Esat Erkec

What is R

R is a very popular data programing language. R is especially used in data analysis, statistics calculations, predictions, data mining and machine learning. R is used by data scientist, economist, genetic scientists, and statisticians. R has very wide usage in real life. Healthcare, finance, marketing, and manufacturing are some of them.

Understanding cursors and replacing them with JOINs in SQL Server

November 24, 2017 by Ben Richardson

Relational database management systems including SQL Server are very good at processing data in sets.

However, if you want to process data on row-by-row basis rather than in sets, cursors are your only choice. Unfortunately, cursors are extremely slow and so where possible should be replaced with JOINS.

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.

An introduction to the bcp Utility (bulk copy program) in SQL Server

November 9, 2017 by Daniel Calbimonte

Introduction

The Bulk copy program aka bcp is the console application used to export and import data from text files to SQL Server or vice versa.

It is very popular because it is fast and easy to download.

An overview of Python vs PowerShell for SQL Server Database Administration

November 2, 2017 by Prashanth Jayaram

Today, Microsoft claims that Linux runs like a First-Class citizen on Azure, .NET Core has been open-sourced, and has been ported over to Linux, taking PowerShell along. PowerShell runs really well on Ubuntu, CentOS, RedHat Linux, and even Mac OS X. There are Alpha builds available for a few other platforms as well, all available for exploitation under the MIT License on GitHub. “Manage anything, anywhere” is what Microsoft is offering to its customers. Keeping with that, we now have:

ETL optimization using SQL TRY Functions

October 24, 2017 by Sifiso Ndlovu

Introduction

An enterprise data warehouse ETL solution typically includes, amongst other steps, a data transformation step that converts source data from one data type into another. It is during this step that type conversion errors may occur and depending on the type of exception handling techniques implemented in the ETL solution (or lack thereof), frustration may occur for both ETL developers and DBAs when trying to identify and resolve type conversion errors. In this article we take a look at a trio of SQL TRY built-in functions that were introduced in SQL Server 2012, namely, TRY_PARSE, TRY_CAST, and TRY_CONVERT and how they could be utilized to reduce type conversion errors in ETL solutions and thereby saving developers needless troubleshooting exercise.

Why would a SQL Server DBA be interested in Python?

October 23, 2017 by Prashanth Jayaram

If we follow blogs and publications on the technological advancement with respect to SQL, we notice the increase in the number of references to Python, of late. Often, that makes us think:

Why so much emphasis on Python these days?
Isn’t knowing PowerShell scripting sufficient for the automation requirements of today?
Is it the time DBAs started learning a programming language such as Python in order to handle their day-to-day tasks more efficiently?
Why do so many job postings these days include “knowledge of scripting” as a requirement?
Is all of this happening because the paradigm is shifting? Can’t the current Microsoft-specific languages such as PowerShell handle the shift?

Working with the SQL Server command line (sqlcmd)

October 18, 2017 by Daniel Calbimonte

Introduction

Sqlcmd allows executing queries, T-SQL sentences and SQL Server scripts using the command line.

In the previous article How to work with the command line and Azure to automate tasks, we worked with the sqlcmd in Azure.

In this new chapter, we will show the following examples in a local SQL Server using sqlcmd:

Monitoring SQL Server database status changes using T-SQL and PowerShell scripts

October 17, 2017 by Prashanth Jayaram

Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays.

How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL

October 2, 2017 by Prashanth Jayaram

In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more.

How to create a linked server to an Azure SQL database

September 27, 2017 by Marko Zivkovic

Linked servers allow to access data from another SQL Server or another data source (e.g. Excel) by using SQL Server Management Studio (SSMS) or Transact-SQL.

This article will explain how to create and configure a linked server to retrieve data from an Azure SQL database. Also, we will explain how to solve some common problems/issues during the process of creating a linked server to an Azure SQL database.

T-SQL commands performance comparison – NOT IN vs SQL NOT EXISTS vs SQL LEFT JOIN vs SQL EXCEPT

September 22, 2017 by Ahmad Yaseen

This articles gives you a performance comparison for NOT IN, SQL Not Exists, SQL LEFT JOIN and SQL EXCEPT.

How to connect to (and query) Power BI and Azure using PowerShell

September 13, 2017 by Craig Porteous

Power BI “as a whole” is a bit of a black box. If you’re like me and used to using SQL Server & its components; SSRS, SSAS etc. you have access to installation directories, Event logs, trace logs, error logs, chocolate logs? You can see full instances & their contents in one go, whether that be databases, reports or cubes. It gives you the control over & responsibility for performance & maintenance. Read more »