Samir Behara

SQL Server Management Studio 2016 Productivity Enhancements

October 11, 2016 by

Problem Statement

Developers who work in both SQL and .NET world must have experienced the differences between Visual Studio IDE and SQL Server Management Studio IDE.

Being a full-stack developer myself, I always felt that SSMS lacked a lot of basic features when compared to the functionalities which Visual Studio IDE provided out of the box. With SSMS being tightly coupled with SQL Server Core engine, the IDE had quite a technical debt associated with it over a period of time.

Read more »
Ahmad Yaseen

My journey to PASS Summit 2016 via SQL Shack

October 3, 2016 by

My name is Ahmad Yaseen and I am a senior SQL Server database administrator at Aramex International Company for more than six years, where I started building my experience in the SQL Server administration field. Being a SQL Server Database administrator in such big company is not that easy, as the difficulties that you may face is not only at the beginning. What makes the DBA job special is that it is a very deep job, in which you will learn new things, have to solve new problems, fall in strange situations and be under pressure every day, but it is an interesting job for me.

Read more »
Steve Simon

Reporting in SQL Server – Customize the visual appearance of your reports

October 3, 2016 by

Introduction

As we are nearing the end of the North American summer, I thought that we would take a lighter look at nifty ways of reporting information. In today’s “fireside chat” we have a look at a unique way of displaying our information using charts and line graphs all in one single chart (see below). We shall take things one step further and work with the color fill of the vertical bars to reflect the values that they represent.

Read more »
Ahmad Yaseen

How to filter and block the data access using SQL Server 2016 Row-Level Security

September 28, 2016 by

SQL Server 2016 came with many new features and enhancements for existing ones, that concentrate on the aspect of SQL Server security. One of the new security features introduced in SQL Server 2016 is Row-Level Security. This feature allows us to control access deeply into the rows level in the database table, based on the user executing the query. This is done within the database layer, in complete transparency to the application process, without the need to manage it with complex coding at the application layer.

Read more »
Sifiso Ndlovu

How to filter multidimensional OLAP cubes in SSRS reports

September 28, 2016 by

Ever since the early days of my career, SQL Server Reporting Services (SSRS) has been one of my preferred data visualization tools simply because end users and developers alike use it for free. Although a majority of my SSRS solutions have been based off a relational dataset that uses Transact SQL (T-SQL), I have also produced several reports that used Multidimensional Expressions (MDX) to connect and retrieve data from SQL Server Analysis Services (SSAS) multidimensional OLAP cube. Recently, I found myself having to refactor some of these SSAS based SSRS reports, particularly converting a single value SSAS-populated parameter into a multi-value parameter. In this article, I explore how you can go about making these changes using SSRS query designer’s design and query modes.

Read more »
Kaloyan Kosev

How to migrate an on premise SQL Server database to Azure

September 27, 2016 by

Lately, database administrators often hear the question “have you tried Azure?“ or “can we use Azure for some of our databases?“. For me it was hard to answer these questions at first, but after a while I got to know what is really important behind these situations. In fact, what really matters is assessing the current environment, calculating the most suitable pricing tier and preparing a smooth migration.

Read more »
Minette Steynberg

5 handy Transact-SQL tips you may (or may not) know

September 23, 2016 by

Introduction

Even if you are a SQL veteran, actually especially if you are, you sometimes get really stuck doing something in the old way not realizing that in newer versions of SQL Server some handy functions have been added that can make your life easier.

In this article, I will mention a couple of these which appeared in SQL Server 2008 or later versions or might have even been there before but most people just didn’t know about.

Read more »
Ahmad Yaseen

How to track the history of data changes using SQL Server 2016 System-Versioned Temporal Tables

September 23, 2016 by

SQL Server 2016 introduces a new type of table that is designed to keep the full history of data changes, where row validity is managed by the system. This new table type is called a System-Versioned Temporal Table. In earlier SQL Server versions, user tables would enable you to hold only the most recent copy of the row, without being able to query the value before the UPDATE or DELETE operations. Using a Temporal Table, you will be able to query the recent state of the row as usual, in addition to the ability to query the full history of that row, which is fully managed by the SQL Server Engine, as you can’t define the rows validity period explicitly.

Read more »
Ahmad Yaseen

Database checkpoints – Enhancements in SQL Server 2016

September 21, 2016 by

When a new row is inserted or an existing one is updated in your database, the SQL Server Database Engine will keep that change in the buffer pool in the memory first, without applying each change directly to the database files for IO performance reasons. These data pages located in the buffer pool and not reflected yet to the database files are called Dirty Pages. The SQL Server Database Engine uses a special kind of processes to write these dirty pages to the data and log database files periodically. These processes are called Checkpoints. The Checkpoint creates a mark that is used by the SQL Server Database Engine to redo any transaction that is committed, written to the database transaction log file without reflecting the data changes to the data files due to an unexpected shutdown or crash. Also, this recovery point that is created by the Checkpoint will be used to roll back any data changes associated with uncommitted transaction, by reversing the operation written in the transaction log file. In this way the SQL Server Engine will guarantee the database consistency. The time that is taken by the SQL Server Database Engine to redo and undo the transactions is called the Recovery Time. All information about the Checkpoints will be written to the database boot page to identify till which point the database files are synchronized with the buffer pool when the system wakes up after crash or shutdown.

Read more »
Ed Pollack

Planning a SQL Server conference – Diving into the details

September 19, 2016 by

Many of us have been given the opportunity to run or help plan a SQL Saturday or another SQL Server event. This conclusion will dig much deeper into the nitty-gritty, such as planning food and budgeting. No two events are the same, and as such, not everything here will be the same for you. Consider these experiences a tool and list of ideas to pull from when deciding how to structure, divide, and conquer your conference!

Read more »
Sifiso Ndlovu

How to stop a runaway SSIS package

September 13, 2016 by

There are many reasons for terminating a long running SSIS Package. Picture a scenario whereby an inexperienced DBA/developer accidentally kicks-off a monthly job instead of a daily job, inadvertently impacting SQL Server performance. Whatever the reasons, you are more than likely to encounter a situation in which you have to temporary stop a long running process.

Read more »