Ahmad Yaseen

SQL Server 2016 Memory-Optimized Tables – The Checkpoint operation

June 21, 2017 by

The SQL Server Database Engine stores data changes in the buffer pool, in memory, before applying it to the database files, for I/O performance reasons. After that, a special kind of background process, called Checkpoint, will write all of these not reflected pages, also known as Dirty Pages, to the database data and log files periodically.

Read more »
Prashanth Jayaram

How to use Python in SQL Server 2017 to obtain advanced data analytics

June 20, 2017 by

On the 19th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last few years, made great strides in accelerating the pace of innovation to enable businesses to meet the demands of a dynamic marketplace and harness the incredible power of data—more securely and faster than ever before.

Read more »
Minette Steynberg

How to create a chatbot with Azure in 5 minutes

June 19, 2017 by

Introduction

As we enter an era of artificial intelligence and serverless computing, the tools to help us create perceived intelligent programs have become so advanced and intuitive that the effort in building a fully functional Q and A chat bot has become almost negligible.

In this article, I will show you how to do just that.

Read more »
Nikola Dimitrijevic

All about locking in SQL Server

June 16, 2017 by

Locking is essential to successful SQL Server transactions processing and it is designed to allow SQL Server to work seamlessly in a multi-user environment. Locking is the way that SQL Server manages transaction concurrency. Essentially, locks are in-memory structures which have owners, types, and the hash of the resource that it should protect. A lock as an in-memory structure is 96 bytes in size.

Read more »
Minette Steynberg

Reporting in SQL Server – Power BI Report Server

June 13, 2017 by

Power BI is a self-service business intelligence tool from Microsoft which has been steadily gaining momentum in the last couple of months. One of the well-known disadvantages of Power BI is that it is basically cloud only. A lot of companies are not yet at the point where they feel comfortable having their data in the cloud or are premise bound for some other reasons such as data-sensitivity, data-sovereignty or compliance.

Read more »
Gerald Britton

How to track SQL Server database space usage with built-in functions and DMVs

June 12, 2017 by

Introduction

In the previous article in this series on dynamic management views in SQL Server, we used DMV’s to discover some basic information about databases on an instance you are connected to, along with some ideas about getting basic file system and security info. I promised to dive into security next but first I’d like to explore a topic that is currently quite critical for the company I work for today: tracking space usage.

Read more »
Daniel Tikvicki

How to monitor total SQL Server indexes size

June 12, 2017 by

SQL Server indexes overview

Just like a book index, SQL Server index has a similar purpose, to provide faster searching, gathering and sorting of information from a database, without need to go through all of the objects over and over. Instead, an index provides the gateway between the table rows and query engine, in order to achieve better both database and query performance.

Read more »
Marko Zivkovic
C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\7.png

How to create and configure a linked server in SQL Server Management Studio

June 9, 2017 by

Linked servers allow submitting a T-SQL statement on a SQL Server instance, which returns data from other SQL Server instances. A linked server allows joining data from several SQL Server instances using a single T-SQL statement when data exists on multiple databases on different SQL instances. By using a linked server to retrieve data from several SQL instances, the only thing that should be done is to connect to one SQL instance.

Read more »
Prashanth Jayaram

Top SQL string functions in SQL Server 2017

June 8, 2017 by

SQL Server 2017 has been in the talk for its many features that simplify a developer’s life. With the previous versions, developers had to write T-SQL, or user-defined functions using temporary tables, and build complex logic, just for string manipulation. In almost every database I work with, I see many user-defined functions for string manipulation and string aggregation.

Read more »
Craig Porteous

Considerations for On-premises data in PowerBI

June 7, 2017 by

Whether you’ve been using PowerBI for a while or you’re just getting started with it, chances are you’ll want to source at least some of your data from on-premises systems. Be that SQL server databases, Oracle, SSAS etc. The same process even applies if you’re sourcing from AWS which I’ll go into in more detail below. All of these data sources will require Microsoft’s On-Premises Data Gateway.

Read more »
Sifiso Ndlovu

Understanding the Impact of NOLOCK and WITH NOLOCK Table Hints in SQL Server

June 6, 2017 by

Every once in a while, SQL Server database administrators find themselves in disagreements with their application developer counterparts – particularly when it comes to some of the latter’s Transact SQL (T-SQL) developmental practices. One of my first observations when I joined my current employer is that almost all T-SQL scripts written by application developers uses the NOLOCK table hint. However, from the interactions that I have had with these esteemed developers it doesn’t seem like they understand how the NOLOCK table hint works. Furthermore, although they seem to be aware of a distinction between NOLOCK and the WITH NOLOCK table hint, they again do not seem to comprehend how the two differ from one another. In this article, I explore the internal workings of the NOLOCK table hint and examine the implications of omitting the WITH keyword.

Read more »
Timothy Smith

SQL Server Database Architecture and Audits for Enhanced Security

June 5, 2017 by

This year, digital security has risen as a top concern for many people, especially after the recent security compromises with Cloudflare in February and ransomware in May. Throughout the last two years, we’ve also seen the rise of sim-swapping where a hacker is able to extract a user’s cell phone data and compromise all emails and two-factor authentication. Unfortunately, I expect these security issues to continue and cause compromises for user’s data while negatively impacting companies involved.

Read more »
Thomas LeBlanc

Initial Attributes and Measures in SSAS Multidimensional Cubes

June 2, 2017 by

Connecting to tables in SQL Server Analysis Service (SSAS) databases is easy. Maybe too easy. The learning curve for developing in a Multidimensional Cube is steep, although a Tabular Model has lessened this curve. The power of encapsulating the business logic of aggregations and analytics into an Analysis Service database is too great not to embrace Analysis Services in an enterprise environment.

Read more »
Ahmad Yaseen

Compression and decompression functions in SQL Server 2016

June 2, 2017 by

The concept of data compression is not a new on for SQL Server Database Administrators , as it is was introduced the first time in SQL Server 2008. In that SQL Server version, you were able to configure the compression at the row and page levels on the table, index, indexed view or the partition. The row and page level compression is not the best choice in all cases, as it does not work well on the Binary Large Objects (BLOB) datatypes, such as the videos, images and text documents.

Read more »
Koen Verbeeck

Analysis Services (SSAS) Multidimensional Design Tips – Creating Dimensions

June 1, 2017 by

In the previous article, Analysis Services (SSAS) Multidimensional Design Tips – Data Source View and Cubes, we discussed best practices for SSAS Multidimensional cubes and data source views. In this article, we continue the series with design tips for creating dimensions as the subject. As with the previous article, most tips are suited for SSAS 2008 (most likely 2005 as well) to 2016 and later versions. Analysis Services Tabular is not covered in this article.

Read more »