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. Read more »
A high level look at SQL Server disaster recovery planning February 5, 2018 by Kandi Humpf The methodology or paradigm, related to SQL Server disaster recovery has definitely changed in recent years. In the past, database administrators were just hopeful if they had any backup at all. Nowadays software is more complicated, and there are more integrated moving parts involved. Read more »
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. Read more »
How to install and configure SQL Operations Studio for Windows February 1, 2018 by Marko Zivkovic SQL Operations Studio is free, lightweight database development and operations cross-platform tool for private and commercial usage, that can be installed on Windows, macOS, and Linux for SQL Server, Azure SQL Database and Azure SQL Data Warehouse. SQL Operations Studio is built to simplify work of database developers, database administrators, and system administrators. SQL Operations Studio boosts your productivity with smart code snippets, keyword completion, IntelliSense, source control integration, the ability to view and save results in CSV, Excel, JSON format, and the capability to organize and manage favorite database connections, etc. The first version of SQL Operations Studio was released in November 2017. Read more »
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. Read more »
How to develop a K-Means model on Azure Machine Learning Studio January 30, 2018 by Esat Erkec In this article, we will discuss the k-means algorithm and how can we develop a k-means model on Azure Machine Learning Studio. Machine learning is an area of artificial intelligence that helps us develop relationships between data and predict the future. There are many secrets that are hidden in the data. To discover these secrets, we need Machine Learning algorithms. Machine learning plays the key role in leveraging existing data to exploit business opportunities. Read more »
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. Read more »
OpenQueryStore for SQL Server – Installation and configuration January 24, 2018 by Marko Zivkovic The OpenQueryStore is an Open source implementation of the popular Query store functionality introduced in SQL Server 2016 CTP2. The OpenQueryStore was first introduced in June 2017. Its main contributors are William Durkin and Enrico van de Laar The OpenQueryStore collects query runtime execution statistics, wait statistics information against a specific database. This information helps identifying performance problems and troubleshooting by quickly finding performance differences. The main difference between the OpenQueryStore and the Query store feature is that the OpenQueryStore supports SQL Server 2005 to SQL Server 2014 while Query Store supports only SQL Server 2016 and higher. Also, the OpenQueryStore is open-source which means that the OpenQueryStore code can be changed whenever you like. Read more »
Ola Hallengren’s SQL Server Maintenance Solution – Index and statistics maintenance January 23, 2018 by Robert Seles This is the third article in Ola Hallengren’s SQL Server Maintenance Solution series. The article will cover the IndexOptimize stored procedure in more detail, along with the index optimization jobs created by Ola Hallengren’s scripts. Read more »
How to move SQL database files (MDF and LDF) to another location January 22, 2018 by Marko Radakovic How often you got to the point that for any reason you don’t have enough space on the specific drive to host a database? In case of database development or other tasks outside the production environment, this should not be a problem as a database can be eventually re-created, restored from a backup and set to be hosted on another location. By default, SQL Server stores database files in its installation folder, specifically in the Data folder: Read more »
How to quickly generate a large number of dimension tables for reporting applications January 19, 2018 by Ed Pollack Description When building reporting structures, we typically have the need to build fact and dimension tables to support the apps that will consume this data. Sometimes we need to generate large numbers of dimension tables to support application needs, such as in Tableau, Entity Framework, or Power BI. Creating this schema by hand is time-consuming and error-prone. Automating it can be a way to improve predictability, maintainability, and save a ton of time in the process! Read more »
Understanding SQL Server query plan cache January 18, 2018 by Ben Richardson Whenever a query is run for the first time in SQL Server, it is compiled and a query plan is generated for the query. Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn’t need to create another query plan; rather it uses the cached query plan which improved database performance. The duration that a query plan stays in the plan cache depends upon how often a query is executed. Query plans that are used more often, stay in the query plan cache for longer durations, and vice-versa. Read more »
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. Read more »
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? Read more »
Using production data for testing in a post GDPR world January 15, 2018 by Brian Lockwood To SQL Server DBAs who are the shepherds of data in organizations, key GDPR questions, in general, center around whether data will need to be treated differently, safeguarded more etc. and specifically, as it relates to allowing production data to be used in testing. That will be the focus of this article as we’ll work our way through the details of this regulation as well as various authoritative articles on the subject, to address this key question. Then we’ll look to ways and means to potentially ameliorate our findings to provide alternatives and workarounds if possible. Read more »
How to integrate SQL Server and Azure Machine Learning January 11, 2018 by Esat Erkec Good, clean and reliable data is important for every company, because well-analyzed data will open new possibilities to success. When we look at successful companies, we see that they’ve analyzed customers, sales, and finance data very well. At this point, a game changer enters: machine learning. Machine learning Machine learning is a type of artificial intelligence which analyzes data in order to predict future and discover hidden patterns. The output helps us make decisions. Machine learning is widely used in finance, healthcare and marketing. Read more »
Ola Hallengren’s SQL Server Maintenance Solution – Database integrity check January 10, 2018 by Robert Seles This is the second article in Ola Hallengren’s SQL Server Maintenance Solution series. It will cover the jobs for database integrity, backup history cleanup and job history cleanup. The installation of Ola Hallengren’s Maintenance Solution is covered in the first article in the series: Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solution Read more »
How to plot a SQL Server 2017 graph database using PowerBI January 9, 2018 by Sifiso W. 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 3rd 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. Read more »
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. Why would a SQL Server DBA be interested in Python An overview of Python vs PowerShell for SQL Server Database Administration Data Interpolation and Transformation using 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. Read more »
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. Read more »