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. Read more »
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. Read more »
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. Read more »
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 »