Karel Coenye

While XML has been suitable as a SQL Server native datatype since SQL 2005, for one reason or the other it’s not often used - with only a very few working examples. This blog post is meant for T-SQL teams looking for a working XML example on SQL Server, including schema binding and XML indexes.

Kohera’s Modern Data platform does not come with a canned data model by default. This makes the framework extremely flexible to adapt to a variety of project workflows, but it also means during the Analysis phase we will need to think about how to structure the data before continuing. A blogpost.

Azure Databricks is a cloud native (Big) Data analytics service, offered as a managed PaaS environment. It’s designed to hide the underlying distributed systems and networking complexity as much as possible from the end user, so you can focus on developing rather than having to stress over infrastructure management.

When I started to jump from DBA to Data Architect, I thought that these roles would diverge further and further until they became two separate roles and functions. The current evolution Microsoft launched proved me wrong though. Enter Azure Synapse Analytics.

Data Lakes are the foundations of the new data platform, enabling companies to represent their data in an uniform and consumable way. In this document, we’ll describe how to setup a Data Lake in such a way that it will become the efficient Data Lake that users are looking for.

As we are nearing the end of a very interesting year, I was mesmerizing about the changes we saw this year. This took me back to what I’d later would call the very beginning of what would become my personal IT-Career, ...

I made a very useful ELT program in python and wanted it do run inside a Databricks cluster. Databricks on Azure fully supports python 3, so I thought I was up for a walk in the park. Trying to import the database connection classes, already gave a small hint of the troubles ahead...

An Azure data warehouse is a real powerhouse. Personally, I consider it to be one of the most performant components that can be used and build into the Lambda architecture. Ludicrous power comes with a drawback of course and that drawback is potential inefficiency.

With Analysis Services, you can mashup and combine data from multiple data sources, define metrics, and secure your data in a single, trusted semantic data model. The data model provides an easier and faster way for your users to browse massive amounts of data with client applications.

One of the most challenging tasks in data mining or machine learning is how to get seasonality into the mix. Therefore I created a construct that can insert the seasonality for the Flemish part of Belgium in SQL. With some minor alterations, this can easily be adapted for the Brussels or Walloon regions.

Moving Azure SQL Datawarehouse to the cloud made it available for the majority of all companies, enabling them to use this workhorse as a powerful engine to drive their analytical needs. Now the "Gen 2" is five times faster than the already blazing fast Gen1.

Are you familiar with the lambda architecture? It actually splits data flows into two components, receiving data centrally and doing as little as possible processing before copying and splitting the data stream into two streams, namely the real time and batch layers.

Data beveiligen is de uitdaging van de eeuw. Er zijn tal van momenten en manieren waarop iemand met slechte bedoelingen aan je gegevens kan komen. Je infrastructuur wordt rechtstreeks aangevallen of gebruikers worden overtuigd om schadelijk software te installeren.

One of the most prominent questions when working with PaaS databases is how to secure them. In the previous blodpost of these defence series, I talked about limiting access to objects through firewalls and encryption. Today I'll discuss protecting and auditing your data. Also, I’ll add some extra resources for a complete overview of the available security features on an Azure SQL Sever Database.

One of the most prominent questions when working with PaaS databases is how to secure them. In these defence series, I will talk about limiting access to objects, protecting your data and auditing. Ok, let’s get started with the first layer. Enjoy!

SQL Server provides custom recommendations by using the Azure SQL Database Advisor, enabling maximal performance. The SQL Database Advisor makes recommendations for creating and dropping indexes, parameterizing queries, and fixing schema issues.

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.

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.

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.

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.

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.

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?

My personal favorite reason to upgrade to SQL2016: StretchDB StretchDb provides cost-effective availability for cold data Stretch warm and cold transactional data dynamically from SQL Server to Microsoft Azure with SQL Server Stretch Database. Unlike typical cold data storage, your data is always online and available to query.

