Dynamic Partitioning in Azure Analysis Services (tabular)

August 6, 2018 by Istvan Martinka

Objective

The real-life requirement

Disclaimer: I assume dear Reader, that you are more than familiar with the general concept of partitioning and star schema modeling. The intended audience is people who used to be called BI developers in the past (with a good amount of experience), but they have all sorts of different titles nowadays that I can’t keep up with… I won’t provide a full Visual Studio solution that you can download and just run without any changes or configuration, but I will give you code can be used after parameterizing according to your own environment.

Top SQL Server bloggers of 2018

August 1, 2018 by SQLShack

We made a collection of the most popular SQL Server bloggers, including a link to each individual blog. The ranking is based on Alexa global score.

If your blog is on this list, you can display the ‘Top blogger’ badge on your blog. Please see the bottom of the page for the instructions on how to display the badge on your website

SQL interview questions on database backups, restores and recovery – Part I

July 30, 2018 by Prashanth Jayaram

So far, we’ve discussed a lot about database backup-and-restore process. The backup database command is an online database copy of the SQL Server database and restore database command gives an option to test the consistency and integrity of the backup file.

As we all know, the backup database command bound with many database options. Indeed, it facilitates the execution of specific backup database command that meets the business requirement.

Is SQL Server Always Encrypted, for sensitive data encryption, right for your environment

July 27, 2018 by Aamir Syed

So, your manager wants you to figure out how to encrypt sensitive Data? Well, Microsoft has introduced a fairly easy way to configure feature called Always Encrypted. Read more »

Interview questions and answers about data science, data understanding and preparation

July 27, 2018 by Dejan Sarka

Q1: In the data science terminology, how do you call the data that you analyze?

In data science, you analyze datasets. Datasets consists of cases, which are the entities you analyze. Cases are described by their variables, which represent the attributes of the entities. The first important question you need to answer when you start a data science project is what exactly is your case. Is this a person, a family, an order? Then you collect all of the knowledge about each case you can get and store this information in the variables.

Database Backup and Restore process in SQL Server – series intro

July 26, 2018 by Prashanth Jayaram

It’s a time to turn the corner from “DBA” to “DBA specialist”. This in-depth guide explores the importance of database backup-and-restore features and skills you’ll need to build good backup-and-restore strategies using the available tools and techniques. Along the way, you’ll pick up some interesting insights and most of the concepts of database backup and restore procedures.

Ready to dive-deep into Backup-and-Restore procedures? Let’s go!

SQL Interview questions on database backups, restores and recovery – Part IV

July 25, 2018 by Prashanth Jayaram

In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database. Also, discuss several T-SQL statements to derive most useful information with reference to data purge, database growth, backup report, restore history and more.

SQL interview questions on database backups, restores and recovery – Part III

July 25, 2018 by Prashanth Jayaram

So far, we’ve discussed a lot about database backup commands. In this article, we’ll discuss more on database restore or database recovery processes. When you initiate a restore process, it undergoes a series of internal stages to restore or recover the data to the specific point of time.

SQL interview questions on database backups, restores and recovery – Part II

July 25, 2018 by Prashanth Jayaram

In this article, we’ll walk through, some of the refined list of SQL Server backup-and-restore, (or recovery) interview Q&A. Also, it includes the high-level overview and links of the “stairway to backup-and-restore series” articles for detailed information. As you read through the lists of Q&A, you’ll learn most of the features and concepts of Backup-and-Restore operations.

Concurrency problems – theory and experimentation in SQL Server

July 24, 2018 by Jefferson Elias

Introduction

Intended audience

This document is intended for application developers and database administrators who are willing to get an overview of comm concurrency problems to which transaction isolation levels respond in the particular case of Microsoft SQL Server.

SQL Server – development practices with referenced views

July 24, 2018 by Timothy Smith

We’ve recently had production failures because our developers changed an important reference. In this case, we had a view which several procedures and views referenced. A developer made a change to the referenced view by removing columns, which caused several procedures and a view that referenced it to fail. We’re considering whether we should stop this practice, or if there are other ways we can prevent changes to an object that’s being referenced by other objects (in our case, a view).

SQL Server Execution Plans types

July 23, 2018 by Ahmad Yaseen

In the previous article, we described, in detail, the different stages that a submitted SQL Server query goes through and how it processed by the SQL Server Relational Engine. The SQL Server Relational Engine generates the Execution Plan and the SQL Server Storage Engine performs the requested data retrieval or modification process. In this article, we will discuss the different types and formats for SQL Server Execution Plans.

Techniques to bulk copy, import and export in SQL Server

July 23, 2018 by Nemanja Popovic

