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 »
SQL Server Query Execution Plans for beginners – Types and Options January 4, 2018 by Ayman Elnory When a DBA is working with the SQL Server, he/she might sometimes say that the execution plan is like a black hole. As a matter of fact, though, you have to look at execution plans as weather forecasts. In this article, I will be discussing various aspects of executions plans, types and common options. Read more »
How to plot a SQL Server 2017 graph database using SQL Server R January 3, 2018 by Sifiso W. 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. Read more »
SQL Server Query Store – Overview December 29, 2017 by Marko Zivkovic The SQL Server Query Store is a relatively new feature introduced in SQL Server 2016. It is basically a SQL Server “flight recorder” or “black box”, capturing a history of executed queries, query runtime execution statistics, execution plans etc. against a specific database. This information helps in identifying performance problems caused by query plan changes and troubleshooting by quickly finding performance differences, even after SQL Server restart or upgrade. All data that SQL Server Query Store capture are stored on disk. Read more »
What’s new in SQL Server Management Studio 17.4; Always On AG dashboard enhancements and more December 27, 2017 by Ahmad Yaseen As all other database engines that require a GUI too to interact with, Microsoft built SQL Server Management Studio as a tool that provides an environment to manage, configure, monitor and administrate the SQL Server infrastructure for different kind of instances, that are installed locally at your machine, on a remote server with Windows or Linux operating systems or hosted in the cloud such as SQL Azure or Azure Data warehouse. Recall that the SQL Server Management Studio consists of the Object Explorer, which is used to view and manage all objects within the SQL Server instance, the Template Explorer, that is used to deal with the scripting text files and the Solution Explorer, that is responsible for building the projects that contain list of T-SQL scripts in SQL Server. Read more »
What’s new in SQL Server Management Studio 17.4; SQL Vulnerability assessment and more December 26, 2017 by Samir Behara Microsoft released the latest version of the SQL Server Management Studio 17.4 a few weeks back. This release brings in exciting new features and bug fixes to the SSMS IDE for SQL Developers and DBAs. In this article, we will look at a new inbuilt tool for identifying security vulnerabilities in your database. We will also understand the improvements made to the XEvent Profiler and ShowPlan features. Read more »
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. Read more »
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 Read more »
SQL Server read-ahead mechanism; concept and performance gains December 21, 2017 by Ahmad Yaseen The user’s read requests in SQL Server are managed and controlled by the SQL Server Relational Engine, that is responsible for determining the most optimized access method, such as index scan or table scan, to retrieve the requested data. These read requests are also optimized internally by the SQL Server Storage Engine, the buffer manager components specifically, that is responsible for determining the general read pattern to be performed. Read more »
How to enable and disable the Identity Cache in SQL Server 2017 December 20, 2017 by Sifiso W. 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. Read more »
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 Read more »
How to create Excel reports based on SSAS information December 18, 2017 by Daniel Calbimonte Introduction In SSAS, when I offer Power BI, Reporting Services, PowerPivot or SharePoint to connect to SSAS, the business analysts look scared. On the other hand, if I talk about MS Excel, everybody seems so happy and comfortable with it. Excel is still the most popular spreadsheet in the world even when there are a lot of free spreadsheets like OpenOffice and LibreOffice to download, in the BI world, Excel is still the most popular. Read more »