In my role as a consultant, many customers ask me for real reasons why to upgrade their existing SQL platforms to the next iteration of SQL Server. As migrating their existing environments always contain a risk, they are often looking for good reasons to upgrade or to stay at their current build level.

Unfortunately, SQL Server’s management studio doesn’t provide you with a simple way to script your resource pools and resource groups. If you want to do this you’ll first have to script all the groups, and then the pools one by one in the correct order. To recreate them you have to do the same but in a reverse order.

One of the challenges of using the free express editions of SQL Server is the lack of the SQL Agent. This means that creating a functioning maintenance plan is a bit more of a challenge then on a regular SQL Server instance. Luckily for us Ola Hallengren created his maintenance plan in such a way that it can easily be altered to make it usable for SQL express editions.

When deploying to the cloud one of the hardest challenges is to get tempdb to scale in the same way as your virtual server. Why is this, well as cloud machines are scalable and thus can change configurations during their lifecycle the number of tempdb files should be able to scale accordingly, so the amount of tempdb files becomes a dynamic value. Luckily for us, this parameter changes only after a shut down and restart of your virtual machine, so if we had a script that could adjust our tempdb needs every time the configuration changes we could deploy this and be more at ease.

While writing my SQL Server template installer, I ran into a big problem – I had to determine if my server was a core edition or not, based on the Windows edition. The biggest challenge was that it had to be as OS-independent as possible. Our customers run several OSs, so I needed something that could run on Windows 2008 R2, Windows 2012 R2 and be future-proof for Windows 2016.

Wondering where to start with your dinosaur database servers that are still floating around somewhere in your highly state-of-the-art environment? Oh yes, everyone else may have forgotten about them, but these machines still exist. While everybody is happily doing their job, you still need to maintain the old servers. After all, you're the DBA and your job is to keep these machines alive.

Cloud computing is fully mature, so we believe that many of your SQL Server systems could be running more efficiently in the cloud. We are convinced that a cloud model, whether hybrid or full cloud, is in fact the best solution for most of our customers. The only questions that remain are: when are you going to migrate and with which model?

With this post I want to show you how you can make a central management server (CMS) selective for different user accounts so that you can use public and management roles, only showing the servers that are necessary to do your job. This enables you to only maintain one CMS, but show different servers (and even groups) dependent of your role (operator, junior DBA, Sys DBA,…) these roles are only limited by your needs and imagination. First I looked into Row- and Cell-Level Security

The goal of this blogpost is to transfer a dataset from the Adventureworks DB and query it in the same way that you would on your own DWH, but running on a DocumentDB NoSQL database. This blogpost is not intended to compare any RDBMS based data warehousing to Document-based data warehousing. It’s purely to demonstrate that you can actually migrate a dataset from a pure RDBMS data structure and transfer it into a document-based NoSQL Engine and run data warehouse queries.

Today I want to show you another powerful way to use your Central Management Server. The CMS can also be used to execute OPENROWSET queries over your complete SQL Server Farm. Now I can hear you all say: “Why would you want to do that?” and “OPENROWSET, you know that you shouldn’t implement that for queries that have to run frequently!”. And to be honest, in most of the cases you’d be right. But it can come in extremely useful in some situations.

What is better? Using GUIDs or Integer values? This is been an age long (religious) debate and there are advocates in both camps stressing on the disadvantages of the other. Both implementations have their advantages and disadvantages. In this blogpost, we'll see that the ultimate SQL Server answer is also valid for this debate: it all depends!

This post might sound a bit like a game of Cluedo, but it happened on a production SQL Server… The issue showed itself on a hosted production VM with 64GB of Ram, where SQL could allocate memory from 32GB to 55GB. The agreements with the hosting partner clearly mentioned that while the CPU could be overcommitted, memory couldn’t. All my alerts suddenly started to show that the machine was using all the available and became completely unresponsive (I couldn’t even open the Event Viewer to unload the logs). Remote connection with management studio gave me this worrisome error: