Blog

16 Aug 2016 Power BI – Gateways to heaven

Although the Power BI gateways have been around for some time now, they weren't really easy to use. Depending on what you needed to do - refresh your data from the Power BI or use the live connection method - you had to install a different gateway. Next to the annoyance of having to install two separate software packages for your Power BI cloud solution, you would think there is no real issue. Wouldn't it be for the fact that you can't install both packages on the same machine.

09 Aug 2016 Coaching

Data warehousing has been a hot topic for a couple of years now. In the beginning this was especially something for larger organisations with vast amounts of data spread all over the company. But over time we have seen a shift in type of organisations needing data warehouses solutions. Also smaller organisations are seeing the benefits of having data stored centrally and integrated.As we see a shift in type of organisation, we also see a shift in type of project.

02 Aug 2016 Natively Stored Procs and In-memory tables: NextGen loading!

Today I was creating a dummy database and wanted to fill this with a certain amount of data. Since I was going to use this database for demo purposes, I created them with foreign keys, primary keys and some indexes. I found that the way I was loading data was quite slow, so I decided to load the data into my database using Natively Stored Procedures and in-memory tables, since this should be the fastest way. The result really baffled me!

19 Jul 2016 Checking your SQL Server maximum memory settings

One of the SQL Server settings with critical impact that is often misconfigured is the maximum memory setting. SQL Server will attempt to take as much memory as possible and is not always eager to release it. This results in the operating system being forced to start actively using the page file. While SQL Server might seem perfectly happy and show good page life and buffer hit ratios, the operating system might in fact be suffering.

11 Jul 2016 Index maintenance on a VLDB – how to tame the beast

Normal day to day maintenance of large data volumes can be difficult. In a previous post I talked about how to handle DBCC CheckDB on a multi-terabyte database. Today I will tackle another issue I came across while dealing with multi-terabyte databases: index maintenance.

05 Jul 2016 Make your Named Instance look like a Default Instance

Recently I was working for a big international customer who, according to policy installs, Named Instances of SQL Server only. During a huge project deployment, an obstacle occurred: this customer had hardcoded the SQL connection string which pointed to the local Default Instance (even worse '.').

28 Jun 2016 How to check the uncheckable: DBCC on VLDB

One of our customers had problems maintaining some monster databases (VLDB). These have about 2 to 4 TB of data and more than 90,000 tables. Building a good maintenance solution for such databases isn't easy. For most of my clients I use the Ola Hallengren solution, which has proved to be excellent before but it just wasn’t suitable here.

21 Jun 2016 How to get your business ready for the cloud?

Cloud computing is no longer just a buzzword, it is here to stay. Companies should prepare by looking into cloud technology and getting their cloud strategy sorted out. If you look at cloud computing in its most basic form, you could simply call it store stuff on the internet. Although this is partially true, the cloud has much more to offer.

14 Jun 2016 Data warehouse and self-service BI tools: friends or foes?

Looking at the BI marketplace today, a lot of products and vendors are telling us that we don't need data warehouses to create those flashy and easy-to-use reports and dashboards. Although from a technical point of view this statement could be valid, in the real world you are not only using the data warehouse to gather information in one location. In the real world we see that a data warehouse does much more than that.

07 Jun 2016 The dawn of a new era

Since a couple of days SQL Server 2016 is finally available. I can't wait to start working with this new exciting version. However, I'm not going to talk about SQL Server 2016 in this blog. I’m going to discuss the management tool we all use for accessing SQL Server databases, because without it, we would not be able to do all of our hocus pocus. This blog is in honour of our little friend called SQL Server Management Studio.

31 May 2016 Creating the database application

Azure and DevOps have become quite the rage in the information technology world. With the increasing desire for companies to build and deploy code faster, and the need for scalability the DevOps philosophy gets more focus as a path to accomplishing these goals. However, applying DevOps in a cloud environment means a shift in the way IT-Pros have been working over the years.

24 May 2016 Four special members make your data warehousing more universal

Not everybody tends to display information in data warehouses in the same manner. For this reason, we have created four so-called special dimension members to avoid confusion and establish some uniform ground rules.

18 May 2016 Migration gone wrong – fall back scenario

Today I’ll be handling an issue I had when migrating a SQL Server 2000 database to SQL Server 2012. We migrated the database from the old, grumpy, and more importantly, unsupported Windows Server 2003 and SQL Server 2005 instance. All was well, and we had no instant issues when users started working with the application. The migration was seamless, we did not expect any future problems. Even the upgrade advisor did not give us any errors!

10 May 2016 Patch Management

If you would ask me what one of the most forgotten but essential elements of database security and stability is, it would be keeping all of the involved software in the database environment up to date. That might seem trivial, but experience shows that in a lot of environments this is often positioned in “the gray area”. Especially when a customer has a lot of third party vendors bringing their own databases. In this time of exploit kits enabling even the non-technically savvy to launch sophisticated attacks, patch management takes on a new importance. Especially when you consider the fact that getting access to any database, is considered hitting the jackpot by a hacker.

03 May 2016 Trace flags SQL Server’s transformer like Tuning

Just like Windows when you install a SQL Server, it’s more a generalist then a specialist. Trace flags is the road to follow when you as a DBA want to tune your SQL server to look like either a F1 car for OLTP or an Optimus prime monster-truck-like-Australian road-train for DWH queries. Both of them are extremely powerful in their own field, but the key is to use that power correctly.

26 Apr 2016 SQL Server Integration Services – loading Excel files

As BI consultants we regularly get the question to load one or more Excel files. Files tend to have different layouts, almost always consist of multiple worksheets and columns often contain different data types, making it more complex to load an Excel file than it would initially appear. After lots of #$@&%*! I grew tired of being frustrated, so I started looking for a solution that would simplify loading intricate Excel files. Here's what I came up with.

19 Apr 2016 When does StretchDB become cheaper than your local SAN?

This blogpost is an addendum to my previous blog “SQL 2016 - 5 (Real) reasons to upgrade, part 2” where I explain about StretchDB. We know that StretchDB is an awesome feature, but what willit cost and how does it compare with your local SAN storage?

12 Apr 2016 New features in SQL 2016: Row Level Security

As a running up to the new release of SQL 2016 I would like to share some research I did about the new features that will be available. Last time I talked about Dynamic Data Masking. This time I will discuss another very interesting new feature namely Row Level Security. We know Row Level Security on Analysis Service level. But Microsoft now also implemented it on Database Service level.