All about SQL Server spinlocks

August 23, 2017 by Nikola Dimitrijevic

As discussed in the article All about latches in SQL Server, spinlocks are also a special form of locks that SQL Server uses to protect data. Spinlocks are lightweight objects used by the SQL Server Operating System (SQLOS) to protect data structure access. To understand, properly, the difference between latches and spinlocks and why the spinlock is an important SQL Server’ object type, it is vital to understand the cycle of query execution in SQL Server

Multi-server Script to Find Orphaned Data Files using PowerShell

August 23, 2017 by Prashanth Jayaram

Having worked in busy dev-test environments, it isn’t uncommon to come across situations where someone detached a database from an SQL server, but forgot to reattach it, or drop it from the server. The next task we are then required to undertake, upon coming across such a situation, is to clean up the traces. This task can be complicated if the detached databases get deregistered from the SQL Server metadata catalog.

In-Memory OLTP Enhancements in SQL Server 2016

August 22, 2017 by Ahmad Yaseen

SQL Server In-Memory OLTP, also known as Hekaton when it was introduced in SQL Server 2014, provides us with the ability to move specific database tables and suitable stored procedures into memory and compile the stored procedures into native x86 code. As a result, you can easily query these database objects directly from memory with the best performance and the least possible data access latency. In addition to that, the SQL Server Engine will no longer use the old latching and locking mechanism to control the data access concurrency. Instead, a high performance row versioning mechanism will be used to control the concurrency. This optimistic concurrency mechanism is 5 times to 20 times faster than the normal disk-based processing, due to reading the data from the memory directly.

What is new in SSMS 17; PowerShell and DAX

August 21, 2017 by Daniel Calbimonte

Introduction

In earlier versions, SSMS was included in the SQL Server installer. Now it is a tool that is installed separately.

In this new article, we will emphasize PowerShell and DAX. PowerShell is handled in a different way than it was in SQL Server 2016. We will give you some tips to handle these differences. In the second part of the article, we will talk about DAX. If you do not have experience with DAX, we will give you an introduction to Multidimensional, Tabular databases and you will be able to understand the new features and run your first DAX query in the new SSMS 17.

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.

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.

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

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.

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.

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.

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

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.

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.

SQL replace: How to replace ASCII special characters in SQL Server

August 7, 2017 by Sifiso 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 the user-defined function that could be applied to successfully remove such characters.

Using SSIS ForEach Loop containers to process files in Date Order

August 3, 2017 by Sifiso 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.

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.

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.

How to Configure TDE database with AlwaysOn using the Azure Key Vault in SQL 2016

July 28, 2017 by Prashanth Jayaram

One of the recent tasks I undertook on configuring Transparent Data encryption (TDE) using asymmetric key protection with Azure Key Vault with Always On opened a different dimension on securing data for me. Even though it seems slightly complex, if you have the key details, the steps are in fact, really straight forward.

$C:\Users\admin\Desktop\Any\Images\NOT_IN.png$

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.

$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.

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.

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).

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 the 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.

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.

How to simplify SQL Server Database Object usage with Synonyms

July 20, 2017 by Ahmad Yaseen

The concept of SQL Server Synonyms was introduced the first time in SQL Server 2005 as an alias name that references an existing database object, replacing its fully qualified name. In this way, it makes the database object more portable and provides more flexibility for the clients to reach and maintain it. You can imagine Synonyms as a layer of abstraction that provides us with an easy way to connect to and manage the database objects without the need to identify the real name and location for these objects.