The process of importing or exporting large amounts of data into a SQL Server database, is referred to as bulk import and export respectively. Fortunately, we are provided with a plethora of native tools for managing these tasks incluing

Create a Soccer Heat map with Power BI

July 20, 2018 by Esat Erkec

When I watched the 2018 FIFA World Cup Russia match, at the end of the match I saw a very interesting and stunning soccer field heat map analysis. After this, I decided to create a soccer field heat map in Power BI. So I wrote this article!

Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio

July 19, 2018 by Prashanth Jayaram

In this 18^th article of the series, we will discuss the concepts of database backup-and-restore of SQL Server Docker containers using Azure Data Studio. Before proceeding, you need to have Docker engine installed and Azure Data Studio configured on your host machine.

This article covers the following topics:

Overview of Azure Data Studio (ADS)
How to use Azure Data Studio integrated terminal
Definition of Docker containers
Step by step instructions to initiate backup-and-restore of SQL Server 2017 Docker containers using the Azure Data Studio interface
And more…

Use cases for Query Store in SQL Server

July 18, 2018 by Aamir Syed

Query store was introduced in SQL Server 2016. It is often referred to as a “flight data recorder” for SQL Server. Its main function is that it captures the history of executed queries as well as certain statistics and execution plans. Furthermore, the data is persistent, unlike the plan cache in which the information is cleared upon a server restart or reboot. You can customize, within Query Store, how much and how long the query store can hold the data.

Multiple methods for scheduling a SQL Server backup automatically

July 17, 2018 by Robert Seles

SQL Server backups are an essential part of every good disaster recovery strategy. That is good. But setting up such backups to run effortlessly, is the goal. In this article, we’ll review the types of backups, recommended practices and three different methods for automatically setting up SQL Server backups on a schedule. Note: these solutions can also be used in combination with one another

Useful T-SQL techniques for development in SQL Server

July 17, 2018 by Timothy Smith

When we’re developing solutions, we can sometimes forget useful commands we can use in T-SQL that make it convenient to remove data, eliminate objects, or carefully remove data. We look at three of these commands with a few examples of where we might consider using them in development, or in rare production cases. While they may offer us speed and convenience in some cases, we also look at some situations where they may not be the best tool to use.

Identifying Object Dependencies in SQL Server Management Studio

July 16, 2018 by Ben Richardson

In relational database systems, objects have different types of relationships with each other. Apart from table relationships (such as one to one, one to many and many to many), objects such as stored procedures, views, custom functions also have dependencies on other objects. It is important to understand object dependencies, particularly if you want to update an object that depends upon other objects.

How to monitor and manage Transparent Data Encryption (TDE) in SQL Server

July 13, 2018 by Sreekanth Bandarla

Transparent Data Encryption (TDE) was originally introduced in SQL Server 2008 (Enterprise Edition) with a goal to protect SQL Server data at rest. In other words, the physical data and log files along with the database backup sitting on file system are protected (encrypted).

Query optimization techniques in SQL Server: Database Design and Architecture

July 13, 2018 by Ed Pollack

Description

One of the best ways to optimize performance in a database is to design it right the first time! Making design and architecture decisions based on facts and best practices will reduce technical debt and the number of fixes that you need to implement in the future.

Eating the frog: How to create a daily-deliverable sprint burn down graph in Excel

July 12, 2018 by Brian Lockwood

In the first article I wrote on how to create a SCRUM-based, sprint burn down in Excel, How to create an advanced sprint burn down chart in Excel, I showed how to easily set up a conventional burn-down with effort points as a percent, to create a pretty nice looking burn down chart, just by entering the IDs of each completed PBI on the date that it was finished.

Understanding Backup and Restore operations in SQL Server Docker Containers

July 12, 2018 by Prashanth Jayaram

In this 17^th article of the series (see the full article index at bottom), we will discuss the concepts of database backup-and-restore operations on SQL Server Docker containers. This is certainly the era of containers era and it is buzz right now, so let us understand the importance of backup-and-restore the databases on the Docker containers.

Spatial SQL data types in SQL Server

July 11, 2018 by Nikhilesh Patel

Spatial data type

SQL server furnishes us with the geometry and geography SQL data types for conserving spatial data, which allows us to render graphical data. To be more specific, it is beneficial for creating, analyzing, comparing and retrieving spatial data.

How to create an advanced sprint burn down chart in Excel

July 10, 2018 by Brian Lockwood

SCRUM is a great way to manage all sorts of tasks including sprints, but you don’t have to purchase expensive software packages to burn one. Excel is a great tool for rolling your own sprint burn down graph or you can just take mine. In this article, I’ll walk you through a burn down spreadsheet I created, show you how to modify it and at the end you can download it for yourself and kick the tires.