An introduction to sp_MSforeachtable; run commands iteratively through all tables in a database August 18, 2017 by Vitor Montalvão Introduction There have always been some undocumented objects in SQL Server that are used internally by Microsoft, but they can be used by anybody that have access to it. One of those objects is a stored procedure called sp_MSforeachtable. sp_MSforeachtable is a stored procedure that is mostly used to apply a T-SQL command to every table, iteratively, that exists in the current database. Read more »
How to configure a Linked Server using the ODBC driver August 17, 2017 by Marko Zivkovic Microsoft Open Database Connectivity (ODBC) is an application programming interface (API) designed to access data from a different of database management systems (DBMS). ODBC is designed for relational data stores. Read more »
How to monitor object space growth in SQL Server August 16, 2017 by Jefferson Elias Introduction There are many situations in a DBA’s life that lead him or her to monitor space growth over time in SQL Server. In short, we often want to know which tables are growing the most and how much they grow. For that reason, it’s preferable to develop a solution that will help us in that particular task and this is exactly what this article is about. The main components will include Read more »
Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL August 15, 2017 by Prashanth Jayaram Introduction Database availability is critical to every enterprise and conversely, unavailability directly can create a severe negative impact to the business in today’s world. As database administrators, it is important that we ensure we take all possible steps to minimize data loss. While it is naïve to think that our databases are invincible because of all such precautions, we can always aim to bring the databases back into operation as quickly as possible by meeting the RPO and RTO. This way, the business is unaffected, and we also meet our SLAs. Read more »
Analysis Services (SSAS) Tabular Models – Attributes and Measures August 14, 2017 by Thomas LeBlanc In this article, a demonstration of the tabular model will try to related multidimensional cube design to the path forward Microsoft is giving used s with new versions of Analysis Services. Even though the descriptive names are changing, the tabular model still accomplishes the centralized business rules multidimensional cubes gave the business intelligence world. This is enforced by speed obtained from in-memory technology and simplification of the interface for the masses. Read more »
How to create geographic maps in Power BI using ArcGIS August 11, 2017 by Andrea Martorana Tusa Introduction This is the fourth article of a series dedicated to discovering geographic map tools in Power BI. At the link below you can find out references to the previous article and the project’s goal: Read more »
All about Latches in SQL Server August 10, 2017 by Nikola Dimitrijevic SQL Server locks, discussed in the article All about locking in SQL Server, which is applied on data for the duration of the logical operation to preserve logical transaction consistency. SQL Server latches, however, are a special type of low-level system locks which are held as long as the physical operation lasts on the memory page in order to protect memory consistency Read more »
SQL Server system databases – the model database August 9, 2017 by Vitor Montalvão Introduction This is my fourth article about SQL Server system databases. In previous articles of the series, I wrote about the tempdb database, the master database and the msdb database. Read more »
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. Read more »
How to replace ASCII special characters in SQL Server August 7, 2017 by Sifiso W. Ndlovu One of the important steps in an ETL process involves the transformation of source data. This could involve looking up foreign keys, converting values from one data type into another, or simply conducting data clean-ups by removing trailing and leading spaces. One aspect of transforming source data that could get complicated relates to the removal of ASCII special characters such as new line characters and the horizontal tab. In this article we take a look at some of the issues you are likely to encounter when cleaning up source data that contains ASCII special characters and we also look at user-defined function that could be applied to successfully remove such characters. Read more »
Using SSIS ForEach Loop containers to process files in Date Order August 3, 2017 by Sifiso W. Ndlovu One positive thing to come out of my recent project that involved rewriting one of the Data Marts from our Data Warehouse environment was a confirmation of my suspicions with regards to the behavior of SQL Server Integration Services’ (SSIS) ForEach Loop Container. You see, I have long suspected that the ForEach File Enumerator type in SSIS’s ForEach Loop Container does not process time stamped text files in an order that could be deemed correct to the human eye. For instance, Figure 1 shows a list of text files containing data relating to Marital Statuses of FIFA 2016 Ballon D’Or nominees. Read more »
How to automate Azure Active Directory (AAD) tasks using the Cloud Shell August 1, 2017 by Daniel Calbimonte Introduction In the article How to create an Azure SQL Database using the Cloud Shell, we learned how to work with the Cloud Shell. In this new article, we will show how to automate Azure Active Directory tasks (AAD) using the Cloud Shell. We will work with loops, files and variables to automate tasks. Read more »
Working with Azure Active Directory and Azure SQL Database July 31, 2017 by Daniel Calbimonte Introduction By default, when someone creates an Azure SQL Server, an Administrator with SQL Authentication is created by default. However, for administrative purposes, it is a best practice to use Azure Active Directory. Read more »
How to Configure a TDE database with AlwaysOn using the Azure Key Vault in SQL Server 2016 July 28, 2017 by Prashanth Jayaram One of the recent tasks that I have undertaken seemed rather like an experiment to me. I set out to configure Transparent Data encryption (TDE) using asymmetric key protection with Azure Key Vault with AlwaysOn. That, to me, opened a whole different dimension on data security. Read more »
SQL Server universal comparison quantified predicates (ANY, ALL, SOME) July 27, 2017 by Nikhilesh Patel SQL server provides us with comparison operators to modify subqueries. This article will start with the definition of universal quantification, with a quick brush up with real life logical examples. Read more »
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. Read more »
How to query Excel data using SQL Server linked servers July 26, 2017 by Marko Zivkovic Linked servers allow SQL Server access to data from a remote data source. A remote data source can be another SQL Server instance or other data sources such MySQL, Access databases, Oracle, Excel workbooks, text files etc. Read more »
Refreshing SQL Server Containers automatically with Watchtower July 25, 2017 by Andrew Pruski Introduction I’ve been working with SQL Server in containers for a while now and one of the challenges that I have is ensuring that the databases within them are kept at the correct version. This is important because you don’t want your development teams testing against databases that have an out of date schema (or reference data). Read more »
How to email SQL query results to your smartphone using the sp_send_dbmail stored procedure July 24, 2017 by Daniel Calbimonte Introduction Sometimes there are tasks that require too much time to finish during business hours and we need to leave the office with some still pending like migration of data, backups or long running queries. In such cases we would still want to receive an email in our smartphones to make sure that the job was completed successfully. Read more »
Performance tuning for Azure SQL Databases July 21, 2017 by Kaloyan Kosev With the latest versions of Azure SQL database, Microsoft has introduced a number of new mechanisms to help users and administrators better optimize their workload. Automatic index management and Adaptive query processing provide us with the possibility to rely on the built-in intelligence mechanism that can automatically tune and improve the performance of our workload. Read more »