Microsoft SQL Server 2016 introduces several significant new features, and enhances some existing ones in reporting services. We have summarised these new features in the previous article. We’ve also explored how to configure the SQL Server Mobile reports in my earlier article.
Read more »SQL Server Management Studio 2016 Productivity Enhancements
October 11, 2016Problem 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 »My journey to PASS Summit 2016 via SQL Shack
October 3, 2016My 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 »Reporting in SQL Server – Customize the visual appearance of your reports
October 3, 2016Introduction
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 »How to filter and block the data access using SQL Server 2016 Row-Level Security
September 28, 2016SQL 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 »How to filter multidimensional OLAP cubes in SSRS reports
September 28, 2016Ever 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 »How to migrate an on premise SQL Server database to Azure
September 27, 2016Lately, 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 »5 handy Transact-SQL tips you may (or may not) know
September 23, 2016Introduction
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 »
How to track the history of data changes using SQL Server 2016 System-Versioned Temporal Tables
September 23, 2016SQL 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 »How to import a sample bacpac file to an Azure SQL Database
September 22, 2016Introduction
In SQL Server 2000, the sample databases were pubs and northwind. They contained sample information to learn SQL Server.
Read more »How to create a simple SSRS Report using a SSAS Tabular model database
September 22, 2016In this article, we will show how to create a simple SQL Server Reporting Services report from a SSAS Tabular model database as a data source, using DAX query, and then deploy it.
Read more »How to configure reporting services (SSRS) for Native mode
September 21, 2016In SQL Server Reporting Services Native mode, a report server has a role of a standalone application server, that provides all viewing, processing, delivery, and management of reports and report models. This is the default mode for SSRS instances.
Read more »Database checkpoints – Enhancements in SQL Server 2016
September 21, 2016When 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 »How-to: SQL Server file-snapshot backups in Azure
September 20, 2016After receiving new additions to backup and restore capabilities of SQL Servers like file and page restores, I thought that nothing will surprise me anymore, but well, here it is; SQL Server file-snapshot backups in Azure.
Read more »How to create a new Git repository on Bitbucket and review committed objects
September 20, 2016For the purpose of this article, Bitbucket is chosen as a Git hosting service. In order to prepare the “communication” between Git hosting service and your application, the following steps need to be performed:
Read more »Planning a SQL Server conference – Diving into the details
September 19, 2016Many 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 »How to query a SSAS Tabular model database with MDX expressions
September 16, 2016Retrieving information from a Tabular database is easy – just query it with some basic DAX functions! In this article, we will learn how to query a Tabular model with some of the MDX expressions, examine the results from DAX querying and analyze data from the tables.
Read more »How to compare query execution plans in SQL Server 2016
September 15, 2016SQL Server 2016 provides great enhancement capability features for troubleshooting purposes. Some of the important features are:
Read more »Planning a SQL Server conference – Introduction
September 15, 2016Many of us have been given the opportunity to run or help plan a SQL Saturday or another SQL Server event. This is an intro to everything I’ve experienced and learned over the past 7 years planning, attending, and volunteering at a variety of SQL Server conferences.
Read more »
How to stop a runaway SSIS package
September 13, 2016There 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 »How to use the xp_cmdshell extended procedure
September 12, 2016Introduction
The xp_cmdshell is a very powerful extended procedure used to run the command line (cmd). This is very useful to run tasks in the operative system like copying files, create folders, share folders, etc. using T-SQL.
In this new article, we will show some useful examples about how to use it.
Read more »How to create a SQL Server Reporting Services (SSRS) report
September 9, 2016SQL Server Reporting Services (SSRS) is part of Microsoft SQL Server services since version 2000. If you are an ordinary user with a basic knowledge of SQL, this article will help you in creating your first SSRS report.
Read more »How to perform backup and restore operations on SQL Server stretch databases
September 7, 2016The Stretch database is one of the latest and most-exciting features that has been released with SQL Server 2016. In the previous article, we examined the concept behind it, the steps to implement it, monitor it and how it can be disabled. As a natural follow-up, we will explore the backup and restore topic when it comes to those kind of databases.
Read more »How to clean Master Data Services data using Data Quality Services in SQL Server
September 6, 2016Introduction
A few weeks back, I was approached by a client who lives in Cape Town, South Africa. The data that he wanted to insert into Master data services was far from clean and he was wondering if there was any way have a positive influence on the data quality.
Read more »How to create and configure SQL Server mobile reports
September 2, 2016Microsoft has recently launched SQL Server 2016 with lots of new features like row level security, always encrypted, stretch database, PolyBase, R Services, hybrid cloud capabilities, Dynamic Data Masking, query store, etc. There is a rich set of business intelligence, security features, and high availability solutions.
Read more »